Blog | Townsend Security

What is MySQL Encryption for Data-at-Rest?

MySQL Enterprise encryption for data-at-rest enables the encryption of tablespaces with transparent data encryption (TDE). It is relatively easy to set up and with the use of a compliant key management server (KMS)—secure.

MySQL Enterprise Transparent Data Encryption (TDE)

InnoDB, MySQL’s storage engine, offers transparent data encryption (TDE) for your sensitive data-at-rest. It secures the tablespaces via a “two tier encryption key architecture” that consists of:

Tablespace encryption keys that encrypt the tablespaces.
A master encryption key that encrypts the tablespace keys.

The only thing that you must add is a trusted, third-party encryption key manager. But more on that later.

With these items in hand, the system works like this:

A tablespace is encrypted, generating a tablespace encryption key.
The tablespace key is encrypted via the master key.
The encrypted tablespace key is stored locally in the tablespace header.
The master key is stored in a trusted, third-party encryption key manager.
The master key’s full lifecycle is managed via the encryption key manager.

In this way, when a user or application needs to access the encrypted data, they just need to authenticate that they are authorized to access the data. From there, InnoDB uses the master key to decrypt the tablespace key and tablespace key is used to decrypt the data. The end user never sees this process, it is transparent to them.

Advantages of Using MySQL Encryption

Advantages of MySQL Enterprise Encryption

Meets Compliance Regulations

Organizations are under increasing pressure to comply with a patchwork of compliance regulations. The good news, MySQL Enterprise edition uses standards based AES encryption for data-at-rest and is also KMIP compatible, so centralized key managers can plug-in to properly manage the master keys. Here are a few compliance regulations that MySQL Enterprise encryption helps you comply with:

Payment Card Industry Data Security Standard (PCI DSS)

Nick Trenc, IT Security Architect at Coalfire Labs, had this to say about encryption and PCI DSS compliance:

One of the key components to the protection of cardholder data at any merchant location is the use of strong cryptography along with just-as-strong cryptographic key management procedures. PCI DSS Requirement 3 outlines what the PCI council believes to be the baseline for strong cryptographic key management procedures and is a key element of any PCI DSS audit.

General Data Protection Regulation (GDPR)

According to GDPR, your security controls must be adequate to account for the risk of accidental, unlawful, or unauthorized disclosure or loss of personal data. If you are not adequately prepared to fend off attacks from hackers or unscrupulous employees and prevent a data breach, you could face stiff fines and lawsuits. Only proper encryption and centralized key management will ensure that should an attack occur, the data will be useless to the attacker.

California Consumer Privacy Act (CCPA)

Here is what Patrick Townsend said about encryption and CCPA:

If you want to avoid the risk of direct or class action litigation related to data loss you should encrypt the sensitive data. Individual and class action litigation only applies to unencrypted sensitive data that is disclosed or lost, for whatever reason. The CCPA is clear on the need for encryption. If you lose unencrypted sensitive data this is direct evidence that you violated your duty to provide reasonable security procedures and practices to protect the sensitive information.

The good news: enabling MySQL Enterprise encryption, coupled with encryption key management, will help keep you in compliance with these regulations. If you are protecting cardholder data, consumer data, or just internal HR records, encrypting that data with MySQL’s TDE will help you meet compliance and keep that sensitive data safe.

Easy to Deploy

MySQL encryption is easy to configure. Entire databases can be encrypted with just a few command line edits. Here are some selected examples from MySQL’s Reference Manual:

To enable encryption for a new file-per-table tablespace:

mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';

To enable encryption for an existing file-per-table tablespace:

mysql> ALTER TABLE t1 ENCRYPTION='Y';

To disable encryption for file-per-table tablespace:

mysql> ALTER TABLE t1 ENCRYPTION='N';

Alliance Key Manager also makes this process easy. Since we are fully integrated with MySQL Enterprise, the configuration process is pretty straight forward. Many times, you can be up and running in a matter of minutes.

KMIP Compatible

As MySQL Enterprise encryption is KMIP 1.1 compatible, you can easily deploy your prefered key manager to manage your encryption keys. This means you are able to use a FIPS 140-2 compliant encryption key manager, like our Alliance Key Manager.

How It Works

MySQL Enterprise has made protecting your sensitive data easy. What’s more, setting up Alliance Key Manager for MySQL is easy as well. Here’s how it works:

First, install and set up the primary and failover Alliance Key Manager servers.
Download the admin authentication certificates from the Alliance Key Manager server to create a secure TLS connection and perform authentication.
Then, create a directory to store your KMIP config file and store certificates needed for the Alliance Key Manager admin / client connection.
Next, you will need to specify your primary key server and high availability failover key server.
Finally, create a master key in Alliance Key Manager and use that to encrypt your tablespace keys in MySQL.

That's it, you have successfully encrypted your MySQL Enterprise database and properly managed the keys! To learn how Alliance Key Manager can help you easily protect your sensitive data in MySQL.

Final Thoughts

Encrypting your sensitive data in with MySQL’s Enterprise encryption has these advantages:

It’s standards based AES-256 encryption. This means that your data is secured with the encryption algorithm that NIST recommends.
It’s KMIP compliant. Your encryption is only strong if your keys are secure. With a trusted third-party key manager protecting your master keys, your encryption will remain strong.
The encryption is transparent to users and applications. No manual processes are needed to access the databases. The data is there, on demand, for all authorized users and applications.

If you haven’t taken advantage of MySQL encryption, now is the time. MySQL encryption makes it simple. Alliance Key Manager makes it secure. Talk to us today.

Excerpt from the White Paper "MySQL & VMware - Encryption and Key Management for Developers."

Whether you develop mostly on Linux or Windows, you can achieve a compliant implementation of encryption with the MySQL database and variations of MySQL like MariaDB. This blog looks at some key decisions you will need to make about the encryption approach, and how we help our customers get encryption right.

Using the MySQL Built-in Encryption Primitives

MySQL supports a number of encryption and encoding operations directly from the SQL language. When encrypting a column you can use the ENCRYPT function, AES_ ENCRYPT function, the older DES_ENCRYPT function, or the encoding or compression algorithms. If you want to use this approach to encryption and decryption, I would recommend that you use AES_ENCRYPT and AES_DECRYPT. For this, primitive MySQL uses the industry standard 128-bit AES algorithm, which is considered strong encryption and meets compliance regulations.

While the DES_ENCRYPT support is still a part of recognized industry standards, you will have a performance benefit when using the stronger 128-bit AES support and are not likely to run into the problem of a future deprecation of the Triple DES algorithm used by the MySQL DES_ENCRYPT method.

Here is an example of a MySQL insert statement that uses AES_ENCRYPT with a hex representation of the encryption key:

INSERT INTO t
VALUES (1,AES_ENCRYPT(‘text’,UNHEX(‘F3229
A0B371ED2D9441B830D21A390C3’)));

Of course, hard-coding the encryption key is poor security practice.

Encryption at the Application Layer

Encrypting and decrypting directly in your SQL statements is not always possible or optimal. Don’t worry, you can also implement encryption in your application code if that makes more sense. Our Alliance Key Manager includes several language-specific software libraries for developers. For example, if you are a Java developer you can install and use our Java .jar files for full support for encryption key retrieval and on-device encryption. If you are a Windows C# developer you can add our Windows .NET Client to your Visual Studio project and have full support for key management. Both Java and C# have great support for encryption - you won’t need support from third parties for AES encryption - but you will need to implement encryption key management the right way.

What are some reasons you might want to do encryption at the application layer?

Minimize the changes to SQL for different databases.
Take advantage of the stronger 256-bit AES encryption method.
Use an AES mode of encryption that uses an Initialization Vector such as Cipher Block Chaining (CBC) mode.
Embed information in the encrypted field about the key used, the version of the key, and the IV.
Create custom logic for encrypting larger blobs in the database.

Whatever the reason or combination of reasons, implementing encryption at the application layer is an easy choice to make with the Alliance Key Manager language SDKs.

Where are the Encryption Keys?

The single biggest challenge a developer will face when deploying encryption for MySQL is how to properly manage encryption keys. Not getting it right leaves the organization open to security failures, audit failures, and litigation. Here are some ways NOT TO STORE encryption keys:

As a part of the SQL statement (see above).
In the application code.
In a file on the same server.
In a file on a separate server.
In a separate table in the MySQL database.

All of these approaches have been the cause of security audit failures for our customers. Don’t let this happen to you.

Summary

Developers are the tip-of-the-spear when it comes to protecting their organizations from data breaches. When they are aware of the critical success factors for an encryption strategy they can dramatically improve the overall security posture of their companies.

At Townsend Security we provide developers with the tools they need to be successful and to get encryption right. Our key management solution, Alliance Key Manager, runs in all of the platform environments that developers need. The applications and software development kits that come with Alliance Key Manager run in VMware, the cloud and everywhere else you might deploy the MySQL database.

Townsend Security Data Privacy Blog

How MySQL Enterprise Transparent Data Encryption Works