How to enable Encryption for MariaDB
Last updated: December 14th 2020
By default, MariaDB stores table data in plain text inside “*.ibd” files on your server. Anyone with read access to the directory can obtain and read tables in your database which might be a security concern.
MariaDB versions greater than 10.1 have a lot of great security features including the support for data encryption, which can be enabled in a few steps. In this article, we’ll explain how you can encrypt your MariaDB databases and tables to avoid the risk of data theft.
- A fresh Webdock cloud Ubuntu instance.
- You have shell (SSH) access to your VPS.
Please note: If you are a Webdock user, you can simply run the "Enable MariaDB Encryption" script (find it in the Script Library) which performs all the below actions as well as forces encryption for new DB tables.
As mentioned in the introduction, MariaDB doesn’t use table encryption by default. So anyone can read plaintext data stored in them. MySQL tables are stored in *.ibd file formats and can be read using the “strings” command.
These files are usually located in “/var/lib/mysql/” directory but if not, they can be found using the “find” command, and then parsed to “strings” command to retrieve clean plaintext data.
# find / -name *.ibd 2>/dev/null /var/lib/mysql/phpmyadmin/pma__users.ibd /var/lib/mysql/test/lorem.ibd ...snip...
# strings /var/lib/mysql/test/lorem.ibd | head -n 20 OR # find / -name *.ibd 2>/dev/null | xargs strings
You’ll get plaintext table data shown in the output.
In order to enable encryption in MariaDB, you’ll first need to generate encrypted keys that’ll be used in encryption.
Generate random 4-5 HEX strings using openssl utility, starting with the line number and a semicolon “;”.
# sudo su # mkdir -p /etc/mysql/encryption # cd /etc/mysql/encryption # echo -n "1;"$(openssl rand -hex 32) > keys 1;d29662dbd8b7bb58b591ae4fc3d3c8d1045dc1e42260b961e09abfc577c57cd3 # echo -n "2;"$(openssl rand -hex 32) >> keys # echo -n "3;"$(openssl rand -hex 32) >> keys # echo -n "4;"$(openssl rand -hex 32) >> keys
This is how these keys will look like
# cat keys 1;d87bd3a4eddbf6ca7312b35f49930833507a7e3470a65a5c0e99525815025afe 2;e916611f0e7d091c33913f3bebd70f475b40dd7fe2f7f63d384ebf4a74181ecf 3;3d69be819dfcf68eb0333651d4d8e88592b56bff59f5e85c774e61129b3b5138 4;e7b0144fa62372278ac25218eb52c61ff8a993c25ec9d2a9edca3aa0355ae0b8
Now, encrypt these keys with a long random password. You can either use your own personal password or you can generate one using openssl.
# openssl rand -hex 128 > password_file
Now encrypt “keys” file using this long random password.
# openssl enc -aes-256-cbc -md sha1 -pass file:password_file -in keys -out keys.enc
The above command will create an encrypted key file “keys.enc” which MariaDB will use to encrypt tables.
Updating MariaDB Configuration
MariaDB uses the “file_key_management” plugin to encrypt tables. We’ll specify the password and key in the database configuration file. MariaDB configuration files are usually located in “/etc/mysql”, by default it reads all “.cnf” files located in “/etc/mysql/mariadb.conf.d”.
You can add the following lines in “/etc/mysql/my.cnf” or create a new configuration file.
# sudo vim /etc/mysql/mariadb.conf.d/encryption.cnf [mariadb] # File Key Management plugin_load_add = file_key_management file_key_management_filename = /etc/mysql/encryption/keys.enc file_key_management_filekey = FILE:/etc/mysql/encryption/password_file file_key_management_encryption_algorithm = aes_cbc # InnoDB/XtraDB Encryption Setup innodb_default_encryption_key_id = 1 innodb_encrypt_tables = ON innodb_encrypt_log = ON innodb_encryption_threads = 4 # Aria Encryption Setup aria_encrypt_tables = ON # Temp & Log Encryption encrypt-tmp-disk-tables = 1 encrypt-tmp-files = 1 encrypt_binlog = ON
After that, change ownership and permission of these sensitive files so no other user can read it.
# cd /etc/mysql/ # sudo chown -R mysql:root ./encryption # sudo chmod 500 /etc/mysql/encryption/
# cd ./encryption # chmod 400 keys.enc password_file # chmod 644 encryption.cnf
Restart the database service.
# sudo service mysql restart OR # sudo service mariadb restart
Encrypting Database Tables
After the configuration has been applied, you can encrypt existing tables in your database using the ‘alter’ command with “ENCRYPTED=YES” option.
# mysql -h 127.0.0.1 -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 10.1.10-MariaDB Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.001 sec) MariaDB [(none)]> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | lorem | +----------------+ 1 row in set (0.001 sec) MariaDB [test]> alter table lorem ENCRYPTED=Yes; Query OK, 0 rows affected (0.002 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]>
Verify that encryption has been applied by running “strings” command again on the database file. You’ll get unreadable encrypted data in the output
# strings /var/lib/mysql/test/lorem.ibd | head -n 20
While creating new tables, you can supply the extra ‘ENGINE=InnoDB ENCRYPTED=YES’ option if you want the table to be encrypted.
MariaDB [test]> CREATE TABLE ipsum ( UserId INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(30) NOT NULL, LastName VARCHAR(30) NOT NULL, ) ENGINE=InnoDB ENCRYPTED=YES;
Or if you don’t want users to create tables without encryption, add this extra line in your encryption configuration file, so that all new tables are encrypted by default.
# sudo vim /etc/mysql/mariadb.conf.d/encryption.cnf ...snip... innodb-encrypt-tables=FORCE ...snip...
You can verify the above steps by running the “strings” command on “.*ibd” files to check whether the encryption is working or not.
In this article, we learned how we can configure MariaDB to encrypt our database tables for enhanced security. If you want to get more information, please refer to their official documentation here.
Thanks go out to community members Sorin and Usama for the research and writing of this article.