Encrypting InnoDB

gowebing 2024-11-30

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

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:

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:

1;55101B33D507041805AF49B95BBAE995B4218C5054D2DA2852C5F0042B837FC3

For information on how to create keys for AWS Key Management System see their MariaDB AWS KMS Encryption 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:

[mariadb]
innodb-encrypt-log

InnoDB Transparent Tablespace Encryption

With tablespace encryption, all InnoDB tables and tablespaces are encrypted including the system tablespace. When configuring 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:

innodb-encrypt-tables=FORCE

Key Rotation

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

Monitoring

The Information Schema INNODB_TABLESPACES_ENCRYPTION table contains current encryption status for every table. For example, if key rotation is occurring, fields KEY_ROTATION_PAGE_NUMBER and 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).

Performance

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

Figures

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:

Sysbench OLTP benchmark transactions in the second

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:

Sysbench OLTP average response time at 95 percentile

Limitations

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.
- mysqlbinlog cannot read encrypted binary logs (MDEV-8813);
- Percona XtraBackup cannot backup instances that use encrypted InnoDB log files (ME-478, PR-200); and
- Percona XtraBackup cannot prepare instances that use encrypted InnoDB log files.
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.

Conclusions

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.