1. Fortanix
  2. Fortanix Data Security Manager (DSM)
  3. Integration / Use-case
  4. Transparent Data Encryption

Using Fortanix Data Security Manager for MySQL Encryption at Rest

Last Updated: 

Overview

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. See MySQL Reference manual section “Using the keyring_okv KMIP Plugin” for details.

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.

Adding App in Fortanix Data Security Manager

Start by adding an App in Fortanix DSM in an appropriate group or a new group. For instructions on how to add a group or app please see the Getting Started Guide

After you have added the application, note down its App ID by copying App UUID from the App table view. Click the icon for “Copy UUID” as shown below. You will need this App ID for the certificate.

MySQLEncry1.pngFigure 1: Copy App UUID

If an App / Client needs to authenticate to Fortanix DSM using the only certificate, then the App ID needs to be embedded in the certificate in one of the following ways:

  • Provided as the value of a custom OID in certificate 1.3.6.1.4.1.49690.1.2.1.
    Standard human-readable UUID encoding: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
  • Provided as the value of Common Name (CN).

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

Creating a client certificate with custom OID value

You can generate a self-signed certificate such that the custom OID is part of the certificate. To achieve this edit the file /etc/ssl/openssl.cnf and add the custom OID in the “new_oids” section. These sections in the file should look as follows:

oid_section             = new_oids

# To use this configuration file with the "-extfile" option of the
# "openssl x509" utility, name here the section containing the
# X.509v3 extensions to use:
# extensions            = 
# (Alternatively, use a configuration file that has only
# X.509v3 extensions in its main [= default] section.)

[ new_oids ]
my_app_id=1.3.6.1.4.1.49690.1.2.1

Now add a description in the “req_distinguished_name” section. In this section add the following line:

my_app_id = custom attribute for app id

Save the file and generate a self-signed certificate as shown below:

  1. Change directory to SDKMS_Certs and run the following command. 
    openssl req -newkey rsa:2048 -nodes -keyout private.key -x509 -days 365 -out certificate.crt

    mysql.png
    Figure 2: Create self-signed certificate mysql2.png
    Figure 3: Certificate generated

    2. This will prompt for the value of the custom attribute where you should enter the App ID you noted earlier.

The generated certificate will have the value of custom OID populated.

Examine the subject in the certificate to verify it contains the custom OID. A correctly generated certificate should look as follows (note the value of custom OID in the subject).

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 18122652583846371291 (0xfb809881cffa5fdb)
    Signature Algorithm: sha256WithRSAEncryption
        Issuer: C=US, ST=California, L=Mountain View, O=Fortanix Inc, OU=Engineering, CN=test.kmip.fortanix.com/emailAddress=test@fortanix.com/1.3.6.1.4.1.49690.1.2.1=acc15bf3-e626-47aa-9373-7b08b3f26ee8
        Validity
            Not Before: Aug  8 23:19:45 2018 GMT
            Not After : Aug  8 23:19:45 2019 GMT
        Subject: C=US, ST=California, L=Mountain View, O=Fortanix Inc, OU=Engineering, CN=test.kmip.fortanix.com/emailAddress=test@fortanix.com/1.3.6.1.4.1.49690.1.2.1=acc15bf3-e626-47aa-9373-7b08b3f26ee8
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:97:a4:5b:d4:11:ee:c6:89:e1:f8:44:39:f9:69:
                    43:be:ee:69:78:5b:32:26:53:9d:a7:46:f4:17:0e:
                    5a:dc:b4:58:23:af:69:a1:86:de:2e:c5:46:14:98:
                    b6:6a:fc:f5:26:73:f7:56:6f:60:d8:2c:52:69:c9:
                    58:2a:d6:fd:4e:6e:22:0d:8c:e5:99:01:10:70:59:
                    6c:68:a2:a8:ee:e6:37:f7:08:8a:8a:75:bb:91:2b:
                    db:ad:1c:03:56:5f:01:ae:55:ff:3a:8b:40:91:e7:
                    04:4d:49:31:76:dc:ec:9e:d5:cb:d5:73:00:4f:13:
                    f2:12:f3:45:9f:df:fc:aa:2d:5f:d4:95:b2:e9:fa:
                    ad:38:d8:36:a5:f3:99:92:e5:b4:0a:39:99:85:ee:
                    13:39:fb:8d:1c:7a:52:03:e3:86:8a:d8:24:e9:28:
                    70:18:72:e0:b5:e6:f2:66:6f:1c:1a:be:f7:23:2c:
                    e0:9f:79:2b:2e:6e:be:c6:b1:31:65:00:cb:9c:8b:
                    bd:c0:56:dc:bd:0c:24:6a:d2:20:91:5f:14:84:63:
                    ef:18:b2:de:33:a8:ec:dd:4e:a5:3f:11:7b:7d:eb:
                    a1:e1:49:fc:d7:9e:26:98:6f:cb:3b:7e:5d:7e:2d:
                    1e:34:ca:3a:f9:12:95:b2:aa:ff:40:95:e1:5e:b9:
                    a5:a3
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Key Identifier: 
                9C:74:2E:5B:16:76:F9:59:9F:E0:B5:53:C9:26:45:45:F7:4C:8D:99
            X509v3 Authority Key Identifier: 
                keyid:9C:74:2E:5B:16:76:F9:59:9F:E0:B5:53:C9:26:45:45:F7:4C:8D:99

        X509v3 Basic Constraints: 
            CA:TRUE
Signature Algorithm: sha256WithRSAEncryption
     72:95:6a:8a:4c:18:53:e9:f6:3d:87:e9:97:d2:48:fe:2b:60:
     ea:e2:ca:81:cb:9b:15:48:38:30:62:16:6b:b0:54:f6:91:2d:
     b0:72:af:36:36:39:8e:78:1f:8c:17:19:df:5c:e5:ae:4d:f4:
     ae:41:39:04:f2:95:d1:0a:99:ef:ef:63:72:5e:83:96:c1:c7:
     f1:d7:f6:45:58:23:76:3d:1a:ba:a3:08:e4:4a:a0:6a:33:8f:
     e5:50:04:b1:08:74:b3:37:9c:fd:f9:9c:5d:27:7d:63:a8:7d:
     40:3e:d5:aa:7d:a7:9e:70:79:38:91:45:68:29:0d:a8:80:42:
     f8:9b:e0:17:bb:93:9f:71:89:04:0f:39:d0:2e:3c:10:62:44:
     6b:41:5d:e5:78:42:50:c5:f7:ee:bc:a8:5e:90:01:ad:3c:f2:
     27:f2:81:16:ba:1e:79:d8:c4:09:cb:01:fd:71:11:9f:91:14:
     72:71:0f:f1:d3:b0:4d:91:78:dd:12:fb:fd:d6:22:93:15:67:
     df:4e:da:df:74:de:68:95:d7:d8:70:48:e2:5f:bc:ec:b2:0f:
     bb:14:83:ad:c9:f9:a0:81:0d:a8:68:64:77:db:5a:71:4a:8b:
     8f:91:d6:ce:e1:33:42:ba:98:76:a1:cd:89:8e:3a:cb:aa:b1:
     8e:ca:42:af

Creating a client certificate with App ID as CN

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

Generate a self-signed certificate as shown in the "Creating a client certificate with custom OID value" section. When prompted for Common Name, you should enter the App ID you noted earlier.

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/emailAddress=test@fortanix.com
        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/emailAddress=test@fortanix.com
        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:

Setting App Authentication Method as certificate

After you have the certificate, you will need to change the authentication method for your app in Fortanix DSM to use a certificate instead of an API key. To change the authentication method, go to the application detail page of your app, navigate to INFO tab, and open the Change authentication method drop-down. Select the method as Certificate and click Save. You will be prompted to upload a certificate. Upload your certificate and click Update. Now your app is set to authenticate using the certificate you created.

Configuring Encryption in MySQL

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

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

NOTE
The location of the configuration file varies based on the version.
Example: 5.7 = /etc/my.cnf
                 8.0 = /etc/mysql/mysql.conf.d/mysqld.cnf
[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.

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:

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.
NOTE
The configuration directory used by keyring_okv as the location for its support files should have a restrictive mode and be accessible only to the account used to run the MySQL server.

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 please see MySQL reference manual section General keyring_okv Configuration

NOTE
Apparmor/SELINUX may cause errors where the keyring plugin directories may not get loaded during database startup. Necessary corrective actions must be taken.

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:
NOTE
STANDBY_SERVER is optional.
SERVER=sdkms.fortanix.com:5696
STANDBY_SERVER=sdkms.fortanix.com:5696
NOTE
For your on-prem Fortanix DSM installation, replace sdkms.fortanix.com with your Fortanix DSM hostname or IP address.
  • 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 sdkms.fortanix.com, 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-----

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        |
+-------------+---------------+

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 6.5.4.8, “General-Purpose Keyring Key-Management Functions”.

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.

2_audit_log.png Figure 4: MySQL activity logs

Comments

Article is closed for comments.

Was this article helpful?
1 out of 1 found this helpful