Encryption of tables and table spaces
Contents
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:
Option | Description |
---|---|
none | Default. Data is not encrypted. |
aes_cbc | This is the recommended algorithm as it's approved by several governments in Europa |
aes_ctr | Newer encryption method that Google is using with table space encryption for their MariaDB installations |
aes_ecb | Don'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 option | Values | Description |
---|---|---|
PAGE_ENCRYPTION | 0 or 1 | 1 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:
Variable | Value | Description |
---|---|---|
innodb-encrypt-tables | Boolean (0 or 1) | Encrypt all tables in the storage engine |
innodb-encryption-rotate-key-age | Seconds | Rotate any page having a key older than this |
innodb-encryption-rotation-iop | Positive integer | Use this many Input/Output operations per second for background key rotation |
innodb-encryption-threads | Positive integer | No of threads performing background key rotation and scrubbing |
innodb-background-scrub-data-check-interval | Seconds | Check at this intervall if spaces needs scrubbing |
innodb-background-scrub-data-compressed | Boolean (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-uncompressed | Boolean (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:
Variable | Description |
---|---|
debug-encryption-key-version | If set, use this fixed key instead of asking the encryption key management for the latest key version to use |
debug-use-static-encryption-keys | Enable 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!