Encryption of tables and table spaces

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

Overview

Encryption of tables and table spaces is a new feature in MariaDB 10.1. Having tables encrypted will make it almost impossible for someone to access or steal your hard disk and get access to the original data. This assumes that you store the encryption keys at another system.

Using encryption has an overhead of roughly 10%.

Which storage engines supports encryption

For the moment, the only engines that fully supports encryption are XtraDB and InnoDB. The Aria storage engine supports also encryption, but only for temporary tables.

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

  • Table encryption. Only tables which you create with PAGE_ENCRYPTION=1 are encrypted.
  • Table space encryption, in which case everything is encrypted (including log files). This feature was created by google.

Choosing encryption algorithm

All encryption methods in MariaDB are based on AES. AES is a block cipher, which has been 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 Europa
aes_ctrNewer encryption method that Google is using with table space encryption for their MariaDB installations
aes_ecbDon't know when to use this... (Documentation needs updating)

Encryption key management

To be able to encrypt data you need a store the encryption key somewhat. MariaDB supports by default two encryption key management systems, 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 configure options:

  • file_key_management_plugin_filename=path-to-key-file. 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. Supported are keys of size 128, 192 or 256 bits. IV 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 IV follows, and finally a 16 byte AES key. Key identifier from 0-255 are supported.

The key with value 0 is reserved for 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. Open SSL 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, page_encryption feature is not available and access to page_encrypted tables is not possible.

example_key_management_plugin

To get really strong encryption, you would like to change your encryption keys 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 that 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!

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 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 for disabling.
PAGE_ENCRYPTION_KEY 0-255 The key identifier for the encryption. The 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 decrypted and re-encrypted.

ALTER TABLE T page_encryption=0;

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

Table space encryption

To use table space 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 scan regularly trough all your tables and upgrades the encryption keys for the pages.

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

Example my.cnf to enable table space 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 is using Aria for on-disk temporary tables that doesn'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 (feature usable with FusionIO) can be used together. This works by first compressing the data and then encrypting it. In this case you both 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

  • The table space encryption was donated to the MariaDB project by Google.
  • The 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.