From MySQL 5.7.11, encryption is supported for InnoDB (file-per-table) tablespaces. This is called Transparent Tablespace Encryption or sometimes referred as Encryption at Rest. This blog post aims to give the internal details of InnoDB Tablespace Encryption.

Keyring Plugin : Why What How ?
Main idea behind encrypting the tablespace files on disk is to make sure that even if the data files can be accessed directly from the file system, encryption  will prevent an unauthorised user from deciphering the contents.  This requires that the information which is needed to encrypt/decrypt the tablespace data shouldn’t be part of  the  server data directory itself. Thus there is a need for a mechanism where we could store the encryption key somewhere apart from the data directory itself.

The MySQL keyring plugin fills this space. The MySQL keyring plugin is used to store encryption key (master key) which is used in encrypting the tablespace data. The MySQL keyring plugin exposes the following  functions.

my_key_generate()
my_key_fetch()
my_key_store()
my_key_delete()

The MySQL server provides a default implementation of the MySQL keyring plugin which uses a flat unencrypted file to store master key. This is available in the MySQL community version. For a secure key management and to meet regulatory requirements, MySQL Enterprise Edition also integrates with KMIP compliant Key Vaults such as Oracle Key Vault, Gemalto KeySecure, Thales Vormetric key management server, Fornetix Key Orchestration etc. which is needed to secure keys by enforcing clear separation of keys from encrypted data. For more information on the keyring plugin.

The following figure depicts the interaction between different components for tablespace encryption.

Master key vs Tablespace key:

In InnoDB Tablespace Encryption:

• Each tablespace has its own tablespace key which is used to encrypt tablespace data pages.

• Each MySQL instance has one master key which is used to encrypt all tablespace keys.

• Encrypted tablespace keys are written on tablespace header on disk.

Q: Why two keys? Why not have a single key and use it to encrypt/decrypt tablespace data?

A: MySQL provides SQL syntax to rotate master key.

Had there been a single master key to encrypt all tablespaces’ datapages, then once master key is rotated, it would require to decrypt and then  re-encrypt every data page of every tablespace in the instance. This will  result in a very costly operation for medium to large deployments. However with the two seperate keys scheme where  the  tablespace specific key which is encrypted using master key, when the master key is rotated, only the tablespace keys need  to be decrypted and re- encrypted with new master key. With this scheme only the first page of every tablespace (.ibd) file is ever read and written during key rotation.

Tablespace Header:

A Tablespace file in InnoDB is made of multiple logical (and physical) pages where page 0 is called the tablespace header page. Page 0 keeps the metadata information (like FLAGS etc.) for the tablespace. Encryption information (tablespace key) is also kept on page0. Page 0 is NEVER encrypted.

Following figure depicts the page layout of a tablespace.

Here, Page 0 (GREEN) is unencrypted page whereas Page1-PageN (RED) are encrypted using tablespace key (which is encrypted by master key) stored in encryption info on page0.

Here is the flow diagram which depicts creation of an encrypted table (file-per-table-tablespace):

But what about performance?

Encryption processing is done at the last stage of the I/O layer. A page in the buffer pool is always unencrypted. So once the page is to be read from disk, if it is encrypted, it is unencrypted at I/O layer and then brought into buffer pool. All subsequent accesses to that page is fulfilled by buffer pool copy. Encryption comes into picture only when page is flushed during which I/O layer encrypts the page before flushing it to the disk. That’s why there is not a big performance impact of having encryption ON for tablespaces.

All good! Any limitation?

• Only AES is supported

• Only Algorithm=copy supported for altering encryption for a table.

• Encrypted tables can’t be moved to shared/system tablespaces as encryption is not supported to shared/system tablespaces.

For more information, click here.

Thank you for using MySQL !