Using SmartKey™ for MySQL Encryption at Rest

Updated: Oct 15, 2018

Overview

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

Cryptographically secure generation and secure management of encryption keys is required for true security of data at rest encrypted by MySQL. Equinix SmartKey with its KMIP support provides a secure and flexible solution for this.

MySQL KMIP key ring plugin authenticates to a KMIP enabled key management server using client certificate. SmartKey supports clients / apps to authenticate using API Key, App Id and certificate or just certificate.

In this article we will describe how to setup an app in SmartKey for MySQL to integrate with SmartKey.

Adding App in SmartKey

Start by adding an App in SmartKey in an appropriate group or a new group. For instructions of how to add a group or app please use Getting Started Guide

Once you have added the application, note down its App-Id by copying App UUID from App table view by clicking on icon for “Copy UUID” as shown below. You will need this App-Id for the certificate.

ec-created

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

  • Provided as 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 value of CN

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

Creating 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 file /etc/ssl/openssl.cnf and add the custom oid in “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 “req_distinguished_name” section. In this section add a line as follows

my_app_id = custom attribute for app id

Save the file and generate self-signed certificate as usual. This will prompt for the value of custom attribute where you should enter the App Id you noted earlier.

Generated certificate will have the value of custom OID populated.

Examine the subject in certificate to verify it contains the custom OID. A correctly generated certificate should look as follows (note the value of custom OID in 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 client certificate with App Id as CN

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

Generate self-signed certificate as usual. When prompted for Common Name, you should enter the App Id you noted earlier.

Generated certificate will have App Id as CN.

Examine the subject in 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

Once you have the certificate, you will need to change the authentication method for your app in SmartKey to use certificate instead of API key. To change the authentication method, go to the application detail page of your app, navigate to the Info tab, and open the “Change authentication method” drop-down. Select method as “Certificate” and click Save. You will be prompted to upload a certificate. Upload your certificate and click on 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):

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

This enables “keyring_okv” plugin. However, the plugin requires additional configuration to point to SmartKey and the KMIP server. In the following section, we will explain this configuration. MySQL will use the certificate you created in the earlier step to authenticate to SmartKey.

Configuring the keyring_okv KMIP Plugin

The plugin keyring_okv needs to be configured to point to SmartKey and point to the credentials that will be used to authenticate to SmartKey. 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 that contains the following items:

  • okvclient.ora: A file that contains details of the KMIP back end (SmartKey) 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 SmartKey KMIP back end. It should have the following files:

    • CA.pem – File containing CA certificate(s) for SmartKey server
    • cert.pem – File containing the client certificate that will be used to authenticate to SmartKey
    • 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 set 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

Configuring keyring_okv for SmartKey

Equinix SmartKey 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 SmartKey.

  • 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 SmartKey.
  • In the configuration directory, create a file named okvclient.ora. It should have following format:

NOTE: STANDBY_SERVER is optional.

SERVER=amer.smartkey.io:5696
STANDBY_SERVER=amer.smartkey.io:5696
  • Copy your private key file that you generated earlier for your client certificate as key.pem under 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 SmartKey installation and copy it into 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 amer.smartkey.io, please copy the following into CA.pem

-----BEGIN CERTIFICATE-----
MIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9w0BAQUFADBh
MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3
d3cuZGlnaWNlcnQuY29tMSAwHgYDVQQDExdEaWdpQ2VydCBHbG9iYWwgUm9vdCBD
QTAeFw0wNjExMTAwMDAwMDBaFw0zMTExMTAwMDAwMDBaMGExCzAJBgNVBAYTAlVT
MRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5j
b20xIDAeBgNVBAMTF0RpZ2lDZXJ0IEdsb2JhbCBSb290IENBMIIBIjANBgkqhkiG
9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4jvhEXLeqKTTo1eqUKKPC3eQyaKl7hLOllsB
CSDMAZOnTjC3U/dDxGkAV53ijSLdhwZAAIEJzs4bg7/fzTtxRuLWZscFs3YnFo97
nh6Vfe63SKMI2tavegw5BmV/Sl0fvBf4q77uKNd0f3p4mVmFaG5cIzJLv07A6Fpt
43C/dxC//AH2hdmoRBBYMql1GNXRor5H4idq9Joz+EkIYIvUX7Q6hL+hqkpMfT7P
T19sdl6gSzeRntwi5m3OFBqOasv+zbMUZBfHWymeMr/y7vrTC0LUq7dBMtoM1O/4
gdW7jVg/tRvoSSiicNoxBN33shbyTApOB6jtSj1etX+jkMOvJwIDAQABo2MwYTAO
BgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUA95QNVbR
TLtm8KPiGxvDl7I90VUwHwYDVR0jBBgwFoAUA95QNVbRTLtm8KPiGxvDl7I90VUw
DQYJKoZIhvcNAQEFBQADggEBAMucN6pIExIK+t1EnE9SsPTfrgT1eXkIoyQY/Esr
hMAtudXH/vTBH1jLuG2cenTnmCmrEbXjcKChzUyImZOMkXDiqw8cvpOp/2PV5Adg
06O/nVsJ8dWO41P0jmP6P6fbtGbfYmbW0W5BjfIttep3Sp+dWOIrWcBAI+0tKIJF
PnlUkiaY4IBIqDfv8NZ5YBberOgOzW6sRBc4L0na4UU+Krk2U886UAb3LujEV0ls
YSEY1QSteDwsOoBrp+uvFRTp2InBuThs4pFsiv9kuXclVzDAGySj4dzp30d8tbQk
CAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
MIIElDCCA3ygAwIBAgIQAf2j627KdciIQ4tyS8+8kTANBgkqhkiG9w0BAQsFADBh
MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3
d3cuZGlnaWNlcnQuY29tMSAwHgYDVQQDExdEaWdpQ2VydCBHbG9iYWwgUm9vdCBD
QTAeFw0xMzAzMDgxMjAwMDBaFw0yMzAzMDgxMjAwMDBaME0xCzAJBgNVBAYTAlVT
MRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxJzAlBgNVBAMTHkRpZ2lDZXJ0IFNIQTIg
U2VjdXJlIFNlcnZlciBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEB
ANyuWJBNwcQwFZA1W248ghX1LFy949v/cUP6ZCWA1O4Yok3wZtAKc24RmDYXZK83
nf36QYSvx6+M/hpzTc8zl5CilodTgyu5pnVILR1WN3vaMTIa16yrBvSqXUu3R0bd
KpPDkC55gIDvEwRqFDu1m5K+wgdlTvza/P96rtxcflUxDOg5B6TXvi/TC2rSsd9f
/ld0Uzs1gN2ujkSYs58O09rg1/RrKatEp0tYhG2SS4HD2nOLEpdIkARFdRrdNzGX
kujNVA075ME/OV4uuPNcfhCOhkEAjUVmR7ChZc6gqikJTvOX6+guqw9ypzAO+sf0
/RR3w6RbKFfCs/mC/bdFWJsCAwEAAaOCAVowggFWMBIGA1UdEwEB/wQIMAYBAf8C
AQAwDgYDVR0PAQH/BAQDAgGGMDQGCCsGAQUFBwEBBCgwJjAkBggrBgEFBQcwAYYY
aHR0cDovL29jc3AuZGlnaWNlcnQuY29tMHsGA1UdHwR0MHIwN6A1oDOGMWh0dHA6
Ly9jcmwzLmRpZ2ljZXJ0LmNvbS9EaWdpQ2VydEdsb2JhbFJvb3RDQS5jcmwwN6A1
oDOGMWh0dHA6Ly9jcmw0LmRpZ2ljZXJ0LmNvbS9EaWdpQ2VydEdsb2JhbFJvb3RD
QS5jcmwwPQYDVR0gBDYwNDAyBgRVHSAAMCowKAYIKwYBBQUHAgEWHGh0dHBzOi8v
d3d3LmRpZ2ljZXJ0LmNvbS9DUFMwHQYDVR0OBBYEFA+AYRyCMWHVLyjnjUY4tCzh
xtniMB8GA1UdIwQYMBaAFAPeUDVW0Uy7ZvCj4hsbw5eyPdFVMA0GCSqGSIb3DQEB
CwUAA4IBAQAjPt9L0jFCpbZ+QlwaRMxp0Wi0XUvgBCFsS+JtzLHgl4+mUwnNqipl
5TlPHoOlblyYoiQm5vuh7ZPHLgLGTUq/sELfeNqzqPlt/yGFUzZgTHbO7Djc1lGA
8MXW5dRNJ2Srm8c+cftIl7gzbckTB+6WohsYFfZcTEDts8Ls/3HB40f/1LkAtDdC
2iDJ6m6K7hQGrn2iWZiIqBtvLfTyyRRfJs8sjX7tN8Cp1Tm5gr8ZDOo0rwAhaPit
c+LJMto4JQtV05od8GiG7S5BNO98pVAdvzr508EIDObtHopYJeS4d60tbvVS3bR0
j6tJLp07kzQoH3jOlOrHvdPJbRzeXDLz
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
MIIHEzCCBfugAwIBAgIQCstMGgsFf8yLN/xkPJ00zjANBgkqhkiG9w0BAQsFADBN
MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMScwJQYDVQQDEx5E
aWdpQ2VydCBTSEEyIFNlY3VyZSBTZXJ2ZXIgQ0EwHhcNMTgwODAyMDAwMDAwWhcN
MjAwODA2MTIwMDAwWjBrMQswCQYDVQQGEwJVUzETMBEGA1UECBMKQ2FsaWZvcm5p
YTEUMBIGA1UEBxMLU2FudGEgQ2xhcmExFjAUBgNVBAoTDUVxdWluaXgsIEluYy4x
GTAXBgNVBAMTEGFtZXIuc21hcnRrZXkuaW8wggEiMA0GCSqGSIb3DQEBAQUAA4IB
DwAwggEKAoIBAQDO4TF4T2edUZtaioRXm9U6xtigpFI6JqfDGh4L5cln1VzZapom
49pgbjvWdBHmNK50uW8xxBFrx3JPWlOnZFWn3xjJo5eL++2PlImUFMFYKH60uQZ3
2BXKH5kJcc0JyLhx/+bVRKcHe4iB/LeEEhbfI0FBtEd2cOp74T3CCh7CK79+svg6
6zdeG9e1BNRVqQ1E3i8dZ0QQAV6QfHFreSTpT6uf9R2vnJcjHjt0ewBFodQlwMsW
PgVmmSncrq0J+rVLTC9XSnu5Hko10GhFLXPE9Xhm+Ba6Oxe0Gx+hCRBrZgxXk7HV
ccTouBwu2HBjfgB+5bqLiinCWgG6gaziZ8pDAgMBAAGjggPPMIIDyzAfBgNVHSME
GDAWgBQPgGEcgjFh1S8o541GOLQs4cbZ4jAdBgNVHQ4EFgQUDWIpI3z4Ea5gZsdT
i+o7TeV8BTEwgZAGA1UdEQSBiDCBhYILc21hcnRrZXkuaW+CD3d3dy5zbWFydGtl
eS5pb4IQYXBwcy5zbWFydGtleS5pb4IQYW1lci5zbWFydGtleS5pb4IUd3d3LmFt
ZXIuc21hcnRrZXkuaW+CFWFwcHMuYW1lci5zbWFydGtleS5pb4IUYXBpLmFtZXIu
c21hcnRrZXkuaW8wDgYDVR0PAQH/BAQDAgWgMB0GA1UdJQQWMBQGCCsGAQUFBwMB
BggrBgEFBQcDAjBrBgNVHR8EZDBiMC+gLaArhilodHRwOi8vY3JsMy5kaWdpY2Vy
dC5jb20vc3NjYS1zaGEyLWc2LmNybDAvoC2gK4YpaHR0cDovL2NybDQuZGlnaWNl
cnQuY29tL3NzY2Etc2hhMi1nNi5jcmwwTAYDVR0gBEUwQzA3BglghkgBhv1sAQEw
KjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNlcnQuY29tL0NQUzAIBgZn
gQwBAgIwfAYIKwYBBQUHAQEEcDBuMCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5k
aWdpY2VydC5jb20wRgYIKwYBBQUHMAKGOmh0dHA6Ly9jYWNlcnRzLmRpZ2ljZXJ0
LmNvbS9EaWdpQ2VydFNIQTJTZWN1cmVTZXJ2ZXJDQS5jcnQwDAYDVR0TAQH/BAIw
ADCCAX4GCisGAQQB1nkCBAIEggFuBIIBagFoAHcApLkJkLQYWBSHuxOizGdwCjw1
mAT5G9+443fNDsgN3BAAAAFk+/Cc+wAABAMASDBGAiEAkJ/u42hX9ydluI/D4Gg0
FhINCYkhl7GDrayyazzdjpcCIQD62FYAJDXy9jFqvU3lT9ND9xuXif4yQTl4wbIU
HUWIRQB2AId1v+dZfPiMQ5lfvfNu/1aNR1Y2/0q1YMG06v9eoIMPAAABZPvwncoA
AAQDAEcwRQIhAKo72wznt52W2yLeCvw7eW9ZHSmYuMxm8gK59H1yldauAiBk9LbO
lLAax55xI7ptTMS6DmfHTcDYL45zB3UjJBjnogB1ALvZ37wfinG1k5Qjl6qSe0c4
V5UKq1LoGpCWZDaOHtGFAAABZPvwnSAAAAQDAEYwRAIgPghJtENxemK4bADCWjpm
mu1AizoICK1GBVu6hLiT9RkCIDGAKSH2QX97fImkga7AnMtIWgpCGyfFpN4JvQDN
IjDuMA0GCSqGSIb3DQEBCwUAA4IBAQDUaLchGbWamHUmjM4p2y4S06udilfyiZ46
LngqET9M8538F+t4Z4XanZApu7Kx51YPwcX+IyFUzH7sdXKxaPXvp8m1XEtpLucF
zSZEoOJnlejkulzUnMoMdHAWk5ds71aavLgBiqZQOzm8e7qnEhASlwfYHcxJVUg1
cfPbTztTs3U6iIx2hg3w1ZoVen9RFu3G7gdm1vedka36746l1nFI/w3NkUD2gJKK
mzF8lbLIdbGG5p/7frEppnJHtaCnamz0Jkqu3bTcttQnw7ABQoyu4BmJDrpFMjy2
AoHJL6HC3Xgp97LklhJHYL2VS1doX0nj9f1Y2QxuTaaIdL3fL4TH
-----END CERTIFICATE-----

Verifying keyring_okv is working

After configuration is complete, restart MySQL for it to load keyring plugin. Look in the logs to make sure there are no errors in connecting to SmartKey. 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 master key (AES-256) in SmartKey. You can check this in SmartKey WebUI under Security Objects page. This master key will is used to encrypt tablespace keys. InnoDB also asks SmartKey to generate a key (AES-256) for encrypting 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 SmartKey you will see a complete audit trail if every time the master key or tablespace key is retrieved. You will also have complete control on 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 you 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';

Following screen shot shows the activity logs for the MySQL application and an audit trail of master key usage.

ec-created