Oracle MySQL and Entrust KeyControl: Integration Guide
Table of Contents
- Introduction
-
Procedures
- Installation overview
- Install and configure Entrust KeyControl
- Create the KMIP Tenant for Oracle MySQL in KeyControl
- Create the KMIP Certificates
- Install the Oracle MySQL server
- Install the keyring_okv plugin
- Import the KeyControl KMIP Certificates to the keyring_okv plugin
- Verify that the keyring_okv plugin is working
- Use keyring_okv plugin to create encrypted tables
- Test that encryption KeyControl is working
- Secure the MySQL database
Introduction
This document describes the integration of Oracle MySQL Enterprise Server with the Entrust KeyControl Key Management Solution (KMS). Entrust KeyControl can serve as a KMS in Oracle MySQL using the open standard Key Management Interoperability Protocol (KMIP).
Documents to read first
This guide describes how to configure the Entrust KeyControl server as a KMS in Oracle MySQL.
To install and configure the Entrust KeyControl server as a KMIP server, see the
Entrust KeyControl nshield HSM Integration Guide
.
You can access this in the Entrust Document Library.
Also refer to the Oracle MySQL online documentation .
Requirements
-
Entrust KeyControl version 5.5 or later.
An Entrust KeyControl license is required for the installation. You can obtain this license from your Entrust KeyControl and Oracle MySQL account team or through Entrust KeyControl customer support.
-
MySQL Enterprise Server 8.0.29 or later.
High-availability considerations
The Entrust KeyControl solution uses an active-active deployment, which provides high-availability capability to manage encryption keys. Entrust recommends this deployment configuration. In an active-active cluster, changes made to any KeyControl node in the cluster are automatically reflected on all nodes in the cluster. For information about the Entrust KeyControl solution, see the Entrust KeyControl Product Overview .
Product configuration
The integration between the Oracle MySQL Enterprise Server, Entrust KeyControl, and nshield HSM has been successfully tested in the following configurations:
Product | Version |
---|---|
CentOS Linux 8 |
4.18.0-383.el8.x86_64 |
Oracle MySQL Enterprise Server |
8.0.29 |
Entrust KeyControl |
5.5 |
MySQL Keyring_okv library |
1.10 |
Procedures
Installation overview
Follow these steps to integrate Oracle MySQL Enterprise Server with Entrust KeyControl.
Install and configure Entrust KeyControl
Follow the installation and setup instructions in the
Entrust KeyControl nshield HSM Integration Guide
.
You can access this in the Entrust Document Library.
Make sure the Entrust KeyControl tenant gets created and KMIP certificates are generated for OracleMySQL. These certificates are used in the configuration of the KMS described below.
Create the KMIP Tenant for Oracle MySQL in KeyControl
To use external key management, MySQL requires an external key management server such as the Entrust KeyControl server. Certificates are required to facilitate the KMIP communications from the KeyControl server to Oracle MySQL and conversely. To be able to create these certificates, you need to have the KMIP tenant for the application in KeyControl.
Now that Entrust KeyControl is installed, create a KMIP Tenant in KeyControl for Oracle MySQL:
-
Log in to the KeyControl instance.
Point your browser to the KeyControl administration URL: http://xx.xxx.xx.xxx
-
Log in as secroot and go to the KMIP page of the application:
-
Select Actions > Create a KMIP Tenant .
The Create a KMIP Tenant dialog appears.
-
In the About tab, enter the tenant name information:
-
For Name , enter OracleMySQL .
-
Optionally, enter a Description .
-
-
Select Next .
-
In the Admin tab, provide an Active Directory:
-
For Active Directory , select Other Active Directory .
-
In Active Directory Domain , select "+" .
The KMIP Active Directory Domain dialog appears.
-
For Domain Name , enter the name of the domain. For example, : example.com .
-
In Domain Controllers , select "+" .
The Add Domain Controller dialog appears.
For Server URL , select LDAP and enter the FQDN/IP of the Active directory server: xx.xxx.xx.xx .
Then select Save and Close .
-
Select Save and Close on the KMIP Active Directory Domain dialog.
-
For Admin , select User .
-
For Name , enter the name of the Active Directory user that will be the administrator of the tenant: [email protected] .
-
For Email , enter the email of the administrator user.
-
Select Create .
The new KMIP tenant is created and appears in the list of tenants.
-
-
Select the tenant to see its details.
-
Copy the Tenant Login URL.
The tenant login URL will be used to log in to the Tenant Administration page in KeyControl.
Create the KMIP Certificates
To be able to establish trust between the KeyControl and Oracle MySQL, you must create certificates in KeyControl and upload/import them into the configuration of Oracle MySQL.
Important
|
Entrust tested using certificates without password protection. The MySQL online documentation describes the steps needed to use a password-protected keyring_okv key, see Password-Protecting the keyring_okv Key File . |
-
Access the KeyControl web interface using the Tenant Administration URL you copied in the previous section.
-
Log in using the Tenant administrator user that you configured during the tenant creation process: [email protected] . Provide the user Active Directory password.
-
Select Security > Client Certificates .
The Manage Client Certificate dialog appears.
-
Select "+" on the right to create a new certificate.
The Create Client Certificate dialog appears.
-
In the Create Client Certificate dialog, enter the following information:
-
For Certificate Name , enter a name for the certificate: keyringokv .
-
For Certificate Expiration , set the date on which you want the certificate to expire.
-
Select Create .
The new certificate appears in the Manage Client Certificate dialog.
-
-
Select the certificate and select Download to download the certificate.
The
certname_<datetimestamp>.zip
downloads. This file contains a user certification/key file calledcertname.pem
and a server certification file calledcacert.pem
. -
Unzip the file so that you have these files available to upload to the MySQL server.
After you create and download these certificates, you need to upload or import them into the MySQL server. First, Install the Oracle MySQL server .
Install the Oracle MySQL server
The process for installing the Oracle MySQL Enterprise Edition depends on the operating system on which you are installing it. See the Oracle online documentation for details on how to install Oracle MySQL Enterprise Edition in your environment.
Install the keyring_okv plugin
The
keyring_okv
plugin is a KMIP 1.1 plugin for KMIP-compatible back-end keyring storage products, such as Entrust KeyControl.
It is available in MySQL Enterprise Edition distributions.
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, on Unix and Unix-like systems, 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
sudo mkdir -p mysql/mysql-keyring-okv/ssl
sudo chmod -R 750 mysql
sudo chown -R mysql mysql
sudo chgrp -R mysql mysql
To be usable during the server startup process, the
keyring_okv
plugin must be loaded using the
--early-plugin-load
option.
Also, set the
keyring_okv_conf_dir
system variable to tell
keyring_okv
where to find its configuration directory.
Edit the
/etc/my.cnf
file and add the plugin into the
mysqld
section:
[mysqld]
early-plugin-load=keyring_okv.so
keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv
Import the KeyControl KMIP Certificates to the keyring_okv plugin
The certificates must be installed before running the
keyring_okv
plugin, so that the plugin can be initialized.
-
Import the certificates into the configuration directory for the
keyring_okv
plugin.The following files need to be imported:
-
A
<cert_name>.pem
file that includes both the client certificate and private key. The administrator needs to open this single file and paste the two sections of the file into thecert.pem
andkey.pem
files in the/usr/local/mysql/mysql-keyring-okv/ssl
directory.-
The client certificate section of the
<cert_name>.pem
file includes the lines"-----BEGIN CERTIFICATE-----"
and"-----END CERTIFICATE-----"
and all text between them.Open or create
/usr/local/mysql/mysql-keyring-okv/ssl/cert.pem
and paste"-----BEGIN CERTIFICATE-----"
and"-----END CERTIFICATE-----"
and all text between them into this file. Make sure it has a carriage return at the end of the file. -
The private key section of the
<cert_name>.pem
file includes the lines"-----BEGIN PRIVATE KEY-----"
and"-----END PRIVATE KEY-----"
and all text in between them.Open or create
/usr/local/mysql/mysql-keyring-okv/ssl/key.pem
and paste"-----BEGIN CERTIFICATE-----"
and"-----END CERTIFICATE-----"
and all text between them into this file. Make sure it has a carriage return at the end of the file.
-
-
A
cacert.pem
file, which is the root certificate for the KMS cluster. It is always namedcacert.pem
.This file needs to be copied to
/usr/local/mysql/mysql-keyring-okv/ssl/CA.pem
.
-
-
In the configuration directory, create a file named
okvclient.ora
. It should have following format:SERVER=xxx.xxx.xxx.xxx:5696 STANDBY_SERVER=xxx.xxx.xxx.xxx:5696
STANDBY_SERVER
is optional.For example:
SERVER=198.51.100.20:5696 STANDBY_SERVER=198.51.100.21:5696
-
Set the permissions on these files:
cd /usr/local/mysql/mysql-keyring-okv sudo chmod -R 750 mysql . sudo chown -R mysql . sudo chgrp -R mysql .
-
If the firewall is running open up the firewall for port 5696.
As the root user on the mysql server:
% firewall-cmd --zone=public --add-port=5696/tcp --permanent % firewall-cmd --zone=public --add-port=5696/udp --permanent % firewall-cmd --reload
-
Disable selinux the next time the server reboots.
To do this, in the
/etc/selinux/config
file setSELINUX=disabled
.To disable on the current shell:
% sudo setenforce 0
-
After completing the preceding procedure, restart the MySQL server:
% sudo systemctl restart mysqld % sudo systemctl status mysqld
It loads the
keyring_okv
plugin, which uses the files in its configuration directory to communicate with KeyControl.
Verify that the keyring_okv plugin is working
After configuration is complete and you restarted MySQL to load the
keyring_okv
plugin, look in the
/varlog/mysqld.log
logs to make sure there are no errors when connecting to KeyControl.
To verify the 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 |
+-------------+---------------+
1 row in set (0.00 sec)
Use keyring_okv plugin to create encrypted tables
When you create the first encrypted table, InnoDB will ask
keyring_okv
to generate the primary key (AES-256) in KeyControl.
This primary key is used to encrypt tablespace keys.
You can check the primary key in the Tenant KeyControl web interface using the
Objects
page.
InnoDB also asks KeyControl to generate a key (AES-256) for the encrypting table. The tablespace key is wrapped using the primary key and stored alongside the encrypted table. For subsequent encrypted tables, only the tablespace key is generated and the same primary key is used to wrap the tablespace key.
With KeyControl, you will see a complete audit trail if every time the primary key or tablespace key is retrieved. You will have complete control on these keys. You can revoke access to a key or disable it, to lock down your data at rest.
To create an encrypted table:
-
Log in into the MySQL database:
% mysql -u root -p<password>
-
Create the encrypted table with the following SQL:
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';
The Objects tab in Tenant KeyControl shows the that the key was created. For example:
You can also check the Audit Logs tab. You should see all the KMIP operations that happened during that key creation process and retrieval. For example:
Test that encryption KeyControl is working
-
Log in into the MySQL database:
% mysql -u root -p<password>
-
Insert a record to the table that was created earlier:
mysql> USE MySQL_TDE_Test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> INSERT INTO test_encryption VALUES (1, 'cleandro'); Query OK, 1 row affected (0.00 sec) mysql> select * from test_encryption; +----+----------+ | id | name | +----+----------+ | 1 | cleandro | +----+----------+ 1 row in set (0.00 sec)
-
Edit the MySQL configuration file and disable the
keyring_okv
plugin:% sudo vi /etc/my.cnf #early-plugin-load=keyring_okv.so #keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv
-
Restart MySQL:
% sudo systemctl restart mysqld
-
Check if you can read the encrypted table:
% mysql -u root -p<password> mysql> use MySQL_TDE_Test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test_encryption; ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
The table is not accessible because MySQL cannot get to the master key from the keyring.
-
Re-enable the keyring in the MySQL configuration file and remove the comments you added previously:
% sudo vi /etc/my.cnf early-plugin-load=keyring_okv.so keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv
-
Restart MySQL:
% sudo systemctl restart mysqld
-
Check you can view the encrypted table:
% mysql -u root -p<password> mysql> use MySQL_TDE_Test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test_encryption; +----+----------+ | id | name | +----+----------+ | 1 | cleandro | +----+----------+ 1 row in set (0.00 sec)
This shows that the configuration of the
keyring_okv
plugin using Entrust KeyControl is working.
Secure the MySQL database
The information below was taken from the following Security Technical Implementation Guides (STIG) page and can be used as guideline to address confidentiality and integrity of all information at rest in a MySQL database.
- Group Title
-
SRG-APP-000231-DB-000154
- Rule Title
-
The MySQL Database Server 8.0 must protect the confidentiality and integrity of all information at rest.
- Discussion
-
This control is intended to address the confidentiality and integrity of information at rest in non-mobile devices and covers user information and system information. Information at rest refers to the state of information when it is located on a secondary storage device (e.g., disk drive, tape drive) within an organizational information system. Applications and application users generate information throughout the course of their application use.
For more information, see InnoDB Data-at-Rest Encryption in the MySQL online documentation.
User-generated data, as well as application-specific configuration data, must be protected. Organizations may choose to employ different mechanisms to achieve confidentiality and integrity protections, as appropriate.
If the confidentiality and integrity of application data is not protected, the data will be open to compromise and unauthorized modification.
Apply appropriate controls to protect the confidentiality and integrity of data at rest in the database.
Using SQL, determine if all data-at-rest is encrypted:
-
Check
audit_log_encryption
:SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'audit_log_encryption';
If
audit_log_encryption
is not set toAES
, this is important. -
Check
binlog_encryption
:SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'binlog_encryption';
If
binlog_encrypt
is not set toON
, this is important. -
Check
innodb_redo_log_encrypt
:SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'innodb_redo_log_encrypt';
If
innodb_redo_log_encrypt
is not set toON
, this is important. -
Check
innodb_undo_log_encrypt
:SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'innodb_undo_log_encrypt';
If
innodb_undo_log_encrypt
is not set toON
, this is important. -
Check
general_log
:SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME like 'general_log';
If
general_log
is notOFF
, this is important.
Using SQL, find the encryption status for all MySQL table and tablespaces:
-
Check tablespaces:
SELECT `INNODB_TABLESPACES`.`NAME`, `INNODB_TABLESPACES`.`ENCRYPTION` FROM `information_schema`.`INNODB_TABLESPACES`;
If any tablespace does not have
ENCRYPTION
set toY (yes)
, this is important. -
Check
innodb_redo_log_encrypt
:SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where variable_name = 'table_encryption_privilege_check';
If
innodb_redo_log_encrypt
is not set toON
, this is important.
Apply appropriate MySQL Database 8.0 controls to protect the confidentiality and integrity of data at rest in the database:
sudo vi /etc/my.cnf
[mysqld]
audit-log=FORCE_PLUS_PERMANENT
audit-log-format=JSON
audit-log-encryption=AES
#Turn on binlog encryption
set persist binlog_encryption=ON;
#Turn on undo and redo log encryption
set persist innodb_redo_log_encrypt=ON;
set persist innodb_undo_log_encrypt=ON;
Enable encryption for a new file-per-table tablespace,
ENCRYPTION
option in a
CREATE TABLE
statement.
The following example assumes that
innodb_file_per_table
is enabled:
mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';
To enable encryption for an existing file-per-table tablespace, specify the
ENCRYPTION
option in an
ALTER TABLE
statement:
mysql> ALTER TABLE t1 ENCRYPTION='Y';
To disable encryption for file-per-table tablespace, set
ENCRYPTION='N'
using
ALTER TABLE
:
mysql> ALTER TABLE t1 ENCRYPTION='N';
To disable
general_log
:
SET PERSIST general_log = 'OFF';
-
Integration GuideOracle MySQL and Entrust KeyControl Integration Guide
-
ProductsnShield Connect
-
ProductsnShield as a Service
-
ProductsKeyControl