MySQL :: Using a MySQL Keyring SECRET and Asymmetric Encryption

Skip to Main Content

The world's most popular open source database

Documentation Downloads MySQL.com

Developer Zone

Section Menu:

MySQL Blog Archive

For the latest blogs go to blogs.oracle.com/mysql

Using a MySQL Keyring SECRET and Asymmetric Encryption

Posted on Apr 6, 2020 by Mike Frank
Category: Security

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:

Bring your own key – Encryped Key File
Hashicorp Vault
And more

Prerequisite

MySQL Enterprise Edition 8.0.19 or higher

select @@version;

1	select @@version;

Lets Get Going

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

To check –

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

1	SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

Next Install MySQL Keyring UDFs

INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_generate RETURNS INTEGER
SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_fetch RETURNS STRING
SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER
SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_type_fetch RETURNS STRING
SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_store RETURNS INTEGER
SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_remove RETURNS INTEGER
SONAME 'keyring_udf.so';

1

2

3

4

5

6

7

8

9

10

11

12

13

INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';

CREATE FUNCTION keyring_key_generate RETURNS INTEGER

SONAME 'keyring_udf.so';

CREATE FUNCTION keyring_key_fetch RETURNS STRING

SONAME 'keyring_udf.so';

CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER

SONAME 'keyring_udf.so';

CREATE FUNCTION keyring_key_type_fetch RETURNS STRING

SONAME 'keyring_udf.so';

CREATE FUNCTION keyring_key_store RETURNS INTEGER

SONAME 'keyring_udf.so';

CREATE FUNCTION keyring_key_remove RETURNS INTEGER

SONAME 'keyring_udf.so';

And MySQL Enterprise Encryption

CREATE FUNCTION asymmetric_decrypt
RETURNS STRING SONAME 'openssl_udf.so';
CREATE FUNCTION asymmetric_derive
RETURNS STRING SONAME 'openssl_udf.so';
CREATE FUNCTION asymmetric_encrypt
RETURNS STRING SONAME 'openssl_udf.so';
CREATE FUNCTION asymmetric_sign
RETURNS STRING SONAME 'openssl_udf.so';
CREATE FUNCTION asymmetric_verify
RETURNS INTEGER SONAME 'openssl_udf.so';
CREATE FUNCTION create_asymmetric_priv_key
RETURNS STRING SONAME 'openssl_udf.so';
CREATE FUNCTION create_asymmetric_pub_key
RETURNS STRING SONAME 'openssl_udf.so';
CREATE FUNCTION create_dh_parameters
RETURNS STRING SONAME 'openssl_udf.so';
CREATE FUNCTION create_digest 
RETURNS STRING SONAME 'openssl_udf.so';

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

CREATE FUNCTION asymmetric_decrypt

RETURNS STRING SONAME 'openssl_udf.so';

CREATE FUNCTION asymmetric_derive

RETURNS STRING SONAME 'openssl_udf.so';

CREATE FUNCTION asymmetric_encrypt

RETURNS STRING SONAME 'openssl_udf.so';

CREATE FUNCTION asymmetric_sign

RETURNS STRING SONAME 'openssl_udf.so';

CREATE FUNCTION asymmetric_verify

RETURNS INTEGER SONAME 'openssl_udf.so';

CREATE FUNCTION create_asymmetric_priv_key

RETURNS STRING SONAME 'openssl_udf.so';

CREATE FUNCTION create_asymmetric_pub_key

RETURNS STRING SONAME 'openssl_udf.so';

CREATE FUNCTION create_dh_parameters

RETURNS STRING SONAME 'openssl_udf.so';

CREATE FUNCTION create_digest

RETURNS STRING SONAME 'openssl_udf.so';

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

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.
A client application’s MySQL user to be able to only encrypt sensitive data – using a public key
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 TABLE `secretdb`.`secrets_table`
(`secid` INT NOT NULL, 
`secrets_tablecol` VARBINARY(3000) NOT NULL);

1

2

3

CREATE TABLE `secretdb`.`secrets_table`

(`secid` INT NOT NULL,

`secrets_tablecol` VARBINARY(3000) NOT NULL);

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

SELECT keyring_key_store
('MySecret', 'SECRET',
create_asymmetric_priv_key('RSA', 2048));

1

2

3

SELECT keyring_key_store

('MySecret', 'SECRET',

create_asymmetric_priv_key('RSA', 2048));

View it

select keyring_key_fetch('MySecret');

1	select keyring_key_fetch('MySecret');

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).

CREATE DEFINER = 'root'@'localhost' 
FUNCTION `secretdb`.`secdb_public_key`() 
RETURNS TEXT(500) deterministic 
SQL SECURITY DEFINER 
RETURN 
RTRIM(CREATE_ASYMMETRIC_PUB_KEY('RSA',keyring_key_fetch('MySecret')));

1

2

3

4

5

6

CREATE DEFINER = 'root'@'localhost'

FUNCTION `secretdb`.`secdb_public_key`()

RETURNS TEXT(500) deterministic

SQL SECURITY DEFINER

RETURN

RTRIM(CREATE_ASYMMETRIC_PUB_KEY('RSA',keyring_key_fetch('MySecret')));

Try it

select `secretdb`.`secdb_public_key`();

1	select `secretdb`.`secdb_public_key`();

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

CREATE USER 'appuser'@'%' IDENTIFIED BY <password>;
GRANT USAGE ON *.* TO `appuser`@`%`;

1 2	CREATE USER 'appuser'@'%' IDENTIFIED BY <password>; GRANT USAGE ON . TO `appuser`@`%`;

Now – lets try it out – Login as this appuser

Get the public key

select `secretdb`.`secdb_public_key`() into @pubk; GRANT SELECT, INSERT ON `secretdb`.* TO `appuser`@`%`'; GRANT EXECUTE ON FUNCTION `secretdb`.`secdb_public_key` TO 'appuser`@'%';

1	select `secretdb`.`secdb_public_key`() into @pubk; GRANT SELECT, INSERT ON `secretdb`.* TO `appuser`@`%`'; GRANT EXECUTE ON FUNCTION `secretdb`.`secdb_public_key` TO 'appuser`@'%';

Ok lets create a secret.

SET @secretphrase='Sooo very secret 1';

1	SET @secretphrase='Sooo very secret 1';

Define the key length and algorithm

SET @key_len = 2048; SET @algo = 'RSA';

1	SET @key_len = 2048; SET @algo = 'RSA';

Encrypt that data

SET @enc_value = ASYMMETRIC_ENCRYPT(@algo, @secretphrase, @pubk);

1	SET @enc_value = ASYMMETRIC_ENCRYPT(@algo, @secretphrase, @pubk);

See its encrypted

select 'Encrypted secret ', @enc_value;

1	select 'Encrypted secret ', @enc_value;

Insert into the table

INSERT INTO `secretdb`.`secrets_table` (`secid`, `secrets_tablecol`) VALUES (1, @enc_value);

1	INSERT INTO `secretdb`.`secrets_table` (`secid`, `secrets_tablecol`) VALUES (1, @enc_value);

Do it again – Another secret

SET @secretphrase='Sooo very secret 2'; select @secretphrase; Set @enc_value = ASYMMETRIC_ENCRYPT(@algo, @secretphrase, @pubk); select 'Encrypted secret 2', @enc_value;

1	SET @secretphrase='Sooo very secret 2'; select @secretphrase; Set @enc_value = ASYMMETRIC_ENCRYPT(@algo, @secretphrase, @pubk); select 'Encrypted secret 2', @enc_value;

INSERT INTO `secretdb`.`secrets_table` (`secid`,`secrets_tablecol`) VALUES (2, @enc_value);

1	INSERT INTO `secretdb`.`secrets_table` (`secid`,`secrets_tablecol`) VALUES (2, @enc_value);

See the encrypted data

select * from `secretdb`.`secrets_table`;

1	select * from `secretdb`.`secrets_table`;

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

Login as root and decrypt the data

select `secrets_table`.`secrets_tablecol` into @encrsecretphrase from `secretdb`.`secrets_table` where secid=2;
select @encrsecretphrase;

1 2	select `secrets_table`.`secrets_tablecol` into @encrsecretphrase from `secretdb`.`secrets_table` where secid=2; select @encrsecretphrase;

select keyring_key_fetch('MySecret') into @privappprivpkey; select @privappprivpkey; Set @enc_pp = ASYMMETRIC_DECRYPT(@algo, @encrsecretphrase, @privappprivpkey); select 'Secret', @enc_pp;

1	select keyring_key_fetch('MySecret') into @privappprivpkey; select @privappprivpkey; Set @enc_pp = ASYMMETRIC_DECRYPT(@algo, @encrsecretphrase, @privappprivpkey); select 'Secret', @enc_pp;

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.

CREATE DEFINER = 'root'@'localhost' FUNCTION `secretdb`.`secdb_private_key`() RETURNS TEXT(500) deterministic SQL SECURITY DEFINER RETURN rtrim(keyring_key_fetch('MySecret'));

1	CREATE DEFINER = 'root'@'localhost' FUNCTION `secretdb`.`secdb_private_key`() RETURNS TEXT(500) deterministic SQL SECURITY DEFINER RETURN rtrim(keyring_key_fetch('MySecret'));

Test as root

select `secretdb`.`secdb_private_key`();

1	select `secretdb`.`secdb_private_key`();

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

CREATE USER 'privuser'@'%' IDENTIFIED BY <password>;
GRANT USAGE ON *.* TO 'privuser'@'%'; 
GRANT SELECT ON `secretdb`.* TO 'privuser'@'%';
GRANT EXECUTE ON FUNCTION `secretdb`.`secdb_private_key` TO 'privuser'@'%';

1

2

3

4

CREATE USER 'privuser'@'%' IDENTIFIED BY <password>;

GRANT USAGE ON *.* TO 'privuser'@'%';

GRANT SELECT ON `secretdb`.* TO 'privuser'@'%';

GRANT EXECUTE ON FUNCTION `secretdb`.`secdb_private_key` TO 'privuser'@'%';

Test access

select `secretdb`.`secdb_private_key`();

1	select `secretdb`.`secdb_private_key`();

Retrieve the data

SET @algo = 'RSA'; select `secrets_table`.`secrets_tablecol` into @encrsecretphrase from `secretdb`.`secrets_table` where secid=2;

1	SET @algo = 'RSA'; select `secrets_table`.`secrets_tablecol` into @encrsecretphrase from `secretdb`.`secrets_table` where secid=2;

See – it is encrypted

select @encrsecretphrase;

Let’s decrypt

Get the private key

select secretdb.secdb_private_key() into @privappprivpkey;

1	select secretdb.secdb_private_key() into @privappprivpkey;

Decrypt the data with the private key

Set @enc_pp = ASYMMETRIC_DECRYPT(@algo, @encrsecretphrase, @privappprivpkey);

1	Set @enc_pp = ASYMMETRIC_DECRYPT(@algo, @encrsecretphrase, @privappprivpkey);

View the secret

select 'The secret is', @enc_pp;

1	select 'The secret is', @enc_pp;

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.

Search

Categories