Using Fortanix Data Security Manager for Percona MySQL Encryption at Rest

1. 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/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 Data Security Manager (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 the <Your_DSM_Service_URL> on the web browser and enter your credentials to log in to the Fortanix DSM.

2.3 Creating a Group

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

1. Click the Groups menu item in the DSM left navigation bar and click the + button on the Groups page to add a new group.

   

2. On the Adding new group page, enter the following details:

   • Title: Enter a title for your group.

   • Description (optional): Enter a short description for the group.

3. Click the SAVE button to create the new group.

The new group has been 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. Click the Apps menu item in the DSM left navigation bar and click the + button on the Apps page to add a new app.

   

2. On the Adding new app page, enter the following details:

   • App name: Enter the name of your application.

   • ADD DESCRIPTION (optional): Enter a short description for the application.

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

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

3. Click the SAVE button to add the new application.

The new application has been 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. Click the Apps menu item in the DSM left navigation bar and click the app created in the Section 2.4: Creating an Application to go to the detailed view of the app.

2. From the top of the app’s page, copy the app UUID to be used 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

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

1. Change directory to SDKMS_Certs and generate a self-signed certificate using the command below:

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

   

   

2. 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.

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

2. Click the SAVE button.

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

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

5. Click the UPDATE button to save the changes.

3.0 Configuring Encryption in Percona MySQL

In order to configure encryption in MySQL, you will need to install and configure the keyring component.

3.1 Keyring Component Installation

Perform the following steps to install a keyring component:

1. Create a manifest in a valid JSON format.

2. Write a configuration file.

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:

1. Go to the directory where mysqld executable exists (mostly /usr/sbin) and create a global manifest file named as mysqld.my file with the following JSON:

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

2. If you will be using a local manifest file, your global and local manifest file should be as shown below.
   Global Manifest:

   {
   "read_local_manifest": true
   }

   Local Manifest

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

3.1.2 Creating a Configuration File

The configuration file for your KMS should be located in the plugin folder where your component_keyring_kmip.so resides (mostly /usr/lib/mysql/plugin) and should have the following JSON:

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

{
 "server_addr": "",
 "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.

1. After performing any component-specific configuration, start the server.

2. Verify component installation by examining the Performance Schema keyring_component_status table.  

   

   NOTE

   If the component cannot be loaded, the server start-up fails. Check the server error log for diagnostic messages. If the component loads but fails to initialize due to configuration problems, the server starts but the Component_status value is Disabled. Check the server error log, correct the configuration issues, and use the ALTER INSTANCE RELOAD KEYRING statement to reload the configuration.

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.

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.