Table and tablespace encryption on MariaDB 10.1
MariaDB has a wide set of security features to protect data (see MariaDB Enterprise Security Webinar). To encrypt the data in a MariaDB 10.1 database, you can enable data-at-rest encryption. MariaDB allows the option to select the most suitable level of the encryption in MariaDB: Temporary files, Aria tables, InnoDB tablespaces, InnoDB tables, InnoDB log files and Binlogs. In this article I will explain how to turn on encryption for InnoDB and discuss how encryption affects performance.
Encryption plugins in MariaDB are needed to use the data-at-rest encryption feature. They're responsible for both key management and for the actual encryption and decryption of data. MariaDB currently supports two encryption plugins for real usage:
- file_key_management plugin: Reads encryption keys from a file. Supports encryption key identifiers, but does not support encryption key versions. Supports two encryption algorithms and allows user to select which to use.
- Amazon Web Services (AWS) Key Management Service (KMS) Encryption Plugin: uses the AWS Key Management Service to facilitate separation of responsibilities and remote logging and auditing of key access requests.
Creating Encryption Keys
There are different methods for creating encryption keys, depending the used encryption plugin. Keys for file_key_management_plugin can be generated using OpenSSL with the following command:
openssl enc -aes-256-ctr -k mylong2016secret@keyfor35fun -P -md sha1 salt=9265402E0907A5D4 key=55101B33D507041805AF49B95BBAE995B4218C5054D2DA2852C5F0042B837FC3 iv =C7040FF9DB066043D16ADBEC4F18053F
The key file is a text file containing a key identifier and the hex-encoded key. For example, keys.txt using the generated key above looks like this:
For information on how to create keys for AWS Key Management System see their MariaDB AWS KMS Enryption Setup Guide. For more advanced information, see our AWS KMS Encryption Plugin Advanced Usage documentation.
InnoDB Specified Table Encryption
Specified Table encryption means that the administrator chooses which tables to encrypt. This allows you to balance security with speed. To use table encryption, you have to load the file-key-management-plugin, define the location of key file, and define the AES method used. To do this, you would add a few lines like these to the MariaDB configuration file:
[mariadb] plugin-load-add=file_key_management file_key_management_filename=/mnt/usb/secret.txt file_key_management_encryption_algorithm=AES_CTR
We recommend that you place the encryption key file on an external storage device (e.g., a USB drive). This external storage can be unmounted after the MariaDB server is started and stored in secure location. After this, the database developer may select which tables contain sensitive data for encryption. Encryption can be enabled for a table when it's created or using the ALTER TABLE statement, as shown in the example below:
CREATE TABLE table1 (col1 INT NOT NULL PRIMARY KEY, secret CHAR(200)) ENGINE=InnoDB ENCRYPTED=YES; CREATE TABLE table2 (col1 INT NOT NULL PRIMARY KEY, secret CHAR(200)) ENGINE=InnoDB; ALTER TABLE table2 ENCRYPTED=YES encryption_key_id=2;
Note that the InnoDB redo-log is not encrypted by default, even when the tables are. Therefore, you should consider also using encryption for the redo-log. InnoDB redo-logs can be encrypted with this one line to the MariaDB configuration file:
InnoDB Transparent Tablespace Encryption
With tablespace encryption, all InnoDB tables and tablespaces are encrypted including the system tablespace. When configurating the server for the type of encryption, we recommended that you also enable InnoDB redo-log encryption. Below is an example of the configuration settings required:
[mariadb] innodb-encrypt-tables innodb-encrypt-log innodb-encryption-threads = 4 plugin-load-add=file_key_management file_key_management_filename=/mnt/usb/secret.txt file_key_management_encryption_algorithm=AES_CTR # for monitoring innodb-tablespaces-encryption
After adding the above setting and restarted the server to implement them, all existing tables and all new tables will be encrypted—unless specified otherwise for a particular table. Despite the configuration, MariaDB does allow encryption to be disabled for tables that don't require encryption. This can be done when the table is created or by altering it later with the ALTER TABLE statement. Here's an example of both scenarios:
CREATE TABLE table3 (col1 INT NOT NULL PRIMARY KEY, notsecret VARCHAR(150)) ENGINE=InnoDB ENCRYPTED=NO; CREATE TABLE table4 (col1 INT NOT NULL PRIMARY KEY, notsecret VARCHAR(150)) ENGINE=InnoDB; ALTER TABLE table4 ENCRYPTED=NO;
If you don't want users to be able to create tables without encryption, you can set the server to force encryption. Just add the following line to the MariaDB confirguration file:
Currently, only Amazon AWS KMS plugin supports key rotation from the encryption plugins intended for serious use. Keys provided by plugin can be rotated using the SET statement like so:
SET global aws_key_management_rotate_key=key_id;
You'll also need to set the key_id equal to -1 so that all keys are rotated.
Tablespace key rotation is based on the age of key used. Key age is the key_version and the age limit as defined using innodb_encryption_rotate_key_age parameter. This parameter will indicate how old keys that are not yet rotated. For example, if innodb_encryption_rotate_key_age is set to a value of 10 and current key_version is set to 20, all tablespaces with key_version greater than 10 will be rotated to use new key_version.
Currently, this key rotation does not happen immediately when the tablespace key_version becomes obsolete. The need for tablespace rotation is checked only when some encrypted tablespace is changed by an INSERT or an UPDATE statement. At that point, a new key_version is requested from plugin. Below is an example showing this:
SELECT * FROM information_schema.innodb_tablespaces_encryption \G *************************** 1. row *************************** SPACE: 0 NAME: NULL ENCRYPTION_SCHEME: 1 KEYSERVER_REQUESTS: 1 MIN_KEY_VERSION: 1 CURRENT_KEY_VERSION: 1 KEY_ROTATION_PAGE_NUMBER: NULL KEY_ROTATION_MAX_PAGE_NUMBER: NULL CURRENT_KEY_ID: 1 *************************** 2. row *************************** SPACE: 1 NAME: sbtest/sbtest1 ENCRYPTION_SCHEME: 1 KEYSERVER_REQUESTS: 1 MIN_KEY_VERSION: 1 CURRENT_KEY_VERSION: 1 KEY_ROTATION_PAGE_NUMBER: NULL KEY_ROTATION_MAX_PAGE_NUMBER: NULL CURRENT_KEY_ID: 1 SET GLOBAL aws_key_management_rotate_key = -1; SELECT * FROM information_schema.innodb_tablespaces_encryption \G ************************** 1. row *************************** SPACE: 0 NAME: NULL ENCRYPTION_SCHEME: 1 KEYSERVER_REQUESTS: 2 MIN_KEY_VERSION: 11 CURRENT_KEY_VERSION: 12 KEY_ROTATION_PAGE_NUMBER: NULL KEY_ROTATION_MAX_PAGE_NUMBER: NULL CURRENT_KEY_ID:1 ************************* 2. row *************************** SPACE: 1 NAME: sbtest/sbtest1 ENCRYPTION_SCHEME: 1 KEYSERVER_REQUESTS: 3 MIN_KEY_VERSION: 11 CURRENT_KEY_VERSION: 12 KEY_ROTATION_PAGE_NUMBER: NULL KEY_ROTATION_MAX_PAGE_NUMBER: NULL CURRENT_KEY_ID: 1
The Information Schema INNODB_TABLESPACES_ENCRYPTION table contains current encryption status for every table. For example, if key rotation is occurring, fields
KEY_ROTATION_MAX_PAGE_NUMBER indicate that background threads currently are working on encrypting or decrypting pages. An example of the output can be seen above. The tablespace with NULL for the name field is a system tablespace (ibdata1).
We used CentOS Linux release 7.1.1503 (Core) using the 3.10.0-229.el7.x86\_64 Linux kernel, ioMemory SX300-1600 with VSL driver 4.2.1 build 1137 and NVMFS 1.1.1 for filesystem. The benchmark used was Sysbench 0.5 with following command:
./sysbench --test=tests/db/oltp.lua --mysql-table-engine=innodb --oltp-test-mode=complex --oltp-read-only=off --oltp-table-size=100000 --max-requests=100000000 --num-threads=128 --max-time=60 --mysql-socket=/mnt/dfs/db/mysql.sock --mysql-user=root run
Figure 1 shows Sysbench OLTP benchmark transactions in the second result. The number of threads used has been variaed and the default unencrypted tables are compared to encrypted tables:
Figure 2 shows the Sysbench OLTP average response time at 95 percentile of results. It's done by varying the number of threads used and comparing default unencrypted tables to encrypted tables:
There are some limitations to the implementation of data-at-rest encryption in MariaDB 10.1.14. Below is a summary of these:
Only data at rest is encrypted. Metadata (e.g., .frm files) and data sent to a client are not encrypted (see Secure Connections).
- Only the MariaDB server can decrypt the data.
- The disk-based Galera gcache is not encrypted (MDEV-9639). On Galera installations you could decrease your gcache size—at the expense of more SST in case of restarts—to have less unencrypted data on your disk.
- The Audit plugin cannot create encrypted output. Instead, send it to syslog and configure the protection there.
- File-based general query log and slow query log cannot be encrypted (MDEV-9639).
- The Aria log is not encrypted (MDEV-9639). This affects only non-temporary Aria tables, though.
- The MariaDB error log is not encrypted. The error log can contain query text and data in some cases, including crashes, assertion failures, and cases in which InnoDB/XtraDB write monitor outputs to the log to aid in debugging. It can also be sent to syslog, if needed.
MariaDB 10.1 provides a mature data-at-rest encryption solution that not only allows users to select suitable level of security using table encryption or fully transparent tablespace encryption.