I’ve previously evaluated MariaDB’s 10.1 implementation of data encryption at rest (https://www.pythian.com/blog/data-encryption-rest), and recently did the same for Oracle’s implementation (https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html) in their MySQL 5.7.
First, here’s a walkthrough of enabling encryption for MySQL 5.7:
1. Install keyring plugin.
1a. Add the following to the [mysqld] section of /etc/my.cnf:
View the code on Gist.
1b. Restart the server:
... service mysqld restart
1c. Verify:
... 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 on.
2a. Check.
... 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 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 above InnoDB tables:
mysql><strong>ALTER</strong> TABLE [TABLE_SCHEMA].[TABLE_NAME] ENGINE=INNODB;
Next, I walked through some testing.
1. Create some data.
... [root@localhost ~]# mysqlslap --concurrency=50 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto-generate-sql-write-number=10000 --no-drop
2. Observe the mysqlslap.t1 table is not automatically encrypted. Unlike MariaDB’s implementation, there is not an option to encrypt tables by default.
2a. 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)
2b. Via the command line:
(Install xxd if required.)
... [root@localhost ~]# yum install vim-common
... [root@localhost ~]# xxd /var/lib/mysql/mysqlslap/t1.ibd | grep -v "0000 0000" | less ... 0010dc0: 5967 4b30 7530 7942 4266 664e 6666 3143 YgK0u0yBBffNff1C 0010dd0: 5175 6470 3332 536e 7647 5761 3654 6365 Qudp32SnvGWa6Tce 0010de0: 3977 6576 7053 3730 3765 4665 4838 7162 9wevpS707eFeH8qb 0010df0: 3253 5078 4d6c 6439 3137 6a7a 634a 5465 2SPxMld917jzcJTe ...
3. Insert some identifiable data into the table:
... mysql> <strong>insert</strong> into mysqlslap.t1 values (1,2,"private","sensitive","data"); Query OK, 1 row affected (0.01 sec) mysql> select * from mysqlslap.t1 where charcol2="sensitive"; +---------+---------+----------+-----------+----------+ | intcol1 | intcol2 | charcol1 | charcol2 | charcol3 | +---------+---------+----------+-----------+----------+ | 1 | 2 | private | sensitive | data | +---------+---------+----------+-----------+----------+ 1 row in set (0.02 sec)
4. 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 ...
5. Encrypt the mysqlslap.t1 table:
... mysql> <strong>alter</strong> table mysqlslap.t1 encryption='Y'; Query OK, 10300 rows affected (0.31 sec) Records: 10300 Duplicates: 0 Warnings: 0
6. Observe the mysqlslap.t1 table is now encrypted:
6a. 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" | +--------------+------------+----------------+
6b. Via the command line:
... [root@localhost ~]# xxd /var/lib/mysql/mysqlslap/t1.ibd | grep "private" [root@localhost ~]#
6c. Observe snippet of the file:
... [root@localhost ~]# xxd /var/lib/mysql/mysqlslap/t1.ibd | grep -v "0000 0000" | less ... 0004160: 56e4 2930 bbea 167f 7c82 93b4 2fcf 8cc1 V.)0....|.../... 0004170: f443 9d6f 2e1e 9ac2 170a 3b7c 8f38 60bf .C.o......;|.8`. 0004180: 3c75 2a42 0cc9 a79b 4309 cd83 da74 1b06 &lt;u*B....C....t.. 0004190: 3a32 e104 43c5 8dfd f913 0f69 bda6 5e76 :2..C......i..^v ...
7. Observe redo log is not encrypted:
... [root@localhost ~]# xxd /var/lib/mysql/ib_logfile0 | less ... 23c6930: 0000 0144 0110 8000 0001 8000 0002 7072 ...D..........pr 23c6940: 6976 6174 6573 656e 7369 7469 7665 6461 ivatesensitiveda 23c6950: 7461 3723 0000 132e 2f6d 7973 716c 736c ta7#..../mysqlsl ...
This is expected because the documentation (https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html) reports 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.”
Conclusions
I found in my testing of MariaDB’s implementation of data encryption at rest that there were still places on the file system that 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
As a bonus to this walkthrough, during this testing, the table definition caught my eye:
...
mysql> show create table mysqlslap.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`intcol1` int(32) DEFAULT NULL,
`intcol2` int(32) DEFAULT NULL,
`charcol1` varchar(128) DEFAULT NULL,
`charcol2` varchar(128) DEFAULT NULL,
`charcol3` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y'
1 row in set (0.00 sec)
As discussed in https://jira.mariadb.org/browse/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 nonencrypted server. DBAs expect mysqldump to create a portable file to recreate the table definition and data on a different version of mysql. During upgrades, for example, you might expect to use this for rollback.
Here is my test. I first did the dump and looked inside the file.
... [root@localhost ~]# mysqldump mysqlslap t1 > mysqlslap_t1_dump [root@localhost ~]# less mysqlslap_t1_dump ... CREATE TABLE `t1` ( `intcol1` int(32) DEFAULT NULL, `intcol2` int(32) DEFAULT NULL, `charcol1` varchar(128) DEFAULT NULL, `charcol2` varchar(128) DEFAULT NULL, `charcol3` varchar(128) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y'; <strong>INSERT</strong> INTO `t1` VALUES ( ... ,(1,2,'private','sensitive','data');
As expected, that definition makes the dump less portable. The restore from dump is not completed and throws an error (this is not remedied by using –force):
On a slightly older 5.7 version:
... mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.8-rc | +-----------+ [root@centosmysql57 ~]# mysql mysqlslap < mysqlslap_t1_dump ERROR 1064 (42000) at line 25: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENCRYPTION='Y'' at line 7
On a different fork:
... MariaDB [(none)]> select version(); +-----------------+ | version() | +-----------------+ | 10.1.12-MariaDB | +-----------------+ 1 row in set (0.00 sec) [root@maria101 ~]# mysql mysqlslap < mysqlslap_t1_dump 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. I do like the encryption showing up in the table definition, for better visibility of encryption. Maybe the fix is to have mysqldump strip this when writing to the dump file.