Using Fortanix Data Security Manager for Percona MySQL Encryption at Rest

1.0 Introduction

This article describes how to set up an application (app) in Fortanix-Data-Security-Manager (DSM) for Percona MySQL to integrate with Fortanix DSM.

Percona is a leading provider of open-source databases like MySQL, MongoDB, PostgreSQL, and so on. Percona Server for MYSQL DB is fully compatible with MySQL and all its capabilities are equated with those of the MYSQL Enterprise Version.

Percona Server for MySQL 8.0.27-18 has added support for the OASIS Key Management Interoperability Protocol (KMIP), making it possible for MYSQL data encryption at rest keys to be stored in external key management systems.

Unlike MYSQL data encryption at rest, here the keyring component can be installed using a manifest file directly. The server uses a manifest and the component consults its configuration file during initialization.

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

Percona Server for MySQL KMIP authenticates to a KMIP-enabled key management server using the client certificate. Fortanix DSM supports clients or apps to authenticate using an API key, app ID, and certificate, or only a certificate.

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 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 <Your_DSM_Service_URL> in a web browser and enter your credentials to log in to Fortanix DSM.

For more information on how to set up an account in Fortanix DSM, refer to the User's Guide: Getting Started with Fortanix Data Security Manager - UI.

2.3 Creating a Group

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

1. In the DSM left navigation panel, click the Groups menu item, and then click the + button to create a new group.

   

2. On the Adding new group page, do the following:

   1. Title: Enter a name for your group.

   2. Description (optional): Enter a short description of the group.

3. Click SAVE to create the new group.

The new group is 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. In the DSM left navigation panel, click the Apps menu item, and then click the + button to create a new app.

   

2. On the Adding new app page, do the following:

   1. App name: Enter the name for your application.

   2. ADD DESCRIPTION (optional): Enter a short description of the application.

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

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

3. Click SAVE to add the new application.

The new application is 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. In the DSM left navigation panel, click the Apps menu item, and then click the app created in Section 2.4: Creating an Application to go to the detailed view of the app.

2. From the top of the app’s page, click the copy icon next to the app UUID to copy it to use in Section 2.6: Generating the Certificate as the value of Common Name (CN) to generate a self-signed certificate and a private key.

2.6 Generating the Certificate

Change directory to SDKMS_Certs and run the following command to generate a self-signed certificate:

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

When prompted for a Common Name, you should enter the app ID you noted earlier. The 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:

2.7 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 Change authentication method and select the Certificate option to change the authentication method to Certificate.

2. Click SAVE.

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

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

5. Click UPDATE to save the changes.

3.0 Configuring Encryption in Percona MySQL

Perform the following steps  to configure encryption in Percona MySQL by installing and setting up the keyring_okv plugin:

3.1 Keyring Component Installation

To enable encryption in Percona MySQL, load the keyring_okv plugin using the --early-plugin-load option in the MySQL configuration file.

Add the following lines to the mysqld section of the MySQL server configuration file (my.cnf). Adjust the plugin file extension (.so) as required for your platform:

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

NOTE

The location of the configuration file depends on the MySQL version:

• MySQL 5.7: /etc/my.cnf

• MySQL 8.0: /etc/mysql/mysql.conf.d/mysqld.cnf

Enabling the keyring_okv plugin is the first step. Additional configuration is required to connect the plugin to Fortanix DSM through the KMIP protocol. This includes referencing the certificate created in the earlier steps for mutual authentication.

3.1.1 Creating Manifest for the Keyring Component

A manifest file indicates which component to load. During start-up, the server reads the global manifest file from the installation directory. The global manifest file can contain the required information or point to a local manifest file located in the data directory. If you have multiple server instances that use different keyring components use a local manifest file in each data directory to load the correct keyring component for that instance.

Perform the following steps to configure the keyring_okv plugin to communicate with Fortanix DSM using the KMIP protocol, create the manifest files required by the MySQL keyring component.

1. Navigate to the directory where the mysqld executable resides—typically /usr/sbin. Create a file named mysqld.my with the following content:

   {
   "read_local_manifest": false,
   "components": "file:///component_keyring_kmip"
   }

   This configuration directly loads the KMIP component from the specified local path.

2. If you prefer to use a local manifest file in conjunction with the global manifest, update the files as follows:
   Global manifest:

   {
   "read_local_manifest": true
   }

   Local manifest

   {
   "components": "file:///component_keyring_kmip"
   }

   NOTE

   Ensure that the component_keyring_kmip path is correctly placed and accessible by the MySQL process.

3.1.2 Creating a Configuration File

To enable MySQL to communicate securely with Fortanix DSM as a Key Management Server (KMS) using the component_keyring_kmip plugin, you must create a configuration file in the plugin directory.

The configuration file should be located in the same directory where the component_keyring_kmip.so shared object resides. This is typically:

cat /usr/lib/mysql/plugin/component_keyring_kmip.cnf

{
 "server_addr": "sdkms.fortanix.com",
 "server_port": "5696",
 "client_ca": "certificate.crt",
 "client_key": "client_key.pem",
 "server_ca": "dsm_ca.crt"
}

Where,

• server_addr is the Fortanix DSM endpoint.

• server_port is the KMIP port of 5696.

• client_ca is the CA certificate of the client certificate in the case of CA-signed client certificate or the client certificate itself in the case of a self-signed client certificate.

• client_key is the client’s private key.

• server_ca is the Fortanix DSM CA certificate.

Perform the following steps after saving the configuration file:

1. Start the MySQL server.

2. Validate the successful component initialization.

3. To verify that the component_keyring_kmip is properly installed and initialized, run the following SQL query:

3.2 Creating Encrypted Tables

When you create the first encrypted table, InnoDB will generate the master key (AES-256) in Fortanix DSM. You can check this in the Fortanix DSM Web UI on the Security Objects page. This master key is used to encrypt tablespace keys. Tablespace keys are generated locally by InnoDB. 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 every time the master 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.

The following 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';

Run the following command to rotate key from the database:

ALTER INSTANCE ROTATE INNODB MASTER KEY;

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