Prerequisites

• 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.

Setting Up

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

Sample output:

/var/lib/mysql/phpmyadmin/pma__users.ibd
/var/lib/mysql/test/lorem.ibd
...snip...

Then

# 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.

Generate Keys

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 "1;"$(openssl rand -hex 32) > keys
# echo "2;"$(openssl rand -hex 32) >> keys
# echo "3;"$(openssl rand -hex 32) >> keys
# echo "4;"$(openssl rand -hex 32) >> keys

This is how these keys will look like

#  cat keys

Sample output:

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

Paste the following content:

[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 /etc/mysql/mariadb.conf.d/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]>

To decrypt the table: Use the same alter command but set "ENCRYPTION=No"

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.

Encrypting All Tables on Specific Database

If you want to encrypt all tables of a specific database, follow these instructions.

To enable encryption on all tables within a specific database, start by generating the necessary ALTER TABLE commands. This will prepare a list of commands to apply encryption to each table.

Step 1: Switch to the Database

USE your_database_name;

Step 2: Generate Encryption Commands for All Tables

Run the following query to generate the ALTER TABLE statements needed to enable encryption on each table in your chosen database. Replace "your_database_name" with the name of your database.

SELECT GROUP_CONCAT(CONCAT('ALTER TABLE ', table_name, ' ENCRYPTED=\'YES\'') SEPARATOR ';\n')
FROM information_schema.tables
WHERE table_schema = 'your_database_name';

Example Output:

ALTER TABLE table1 ENCRYPTED='YES';
ALTER TABLE table2 ENCRYPTED='YES';
ALTER TABLE table3 ENCRYPTED='YES';
ALTER TABLE table4 ENCRYPTED='YES';
ALTER TABLE table5 ENCRYPTED='YES';

Step 3: Execute the Generated Commands

Once the commands are generated as shown above, copy and execute each ALTER TABLE command to enable encryption on the tables.

Conclusion

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.