Data encryption at rest in Oracle MySQL 5.7
Tags:
Azure,
Microsoft Sql Server,
Technical Track,
Cloud,
Data Discovery,
Azure Sql Db,
Data Classification,
Microsoft Azure Sql Database
I've previously evaluated MariaDB's 10.1 implementation of data encryption at rest (
https://blog.pythian.com/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: https://gist.github.com/parham-pythian/a625bf472456da4774dec424dbbb4932 1b. Restart the server: [code lang="sql"]... service mysqld restart[/code] 1c. Verify: [code lang="sql"]... mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%'; +--------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------+---------------+ | keyring_file | ACTIVE | +--------------+---------------+[/code] 2. Ensure innodb_file_per_table is on. 2a. Check. [code lang="sql"]... mysql> show global variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+[/code] 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: [code lang="sql"] innodb_file_per_table=ON[/code] Get list of available InnoDB tables: [code lang="sql"] mysql>select table_schema, table_name, engine from information_schema.tables where engine='innodb' and table_schema not in ('information_schema');[/code] Run ALTER ... ENGINE=INNODB on each above InnoDB tables: [code lang="sql"] mysql><strong>ALTER</strong> TABLE [TABLE_SCHEMA].[TABLE_NAME] ENGINE=INNODB;[/code]
Next, I walked through some testing. 1. Create some data. [code lang="sql"]... [root@localhost ~]# mysqlslap --concurrency=50 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto-generate-sql-write-number=10000 --no-drop[/code] 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: [code lang="sql"]... mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%'; Empty set (0.05 sec)[/code] 2b. Via the command line: (Install xxd if required.) [code lang="sql"]... [root@localhost ~]# yum install vim-common[/code] [code lang="sql"]... [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 ...[/code] 3. Insert some identifiable data into the table: [code lang="sql"]... 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)[/code] 4. Observe this data via the command line: [code lang="sql"]... [root@localhost ~]# xxd /var/lib/mysql/mysqlslap/t1.ibd | grep -v "0000 0000" | less ... 04fa290: 0002 7072 6976 6174 6573 656e 7369 7469 ..privatesensiti ...[/code] 5. Encrypt the mysqlslap.t1 table: [code lang="sql"]... mysql> <strong>alter</strong> table mysqlslap.t1 encryption='Y'; Query OK, 10300 rows affected (0.31 sec) Records: 10300 Duplicates: 0 Warnings: 0[/code] 6. Observe the mysqlslap.t1 table is now encrypted: 6a. Via the mysql client: [code lang="sql"]... 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" | +--------------+------------+----------------+[/code] 6b. Via the command line: [code lang="sql"]... [root@localhost ~]# xxd /var/lib/mysql/mysqlslap/t1.ibd | grep "private" [root@localhost ~]#[/code] 6c. Observe snippet of the file: [code lang="sql"]... [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 ...[/code] 7. Observe redo log is not encrypted: [code lang="sql"]... [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 ...[/code] 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: [code lang="sql"]... 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)[/code] 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. [code lang="sql"]... [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');[/code] 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: [code lang="sql"]... 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[/code] On a different fork: [code lang="sql"]... 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'[/code] 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.
