Using Fortanix Data Security Manager for MySQL Encryption at Rest

1.0 Introduction

MySQL Enterprise edition supports the encryption of data at rest. MySQL Server supports a keyring service that enables internal server components and plugins to securely store sensitive information for later retrieval. One of the plugins is called “keyring_okv”, which is a KMIP 1.1 plugin for use with KMIP-compatible back end keyring storage, like Fortanix Data Security Manager. For more information, refer to Using the keyring_okv KMIP Plugin.

Cryptographically secure generation and secure management of encryption keys are required for true security of data at rest encrypted by MySQL. Fortanix-Data-Security-Manager (DSM) with its KMIP support provides a secure and flexible solution for this.

MySQL KMIP keyring plugin authenticates to a KMIP enabled key management server using the client certificate. Fortanix DSM supports clients/apps to authenticate using API Key, App ID, and Certificate or only Certificate.

This article describes how to set up an app in Fortanix DSM for MySQL to integrate with Fortanix DSM.

2.0 Configure Fortanix DSM

A Fortanix DSM service must be configured, and the URL must be accessible. To create a Fortanix DSM account and group, refer to the following sections:

2.1 Signing Up

To get started with the Fortanix Data Security Manager (DSM) cloud service, you must register an account at <Your_DSM_Service_URL>. For example, https://eu.smartkey.io.

For detailed steps on how to set up the Fortanix DSM, refer to the User's Guide: Sign Up for Fortanix Data Security Manager SaaS documentation.

2.2 Creating an Account

Access the <Your_DSM_Service_URL> on the web browser and enter your credentials to log in to the Fortanix DSM.

2.3 Creating a Group

Perform the following steps to create a group in the Fortanix DSM:

1. Click the Groups menu item in the DSM left navigation bar and click the + button on the Groups page to add a new group.

   

2. On the Adding new group page, enter the following details:

   • Title: Enter a title for your group.

   • Description (optional): Enter a short description for the group.

3. Click the SAVE button to create the new group.

The new group has been added to the Fortanix DSM successfully.

2.4 Creating an Application

Perform the following steps to create an application (app) in the Fortanix DSM:

1. Click the Apps menu item in the DSM left navigation bar and click the + button on the Apps page to add a new app.

   

2. On the Adding new app page, enter the following details:

   • App name: Enter the name of your application.

   • ADD DESCRIPTION (optional): Enter a short description for the application.

   • Authentication method: Select the default API Key as the method of authentication from the drop down menu. For more information on these authentication methods, refer to User's Guide: Authentication documentation.

   • Assigning the new app to groups: Select the group created in Section 2.3: Creating a Group from the list.

3. Click the SAVE button to add the new application. 

The new application has been added to the Fortanix DSM successfully.

2.5 Copying the App UUID

Perform the following steps to copy the app UUID from the Fortanix DSM:

1. Click the Apps menu item in the DSM left navigation bar and click the app created in the Section 3.4: Creating an Application to go to the detailed view of the app. 

2. From the top of the app’s page, copy the app UUID to be used in Section 3.0: Generating the Certificate to generate a self-signed certificate and a private key.

The following sections will explain how to generate a client certificate to use with MySQL for each of these methods.

3.0 Generating the Certificate

You can generate a self-signed certificate such that the CN contains the App ID.

Generate a self-signed certificate using the command below:

openssl req -newkey rsa:2048 -nodes -keyout private.key -x509 -days 365 -out certificate.crt

Generated certificate will have the App ID as CN.

Examine the subject in the certificate to verify it contains the App ID as CN. A correctly generated certificate should look as follows (note the value of CN):

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 11285796284824083476 (0x9c9f33ed245cdc14)
    Signature Algorithm: sha256WithRSAEncryption
        Issuer: C=US, ST=CA, L=Mountain View, O=Fortanix, OU=Test, CN=da7f2800-4122-4681-aebf-90beb779b73f/[email protected]
        Validity
            Not Before: Aug  8 23:31:20 2018 GMT
            Not After : Aug  8 23:31:20 2019 GMT
        Subject: C=US, ST=CA, L=Mountain View, O=Fortanix, OU=Test, CN=da7f2800-4122-4681-aebf-90beb779b73f/[email protected]
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:d2:ae:15:66:bf:78:d4:98:f4:4d:a5:57:bf:04:
                    08:76:83:1f:40:e8:8b:c4:da:8a:a0:71:22:43:84:
                    6d:c9:05:f2:81:91:83:04:75:bd:c9:83:86:92:bf:
                    ff:a0:e4:b4:e4:ee:56:09:10:2a:dc:e2:f4:0c:65:
                    43:96:a1:31:0d:15:92:49:87:ee:46:91:5d:f1:8c:
                    61:b3:ca:4a:9f:be:01:00:d5:30:5f:ee:56:35:75:
                    3c:e1:0d:a6:34:66:7f:3b:26:69:97:33:6d:2e:c7:
                    fd:c9:42:7d:14:f7:12:18:4a:5b:a6:90:52:7a:4b:
                    1b:45:b3:79:33:31:99:03:1d:a4:ed:51:dc:7b:43:
                    20:02:bb:08:22:27:27:8c:51:6a:5f:59:87:45:95:
                    d7:f3:ca:fa:30:3d:d5:a6:50:77:03:e3:de:eb:30:
                    17:45:48:fe:5b:76:d4:c1:03:3f:b8:99:73:ae:ad:
                    ae:e2:69:95:e2:14:1e:42:b1:ac:72:cd:0b:c6:01:
                    e3:20:8d:5a:6a:5d:19:79:17:f0:80:5f:75:fc:d5:
                    da:9c:af:07:d8:c7:96:02:a5:94:19:64:d7:9a:e4:
                    56:f1:cf:54:b9:a7:29:28:22:52:f2:c4:8a:97:04:
                    45:b1:9b:b5:4f:c0:18:53:ff:08:3f:3b:81:bd:f1:
                    d1:e9
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Key Identifier: 
                87:65:C6:B6:B6:3A:0A:A6:30:BA:CB:D2:27:9E:C4:E6:2E:7F:2F:6D
            X509v3 Authority Key Identifier: 
                keyid:87:65:C6:B6:B6:3A:0A:A6:30:BA:CB:D2:27:9E:C4:E6:2E:7F:2F:6D

            X509v3 Basic Constraints: 
                CA:TRUE
    Signature Algorithm: sha256WithRSAEncryption
         71:da:8c:da:ab:9d:6d:8a:f1:9c:56:a9:7d:e2:e2:1b:fd:90:
         b7:5e:45:db:d4:69:47:ca:98:2f:b0:3b:2c:1f:49:3a:75:dd:
         1d:96:b3:bd:11:a6:d7:06:60:4f:18:11:e1:cf:db:5c:52:03:
         29:78:47:6e:36:c0:64:d8:4d:34:00:d9:94:55:48:a9:d4:b2:
         b2:ed:b8:13:fc:3d:c6:b4:61:a3:56:aa:9d:73:80:62:38:da:
         0c:94:b0:4a:e6:86:da:6a:f9:aa:f3:a4:3c:48:32:93:f7:d3:
         27:f9:2c:77:b4:91:9c:84:62:96:86:7d:d2:c8:20:79:d1:12:
         ef:f0:cc:15:31:ea:86:e9:b4:02:00:55:83:0f:6a:c6:5b:d2:
         19:67:9b:b2:44:f8:3b:36:f9:b0:02:b2:98:7d:1e:fa:95:58:
         92:92:57:68:f8:56:bb:43:db:01:08:bb:d6:ab:52:e6:c7:88:
         7a:1c:8d:f4:31:90:70:0a:dd:d2:96:7c:8b:93:8f:1f:4a:80:
         fe:3a:f8:df:82:a7:99:ac:2f:e8:02:e5:8b:fe:ec:3b:3b:0a:
         a3:c0:82:4d:f7:93:66:a1:76:6f:fa:c2:19:8e:d8:b6:b4:27:
         8c:57:22:a4:f7:e6:45:61:27:af:fc:5f:51:88:eb:32:

4.0 Updating the Authentication Method

Perform the following steps to change the authentication method:

1. Go to the detailed view of the app created in Section 2.4: Creating an Application and click the Change authentication method button and select the Certificate option to change the authentication method to Certificate.

2. Click the SAVE button.

3. On the Add certificate dialog box, click the UPLOAD NEW CERTIFICATE button to upload the certificate file or paste the content of the certificate generated in previous section.

4. Select both the check boxes to confirm your understanding about the action.

5. Click the UPDATE button to save the changes.

5.0 Configure Encryption in MySQL

In order to configure encryption in MySQL, you will need to install and configure “keyring_okv” plugin.

5.1 Keyring Plugin Installation

To load the plugin, use the --early-plugin-load option to name the plugin library file that contains it. For keyring_okv, use these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary):

[mysqld]
early-plugin-load=keyring_okv.so

This enables “keyring_okv” plugin. However, the plugin requires additional configuration to point to Fortanix DSM and the KMIP server. The following section explains this configuration. MySQL will use the certificate you created in the earlier step to authenticate to Fortanix DSM.

5.2 Configuring the keyring_okv KMIP Plugin

The plugin keyring_okv needs to be configured to point to DSM and point to the credentials that will be used to authenticate to Fortanix DSM. This configuration has two steps as follows:

5.3 General keyring_okv Configuration

The keyring_okv_conf_dir system variable configures the location of the directory used by keyring_okv for its support files.

The keyring_okv_conf_dir variable must name a directory that contains the following items:

• okvclient.ora: A file that contains details of the KMIP back end (Fortanix DSM) with which keyring_okv will communicate.

• ssl: This is a directory that contains the certificate and key files required to establish a secure connection with the Fortanix DSM KMIP back end. It should have the following files:

  • CA.pem – File containing CA certificate(s) for Fortanix DSM server.

  • cert.pem – File containing the client certificate that will be used to authenticate to Fortanix DSM.

  • key.pem – File containing the private key for the client certificate mentioned above.

For example, to use the /usr/local/mysql/mysql-keyring-okv directory, the following commands (executed as root) create the directory and sets its mode and ownership:

cd /usr/local/mysql
mkdir mysql-keyring-okv
chmod 750 mysql-keyring-okv
chown mysql mysql-keyring-okv
chgrp mysql mysql-keyring-okv

Now set the keyring_okv_conf_dir system variable to tell keyring_okv where to find its configuration directory. Add the following line in the server my.cnf file (after early-plugin line you added before):

[mysqld]
keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv

For more details, refer to MySQL reference manual section General keyring_okv Configuration.

5.4 Configuring keyring_okv for Data Security Manager

Fortanix DSM supports the KMIP protocol which can be used by the keyring_okv keyring plugin (which supports KMIP 1.1) as its KMIP back end for keyring storage.

Use the following procedure to configure keyring_okv to work with Fortanix DSM.

• In the configuration directory (keyring_okv_conf_dir explained above), create a subdirectory named ssl to use for storing the required SSL certificate and key files for authenticating to Fortanix DSM.

• In the configuration directory, create a file named okvclient.ora. It should have the following format:

SERVER=<FORTANIX_DSM_URL>:5696
STANDBY_SERVER=<FORTANIX_DSM_URL>:5696

• Copy the private key file that you generated earlier for your client certificate as key.pem under the subdirectory ssl.

• Copy your client certificate file that you generated earlier for your client certificate as cert.pem under subdirectory ssl.

• Find the CA certificate for your Fortanix DSM installation and copy it into the file CA.pem under subdirectory ssl.

Please note that if your CA certificate has a chain then the complete chain must be copied into this file. For connecting to , please copy the following into the file CA.pem.

-----BEGIN CERTIFICATE-----
MIIFazCCA1OgAwIBAgIRAIIQz7DSQONZRGPgu2OCiwAwDQYJKoZIhvcNAQELBQAw
TzELMAkGA1UEBhMCVVMxKTAnBgNVBAoTIEludGVybmV0IFNlY3VyaXR5IFJlc2Vh
cmNoIEdyb3VwMRUwEwYDVQQDEwxJU1JHIFJvb3QgWDEwHhcNMTUwNjA0MTEwNDM4
WhcNMzUwNjA0MTEwNDM4WjBPMQswCQYDVQQGEwJVUzEpMCcGA1UEChMgSW50ZXJu
ZXQgU2VjdXJpdHkgUmVzZWFyY2ggR3JvdXAxFTATBgNVBAMTDElTUkcgUm9vdCBY
MTCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAK3oJHP0FDfzm54rVygc
h77ct984kIxuPOZXoHj3dcKi/vVqbvYATyjb3miGbESTtrFj/RQSa78f0uoxmyF+
0TM8ukj13Xnfs7j/EvEhmkvBioZxaUpmZmyPfjxwv60pIgbz5MDmgK7iS4+3mX6U
A5/TR5d8mUgjU+g4rk8Kb4Mu0UlXjIB0ttov0DiNewNwIRt18jA8+o+u3dpjq+sW
T8KOEUt+zwvo/7V3LvSye0rgTBIlDHCNAymg4VMk7BPZ7hm/ELNKjD+Jo2FR3qyH
B5T0Y3HsLuJvW5iB4YlcNHlsdu87kGJ55tukmi8mxdAQ4Q7e2RCOFvu396j3x+UC
B5iPNgiV5+I3lg02dZ77DnKxHZu8A/lJBdiB3QW0KtZB6awBdpUKD9jf1b0SHzUv
KBds0pjBqAlkd25HN7rOrFleaJ1/ctaJxQZBKT5ZPt0m9STJEadao0xAH0ahmbWn
OlFuhjuefXKnEgV4We0+UXgVCwOPjdAvBbI+e0ocS3MFEvzG6uBQE3xDk3SzynTn
jh8BCNAw1FtxNrQHusEwMFxIt4I7mKZ9YIqioymCzLq9gwQbooMDQaHWBfEbwrbw
qHyGO0aoSCqI3Haadr8faqU9GY/rOPNk3sgrDQoo//fb4hVC1CLQJ13hef4Y53CI
rU7m2Ys6xt0nUW7/vGT1M0NPAgMBAAGjQjBAMA4GA1UdDwEB/wQEAwIBBjAPBgNV
HRMBAf8EBTADAQH/MB0GA1UdDgQWBBR5tFnme7bl5AFzgAiIyBpY9umbbjANBgkq
hkiG9w0BAQsFAAOCAgEAVR9YqbyyqFDQDLHYGmkgJykIrGF1XIpu+ILlaS/V9lZL
ubhzEFnTIZd+50xx+7LSYK05qAvqFyFWhfFQDlnrzuBZ6brJFe+GnY+EgPbk6ZGQ
3BebYhtF8GaV0nxvwuo77x/Py9auJ/GpsMiu/X1+mvoiBOv/2X/qkSsisRcOj/KK
NFtY2PwByVS5uCbMiogziUwthDyC3+6WVwW6LLv3xLfHTjuCvjHIInNzktHCgKQ5
ORAzI4JMPJ+GslWYHb4phowim57iaztXOoJwTdwJx4nLCgdNbOhdjsnvzqvHu7Ur
TkXWStAmzOVyyghqpZXjFaH3pO3JLF+l+/+sKAIuvtd7u+Nxe5AW0wdeRlN8NwdC
jNPElpzVmbUq4JUagEiuTDkHzsxHpFKVK7q4+63SM1N95R1NbdWhscdCb+ZAJzVc
oyi3B43njTOQ5yOf+1CceWxG1bQVs5ZufpsMljq4Ui0/1lvh+wjChP4kqKOJ2qxq
4RgqsahDYVvTH9w7jXbyLeiNdd8XM2w9U/t7y0Ff/9yi0GE44Za4rF2LN9d11TPA
mRGunUHBcnWEvgJBQl9nJEiU0Zsnvgc/ubhPgXRR4Xq37Z0j4r7g1SgEEzwxA57d
emyPxgcYxn/eR44/KJ4EBs+lVDR3veyJm+kXQ99b21/+jh5Xos1AnX5iItreGCc=
-----END CERTIFICATE-----

-----BEGIN CERTIFICATE-----
MIIFjTCCA3WgAwIBAgIRANOxciY0IzLc9AUoUSrsnGowDQYJKoZIhvcNAQELBQAw
TzELMAkGA1UEBhMCVVMxKTAnBgNVBAoTIEludGVybmV0IFNlY3VyaXR5IFJlc2Vh
cmNoIEdyb3VwMRUwEwYDVQQDEwxJU1JHIFJvb3QgWDEwHhcNMTYxMDA2MTU0MzU1
WhcNMjExMDA2MTU0MzU1WjBKMQswCQYDVQQGEwJVUzEWMBQGA1UEChMNTGV0J3Mg
RW5jcnlwdDEjMCEGA1UEAxMaTGV0J3MgRW5jcnlwdCBBdXRob3JpdHkgWDMwggEi
MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCc0wzwWuUuR7dyXTeDs2hjMOrX
NSYZJeG9vjXxcJIvt7hLQQWrqZ41CFjssSrEaIcLo+N15Obzp2JxunmBYB/XkZqf
89B4Z3HIaQ6Vkc/+5pnpYDxIzH7KTXcSJJ1HG1rrueweNwAcnKx7pwXqzkrrvUHl
Npi5y/1tPJZo3yMqQpAMhnRnyH+lmrhSYRQTP2XpgofL2/oOVvaGifOFP5eGr7Dc
Gu9rDZUWfcQroGWymQQ2dYBrrErzG5BJeC+ilk8qICUpBMZ0wNAxzY8xOJUWuqgz
uEPxsR/DMH+ieTETPS02+OP88jNquTkxxa/EjQ0dZBYzqvqEKbbUC8DYfcOTAgMB
AAGjggFnMIIBYzAOBgNVHQ8BAf8EBAMCAYYwEgYDVR0TAQH/BAgwBgEB/wIBADBU
BgNVHSAETTBLMAgGBmeBDAECATA/BgsrBgEEAYLfEwEBATAwMC4GCCsGAQUFBwIB
FiJodHRwOi8vY3BzLnJvb3QteDEubGV0c2VuY3J5cHQub3JnMB0GA1UdDgQWBBSo
SmpjBH3duubRObemRWXv86jsoTAzBgNVHR8ELDAqMCigJqAkhiJodHRwOi8vY3Js
LnJvb3QteDEubGV0c2VuY3J5cHQub3JnMHIGCCsGAQUFBwEBBGYwZDAwBggrBgEF
BQcwAYYkaHR0cDovL29jc3Aucm9vdC14MS5sZXRzZW5jcnlwdC5vcmcvMDAGCCsG
AQUFBzAChiRodHRwOi8vY2VydC5yb290LXgxLmxldHNlbmNyeXB0Lm9yZy8wHwYD
VR0jBBgwFoAUebRZ5nu25eQBc4AIiMgaWPbpm24wDQYJKoZIhvcNAQELBQADggIB
ABnPdSA0LTqmRf/Q1eaM2jLonG4bQdEnqOJQ8nCqxOeTRrToEKtwT++36gTSlBGx
A/5dut82jJQ2jxN8RI8L9QFXrWi4xXnA2EqA10yjHiR6H9cj6MFiOnb5In1eWsRM
UM2v3e9tNsCAgBukPHAg1lQh07rvFKm/Bz9BCjaxorALINUfZ9DD64j2igLIxle2
DPxW8dI/F2loHMjXZjqG8RkqZUdoxtID5+90FgsGIfkMpqgRS05f4zPbCEHqCXl1
eO5HyELTgcVlLXXQDgAWnRzut1hFJeczY1tjQQno6f6s+nMydLN26WuU4s3UYvOu
OsUxRlJu7TSRHqDC3lSE5XggVkzdaPkuKGQbGpny+01/47hfXXNB7HntWNZ6N2Vw
p7G6OfY+YQrZwIaQmhrIqJZuigsrbe3W+gdn5ykE9+Ky0VgVUsfxo52mwFYs1JKY
2PGDuWx8M6DlS6qQkvHaRUo0FMd8TsSlbF0/v965qGFKhSDeQoMpYnwcmQilRh/0
ayLThlHLN81gSkJjVrPI0Y8xCVPB4twb1PFUd2fPM3sA1tJ83sZ5v8vgFv2yofKR
PB0t6JzUA81mSqM3kxl5e+IZwhYAyO0OTg3/fs8HqGTNKd9BqoUwSRBzp06JMg5b
rUCGwbCUDI0mxadJ3Bz4WxR6fyNpBK2yAinWEsikxqEt
-----END CERTIFICATE-----

5.5 Verifying keyring_okv is working

After configuration is complete, restart MySQL for it to load the keyring plugin. Look in the logs to make sure there are no errors in connecting to Fortanix DSM. To verify plugin installation, with the MySQL server running, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement. For example:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| keyring_okv | ACTIVE        |
+-------------+---------------+

5.6 Using keyring_okv plugin - UDF

If you intend to use keyring user-defined functions (UDFs) in conjunction with the keyring plugin, install the UDFs following keyring installation using the instructions in Section 8.4.4.12, General-Purpose Keyring Key-Management Functions.

5.7 Using keyring_okv plugin – Creating encrypted tables

When you create the first encrypted table - InnoDB will ask keyring_okv to generate the master key (AES-256) in Fortanix DSM. You can check this in DSM WebUI under Security Objects page. This master key will is used to encrypt tablespace keys. InnoDB also asks Fortanix DSM to generate a key (AES-256) for encrypting the table. The tablespace key is wrapped using the master key and stored alongside the encrypted table. For subsequent encrypted tables, only the tablespace key is generated and the same master key is used to wrap the tablespace key

With Fortanix DSM you will see a complete audit trail if every time the master key or tablespace key is retrieved. You will also have complete control over these keys and you can revoke access to a key or disable it, in case you want to lock down your data at rest.

Here is an example of how to create an encrypted table

CREATE DATABASE MySQL_TDE_Test;
USE MySQL_TDE_Test;
CREATE TABLE `test_encryption` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';

Rotate key from the database using the following command:

ALTER INSTANCE ROTATE INNODB MASTER KEY;

The following screenshot shows the activity logs for the MySQL application and an audit trail of the master key usage.