Encryption and Key Management for MySQL Developers

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.