dasini.net – Diary of a MySQL expert

When thinking about security within a MySQL installation, you should consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools / features / plugins in order to protect your data including some advanced features like  Audit, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, User Account Locking, etc…

In this sixth episode of the MySQL Security series, we will see how data-at-rest encryption helps organizations implement stronger security controls and satisfy regulatory compliance.

You will be able to protect the privacy of your information, prevent data breaches and help meet popular regulatory requirements including GDPR, PCI DSS, HIPAA with MySQL Enterprise Transparent Data Encryption aka TDE.

MySQL Keyring

MySQL Enterprise Transparent Data Encryption relies on a MySQL Keyring plugin for master encryption key management. MySQL Server provides many plugin choices including :

• keyring_file : that stores keyring data in a file local to the server host. Available in all MySQL distributions.
• keyring_encrypted_file : that stores keyring data in an encrypted file local to the server host. Available in MySQL Enterprise Edition.
• keyring_okv : a KMIP 1.1 plugin for use with KMIP-compatible backend keyring storage products such as Oracle Key Vault and Gemalto SafeNet KeySecure Appliance. Available in MySQL Enterprise Edition.
• keyring_aws : that communicates with the Amazon Web Services Key Management Service for key generation and uses a local file for key storage. Available in MySQL Enterprise Edition.

In this article I’ll use the keyring_encrypted_file plugin with MySQL Enterprise Edition 5.7.21.

Updated on 22nd of August 2018
Note: MySQL Enterprise Transparent Data Encryption works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

Warning
The keyring_file and keyring_encrypted_file plugins for encryption key management are not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

These keyring plugins would be use with MySQL Enterprise Audit to store the audit log file encryption password (you can read my previous article) and InnoDB tables when encrypted with  MySQL Enterprise Transparent Data Encryption, actually the topic of this article but you already know that, right? :).

Keyring Plugin Installation : keyring_encrypted_file

Like I stated before many keyring plugins are available and I’ll use the keyring plugin : keyring_encrypted_file, that stores keyring data in an encrypted file local to the server host.

Only one keyring plugin should be enabled at a time. So let’s check that we have none so far :

mysql>
SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME = 'keyring_encrypted_file'\G
Empty set (0.01 sec)

To be usable by the server, the plugin library file must be located in the MySQL plugin directory :

mysql>
SHOW VARIABLES LIKE 'plugin_dir';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| plugin_dir    | /var/lib/plugin/ |
+---------------+------------------+


system ls  /var/lib/plugin/ | grep keyring
keyring_encrypted_file.so
keyring_file.so
keyring_okv.so
keyring_udf.so

Update the MySQL configuration file and restart the MySQL server :

[mysqld]
early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/var/lib/mysql-keyring/keyring-encrypted
keyring_encrypted_file_password=V&rySec4eT

• To be usable during the server startup process, keyring_encrypted_file must be loaded using the early-plugin-load option.
• To specify the password for encrypting the keyring data file, set the keyring_encrypted_file_password system variable.
• The keyring_encrypted_file_data system variable optionally configures the location of the file used by the keyring_encrypted_file plugin for data storage.

Warning
Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.

mysql> 
SELECT PLUGIN_NAME, PLUGIN_TYPE, PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME = 'keyring_encrypted_file'\G
*************************** 1. row ***************************
       PLUGIN_NAME: keyring_encrypted_file
       PLUGIN_TYPE: KEYRING
     PLUGIN_STATUS: ACTIVE
    PLUGIN_LIBRARY: keyring_encrypted_file.so
PLUGIN_DESCRIPTION: store/fetch authentication data to/from an encrypted file

We can have a look at the Keyring file. Its location could be found with keyring_file_data variable :

mysql>
SHOW VARIABLES LIKE 'keyring_encrypted_file_data'\G
*************************** 1. row ***************************
Variable_name: keyring_encrypted_file_data
        Value: /var/lib/mysql-keyring/keyring-encrypted


system ls -lh /var/lib/mysql-keyring/keyring-encrypted
... 0 Apr 5 16:36 /var/lib/mysql-keyring/keyring-encrypted

After a keyring plugin is configured, InnoDB tables may be encrypted.

InnoDB Transparent Encryption

InnoDB supports data encryption for InnoDB tables stored in file-per-table tablespaces.

I’m using Sakila Database, available here.

Below a couple of random queries :

mysql> 
USE sakila;

SELECT rental_id
FROM rental
WHERE inventory_id = 3043
    AND customer_id = 53
    AND return_date IS NULL;
+-----------+
| rental_id |
+-----------+
|     11657 |
+-----------+
1 row in set (0,00 sec)


-- Find Overdue DVDs
SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, address.phone, film.title
FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id
  INNER JOIN address ON customer.address_id = address.address_id
  INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
  INNER JOIN film ON inventory.film_id = film.film_id
WHERE rental.return_date IS NULL
  AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()
  LIMIT 5;
+------------------+--------------+------------------------+
| customer         | phone        | title                  |
+------------------+--------------+------------------------+
| KNIGHT, GAIL     | 904253967161 | HYDE DOCTOR            |
| MAULDIN, GREGORY | 80303246192  | HUNGER ROOF            |
| JENKINS, LOUISE  | 800716535041 | FRISCO FORREST         |
| HOWELL, WILLIE   | 991802825778 | TITANS JERK            |
| DIAZ, EMILY      | 333339908719 | CONNECTION MICROCOSMOS |
+------------------+--------------+------------------------+
1 row in set (0,01 sec)

Data-at-Rest Encryption

Usually it is not necessary to encrypt all the tables. After a deep data analysis let’s assume 2 relevant InnoDB tables must be encrypted : customer and staff.

So let’s encrypted these tables!

We can do it easily with a CREATE or ALTER TABLE statement using ENCRYPTION=”Y” :

mysql> 
ALTER TABLE sakila.customer ENCRYPTION="Y";
Query OK, 599 rows affected (0,19 sec)
Records: 599  Duplicates: 0  Warnings: 0


ALTER TABLE sakila.staff ENCRYPTION="Y";
Query OK, 2 rows affected (0,15 sec)
Records: 2  Duplicates: 0  Warnings: 0

There we go!

Note
Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE … ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITHM=INPLACE is not supported.

Identify Tables that Use InnoDB Tablespace Encryption

This information is available in INFORMATION_SCHEMA.TABLES :

mysql>
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE CREATE_OPTIONS = 'ENCRYPTION="Y"';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| sakila       | customer   | ENCRYPTION="Y" |
| sakila       | staff      | ENCRYPTION="Y" |
+--------------+------------+----------------+

Query encrypted Tables

Transparent Data Encryption means transparent for the clients ie no change in your applications.

In other words, you don’t have to modify your queries and sames queries with same datasets will give me the same result :

mysql> 
USE sakila;

SELECT rental_id
FROM rental
WHERE inventory_id = 3043
    AND customer_id = 53
    AND return_date IS NULL;
+-----------+
| rental_id |
+-----------+
|     11657 |
+-----------+
1 row in set (0,00 sec)


-- Find Overdue DVDs
SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, address.phone, film.title
FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id
  INNER JOIN address ON customer.address_id = address.address_id
  INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
  INNER JOIN film ON inventory.film_id = film.film_id
WHERE rental.return_date IS NULL
  AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()
  LIMIT 5;
+------------------+--------------+------------------------+
| customer         | phone        | title                  |
+------------------+--------------+------------------------+
| KNIGHT, GAIL     | 904253967161 | HYDE DOCTOR            |
| MAULDIN, GREGORY | 80303246192  | HUNGER ROOF            |
| JENKINS, LOUISE  | 800716535041 | FRISCO FORREST         |
| HOWELL, WILLIE   | 991802825778 | TITANS JERK            |
| DIAZ, EMILY      | 333339908719 | CONNECTION MICROCOSMOS |
+------------------+--------------+------------------------+
1 row in set (0,01 sec)

Master Key Rotation

Legal and regulatory requirements require a periodically rotation of the master encryption key. It’s also more than recommended to rotate the master key whenever you suspect a security breach. Rotating the master encryption key only changes the master encryption key and re-encrypts tablespace keys.

To rotate the master encryption key use ALTER INSTANCE ROTATE INNODB MASTER KEY :

mysql> 
system ls -lh /var/lib/mysql-keyring/keyring-encrypted
... 197 Apr  5 16:44 /var/lib/mysql-keyring/keyring-encrypted


ALTER INSTANCE ROTATE INNODB MASTER KEY;
Query OK, 0 rows affected (0,01 sec)


system ls -lh /var/lib/mysql-keyring/keyring-encrypted
... 325 Apr  5 17:18 /var/lib/mysql-keyring/keyring-encrypted

Et voilà!

Now you know the MySQL Enterprise Transparent Encryption basics \o/

I would like to address a last item, regarding a common customer question about the disk footprint of an encrypted table.

So let’s play a little bit more with TDE.

We have a table, encryptedTable, not yet encrypted with 5 millions rows

Table size is 722 MB

mysql> 
SHOW TABLE STATUS LIKE 'encryptedTable'\G
*************************** 1. row ***************************
           Name: encryptedTable
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4986035
 Avg_row_length: 61
    Data_length: 304922624
Max_data_length: 0
   Index_length: 449921024
      Data_free: 2097152
 Auto_increment: NULL
    Create_time: 2018-04-10 12:02:35
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 


-- Rows = 5 million
SELECT count(*) FROM encryptedTable;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+


-- Table size = 722MB
SELECT   
    CONCAT(sum(ROUND(data_length / ( 1024 * 1024 ), 4)), 'MB') DATA,   
    CONCAT(sum(ROUND(index_length / ( 1024 * 1024 ),4)), 'MB') INDEXES, 
    CONCAT(sum(ROUND(data_free / ( 1024 * 1024 ), 4)), 'MB') DATA_FREE, 
    CONCAT(sum(ROUND(( data_length + index_length+data_free) / ( 1024 * 1024 ), 4)), 'MB') 'TOTAL' 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'
    AND TABLE_NAME = ('encryptedTable')\G
*************************** 1. row ***************************
     DATA: 290.7969MB
  INDEXES: 429.0781MB
DATA_FREE: 2.0000MB
    TOTAL: 721.8750MB

Table is now encrypted

ALTER TABLE encryptedTable ENCRYPTION="Y";
Query OK, 5000000 rows affected (6 min 42,58 sec)
Records: 5000000  Duplicates: 0  Warnings: 0

Table size is now : 870 MB

-- Table size = 870MB
SELECT   
    CONCAT(sum(ROUND(data_length / ( 1024 * 1024 ), 4)), 'MB') DATA,   
    CONCAT(sum(ROUND(index_length / ( 1024 * 1024 ),4)), 'MB') INDEXES, 
    CONCAT(sum(ROUND(data_free / ( 1024 * 1024 ), 4)), 'MB') DATA_FREE, 
    CONCAT(sum(ROUND(( data_length + index_length+data_free) / ( 1024 * 1024 ), 4)), 'MB') 'TOTAL' 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'
    AND TABLE_NAME = ('encryptedTable')\G
*************************** 1. row ***************************
     DATA: 249.7813MB
  INDEXES: 614.6563MB
DATA_FREE: 6.0000MB
    TOTAL: 870.4375MB

Is there a way to reclaim space (and optionally improve I/O efficiency)?  Yes of course, OPTIMIZE TABLE :

OPTIMIZE TABLE test.encrypTable\G
*************************** 1. row ***************************
   Table: test.encrypTable
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: test.encrypTable
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (1 min 32,52 sec)

Table size is again 722 MB

-- Table size = 722MB
SELECT   
    CONCAT(sum(ROUND(data_length / ( 1024 * 1024 ), 4)), 'MB') DATA,   
    CONCAT(sum(ROUND(index_length / ( 1024 * 1024 ),4)), 'MB') INDEXES, 
    CONCAT(sum(ROUND(data_free / ( 1024 * 1024 ), 4)), 'MB') DATA_FREE, 
    CONCAT(sum(ROUND(( data_length + index_length+data_free) / ( 1024 * 1024 ), 4)), 'MB') 'TOTAL' 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'
    AND TABLE_NAME = ('encryptedTable')\G
*************************** 1. row ***************************
     DATA: 290.7969MB
  INDEXES: 429.0781MB
DATA_FREE: 2.0000MB
    TOTAL: 721.8750MB

MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.

It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.

MySQL Enterprise Edition server Trial Download (Note – Select Product Pack: MySQL Database).

In order to go further

MySQL Security Series

1. Password Validation Plugin
2. Password Management
3. User Account Locking
4. The Connection-Control Plugins
5. Enterprise Audit
6. Enterprise Transparent Data Encryption (TDE)
7. Enterprise Firewall
8. Enterprise Data Masking and De-Identification

Reference Manual

• The MySQL Keyring
• Encrypting InnoDB Tables
• Presentation of MySQL Enterprise Transparent Data Encryption (TDE)
• MySQL : InnoDB Transparent Tablespace Encryption

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
MySQL, Security, Tuto by Olivier DASINI