Table and Tablespace Encryption

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.1.3

Encryption of tables and tablespaces was added in MariaDB 10.1.3

Overview

Having tables encrypted makes it almost impossible for someone to access or steal a hard disk and get access to the original data. This assumes that encryption keys are stored on another system.

Using encryption has an overhead of roughly 10%.

Which storage engines support encryption?

For the moment, the only engines that fully support encryption are XtraDB and InnoDB. The Aria storage engine also supports encryption, but only for tables created with ROW_FORMAT=PAGE (the default).

MariaDB supports 2 different way to encrypt data in InnoDB/XtraDB:

  • Table encryption: Only tables which you create with PAGE_ENCRYPTION=1 are encrypted. This feature was created by eperi.
  • Tablespace encryption: Everything is encrypted (including log files). This feature was created by Google.

Choosing an encryption algorithm

All encryption methods in MariaDB are based on AES. AES is a block cipher and was chosen as the official Advanced Encryption Standard.

You specify which encryption method to use with the --encryption-algorithm=name startup option for MariaDB. The options are:

OptionDescription
noneDefault. Data is not encrypted.
aes_cbcThis is the recommended algorithm as it's approved by several governments in Europe.
aes_ctrNewer block cipher mode that Google is using with tablespace encryption for their MariaDB installations.
aes_ecbThis block cipher mode is used internally for the counter computation. You may use it for encryption but it does not provide a strong confidentiality.

Encryption key management

To be able to encrypt data encryption key must be stored somewhere. By default, MariaDB supports two encryption key management systems. Both are implemented as plugins.

file_key_management_plugin

The file_key_management_plugin is a key management plugin that provides encryption keys from a file. This plugin has the following configuration options:

  • file_key_management_plugin_filename=path-to-key-file: Where the file is located (the path-to-key-file argument is required).
  • file_key_management_plugin_filekey: An optional key to decrypt the key file.

Example usage in a my.cnf file:

[mysqld]
encryption-algorithm=aes_cbc
file_key_management_plugin_filename = /home/mdb/keys.enc
file_key_management_plugin_filekey = secret

The key file contains AES keys and initialization vectors as hex-encoded strings. 128, 192 or 256-bit keys are supported. The ID consists of 16 bytes. An example key file entry:

1;F5502320F8429037B8DAEF761B189D12;770A8A65DA156D24EE2A093277530142

1 is the key identifier which can be used for table creation; a 16 byte ID follows, and finally a 16 byte AES key. Key identifiers from 0-255 are supported.

The key with value 0 is reserved for the encryption of InnoDB log files.

The key file should be encrypted and the key to decrypt the file can be given with the optional file_key_management_plugin_filekey parameter. The OpenSSL command line utility can be used to create an encrypted key file. For example:

openssl enc –aes-256-cbc –md sha1 –k secret –in keys.txt –out keys.enc
openssl enc –aes-256-cbc –md sha1 –k <initialPwd> –in secret –out secret.enc

If the key file can not be read at server startup, for example if the file key is not present, the page_encryption feature is not available and access to page_encrypted tables is not possible.

example_key_management_plugin

To get really strong encryption, encryption keys must be changed at regular intervals.

The example_key_management_plugin is an example of how to do this. By replacing this plugin with your own key management system which gets the key remotely you can create a very secure system.

This is what Google is using internally.

Note that the example_key_management_plugin is only a template for your own plugin!

eperi Gateway for Databases

eperi provides a key management and encryption solution for MariaDB. This solution is compatible with all forms of encryption in MariaDB.

The keys are stored at the key server and optionally all encryption is done on the key server as well. This prevents an attacker with file system access from unauthorized reading the database files (see Why Encrypt MariaDB Data?).

The eperi Gateway for Databases provides the following key benefits:

  • Key management outside the database
  • No keys on the databases server hard disk
  • Graphical user interface for configuration
  • Encryption and decryption outside the database, supporting HSMs for maximum security

Encrypting data

Table level encryption

Table level encryption means that you choose which tables to encrypt. This allows you to balance security with speed.

To use table encryption, you have to:

  • Set the value of encryption-algorithm to the algorithm of your choice.
  • Load the file-key-management-plugin or some other similar plugin. (Add enable-file-key-management-plugin to your my.cnf file).

To encrypt a table you have to use some of the new options for the CREATE TABLE or ALTER TABLE statement:

Table optionValuesDescription
PAGE_ENCRYPTION0 or 11 for enabling this feature, 0 to disable.
PAGE_ENCRYPTION_KEY0-255 The key identifier. This value identifies the encryption key used.

Examples:

CREATE TABLE T (id int, value varchar(255))
PAGE_ENCRYPTION=1 PAGE_ENCRYPTION_KEY=17;

This creates table T which is encrypted with key 17.

ALTER TABLE T PAGE_ENCRYPTION=1 PAGE_ENCRYPTION_KEY=18;

Alters table T to be encrypted with key 18. If it was encrypted before, it's first decrypted and then re-encrypted.

ALTER TABLE T page_encryption=0;

Disables the encryption of table T. If it was encrypted before, it's decrypted.

Tablespace encryption

To use tablespace encryption, you have to:

  • Set the value of encryption-algorithm to the algorithm of your choice.
  • Set innodb-encrypt-tables to 1.
  • Load file-key-management-plugin or some other similar plugin. (Add enable-file-key-management-plugin to your my.cnf file).
  • Enable innodb-tablespaces-encryption plugin.
  • Enable innodb-tablespaces-scrubbing plugin.

To configure encryption, you can set the following variables:

VariableValueDescription
innodb-encrypt-tables Boolean (0 or 1) Encrypt all tables in the storage engine
innodb-encryption-rotate-key-age SecondsRotate any page having a key older than this
innodb-encryption-rotation-iopPositive integer  Use this many Input/Output operations per second for background key rotation
innodb-encryption-threads Positive integerNo of threads performing background key rotation and scrubbing
innodb-background-scrub-data-check-intervalSeconds Check at this intervall if spaces needs scrubbing
innodb-background-scrub-data-compressedBoolean (0 or 1)Enable scrubbing of compressed data by background threads (same as encryption_threads)
innodb-background-scrub-data-interval Seconds Scrub spaces that were last scrubbed longer than this many seconds ago.
innodb-background-scrub-data-uncompressedBoolean (0 or 1)  Enable scrubbing of uncompressed data by background threads (same as encryption_threads)

Scrubbing means that there is a background process that regularly scans through all tables and upgrades the encryption keys for the pages.

This happens either as part of purge (non compressed) or scrubbing by scanning whole tablespaces (added into key rotation threads). Purge is a a type of garbage collection that InnoDB internally runs to improve performance.

Example my.cnf to enable tablespace encryption:

[[mysqld]]
encryption-algorithm=aes_ctr
innodb-tablespaces-encryption
innodb-tablespaces-scrubbing
innodb-encrypt-tables
innodb-encryption-threads=4

Encryption of Aria tables

Only tables created with ROW_FORMAT=PAGE can be encrypted. This is the default row format for Aria.

You can specify that all Aria tables of the above type are encrypted by specifying:

[[mysqld]]
aria-encrypt-tables=1

Encryption of Aria temporary tables

MariaDB uses Aria for on-disk temporary tables that don't fit into MEMORY tables.

To ensure that no one can access data stored in temporary tables created as part of query execution, you can encrypt the temporary data by specifying in your my.cnf file:

encrypt-tmp-disk-tables=1

This works by creating and using a random encryption key for every new temporary table.

Encryption and compression

Encryption and compression (a feature usable with FusionIO) can be used together. This works by first compressing the data and then encrypting it. In this case you save space and the data is even harder to decrypt.

Variables and configure options used for testing of encryption:

VariableDescription
debug-encryption-key-versionIf set, use this fixed key instead of asking the encryption key management for the latest key version to use
debug-use-static-encryption-keysEnable use of nonrandom encryption keys. Only to be used in internal testing.

Thanks

  • Tablespace encryption was donated to the MariaDB project by Google.
  • Table encryption was donated to the MariaDB project by eperi.

We are grateful to these companies for their support of MariaDB!

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.