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:
- Oracle Key Vault
- Gemalto KeySecure
- Thales Vormetric Key Management Server
- Fornetix Key Orchestration
- New! Townsend Alliance Key Manager
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
|
1 |
select @@version; |
Lets Get Going
As administrator (Example running this as admin – root@localhost)
If not installed – install MySQL Keyring
To check –
|
1 |
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%'; |
Next Install MySQL Keyring UDFs
|
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
|
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:
|
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
|
1
2
3
|
SELECT keyring_key_store ('MySecret', 'SECRET', create_asymmetric_priv_key('RSA', 2048)); |
View it
|
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).
|
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
|
1 |
select `secretdb`.`secdb_public_key`(); |
Create your application’s MySQL user and GRANT access to the table and the function.
|
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
|
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.
|
1 |
SET @secretphrase='Sooo very secret 1'; |
Define the key length and algorithm
|
1 |
SET @key_len = 2048; SET @algo = 'RSA'; |
Encrypt that data
|
1 |
SET @enc_value = ASYMMETRIC_ENCRYPT(@algo, @secretphrase, @pubk); |
See its encrypted
|
1 |
select 'Encrypted secret ', @enc_value; |
Insert into the table
|
1 |
INSERT INTO `secretdb`.`secrets_table` (`secid`, `secrets_tablecol`) VALUES (1, @enc_value); |
Do it again – Another secret
|
1 |
SET @secretphrase='Sooo very secret 2'; select @secretphrase; Set @enc_value = ASYMMETRIC_ENCRYPT(@algo, @secretphrase, @pubk); select 'Encrypted secret 2', @enc_value; |
|
1 |
INSERT INTO `secretdb`.`secrets_table` (`secid`,`secrets_tablecol`) VALUES (2, @enc_value); |
See the encrypted data
|
1 |
select * from `secretdb`.`secrets_table`; |
Note: appuser can’t decrypt without the private key.
Login as root and decrypt the data
|
1
2
|
select `secrets_table`.`secrets_tablecol` into @encrsecretphrase from `secretdb`.`secrets_table` where secid=2; select @encrsecretphrase; |
|
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.
|
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
|
1 |
select `secretdb`.`secdb_private_key`(); |
Now lets create another user – who should also have access to the data.
|
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
|
1 |
select `secretdb`.`secdb_private_key`(); |
Retrieve the data
|
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
|
1 |
select secretdb.secdb_private_key() into @privappprivpkey; |
Decrypt the data with the private key
|
1 |
Set @enc_pp = ASYMMETRIC_DECRYPT(@algo, @encrsecretphrase, @privappprivpkey); |
View the secret
|
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.