Here’s just a few notes detailing my investigations into table & tablespace encryption in MariaDB 10.1.3.
First Generate encryption keys
linux> openssl enc -aes-256-cbc -k secretPassword -P -md sha1
Next build your key file from the above output. This should be in the following format..
This will look something like…
Where 1 is the id of the key. I can’t see anything specific in the documentation but presumably you’d just start a new line for each key. Place this into a file called key.txt. It’s worth noting that you must generate the enc file in the directory you will point to in MariaDB. Decryption doesn’t seem to work after they are moved.
linux> openssl enc -aes-256-cbc -md sha1 -k secretPassword2 -in key.txt -out key.enc
Don’t forget to cleanup the server of any unencrypted key files. You may wish to keep copies securely on a different server. Now we need to configure MariaDB
linux> vi /etc/my.cnf.d/server.cnf
Add the following options…
[mariadb-10.1] plugin-load-add=file_key_management_plugin.so file-key-management-plugin encryption_algorithm=aes_cbc file_key_management_plugin_filename=/home/mdb/key.enc file_key_management_plugin_filekey=secretPassword2
Note that the filekey password will be visible to anyone on the server. In MariaDB 10.1.4 we can set it with a file path, i.e. FILE:/path/to/pwd.txt, and it would be a sensible idea to do so for production. Now create and view some data using the table encryption feature…
linux> systemctl restart mysql mariadb> CREATE DATABASE encrypted; maraidb> USE encrypted; mariadb> CREATE TABLE test (id INTEGER NOT NULL PRIMARY KEY, col1 VARCHAR(100)) ENGINE=Innodb PAGE_ENCRYPTION=1; mariadb> INSERT INTO test VALUES (101, ‘Hello, World!’); mariadb> SELECT * FROM test; # data will display mariadb> exit
Lets simulate someone stealing the data files and attempting to access them. We can do this by simply renaming the key file and restarting the MariaDB server…
linux> mv /home/mdb/key.enc /home/mdb/_key.enc linux> systemctl restart mysql linux> mysql -u root -p
mariadb> USE encrypted; mariadb> SELECT * FROM test;
We will receive the following error…
ERROR 1932 (42S02): Table ‘encrypted.test’ doesn’t exist in engine
The error log will also contain lots of messages complaining about missing ibd files, tablespace corruption and so on. Now put the key back…
linux> mv /home/mdb/_key.enc /home/mdb/key.enc linux> systemctl restart mysql linux> mysql -u root -p
The data can be accessed again…
mariadb> USE encryption mariadb> SELECT * FROM test;
To turn off encryption on individual tables with ALTER TABLE…
mariadb> ALTER TABLE test PAGE_ENCRYPTION = 0;
I also had a quick stab at tablespace encryption but ran into a whole series of problems. Lots of the documentation appears to be wrong. For example innodb-encrypt-tables insists it must be set with a value while the documentation shows otherwise. Looks like there’s been further work on encryption for 10.1.4 so I’ll have another go with that.
UPDATE 2015-04-18: I had a play with MariaDB 10.1.4. Various issues found. See the bug report.