Microsoft Azure Microsoft SQL Server

Data encryption at rest in Oracle MySQL 5.7

5 min read

Apr 20, 2016 12:00:00 AM

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>ALTER 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> insert 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> alter 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'; INSERT 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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Data encryption at rest in Oracle MySQL 5.7

Share this

Share this

More resources

How Does Transparent Data Encryption (TDE) Impact Tempdb Databases?

Data encryption at rest

Analytics with Limitless Scale on Microsoft Azure - Part 1

Ready to unlock value from your data?