Data encryption at rest in Oracle MySQL 5.7

I’ve previously evaluated MariaDB’s 10.1 implementation of data encryption at rest, and recently did the same for Oracle’s implementation in their MySQL 5.7.

Enabling Tablespace Encryption in MySQL 5.7

First, here’s a walkthrough of enabling encryption for MySQL 5.7:

1. Install the Keyring Plugin

1a. Add the following to the [mysqld] section of /etc/my.cnf: [Gist Link Provided in Content]

1b. Restart the server:

service mysqld restart 

1c. Verify the status

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

2. Ensure innodb_file_per_table is Enabled

2a. Check current status:

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

2b. If OFF, add the following to the [mysqld] section of /etc/my.cnf, restart, and alter each existing table to move it to its own tablespace:

innodb_file_per_table=ON 

Get a list of available InnoDB tables:

mysql> select table_schema, table_name, engine from information_schema.tables where engine='innodb' and table_schema not in ('information_schema'); 

Run ALTER ... ENGINE=INNODB on each of the above tables

mysql> ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] ENGINE=INNODB; 

Testing and Verification Walkthrough

Next, I walked through some practical testing to see how the encryption behaves under standard conditions.

1. Creating and Inspecting Sample Data

Create some data using mysqlslap:

[root@localhost ~]# mysqlslap --concurrency=50 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto-generate-sql-write-number=10000 --no-drop 

Observe that the mysqlslap.t1 table is not automatically encrypted. Unlike MariaDB’s implementation, there is no option to encrypt tables by default.

Verify via the MySQL client:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%'; Empty set (0.05 sec) 

Verify via the command line (inspecting the .ibd file)

[root@localhost ~]# xxd /var/lib/mysql/mysqlslap/t1.ibd | grep -v "0000 0000" | less ... 0010dc0: 5967 4b30 7530 7942 4266 664e 6666 3143  YgK0u0yBBffNff1C ... 

Insert some identifiable, sensitive data into the table:

mysql> insert into mysqlslap.t1 values (1,2,"private","sensitive","data"); mysql> select * from mysqlslap.t1 where charcol2="sensitive"; 

Observe this data via the command line:

[root@localhost ~]# xxd /var/lib/mysql/mysqlslap/t1.ibd | grep -v "0000 0000" | less ... 04fa290: 0002 7072 6976 6174 6573 656e 7369 7469  ..privatesensiti ... 

2. Applying and Verifying Encryption

Encrypt the mysqlslap.t1 table:

mysql> alter table mysqlslap.t1 encryption='Y'; Query OK, 10300 rows affected (0.31 sec) 

Verify the table is now encrypted via the MySQL client:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%'; +--------------+------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | +--------------+------------+----------------+ | mysqlslap    | t1         | ENCRYPTION="Y" | +--------------+------------+----------------+ 

Inspect the file snippet via xxd to confirm the text is no longer readable:

[root@localhost ~]# xxd /var/lib/mysql/mysqlslap/t1.ibd | grep "private" [root@localhost ~]# 

3. Identifying Residual Vulnerabilities in Logs

Observe that the redo log is not encrypted:

[root@localhost ~]# xxd /var/lib/mysql/ib_logfile0 | less ... 23c6940: 6976 6174 6573 656e 7369 7469 7665 6461  ivatesensitiveda ... 

This is expected because the documentation reports that encryption of files outside the tablespace is not supported: "Tablespace encryption only applies to data in the tablespace. Data is not encrypted in the redo log, undo log, or binary log."

Final Conclusions

In my testing of MariaDB’s implementation of data encryption at rest, I found that there were still places on the file system where a bad actor could view sensitive data. I’ve found the same in this test of Oracle’s implementation. Both leave data exposed in log files surrounding the tablespace files.

Bonus: Portability Challenges with mysqldump

During this testing, the table definition caught my eye:

mysql> show create table mysqlslap.t1\G ... ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y' 

As discussed in MDEV-9571, the MariaDB implementation does not include the encrypted=yes information in the table definition when tables are implicitly encrypted. I was curious what would happen if I did a mysqldump of this encrypted table and attempted to restore it to a non-encrypted server.

Portability Test Results

Dumping the table:

[root@localhost ~]# mysqldump mysqlslap t1 > mysqlslap_t1_dump 

As expected, the ENCRYPTION='Y' definition makes the dump less portable. Attempting to restore it on a slightly older 5.7 version (5.7.8-rc) or a different fork like MariaDB (10.1.12) results in errors:

Error on MySQL 5.7.8-rc:

Error on MariaDB: ERROR 1911 (HY000) at line 25: Unknown option 'ENCRYPTION'

This doesn’t have anything to do with the encrypted state of the data in the table, just the table definition itself. While I like that encryption shows up in the definition for visibility, the fix might be for mysqldump to strip this flag when writing the dump file to ensure compatibility across environments.

MySQL Database Consulting Services

Ready to optimize your MySQL Database for the future?