MySQL Security – MySQL Enterprise Transparent Data Encryption

April 10, 2018

When thinking about security within a MySQL installation, you should consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools / features / plugins in order to protect your data including some advanced features like  AuditFirewall, Password Management, Password Validation Plugin, User Account Locking, etc…

MySQL Security

In this sixth episode of the MySQL Security series, we will see how data-at-rest encryption helps organizations implement stronger security controls and satisfy regulatory compliance.

You will be able to protect the privacy of your information, prevent data breaches and help meet popular regulatory requirements including GDPR, PCI DSS, HIPAA with MySQL Enterprise Transparent Data Encryption aka TDE.

 

MySQL Keyring

MySQL Enterprise Transparent Data Encryption relies on a MySQL Keyring plugin for master encryption key management. MySQL Server provides many plugin choices including :

 

In this article I’ll use the keyring_encrypted_file plugin with MySQL Enterprise Edition 5.7.21.

 

Updated on 22nd of August 2018
Note
: MySQL Enterprise Transparent Data Encryption works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

 

Warning
The keyring_file and keyring_encrypted_file plugins for encryption key management are not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

 

These keyring plugins would be use with MySQL Enterprise Audit to store the audit log file encryption password (you can read my previous article) and InnoDB tables when encrypted with  MySQL Enterprise Transparent Data Encryption, actually the topic of this article but you already know that, right? :).

 

Keyring Plugin Installation : keyring_encrypted_file

Like I stated before many keyring plugins are available and I’ll use the keyring plugin : keyring_encrypted_file, that stores keyring data in an encrypted file local to the server host.

 

Only one keyring plugin should be enabled at a time. So let’s check that we have none so far :

 

To be usable by the server, the plugin library file must be located in the MySQL plugin directory :

 

Update the MySQL configuration file and restart the MySQL server :

  • To be usable during the server startup process, keyring_encrypted_file must be loaded using the early-plugin-load option.
  • To specify the password for encrypting the keyring data file, set the keyring_encrypted_file_password system variable.
  • The keyring_encrypted_file_data system variable optionally configures the location of the file used by the keyring_encrypted_file plugin for data storage.

 

Warning
Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.

 

 

We can have a look at the Keyring file. Its location could be found with keyring_file_data variable :

 

After a keyring plugin is configured, InnoDB tables may be encrypted.

 

InnoDB Transparent Encryption

MySQL Enterprise Transparent Data Encryption

InnoDB supports data encryption for InnoDB tables stored in file-per-table tablespaces.

I’m using Sakila Database, available here.

Below a couple of random queries :

 

Data-at-Rest Encryption

Usually it is not necessary to encrypt all the tables. After a deep data analysis let’s assume 2 relevant InnoDB tables must be encrypted : customer and staff.

So let’s encrypted these tables!

We can do it easily with a CREATE or ALTER TABLE statement using ENCRYPTION=”Y” :

There we go!

 

Note
Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE … ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITHM=INPLACE is not supported.

 

Identify Tables that Use InnoDB Tablespace Encryption

This information is available in INFORMATION_SCHEMA.TABLES :

 

Query encrypted Tables

Transparent Data Encryption means transparent for the clients ie no change in your applications.

In other words, you don’t have to modify your queries and sames queries with same datasets will give me the same result :

 

Master Key Rotation

Legal and regulatory requirements require a periodically rotation of the master encryption key. It’s also more than recommended to rotate the master key whenever you suspect a security breach. Rotating the master encryption key only changes the master encryption key and re-encrypts tablespace keys.

To rotate the master encryption key use ALTER INSTANCE ROTATE INNODB MASTER KEY :

Et voilà!

Now you know the MySQL Enterprise Transparent Encryption basics \o/

 

I would like to address a last item, regarding a common customer question about the disk footprint of an encrypted table.

So let’s play a little bit more with TDE.

We have a table, encryptedTable, not yet encrypted with 5 millions rows

Table size is 722 MB

 

Table is now encrypted

 

Table size is now : 870 MB

 

Is there a way to reclaim space (and optionally improve I/O efficiency)?  Yes of course, OPTIMIZE TABLE :

 

Table size is again 722 MB

 

 

MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.

It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.

MySQL Enterprise Edition server Trial Download (Note – Select Product Pack: MySQL Database).

 

MySQL Enterprise Edition

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

 

6 Responses to “MySQL Security – MySQL Enterprise Transparent Data Encryption”

  1. […] / features / plugins in order to protect your data including some advanced features like Audit, TDE, Password Management, Password Validation Plugin, User Account Locking, […]

  2. […] many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Audit, Firewall, Password Management, Password Validation Plugin, The Connection-Control Plugins, […]

  3. […] many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Audit, Firewall, Password Management, Password Validation Plugin, User Account Locking, […]

  4. […] many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Audit, Firewall, Password Management, User Account Locking, The Connection-Control Plugins, […]

  5. […] many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Audit, Firewall, Password Validation Plugin, User Account Locking, The Connection-Control […]

  6. […] many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Firewall, Password Management, Password Validation Plugin, […]

Leave a Reply