InnoDB / XtraDB Enabling Encryption

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

In order to enable data-at-rest encryption for tables using the InnoDB or XtraDB storage engines, you first need to configure the Server for Encryption Key Management, such as through the file_key_management or aws_key_management plugins. Once this is done, you can enable encryption by setting the innodb_encrypt_tables system variable to encrypt the InnoDB system and file tablespaces and setting the innodb_encrypt_log system variable to encrypt the InnoDB Redo Log.

Setting these system variables enables the encryption feature for InnoDB and XtraDB tables on your server. To use the feature, you need to use the ENCRYPTION_KEY_ID table option to set what encryption key you want to use and set the ENCRYPTED table option to enable encryption.

When encrypting any InnoDB or XtraDB tables, the best practice is also enable encryption for the Redo Log. If you have encrypted InnoDB tables and have not encrypted the Redo Log, data written to an encrypted table may be found unencrypted in the Redo Log.

Enabling Encryption for Automatically Encrypted Tablespaces

The innodb_encrypt_tables system variable controls the configuration of automatic encryption of InnoDB tables. It has the following possible values:

OptionDescription
OFFDisables table encryption.
ONEnables table encryption, but allows unencrypted tables to be created.
FORCEEnables table encryption, and doesn't allow unencrypted tables to be created. Added in MariaDB 10.1.4.

When innodb_encrypt_tables is set to ON, every InnoDB table is automatically encrypted unless a specific InnoDB table has been explicitly set to be unencrypted by setting ENCRYPTED=NO for the table. For example, the following table will not be automatically encrypted:

ALTER TABLE my_db.my_unencrypted_table
   ENCRYPTED=NO;

When innodb_encrypt_tables is set to FORCE, every InnoDB table is automatically encrypted, and InnoDB will not allow ENCRYPTED=NO to be set for any tables.

When innodb_encrypt_tables is set to ON for FORCE, then you must ensure that innodb_encryption_threads is set to a non-zero value, so that InnoDB can perform any necessary encryption operations in the background. See background operations for more information about that. innodb_encryption_rotate_key_age must also be set to a non-zero value for the initial encryption operations to happen in the background. See disabling key rotations for more information about that.

Enabling Encryption for Manually Encrypted Tablespaces

If you do not want to automatically encrypt every InnoDB table, then it is possible to manually enable encryption for just the subset of InnoDB tables that you would like to encrypt. MariaDB provides the ENCRYPTED and ENCRYPTION_KEY_ID table options that can be used to manually enable encryption for specific InnoDB tables. These table options can be used with CREATE TABLE and ALTER TABLE statements. These table options can only be used with InnoDB tables that have their own InnoDB's file-per-table tablespaces, meaning that tables that were created with innodb_file_per_table=ON set.

Table OptionValueDescription
ENCRYPTEDBooleanDefines whether to encrypt the table
ENCRYPTION_KEY_ID32-bit integerDefines the identifier for the encryption key to use

You can manually enable or disable encryption for a table by using the ENCRYPTED table option. If you only need to protect a subset of InnoDB tables with encryption, then it can be a good idea to manually encrypt each table that needs the extra protection, rather than encrypting all InnoDB tables globally with innodb_encrypt_tables. This allows you to balance security with speed, as it means the encryption and decryption performance overhead only applies to those tables that require the additional security.

You can also manually specify a encryption key for a table by using the ENCRYPTION_KEY_ID table option. This allows you to use different encryption keys for different tables. If not specified, then the table will be encrypted with the key identified by the innodb_default_encryption_key_id system variable.

For instance, you might create a table using a statement like this:

CREATE TABLE my_db.my_encrypted_table (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(255),
   email VARCHAR(255))
ENGINE = InnoDB
ENCRYPTED = YES
ENCRYPTION_KEY_ID = 3;

In the event that you have an existing table and you want to manually enable encryption for that table, then you can do the same with an ALTER TABLE statement:

ALTER TABLE my_db.my_encrypted_table
   ENCRYPTED=YES
   ENCRYPTION_KEY_ID=3;

Enabling Encryption for Redo Log

We can use the following process to safely enable encryption of the InnoDB redo log:

1.) Stop the MariaDB server process.

2.) Set innodb_encrypt_log=ON in the MariaDB configuration file.

3.) Start the MariaDB server process.

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.