InnoDB / XtraDB Enabling Encryption

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

There are two variables that relate to enabling encryption for InnoDB and XtraDB data. The innodb_encrypt_tables system variable allows you to encrypt InnoDB's system tablespace and InnoDB's file-per-table tablespaces. The innodb_encrypt_log system variable allows you to encrypt InnoDB's redo log.

There are also two table options that relate to enabling encryption for InnoDB and XtraDB data. These are the ENCRYPTED and ENCRYPTION_KEY_ID table options.

If you would like to use encryption, then it is the best practice to encrypt both InnoDB tablespace files and InnoDB redo log files. Otherwise, if you only encrypted the tablespace files, then your data would remain unencrypted in the redo log files.

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.