Using a MySQL Keyring SECRET and Asymmetric Encryption

For an encrypt only or decrypt/encrypt applications

The following is an example of how to allow applications to asymmetrically encrypt data using a public key. In MySQL 8.0.19 support for the SECRET datatype was added to our Keyring technology. With this technology, users can securely manage their own keys using:

Oasis KMIP protocol implementations:

Other APIs for Key Management:

Prerequisite

MySQL Enterprise Edition 8.0.19 or higher

Lets Get Going

As administrator (Example running this as admin – root@localhost)
If not installed – install MySQL Keyring

To check –

Next Install MySQL Keyring UDFs

And MySQL Enterprise Encryption

Ok now to set up an example – in this case we want

  1. To protect the private key by pushing to a keyring
    • If you lose the private key – you’ve lost any encrypted data.
    • If the key is stolen – thieves can read your sensitive data.
  2. A client application’s MySQL user to be able to only encrypt sensitive data – using a public key
  3. A different user to be able to decrypt that data – using a private key

Create table for storing your sensitive “secret” data

Now that we are ready, let us look at this example:

Create a private PEM key and store it on the MySQL keyring

View it

Create a function to allow an application user to get the public key and encrypt a “secret”.

In this case the user can not run keyring_key_fetch (they don’t have permission) so the function runs the SQL SECURITY as the DEFINER (root in this case).This function will only return this public key (by extracting it from the private key on the keyring).

Try it

Create your application’s MySQL user and GRANT access to the table and the function.

Now – lets try it out – Login as this appuser

Get the public key

Ok lets create a secret.

Define the key length and algorithm

Encrypt that data

See its encrypted

Insert into the table

Do it again – Another secret

See the encrypted data

Note: appuser can’t decrypt without the private key.

Login as root and decrypt the data

As root you can see the data

Ok – so now let’s say we want to provide a user other than root access to the private key.

Test as root

Now lets create another user – who should also have access to the data.

Test access

Retrieve the data

See – it is encrypted

select @encrsecretphrase;

Let’s decrypt

Get the private key

Decrypt the data with the private key

View the secret

That’s it.

Common questions

What if I want to encrypt/decrypt the data in my application?

Yes, you could do that using openssl or compatible libraries with the public or private key (its in PEM format). Just be sure you insert/update the data as binary.

What if my data is bigger than can be handled by Asymmetric Encryption?

You can have the same benefits of public keys by implementing hybrid encryption.

However, now with SECRET supported, there is no need to store the keys in tables which is less secure – now you can place them on the more secure keyring as a SECRET type.

Going Forward

Try this out, it won’t take long. And, there are endless other permutations to solving data security with keyrings, asymmetric encryption, symmetric encryption, permissions and other access controls.

For example – you could only store the public key on a master and a private key on a read only slave for instance. The private key could only exist within an application and not on a mysql keyring. Or you could write a function with permissions to users that decrypts if privileged but doesn’t show the private key at all.

If you have a security challenge that’s got you stumped let us know.

As always, thanks for using MySQL.

 

2 thoughts on “Using a MySQL Keyring SECRET and Asymmetric Encryption

Leave a Reply