Table and Tablespace Encryption
MariaDB starting with 10.1
Encryption of tables and tablespaces is a new feature in MariaDB 10.1.
Contents
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 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. 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:
Option | Description |
---|---|
none | Default. Data is not encrypted. |
aes_cbc | This is the recommended algorithm as it's approved by several governments in Europe. |
aes_ctr | Newer block cipher mode that Google is using with tablespace encryption for their MariaDB installations. |
aes_ecb | This 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 (thepath-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. (Addenable-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 to disable. |
PAGE_ENCRYPTION_KEY | 0-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
to1
. - 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 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:
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
- 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!