Archive | mars 24th, 2020

Data-at-Rest Encryption with MariaDB

Data-at-Rest Encryption Overview


Source  :

Encryption of tables and tablespaces was added in MariaDB 10.1.3.

Having tables encrypted makes it almost impossible for someone to access or steal a hard disk and get access to the original data. MariaDB got Data-at-Rest Encryption with MariaDB 10.1. This functionality is also known as « Transparent Data Encryption (TDE) ».

This assumes that encryption keys are stored on another system.

Using encryption has an overhead of roughly 3-5%.

MariaDB encryption is fully supported for the XtraDB and InnoDB storage engines. Encryption is also supported for the Aria storage engine, but only for tables created with ROW_FORMAT=PAGE (the default), and for the binary log (replication log).

MariaDB allows the user to configure flexibly what to encrypt. In XtraDB or InnoDB, one can choose to encrypt:

  • everything — all tablespaces (with all tables)
  • individual tables
  • everything, excluding individual tables

Additionally, one can choose to encrypt XtraDB/InnoDB log files (recommended).

MariaDB’s data-at-rest encryption requires the use of a key management and encryption plugin. These plugins are responsible both for the management of encryption keys and for the actual encryption and decryption of data.

MariaDB supports the use of multiple encryption keys. Each encryption key uses a 32-bit integer as a key identifier. If the specific plugin supports key rotation, then encryption keys can also be rotated, which creates a new version of the encryption key.

How MariaDB manages encryption keys depends on which encryption key management solution you choose. Currently, MariaDB has three options:

File Key Management Plugin

The File Key Management plugin that ships with MariaDB is a basic key management and encryption plugin that reads keys from a plain-text file. It can also serve as example and as a starting point when developing a key management plugin.

For more information, see File Key Management Plugin.

AWS Key Management Plugin

The AWS Key Management plugin is a key management and encryption plugin that uses the Amazon Web Services (AWS) Key Management Service (KMS). The AWS Key Management plugin depends on the AWS SDK for C++, which uses the Apache License, Version 2.0. This license is not compatible with MariaDB Server’s GPL 2.0 license, so we are not able to distribute packages that contain the AWS Key Management plugin. Therefore, the only way to currently obtain the plugin is to install it from source.

For more information, see AWS Key Management Plugin.

Eperi Key Management Plugin

The Eperi Key Management plugin is a key management and encryption plugin that uses the eperi Gateway for Databases. The eperi Gateway for Databases stores encryption keys on the key server outside of the database server itself, which provides an extra level of security. The eperi Gateway for Databases also supports performing all data encryption operations on the key server as well, but this is optional.

For more information, see Eperi Key Management Plugin.


File Key Management Encryption Plugin


The File Key Management plugin that ships with MariaDB is a key management and encryption plugin that reads encryption keys from a plain-text file.

The File Key Management plugin is the easiest key management and encryption plugin to set up for users who want to use data-at-rest encryption. Some of the plugin’s primary features are:

  • It reads encryption keys from a plain-text key file.
  • As an extra protection mechanism, the plain-text key file can be encrypted.
  • It supports multiple encryption keys.
  • It does not support key rotation.
  • It supports two different algorithms for encrypting data.

It can also serve as an example and as a starting point when developing a key management and encryption plugin with the encryption plugin API.

The File Key Management plugin is included in MariaDB packages as the or file_key_management.dll shared library. The shared library is in the main server package, so no additional package installations are necessary.

Although the plugin’s shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. The plugin can be installed by providing the --plugin-load or the --plugin-load-add options. This can be specified as a command-line argument to mysqld or it can be specified in a relevant server option group in an option file. For example:

plugin_load_add = file_key_management


The default MariaDB option file is called my.cnf on Unix-like operating systems and my.ini on Windows. Depending on how you’ve installed MariaDB, the default option file may be in a number of places, or it may not exist at all.

In order to encrypt your tables with encryption keys using the File Key Management plugin, you first need to create the file that contains the encryption keys. The file needs to contain two pieces of information for each encryption key. First, each encryption key needs to be identified with a 32-bit integer as the key identifier. Second, the encryption key itself needs to be provided in hex-encoded form. These two pieces of information need to be separated by a semicolon.

$ sudo openssl rand -hex 32 >> /etc/mysql/encryption/keyfile
$ sudo openssl rand -hex 32 >> /etc/mysql/encryption/keyfile
$ sudo openssl rand -hex 32 >> /etc/mysql/encryption/keyfile


The key file still needs to have a key identifier for each encryption key added to the beginning of each line. Key identifiers do not need to be contiguous. Open the new key file in your preferred text editor and add the key identifiers. For example, the key file would look something like the following after this step:

If the key file is unencrypted, then the File Key Management plugin only requires the file_key_management_filename system variable to be configured.



loose_file_key_management_filename = /etc/mysql/encryption/keyfile


Note that the loose option prefix is specified. This option prefix is used in case the plugin hasn’t been installed yet.


The File Key Management plugin currently supports two encryption algorithms for encrypting data: AES_CBC and AES_CTR. Both of these algorithms use Advanced Encryption Standard (AES) in different modes. AES uses 128-bit blocks, and supports 128-bit, 192-bit, and 256-bit keys. The modes are:

  • The AES_CBC mode uses AES in the Cipher Block Chaining (CBC) mode.
  • The AES_CTR mode uses AES in two slightly different modes in different contexts. When encrypting tablespace pages (such as pages in InnoDB, XtraDB, and Aria tables), it uses AES in the Counter (CTR) mode. When encrypting temporary files (where the cipher text is allowed to be larger than the plain text), it uses AES in the authenticated Galois/Counter Mode (GCM).

The recommended algorithm is AES_CTR, but this algorithm is only available when MariaDB is built with recent versions of OpenSSL. If the server is built with wolfSSL or yaSSL, then this algorithm is not available. See TLS and Cryptography Libraries Used by MariaDB for more information about which libraries are used on which platforms.

| Level   | Code | Message                                                             |
| Warning |  140 | InnoDB: ENCRYPTION_KEY_ID 500 not available                         |
| Error   | 1005 | Can't create table `db1`.`tab3` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB     |
3 rows in set (0.00 sec)

Posted in Boulot1 Commentaire


mars 2020