Encrypting Data for InnoDB / XtraDB

You are viewing an old version of this article. View the current version here.

MariaDB can encrypt data in tables that use the InnoDB and XtraDB storage engines.

For encryption with the Aria storage engine, see Encrypting Data for Aria.

Basic Configuration

In order to enable encryption for tables using the InnoDB and XtraDB storage engines, there are several server system variables that you need to set and configure. Most users will want to set innodb_encrypt_tables, innodb_encrypt_logs, innodb_encryption_threads, and innodb_encryption_rotate_key_age. InnoDB and XtraDB both use the same server system variables to configure support for data-at-rest encryption

Users of data-at-rest encryption will also need to have a key management and encryption plugin configured. Some examples are File Key Management Plugin and AWS Key Management Plugin.

[mariadb]
...

# File Key Management
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/keys.enc
file_key_management_filekey = FILE:/etc/mysql/.key
file_key_management_encryption_algorithm = aes_cbc

# InnoDB/XtraDB Encryption
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
innodb_encryption_rotate_key_age = 1

Limitations

Wrong create options

There are several cases where a CREATE TABLE or ALTER TABLE statement that involves encryption on an InnoDB table can throw the following error:

Error: 140 Wrong create options.

You can usually execute SHOW WARNINGS to get more details about the cause of the error. For example:

create table `table1` ( `id` int(4) primary key , `name` varchar(50));
ERROR 1005 (HY000): Can't create table `dba_test`.`table1` (errno: 140 "Wrong create options")
show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------+
| Warning | 140 | InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 |
| Error | 1005 | Can't create table `dba_test`.`table1` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

This error is known to occur in at least the following cases:

Tablespaces Created on MySQL 5.1.47 or Earlier

MariaDB's data-at-rest encryption implementation re-used previously unused fields in InnoDB's buffer pool pages to identify the encryption key version and the post-encryption checksum. Prior to MySQL 5.1.48, these unused fields were not initialized in memory due to performance concerns. These fields still had zero values most of the time, but since they were not explicitly initialized, that means that these fields could have occasionally had non-zero values that could have been written into InnoDB's tablespace files. If MariaDB were to encounter an unencrypted page from a tablespace file that was created on an early version of MySQL that also had non-zero values in these fields, then it would mistakenly think that the page was encrypted.

The fix for MDEV-12112 that was included in MariaDB 10.1.38, MariaDB 10.2.20, and MariaDB 10.3.12 changed the way that MariaDB distinguishes between encrypted and unencrypted pages, so that it is less likely to mistake an unencrypted page for an encrypted page.

See MDEV-18097 about plans for permanently fixing this issue.

Seeing Which Tables are Encrypted

The Information Schema INNODB_TABLESPACES_ENCRYPTION table gives encryption information about InnoDB tables, including which tables are encrypted.

For example:

SELECT * FROM information_schema.innodb_tablespaces_encryption\G
*************************** 1. row ***************************
                       SPACE: 9
                        NAME: test/t3
           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
        ROTATING_OR_FLUSHING: 0
...
*************************** 3. row ***************************
                       SPACE: 11
                        NAME: test/t5
           ENCRYPTION_SCHEME: 0
          KEYSERVER_REQUESTS: 0
             MIN_KEY_VERSION: 0
         CURRENT_KEY_VERSION: 1
    KEY_ROTATION_PAGE_NUMBER: NULL
KEY_ROTATION_MAX_PAGE_NUMBER: NULL
              CURRENT_KEY_ID: 1
        ROTATING_OR_FLUSHING: 0
*************************** 4. row ***************************
                       SPACE: 12
                        NAME: test/t6
           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: 3
        ROTATING_OR_FLUSHING: 0

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.