With MySQL version 5.7.12 and up, Oracle continues to improve MySQL’s security features by adding MySQL Enterprise Transparent Data Encryption (TDE) for InnoDB tables stored in innodb_file_per_table tablespaces. This feature provides at-rest encryption for physical tablespace data files.
MySQL Enterprise TDE uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDB table is encrypted, a tablespace key is encrypted with the master key and the encrypted value of the tablespace key is stored in the tablespace header. When encrypting tablespace data, InnoDB transparently uses the master encryption key to decrypt the tablespace key and then uses it to encrypt (on write) and decrypt (on read).
The master encryption key is stored in memory on the MySQL keyring. Using a key vault, this key ring is both persisted and protected. The master key nor any of the tablespace keys are ever written to disk in plain text. When the the user first enables TDE, and when the server starts up or when the master key is rotated – the master key is requested and updated from the key vault. Databases and their tablespaces can get large, and had we chosen a single-tiered method, then key rotation would require re-encrypting entire tablespaces. This process is slow, costly and risky. With a two-tiered scheme, the decrypted version of a tablespace key never changes, but the master encryption key may be changed as required. This action is referred to as master-key rotation and it re-encrypts all of the tablespace keys in one atomic operation very rapidly (milliseconds).
There are various key vaults in the market and there’s also a common industry protocol supported by most vaults. The protocol is KMIP and it comes from OASIS. Losing keys (whether by accident, mismanagement, or getting hacked) means you lose your data. To ensure your keys are available, secure, auditable, etc., you will want to use a key-vault management software application such as Oracle Key Vault. Oracle Key Vault enables you to quickly deploy encryption and other security solutions by centrally managing encryption keys not only for MySQL, but across many things, including (but not limited to) Oracle Wallets, Java Keystores, and credential files.
Using the label for TDE requires:
1 – Applications aren’t aware of encryption – it is provided seamlessly and doesn’t require any changes
2 – The data is encrypted at-rest – so the operating system users or other access to the files on the media or the file system can’t read the data (without a key)
3 – The keys are secured, available and protected – you can’t leave the key in the door (or under the mat)
4 – The keys are never written to the filesystem (in plain text)
MySQL does include tablespace encryption with the community version but that is not TDE as it doesn’t meet requirements #3 and #4 – the keys aren’t necessarily secured.
MySQL Enterprise TDE supports the Advanced Encryption Standard (AES) block-based encryption algorithm. It uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption. (from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html)
In this post, I will show you how easy it is to use this new feature to encrypt your InnoDB tables (requires a licensed version of the MySQL Enterprise Edition MySQL 5.7.12 or higher).
Setting up MySQL TDE
Get Oracle Key Vault Credentials
First you will need to set up an account in Oracle Key Vault and create an endpoint (the key storage that keyring_okv plugin will talk to). Once you are done with that, you will have to create a configuration folder for keyring_okv. This folder will contain credentials to log into Oracle Key Vault. This, of course, needs to be stored securely. Go to this link to find information on how to accomplish this:
https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/keyring-okv-plugin.html
As the result you should have a configured endpoint in Oracle Key Vault and the configuration folder which should look like this:
|
1
2
3
4
5
6
7
8
9
10
11
|
# ls -l total 8 -rw-rw-r-- 1 rob rob 427 lip 7 18:49 okvclient.ora drwxrwxr-x 2 rob rob 4096 lip 7 18:49 ssl # ls -l ssl 10:47 $ ls -l ssl total 16 -rw-rw-r-- 1 rob rob 1200 lip 7 18:49 CA.pem -rw-rw-r-- 1 rob rob 1209 lip 7 18:49 cert.pem -rw-rw-r-- 1 rob rob 1027 lip 7 18:49 cert_req.pem -rw-rw-r-- 1 rob rob 1675 lip 7 18:49 key.pem |
From now on we will call this folder okv_enrollment.
Note: If you want to trial Oracle Key Vault it can be downloaded from the Oracle Software Delivery Cloud at https://edelivery.oracle.com/.
Install MySQL Server
Next you will need to install MySQL 5.7.12, and follow the post-installation steps, which include initializing the data directory.
Load keyring_okv plugin
To use the tablespace encryption feature, you will also need to load the keyring plugin (keyring_okv) by adding the following to your configuration file (under the [mysqld] section, let us assume that your path to the Oracle Key Vault folder is /home/user/okv_enrollment):
|
1
2
3
|
[mysqld] early-plugin-load=keyring_okv.so keyring_okv_conf_dir=/home/user/okv_enrollment |
Before you attempt to create an encrypted table, you can check and make sure the plugin is active via this command:
|
1
2
3
4
5
6
7
8
|
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS -> WHERE plugin_name='keyring_okv'; +--------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------+---------------+ | keyring_okv | ACTIVE | +--------------+---------------+ |
Creating encrypted tables
To create a table with encryption, you only need to add ENCRYPTION = ‘Y’ to the CREATE TABLE statement. This example will create a new database and an encrypted table:
|
1
2
3
4
5
6
7
|
CREATE DATABASE scripting; USE scripting; CREATE TABLE `test_encryption` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `server_name` varchar(15) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y'; |
|
1
2
3
4
5
6
7
8
9
10
|
Technical side note (can be skipped :) As this is the first encrypted table we are creating - InnoDB will first ask keyring_okv to generate master key in Oracle Key Vault. From now on this key will be used to encrypt tablespace keys. Next InnoDB will ask Oracle Key Vault to generate random key for encrypting table test_encryption. keyring_okv will use Oracle Key Vault to ensure that this key will get generated with high entropy. This random key will then get encrypted using the master key and stored alongside the encrypted test_encryption table. The next time encrypted table gets created - only the tablespace key will get generated and the master key will get reused (to encrypt new tablespace key). |
This encryption feature only works with InnoDB tables. If you try this with a MyISAM table, you will get an error:
|
1
2
3
4
5
6
|
CREATE TABLE `test_encryption2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `server_name` varchar(15) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y'; ERROR 1031 (HY000): Table storage engine for 'test_encryption2' doesn't have this option |
You may view a list of all of the encrypted tables via this command:
|
1
2
3
4
5
6
7
8
|
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES -> WHERE CREATE_OPTIONS like 'ENCRYPTION="Y"'; +--------------+-----------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | +--------------+-----------------+----------------+ | scripting | test_encryption | ENCRYPTION="Y" | +--------------+-----------------+----------------+ 1 row in set (0.20 sec) |
Encrypting tables with different endpoints
It is possible to use different endpoints with one instance of MySQL. You can start server with (say) endpoint1 and encrypt table test_encryption with it. Then you can restart the server with some other endpoint and use it to encrypt table (for instance) test_more_encryption. However test_encryption will not be accessible. You will see the following error:
|
1 |
mysql> select * from test_encryption; ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded. |
Also please note that, although keyring_okv_conf_dir is settable in runtime, it should be changed with care. Please see Appendix 1, at the bottom of this article, for more information.
Overall using multiple endpoints with one server is not encourage and should be done with extra care.
Encrypting existing tables
To enable encryption for an existing InnoDB table, specify the ENCRYPTION option in an ALTER TABLE statement.
|
1 |
mysql> ALTER TABLE t1 ENCRYPTION='Y'; |
To disable encryption for an InnoDB table, set ENCRYPTION=’N’ using ALTER TABLE.
|
1 |
mysql> ALTER TABLE t1 ENCRYPTION='N'; |
One warning – “Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE … ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITM=INPLACE is not supported.” (from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html)
In other words, it may take some time to encrypt (or decrypt) an already-existing InnoDB table.
Rotating InnoDB master key
For security reasons InnoDB master key should be rotated periodically. This operation creates a new master key in Oracle Key Vault, re-encrypts all the tablespace keys with the new master key and deactivates the old master key. In case when rotation gets interrupted by the server crash/restart it will be continued on the next server startup. To start innoDB key rotation use this command:
|
1 |
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY; |
For more information go this link (paragraph InnoDB Tablespace Encryption and Master Key Rotation):
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
Moving encrypted tables between servers
You can also move a copy of an encrypted table to another server, following these steps. There are some limitations you will need to review.
“When exporting a tablespace that is encrypted using the InnoDB tablespace encryption feature, InnoDB generates a .cfp file in addition to a .cfg metadata file. The .cfp file must be copied to the destination server together with the .cfg file and tablespace file before performing the ALTER TABLE … IMPORT TABLESPACE operation on the destination server. The .cfp file contains a transfer key and an encrypted tablespace key. On import, InnoDB uses the transfer key to decrypt the tablespace key. For related information, see Section 14.4.10, InnoDB Tablespace Encryption.” (from: https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html#innodb-transportable-tablespace-limitations
You now know how to use encryption for your InnoDB tables.
Troubleshooting
- In case keyring_okv can not be accessed after restarting mysqld, you will see an error like this in the error log:
|
1
2
|
[ERROR] Plugin keyring_okv reported: 'Could not connect to the OKV server' [ERROR] Plugin keyring_okv reported: 'keyring_okv initialization failure. Please check that the keyring_okv_conf_dir points to a readable directory and that the directory contains Oracle Key Vault configuration file and ssl materials. Please also check that Oracle Key Vault is up and running. |
As error indicates it seems that Oracle Key Vault server is either down or there is something wrong with the credential directory. Make sure MySQL Server has privileges to access the configuration directory and that keyring_okv_conf_dir variable is set correctly (you can change it in runtime). Check also that structure of the configuration file is correct (see point Get Oracle Key Vault Credentials).
- In case you forget to set keyring_okv_conf_dir variable during server startup or you install the plugin in runtime you will see the following error in the log file:
|
1 |
[Warning] Plugin keyring_okv reported: 'For keyring_okv to be initialized, please point keyring_okv_conf_dir variable to a directory with Oracle Key Vault configuration file and ssl materials |
As you can see you have to set keyring_okv_conf_dir variable.
Appendix 1. Things to consider when changing keyring_okv_conf_dir in runtime
When server is loaded with keyring_okv – InnoDB fetches Master Key from Oracle Key Vault and uses it to decrypt each tablespace key – then it caches the decrypted tablespace keys. Thus the following code works:
|
1
2
3
4
5
6
7
|
CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB; INSERT INTO t1 VALUES(0, "aaaaa"); SELECT * FROM t1; # Changing endpoint SET @@global.keyring_okv_conf_dir= /home/user/okv_enrollment2'; # Table should accessible,keys in memory. SELECT * FROM t1; |
Although, we have changed the endpoint, table t1 is accessible because its decrypted tablespace key was cached. This seems nice, but you have to be aware that changing endpoint in runtime does not implicitly mean blocking access to tables encrypted with the previously loaded endpoints. Also, we can get into troubles when endpoint we are changing to does not contain master key created by the server instance we are using. Then the following is not possible:
|
1
2
|
mysql> create table `test_encryption_4` (`id` int(10) unsigned) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y'; ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded. |
This is because InnoDB tries to fetch Master Key from the endpoint and it fails. This is different from starting the server with keyring_okv – then, when InnoDB cannot find Master Key in the keyring it asks keyring to generate a new one.
Here are some various links regarding MySQL Enterprise TDE:
https://www.mysql.com/products/enterprise/tde.html
http://www.mysql.com/news-and-events/web-seminars/mysql-security-transparent-data-encryption/
MySQL Version 5.7+
https://dev.mysql.com/doc/refman/5.7/en/faqs-tablespace-encryption.html
https://dev.mysql.com/doc/refman/5.7/en/keyring-okv-plugin.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_keyring_okv_conf_dir
MySQL Version 8.0+
https://dev.mysql.com/doc/refman/8.0/en/faqs-tablespace-encryption.html
https://dev.mysql.com/doc/refman/8.0/en/keyring-okv-plugin.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html
https://dev.mysql.com/doc/refman/8.0/en/keyring-installation.html
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html