MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
Hybrid Data Encryption by Example using MySQL Enterprise Edition

Sharing keys, passphrases with applications is problematic, especially with regard to encrypting data. Too often applications are developed where “the keys are left in the door” or at best “under the mat” – hard coded, in a clear text property file… exposed and vulnerable.  And then there’s the whole issue of managing and changing and rotating keys.

Solving with Hybrid Approach

In this example, I’m going to show you a 2 tiered “hybrid” approach that uses Symmetric and Asymmetric methods together to protect your data. For simplicity I am going to demonstrate using SQL statements, but in practice you’d change out a few client side calls, which I comment on later.  If needed, I’ll provide another blog showing a native client example in code versus SQL. But for now the point is to show the mechanics of this in its simplest form.  This is just one possible use case, there are certainly other ways to tweak and change this to best fit your application needs.

Use case example –  User of an app enters sensitive data that is only to be viewed by an authorized user.

Patient   – Doctor
Buyer      – Seller
Investor – Broker
Student – Teacher

The “low trust” app is gathering “sensitive” information.  We don’t want to “trust” that app with keys or certificates.  This application might be more exposed and is only providing data.  The “high trust” app has authorized/trusted individuals who will see this “sensitive” data.  Additionally software developers and IT admins are not authorized to see this data.

In a nutshell

On the low trust app:

  • Get a public key (only encrypts)
  • Generate a random key (for symmetric encryption)
  • Symmetrically encrypt the sensitive data with that random key
  • Store the symmetrically encrypted data
  • Encrypt the random key with the public key
  • Store the asymmetrically encrypted random key

On the trusted app:

  • Get a private key (in this case from a protected database table)
  • Decrypt the random key with the private key
  • Decrypt the sensitive data with the random key – Read the data

On some schedule typically quarterly, you can rotate the private encryption key without re-encrypting the data.

  • Generate a new private and public key pair
  • Just Update the asymmetrically encrypted symmetric keys table by decrypting the keys with the old private key and encrypting with the new public key.
  • Store the new public and private keys.

A big plus – when rotating the key you never have to actually decrypt and re-encrypt the raw data.  Nor do you have to make any changes to my applications. This is a two-tiered “hybrid” approach.  And there are additional benefits – symmetric encryption is high performance versus asymmetric which is computationally costly, thus with this hybrid approach your data can be quite large (say a big JSON document) and encryption performance is high.

A scripted example

I am showing in SQL for simplicity – your apps will be coded in a programming language – I’ll describe the changes needed for that at the end. Additionally you’ll see some select ‘blah blab’ @variable name along the way.  These are just to echo so you can look at what’s happening.

The Users

  • 1 – Appuser
    • Don’t want to trust this user much
    • The application using Appuser that gets data and stores it in the database encrypted
  • 2- Trusteduser
    • This user is trusted to see the data

I am using a schema called secdemo.

Initial setup

Connect to MySQL as an admin:

This table stores the symmetric key encrypted with the public key.  Note the privileges – there must not be a grant to secretsprivkey for appuser:

Now to create Asymmetric Keys:

The “low trust” web application will now get the sensitive information and encrypt the data.

Connect as appuser:

Now the data is stored encrypted, and the symmetric key is encrypted with the public key is stored. The data can’t be decrypted with the public key. The low trust app doesn’t have any persisted keys.

Connect as trusteduser:

Next the trusted user wants to see the secret information:

What’s Next

So I just showed you an example. It’s not an actual application, but hopefully it makes it simple to see the mechanics of how hybrid encryption works.  In an actual client application you would basically change out 3 calls from SQL to code/language of your choice – C, C++, Java, .NET, Nodejs, PHP, etc.

  1. SET @passphrase = SHA2(lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0), 224);
    • To some other random to generate the secret passphrase
  2. SET @enc_keepsecret=AES_ENCRYPT(@keepsecret,@passphrase);
    • And make a library call to encrypt your secret information with AES
  3. SET @enc_pp = ASYMMETRIC_ENCRYPT(@algo, @passphrase, @webpubkey);
    • And asymmetrically encrypt your secret information

The other change you might make is where the private key is stored. There are some more secure options related to that if you want to make sure your DBA can’t read this data. I’ll blog about that separately.

Under the hood its OpenSSL

Our functions use the OpenSSL library so you could use OpenSSL library calls or other compatible libraries.  These methods are standards so you should be able to mix and match.

Also I used a secret key for each item I inserted.  That’s just 1 way to do it. I’ve included a link to the full SQL text for download if you want to give it a try.

Conclusion

This is a simple and secure method to use encryption to protect the privacy of the data.  Public key asymmetric encryption used along with symmetric encryption – thus hybrid encryption, has far more uses that it’s been applied to thus far. Which is a shame as it solves many of today’s problems quite nicely. Note: I tried to keep things simple in the blog by design. I plan to write more blogs related to patterns like this for data protection and privacy.

References