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  Audit, Data Masking & De-Identification, Firewall, 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 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
  8. Enterprise Data Masking and De-Identification

Reference Manual

MySQL Security

Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

16 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, […]

  7. […] 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, […]

  8. […] / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Password Expiration Policy, Password […]

  9. […] / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password […]

  10. […] / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password […]

  11. […] / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password […]

  12. […] / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password […]

  13. […] / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password […]

  14. […] / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password […]

  15. Hi Oliver , Please help me with article on encryption of MySQL 5.7 database where our root user also should not see the table data.

  16. Hi Manish,
    not sure to understand what you mean.
    This article is about TDE, if the user has the privileges to see or update the data it will be able to do it even if they are encrypted.
    What you want to achieve would probably be done using the relevant grant privileges (https://dev.mysql.com/doc/refman/5.7/en/grant.html)
    As an alternative you can also have a look at MySQL Enterprise Encryption (not available in the community version) https://www.mysql.com/products/enterprise/encryption.html
    Other option could be the Audit plugin (not available in the community version) http://dasini.net/blog/2018/04/04/mysql-security-mysql-enterprise-audit/