LabSrc

Security is often one of the last things IT professionals think about when starting a project. Most of the time tight deadlines and an overworked staff lead to a less than perfect implementation. Security, along with other important things...looking at you Documentation...get pushed to the back burner and sometimes forgotten about altogether. One safeguard that is seldom implemented is database encryption.

Most people don't realize that databases are used everywhere. You use them everyday without even knowing it. In fact you're using one right now just by reading this article. Pretty much every site on the web uses a database in one form or another and while encrypting a site's database may seem trivial or unnecessary, there may be a lot of data residing in that database you don't want accidentally exposed to indivduals, nefarious or otherwise. For example, if your website allows for user registration or stores customer information like most retail sites do, it is imperative that you take steps to secure your site's database. In this guide, I'll be using the latest stable version of MariaDB (10.3) on the latest LTS release of Ubuntu (18.04) to help you take your first step in securing your databases.

How Exposed is My Data?

If you have your server permissioned correctly, it's still relatively hard to get access to your database's data without your server's admin password and/or the mysql admin password. However, if you follow tech news, you know that it seems like every day there's a new exploit that grants access to your server. Putting another barrier in front of unscrupulous people who take advantage of these exploits is just good practice.

If you're curious just how much data is stored in plain text, you should take a look at your SQL's storage directory. On my server I found it in /var/lib/mysql. You'll probably need to run the following commands as root, but you should be able to list all of the the table names as well as look into the tables themselves.

sudo ls /var/lib/mysql/databasename
sudo more /var/lib/mysql/databasename/tablename.ibd

After running the second command, you may see a bunch of white space and gobbledygook...did you know that gobbledygook is in the dictionary?...weird. Anyway, beyond all of that you may also find that there is a lot of information that's stored in plain text. People may be under the assumption that when data is stored in a database that it would be saved in some unreadable format, but unfortunately that isn't the case. Don't fret though. I'm going to help you rectify this issue.

Some Caveats Before We Begin

Before you get all fired up thinking your databases will have Fort Knox level security after this following this guide, let me stress this is just one aspect of database security.

In this guide, I'll be using MariaDB's built-in method of encryption called Data at Rest Encryption. This descriptive title alludes to the fact that only data which is not in transit to a client or user is encrypted. If a user is accessing data from your database, you'll need to rely on other means to encrypt that transfer of data such as SSL, but that's a topic for another time.

Instead, I'll be focusing on how you can use MariaDB's encryption plugin to encrypt your databases. There's two main drawbacks with using this plugin, however. The first is that it doesn't support automatic encryption key rotation and the second is that encryption keys are intended to reside on the same server as the databases. These issues will raise flags with any security expect, but I'll touch on some workarounds for both issues at the end of this guide. I felt it was important to bring these drawbacks to light before going any further.

If you're not opposed to spending a little cheddar, there is an alternative to MariaDB's encryption plugin that doesn't suffer from these issues. It's the Amazon Web Services (AWS) Key Management plugin. As the name suggests, it requires that you have an AWS account to be able to use it. Amazon does offer a free 12-month trial, but you'll need to pay a modest monthly fee depending on your storage usage once the trial expires. While I haven't tried this plugin out personally, I know that it does allow for automatic encryption key rotation while keeping your keys stored on AWS servers. Your keys will now reside in the "cloud" a.k.a. the internet, so you'll have to accept the risks that come with that.

Aside from these two drawbacks, I'd like to touch on the impact encryption can have on performance. For a small database server that only processes a few queries per minute, the impact to performance will be negligible. The access times may take a small hit, but to the average user, the transition would probably be seamless. If, however, you're running a large scale database server that processes thousands of requests per minute and/or has extremely large queries run against it, the performance impact could be big. Hopefully, if you are running a large-scale environment, you have the resources to compensate for this hit, but I urge you to test out encryption in a lab environment before rolling it out into production.

I hope I haven't scared you off, but having a false sense of security can be almost as bad as having no security at all. At the very least, I want this guide to get you thinking about your database security.

Always Start With a Backup

As the heading suggests, never make a major change without taking backups first. You'll want to take backups of all of the databases residing on the server as we'll be turning on encryption by default for the entire MariaDB instance. If a database is tied to a website, you may already have the means to backup the entire site including the database, but that would depend heavily on your setup. In this scenario, we'll pretend that we're working with just databases.

From a terminal on the Database Server, run the following:

mysqldump -u username -p -v --all-databases --single-transaction > alldbbackups.sql

*TIP: You can use this command with MySQL as well as MariaDB.

For the username you'll want to enter the user that has admin rights to every database. You'll be prompted for the password after you enter the command so the password isn't saved in your history.

*NOTE: Make sure to store this backup in a secure location and to delete it once you're sure you have everything working properly.

Concealing Maria

Generating Some Keys

We're first going to create a set of encryption keys which will be used to encrypt our databases.

sudo bash -c 'openssl rand -hex 16 >> /etc/mysql/keys'
sudo bash -c 'openssl rand -hex 16 >> /etc/mysql/keys'
sudo bash -c 'openssl rand -hex 16 >> /etc/mysql/keys'

No need to get your eyes checked. Yes, we're running the same command 3 Times to generate 3 random hex keys which we'll store in a text file within the MariaDB config directory.

We then need to number the keys we just created.

sudo bash -c 'awk '\''{print (NR ";" $0)}'\'' /etc/mysql/keys > /etc/mysql/tempkeys && mv /etc/mysql/tempkeys /etc/mysql/keys'

The keys in your text file should now look something like this.

*NOTE: You are not limited to just three encryption keys. You can have as many encryption keys as you want and are free to rotate to any of them at any time. If you feel the need to regenerate a new key file and are not reusing any of your original keys, make sure to decrypt all of your tables and logs before switching them out.

Encrypting Our Encryption Keys

Leaving our encryption keys in plain text would make it too easy for someone to decrypt our encrypted databases so we're going to get a little crazy. Put on your tinfoil hats because we're going to encrypt our encryption keys.

 sudo openssl enc -aes-256-cbc -md sha1 -k yourpassword -in /etc/mysql/keys -out /etc/mysql/keys.enc && sudo rm /etc/mysql/keys

Make sure to change yourpassword in the above command to a unique, hard to guess password. None of this Password123 business. You might even want to use a random password generator in this instance.

*NOTE: Please note there is a leading space in front of sudo which will prevent this command from being saved in your shell's history. This little trick works by default in Ubuntu, but if you're on another distro you should test it out before running it.

*NOTE: For those eagle-eyed, extremely sharp individuals who noticed that the above command uses sha1 to salt the keys instead of sha256, it's because MariaDB doesn't seem able to decrypt keys using that method. I've tried multiple times and MariaDB fails to start when sha256 salted keys are used.

Along with encrypting our encryption keys, we've also deleted the original text file that contained our unencrypted keys as it's no longer needed. You can always decrypt your keys as long as you have your password which we're now going to store our password in a hidden text file.

*TIP: If you ever need to decrypt your keys, you can do so by running sudo openssl enc -aes-256-cbc -md sha1 -d -in /etc/mysql/keys.enc -out /etc/mysql/decrypted-keys.txt. You'll be prompted to enter the password we'll be storing in /etc/mysql/.keypass.

sudo nano /etc/mysql/.keypass

Run this command and type in the password you chose for the previous command into the first line of this empty text file and save it. It's important that you don't add in any other text or comments into this text file. It should contain only the password. This file will be used by MariaDB to automatically decrypt the text file that houses our encryption keys.

Lastly we're going to change the permissions on both of our files to keep curious eyes away. We need to change the owner of the file to the user that MariaDB is running as. On Ubuntu, the user is mysql.

sudo chown mysql:root /etc/mysql/.keypass /etc/mysql/keys.enc
sudo chmod 600 /etc/mysql/.keypass /etc/mysql/keys.enc

*TIP: If you want to find out what user MariaDB is running as, you can run ps aux | grep mysql to find out.

Letting Maria in on the Secret

We've got our encrypted encryption keys ready to go, so now it's time to edit the mariadb.cnf config file and enable encryption.

sudo nano /etc/mysql/mariadb.cnf

Your mariadb.cnf file may look drastically different from mine, but the main thing is that we add these encryption settings under the [mysqld] section. It should look something like this.

Most of the settings are self-explanatory, but a few may leave you scratching your head.

Under the File Key Management section, we're enabling MariaDB's built-in encryption plugin, pointing it at our encryption keys & the encryption key password file and choosing our encryption method, aes_ctr.

In the InnoDB/XtraDB Encryption Options section, we select the first of our three encryption keys as the default key and enable encryption for all tables and transaction logs within our MariaDB instance. The innodb_encryption_threads option tells MariaDB how many CPU threads it can use to handle the encryption and decryption process. This setting will depend heavily on your server's CPU core count, core speeds, memory as well as the size and usage of your databases. A good jumping off point is setting the thread count to one per CPU core and adjusting as needed. You may have also noticed that the section is labeled InnoDB/XtraDB Encryption Options. This was done purposely because these settings apply to both of MariaDB's storage engines, XtraDB and the default, InnoDB.

The Aria Encryption Setup section won't apply to most users. It's more of a future proof option. Starting with MariaDB 10.4, system tables will be using the Aria storage engine in favor of InnoDB. This setting will ensure those tables are encrypted.

The last section enables encryption for MariaDB's temp files & tables as well as binary logs which contain a history of all writes to the database. If you are replicating databases between multiple servers, enabling this option could potentially cause issues. Please have a tested plan in place before enabling any of these options if you have replication enabled.

Bounce Maria

The only thing left to do is to restart our MariaDB service and pray everything works.

sudo systemctl restart mariadb

If MariaDB doesn't restart, don't freak out yet. First check the status of the service and Google any errors you see.

sudo systemctl -l status mariadb

The first time I tested out encrypting my databases, I ran into a permissions issue where the user MariaDB runs as couldn't access the encryption keys.

Checking Your Work

Once you have MariaDB running, it's time to check if our databases are encrypting. Start by logging into the mariadb console.

mysql -u root -p

After entering your password, run the following command to check the status of your tables.

SELECT * FROM information_schema.innodb_tablespaces_encryption;

*TIP: If your terminal is too narrow, add a \G to the end of the above the command to get a more compact and readable output.

If you see the ENCRYPTION_SCHEME and the CURRENT_KEY_ID set to "1", your table has successfully encrypted. If any of your tables have their ENCRYPTION_SCHEME set to "0", check the ROTATING_OR_FLUSHING status. If it's set to "1", that means that the table is still being encrypted. Give it some time and eventually you will see the ENCRYPTION_SCHEME change to "1".

If you'd rather verify the encryption with your own eyes. Re-run the commands from the beginning of this guide to check if your tables are now encrypted.

sudo ls /var/lib/mysql/databasename
sudo more /var/lib/mysql/databasename/tablename.ibd

You shouldn't see any plain text in your tables anymore.

Overcoming Maria's Shortcomings

Rotating Keys

In the caveats section, I mentioned two drawbacks of using MariaDB's built-in encryption plugin. To get around the first, which is not being able to automatically rotate encryption keys, I'll show you how to manually rotate them. This has to be done on a per table basis which is a huge pain in the butt, but I'm working on a script which will automate this process. I'll update this post with a link when it's completed.

*UPDATE: Automated Key Rotation Script Completed - I wrote a script which will create a stored procedure within your MariaDB instance which will automatically rotate the keys of all encrypted tables. You can either choose to rotate all tables to the same key or have every table increment to the next available key. Tables already using the highest number key will rollover to the first key. The best part is you can also create an event to run the stored procedure on a schedule with no user interaction needed.
I chose this method over say a bash script as there's currently no entirely secure way to store MariaDB credentials for automated processes. You can find more info as well as the script at my Github Repository

We'll first need to bring up a list of all of the tables within your MariaDB instance.

After logging into your MariaDB console, run the following to list all of your tables

SELECT table_schema, table_name FROM information_schema.tables;

Find the database and table you'd like to rotate encryption keys on and run the the following within the MariaDB console

ALTER TABLE databasename.tablename ENCRYPTION_KEY_ID=2;

You can check if the key rotation was successful by running a slightly modified version of the command to check all encryption statuses.

SELECT * FROM information_schema.innodb_tablespaces_encryption WHERE NAME='databasename/tablename'\G;

When the key rotation has completed, you should see CURRENT_KEY_ID: 2. This means the table is now using the second of our three encryption keys to encrypt the table. If you have a fairly large table, its possible the table is still being rotated to the new key in which case you'll instead see ROTATING_OR_FLUSHING: 1. When this changes to "0", you key id will change to "2".

Where Did I Put My Keys?

The second drawback, which is storing the encryption keys on the same server as your databases, is not so easy to get around. The first option you have is to store your encryption keys on another server. You'll need to mount a share from your remote server to your database server via SSH or SAMBA. Utilizing either SSHFS or cifs-utils, add an entry to your fstab file to have the share automatically mount at boot. You'll also need to make sure that the shares are mounted before MariaDB starts up. You can achieve this by making the remote mount service, remote-fs a dependency of the MariaDB service. Of course the issue with this method is that your other server must be running while your database server boots up or during any key rotation.

*NOTE: If anyone is interested in an indepth guide on how to automatically mount remote shares, comment down below and I'll do a future writeup on it.

The other way to get your keys off of the database server is to store them on a USB thumb drive. This is much simpler than setting up remote shares, but has the drawback of having to remember to plug in the drive during bootup or key rotation and unplugging the drive and storing it somewhere secure when done. If you forget and just leave the drive plugged in all the time, it's the same as storing the keys directly on the database server which defeats the purpose.

Wrapping Things Up

I hope you were able to follow along without dozing off and were able to takeaway some Srcbits of information. It seems like I always think a writeup is going to be short and sweet and it really ends up being long and bitter. If anyone has questions or insights or has a better way to encrypt databases, feel free to commment down below. Thanks and have a great 2019!