Encrypt a MariaDB table

Hi,

This is a short description on how to enable encryption in a MariaDB.

Log in to the MariaDB node using the ssh gateway or Web SSH in the control panel.

Create a keyfile with two rows:

openssl rand -hex 32 >> ./keyfile
openssl rand -hex 32 >> ./keyfile

Add a ID 1 and 2 before each line followed by a ";" so it will look like this

1;6552d3f86135a07138fa24a695f7633edc3d3398682187a14b48307bc33e2715
2;d6ff4f3ec34184182c850a89c920e6a8b72d4eb4924b25e6e7c39c73455b81c0

Generate an encryption key file:

openssl rand -hex 128 > ./keyfile.key

Encrypt the keyfile:

openssl enc -aes-256-cbc -md sha1 -pass file:keyfile.key -in:keyfile -out:keyfile.enc

You can now remove the original keyfile and set permissions on the files.

rm ./keyfile
chmod 600 keyfile.*

Edit /etc/my.cnf and add the following rows in the [mysqld] section:

plugin_load_add = file_key_management
file_key_management_filename = /home/jelastic/keyfile.enc
file_key_management_filekey = FILE:/home/jelastic/keyfile.key
file_key_management_encryption_algorithm = AES_CTR

In MariaDB create an encrypted table:

CREATE TABLE mytablet (i int) ENGINE=InnoDB ENCRYPTED=YES;

Now you can check that you have an encrypted table:

MariaDB [enc]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
+--------------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+--------------+-------------------+----------------+
| enc/mytablet | 1 | 1 |
+--------------+-------------------+----------------+
1 rows in set (0.000 sec)

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.