Data Encryption at Rest in MySQL 5.7

Introduction

Data security is a very important concern in any enterprise solution supporting the Health Insurance Portability and Accountability Act (HIPAA), Sarbanes Oxley, PII (names, driver licenses, financial data, social security number), Payment Card Industry Data Security Standard (PCI DSS) requirements, and several others. While we can find a lot of solutions to protect data in transit (on the wire) such as SSL and related protocols; data at rest (on the file system) has been a real risk concern in MySQL for several solutions in the past. Without data encryption at rest, someone with access to the file system can see the data without any database-level privilege using traditional Unix commands such as strings and xxd.

In order to protect sensitive data, MySQL 5.7 has introduced the capability for encrypting data in the InnoDB engine.

In this article, I’ll explain how to encrypt data using a walkthrough to illustrate the related principles and concepts.

Database-level encryption

Although we have several alternatives for data encryption, such as application-level encryption and filesystem encryption, it can be very expensive to encrypt the whole file system when we only need to protect a part of it; also, it’s not always possible to change the code and develop an application-level encryption mechanism. In these scenarios it is very important to use database-level encryption.

Encrypting data in MySQL 5.7 has the following advantages:

• Strong AES 256 encryption for the InnoDB tables
• Transparent to applications. No application code, schema, or data type changes
• Transparent to DBAs. Keys are not managed by the DBAs.
• Key Management. Keys can be securely stored away from the data and key rotation is very simple.

It’s notable that MySQL 5.7 encrypts only InnoDB tables, leaving the following files exposed: binlogs, redo logs, relay logs on unencrypted slaves, slow log, error log, general log, and audit log.

Another point to consider is to protect the master key inside the keyfile. If the master key is comprised, then the data security mechanism is open for leaks. So the solution is to place a keyfile on a partition that can be unmounted when MySQL starts (a directory mounted only during database startup). It's very important not to lose the keyfile along with the master key, because the data won't be unencrypted.

Demo

Let’s start by allowing encryption on MySQL by adding the following options to the my.cnf configuration file as shown in listing 01.

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring

Listing 01

Next step is to start the service and verify that the plugin is active as shown in listing 02.

mysql> SELECT plugin_name, plugin_status
FROM INFORMATION_SCHEMA.PLUGINS
WHERE plugin_name LIKE 'keyring%';
 
+--------------+---------------+
| plugin_name  | plugin_status |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+

Listing 02

We can check the settings as shown in listing 03.

mysql> show global variables like '%keyring%';
+-------------------+------------------------+
| Variable_name     | Value                  |
+-------------------+------------------------+
| keyring_file_data | /var/lib/mysql/keyring |
+-------------------+------------------------+

Listing 03

In order to encrypt tables in InnoDB, we need to make sure that the innodb_file_per_table option is ON as shown in listing 04.

mysql> show global variables like 'innodb_file_per_table';
 
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

Listing 04

In case the option is OFF, we need to add the following options to the my.cnf configuration file as shown in listing 05 and restart the service.

[mysqld]
innodb_file_per_table=ON

Listing 05

Let’s see the encryption in action. Let’s create a table for storing sensitive data as shown in listing 06.

mysql> CREATE TABLE sample_db.sensitive_data_tb (
 id int primary key auto_increment,
 payload varchar(256)
) engine=innodb;

Listing 06

Let’s add a row to this table as shown in listing 07.

mysql> INSERT INTO sample_db.sensitive_data_tb(payload)
VALUES('Private sensitive data');

Listing 07

Now, let's use the strings command to extract each string of printable characters in the binary data file related to the sensitive_data_tb table as shown in listing 08.

$ strings /var/lib/mysql/sample_db/sensitive_data_tb.ibd
 
infimum
supremum
Private sensitive data

Listing 08

We can also check that the keyfile is empty (there is no master key) as shown in the listing 09.

$ cat /var/lib/mysql/keyring

Listing 09

In order to encrypt the data in the sensitive_data_tb table, we need to alter the table as shown in the listing 10.

mysql> ALTER TABLE sample_db.sensitive_data_tb encryption='Y';

Listing 10

If we try to print the strings inside the sensitive_data_tb table, now we can see that the data is encrypted and it’s not possible to see it from the file system, as shown in listing 11.

$ strings /var/lib/mysql/sample_db/sensitive_data_tb.ibd
 
bbf7aee4-a14a-11e6-8d4c-080027001884
{i<|4w
RMt_

Listing 11

Although we cannot see the data from the file system, we can see the data from MySQL using a traditional SQL statement without anything special on it, as shown in listing 12.

mysql> SELECT * FROM sample_db.sensitive_data_tb;
 
+----+------------------------+
| id | payload                |
+----+------------------------+
|  1 | Private sensitive data |
+----+------------------------+

Listing 12

We can also verify that a master key is generated by MySQL, as shown in listing 13.

$ cat /var/lib/mysql/keyring

Keyring file version:1 XXXX

Listing 13

We can also rotate the master key by executing the alter instance statement as shown in the listing 14.

mysql> alter instance rotate innodb master key;

Listing 14

We can verify this rotation by checking the timestamp of the keyfile as shown in listing 15.

$ ls -al /var/lib/mysql/keyring

Listing 15

Finally, if we want to know the list of encrypted tables, we can do it using the following query, as shown in listing 16.

mysql> SELECT *
FROM information_schema.tables
WHERE create_options LIKE '%ENCRYPTION="Y"%';

Listing 16

Conclusion

In this article, I've explained the principles and concepts related to data encryption at rest on the database level in new versions of MySQL. I’ve also illustrated the ideas through real-world examples. Now you can apply these ideas and this demo to your own solution to protect sensitive data.