Chapter 1: About This Manual

Using AKM to encrypt data in MySQL

MySQL Enterprise supports KMIP integration at the database level. Alliance Key Manager supports KMIP 1.1. Customers using MySQL can create a new key on AKM using the KMIP interface. They can then use this encryption key as a MySQL “master key” to encrypt MySQL database keys.

Who is this for?

This guide is designed to help MySQL administrators and project managers use an external encryption key stored on Alliance Key Manager as the MySQL “master key”. An existing encryption key on AKM may be retrieved, or a new encryption key can be created from MySQL and stored on AKM for later retrieval.

Resources

The following documents provide additional information on the installation and use of Alliance Key Manager:

Notices

This product and documentation is covered by U.S. and International copyright law. This product may incorporate software licensed under one or more open source license agreements. Government users please note that this product is provided under restricted government use license controls. Please refer to the AKM End User License Agreement for more information.

Change log

The following table provides information on the changes to this documentation:

Version Date Description
4.6.2.001 11/18/2019 Initial Release

Chapter 2: Preparation

Checklist

  • Install and set up the primary AKM server (instructions are located in platform specific deployment guides).
  • A functioning instance of MySQL Enterprise or CGE.
  • Download admin authentication certificates from the AKM server. /home/admin/downloads
  • The IP address of the AKM server and KMIP port number (the default is 5696).

Certificates

MySQL and the AKM server use certificate and private keys to establish a secure TLS connection and perform authentication. You will need to store the following certificates and private keys on your MySQL server in order to authenticate with the AKM server:

  • The primary AKM’s certificate authority (CA) certificate in .pem format (AKMRootCACertificate.pem)

  • Admin (Crypto Officer) certificate (AKMAdminCertificate.pem) and private key (AKMAdminPrivateKey) in .pem format.

These certificates and private keys are generated on initialization and stored on the AKM server in /home/admin/downloads and can be retrieved through the webUI or sftp.

SECURITY ALERT: Private key files must be protected during creation, distribution, and storage to prevent loss. The loss of these files will compromise the security of the AKM server. Depending on the file format, the private key files may be bundled with a certificate or they may be separate files. Transfer the private key files by sharing them over a secure network, placing them in a password-protected zip file, sending them using SFTP, or another secure method. Use the same level of care you would employ to protect encryption keys, including encryption. In the event the private keys are compromised or lost, you should immediately replace the certificate authority on the AKM server and all client certificates in that chain of trust.

 

Chapter 3: Install and Configure

Create folders for KMIP config

You will need to create a directory to store your KMIP config file and store certificates needed for the AKM admin / client connection.

cd /etc/mysql
sudo mkdir -p keyring_okv/ssl

Set primary and failover servers

Here you will need to specify a primary key server using IP address, as shown below. If you would like to establish a high availability failover key server you can do so here under “STANDBY_SERVER”

cd keyring_okv
vi okvclient.ora
  SERVER=x.x.x.x:5696
  STANDBY_SERVER=x.x.x.x:5696

Copy admin cert, private key and CA cert to keyring_okv/ssl directory

You can SCP the admin certificate, private key and CA certificate to the directory you would have created in a previous step. The following certificates should be placed in the directory path below. AKMAdminCertificate.pem AKMAdminPrivateKey.pem AKMRootCACertificate.pem

cd /etc/mysql/keyring_okv/ssl/

MySQL expects the certificates to have specific names. The next step will be to create symbolic links so that the certificates are recognized by MySQL.

ln -s AKMAdminCertificate.pem cert.pem
ln -s AKMAdminPrivateKey.pem key.pem
ln -s AKMRootCACertificate.pem CA.pem

OR rename the files

mv AKMAdminCertificate.pem cert.pem
mv AKMAdminPrivateKey.pem key.pem
mv AKMRootCACertificate.pem CA.pem

You will want to check to make sure the certificate files are correct with hte command below.

ls -la
-rwxr-xr-x 1 mysql mysql 4471 Jul 19 23:43 AKMAdminCertificate.pem
-rwxr-xr-x 1 mysql mysql 1704 Jul 19 23:43 AKMAdminPrivateKey.pem
-rwxr-xr-x 1 mysql mysql 1375 Jul 19 23:43 AKMRootCACertificate.pem
lrwxrwxrwx 1 mysql mysql   24 Aug 27 17:24 CA.pem -> AKMRootCACertificate.pem
lrwxrwxrwx 1 mysql mysql   23 Aug 27 17:24 cert.pem -> AKMAdminCertificate.pem
lrwxrwxrwx 1 mysql mysql   22 Aug 27 17:24 key.pem -> AKMAdminPrivateKey.pem

Set permissions

The keyring_okv directory you created earlier for certificate storage must have permissions set at this point.

cd /etc/mysql
chmod -R 750 keyring_okv
chown -R mysql:mysql keyring_okv

Edit MySQL config to load KMIP plugin

The MySQL config file “my.cnf” will need to be edited to load the KMIP plugin and tell MySQL where to find the configuration. Append to bottom of file

vi /etc/mysql/my.cnf
  [mysqld]
  early-plugin-load=keyring_okv.so
  keyring_okv_conf_dir=/etc/mysql/keyring_okv

Restart mysql

To apply the configuration and test you will need to restart MySQL.

sudo service mysql restart

Check log output

sudo tail /var/log/mysql/error.log

Example output:

2019-08-27T20:28:48.650874Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.16-commercial) starting as process 3782
2019-08-27T20:28:48.656919Z 0 [Warning] [MY-011383] [Server] Plugin keyring_okv reported: 'Could not find entry for standby server in configuration file /etc/mysql/keyring_okv/okvclient.ora'
2019-08-27T20:28:49.128976Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-08-27T20:28:49.147703Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.16-commercial'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Enterprise Server - Commercial.
2019-08-27T20:28:49.278513Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

Chapter 4: Verify installation

Login to mysql using the root user established during installation to verify.

mysql -u root -p

Verify KMIP plugin is working:

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

Enable global table encryption default if you want

SET GLOBAL default_table_encryption=ON;

Or set it per-table as follows:

Create a database

CREATE database my_test_db;

Switch to it

USE my_test_db;

Create the table

CREATE TABLE test_table (my_column INT) ENCRYPTION = 'Y';

Rotate master key if you want

ALTER INSTANCE ROTATE INNODB MASTER KEY;

Display databases & tables that are using encryption

SELECT SPACE, NAME, SPACE_TYPE, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE ENCRYPTION='Y'\G
  *************************** 1. row ***************************
    SPACE: 2
        NAME: enc_test/t1
  SPACE_TYPE: Single
  ENCRYPTION: Y
  *************************** 2. row ***************************
    SPACE: 4
        NAME: my_test_db/test_table
  SPACE_TYPE: Single
  ENCRYPTION: Y

VALID CERT LOG:

2019-09-26T20:09:05.832515Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.17-commercial)  MySQL Enterprise Server - Commercial.
2019-09-26T20:09:06.299256Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17-commercial) starting as process 26034
2019-09-26T20:09:06.305938Z 0 [Warning] [MY-011383] [Server] Plugin keyring_okv reported: 'Could not find entry for standby server in configuration file /etc/mysql/keyring_okv/okvclient.ora'
2019-09-26T20:09:07.495168Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-09-26T20:09:07.521215Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17-commercial'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Enterprise Server - Commercial.
2019-09-26T20:09:07.558559Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

VALID CERTS WITH STANDBY LOG:

2019-10-07T21:04:45.290539Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-10-07T21:04:45.307903Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17-commercial'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Enterprise Server - Commercial.
2019-10-07T21:04:45.410846Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2019-10-07T21:05:51.410598Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.17-commercial)  MySQL Enterprise Server - Commercial.
2019-10-07T21:05:51.877297Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17-commercial) starting as process 3811
2019-10-07T21:05:53.865910Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-10-07T21:05:53.884302Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17-commercial'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Enterprise Server - Commercial.
2019-10-07T21:05:54.125645Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

INVALID CERT ERROR(Same for invalid/expired):

sqltester@mysql-ubuntu16:~$ sudo tail /var/log/mysql/error.log
2019-09-26T20:06:28.239439Z 0 [ERROR] [MY-011377] [Server] Plugin keyring_okv reported: 'keyring_okv initialization failure. Please check that the keyring_okv_conf_dir points to a readable directory and that the directory contains Oracle Key Vault configuration file and ssl materials. Please also check that Oracle Key Vault is up and running.'
2019-09-26T20:06:28.239449Z 0 [ERROR] [MY-010202] [Server] Plugin 'keyring_okv' init function returned error.
2019-09-26T20:06:30.174499Z 1 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring plugin is loaded.
2019-09-26T20:06:30.174510Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./my_test_db/test_table.ibd can't be decrypted, please confirm the keyfile is match and keyring plugin is loaded.
2019-09-26T20:06:30.174604Z 1 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring plugin is loaded.
2019-09-26T20:06:30.174613Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./enc_test/t1.ibd can't be decrypted, please confirm the keyfile is match and keyring plugin is loaded.
2019-09-26T20:06:30.529347Z 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_new failed
2019-09-26T20:06:30.548661Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17-commercial'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Enterprise Server - Commercial.
2019-09-26T20:06:30.723368Z 0 [Warning] [MY-011302] [Server] Plugin mysqlx reported: 'Failed at SSL configuration: "SSL_CTX_new failed"'
2019-09-26T20:06:30.723721Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060