Table Encryption
MariaDB starting with 10.1.3
Encryption of tables and tablespaces was added in MariaDB 10.1.3. There were substantial changes made in MariaDB 10.1.4, and the description below applies only to MariaDB 10.1.4 and later
Contents
The XtraDB/InnoDB on disk format for encrypted tablespaces and logs was changed in MariaDB 10.1.4. Tables encrypted in MariaDB 10.1.3 may fail to open.
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 allows the user to configure flexibly what to encrypt. One can choose to encrypt everything in XtraDB/InnoDB — all tablespaces (with all tables) and log files. Or individual tables. Or everything, but excluding individual tables.
Encryption key management
MariaDB encryption supports multiple encryption keys, they are identified by a key identifier — a 32-bit integer. To support automatic key rotation every key additionally might have different versions. XtraDB and InnoDB can automatically re-encrypt the data from an older to a newer version of the same key. But how different keys are stored and rotated depends on the key management solution that you choose.
Key management in MariaDB is provided by encryption plugins. MariaDB includes one such plugin — file_key_management.
file_key_management plugin
The file_key_management plugin is an encryption plugin that reads encryption keys from a file. This plugin has the following configuration options:
file_key_management_filename
: Where the file is located. This option is required, the plugin will not work without it.file_key_management_filekey
: An optional key to decrypt the key file. If the key starts withFILE:
the rest of the value is interpreted as a path to the file that contains the key.file_key_management_encryption_algorithm
: the encryption algorithm to use.
file_key_management_filename
The key file contains encryption keys identifiers (32-bit numbers) and hex-encoded encryption keys, separated by a semicolon. 128, 192 or 256-bit keys are supported. Comments start from the hash character. An example key file entry:
# this is a comment 1;770A8A65DA156D24EE2A093277530142 18;F5502320F8429037B8DAEF761B189D12F5502320F8429037B8DAEF761B189D12
1 is the key identifier which can be used for table creation, it is followed by the encryption key, in hex.
Key identifiers can be specified per table, but the system XtraDB/InnoDB tablespace and log files always use the key number 1, so it generally must always exist.
This plugin does not support key rotation — all keys always have the version 1.
If the key file can not be read at server startup, for example if the file key is not present, the encryption will not work and encrypted tables will be unreadable.
file_key_management_filekey
The key file (from above) may be encrypted and the key to decrypt the file can be given
with the optional file_key_management_filekey
parameter.
To encrypt the key file use the The OpenSSL command line utility. For example:
openssl enc -aes-256-cbc -md sha1 -k secret -in keys.txt -out keys.enc
Note, that you must use -aes-256-cbc
and sha1
otherwise the plugin won't be able to decrypt the key file.
file_key_management_encryption_algorithm
This plugin supports two encryption algorithms, both use AES but with different block cipher modes.
The file_key_management_encryption_algorithm
can be set to AES_CBC
or AES_CTR
.
Example usage in a my.cnf file:
[mysqld] file_key_management_encryption_algorithm=aes_cbc file_key_management_filename = /home/mdb/keys.enc file_key_management_filekey = secret
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
To enable encryption you have to load an encryption plugin, for example a file_key_management plugin. And configure storage engines to use it. The latter is storage engine specific:
XtraDB and InnoDB
- Set
innodb-encrypt-tables
toON
orFORCE
. - Set
innodb-encrypt-log
toON
.
To fine-tune the encryption, you can use following variables:
Variable | Value | Description |
---|---|---|
innodb-encrypt-tables | ON , OFF , or FORCE | Enable encryption for tables |
innodb-encrypt-log | Boolean | Enable encryption for log files |
innodb-encryption-rotate-key-age | Positive integer | Re-encrypt in background all pages that were encrypted with a key at least that many versions old |
innodb-encryption-rotation-iops | Positive integer | Use this many Input/Output operations per second for background key rotation |
innodb-encryption-threads | Positive integer | Number of threads performing background key rotation and scrubbing |
Note, that generally you should not enable only innodb-encrypt-tables
while keeping innodb-encrypt-log
disabled. In this setup log files will contain your data unencrypted. The opposite case is fine, you may want to enable only innodb-encrypt-log
, disable innodb-encrypt-tables
, and activate encryption per table with the ENCRYPTED=YES
option.
Also it'a good idea to enable encryption for temporary tables (see below).
Example my.cnf to enable XtraDB encryption:
[mysqld] plugin-load-add=file_key_management.so file-key-management file-key-management-filename = /mount/usb1/keys.txt innodb-encrypt-tables innodb-encrypt-log innodb-encryption-threads=4
Specifying what tables to encrypt
You can choose what tables to encrypt. This allows you to balance security with speed.
To encrypt a table use the following options for the CREATE TABLE or ALTER TABLE statement:
Table option | Values |
---|---|
ENCRYPTED | YES or NO |
ENCRYPTION_KEY_ID | Positive integer |
Examples:
CREATE TABLE T (id int, value varchar(255)) ENCRYPTED=YES ENCRYPTION_KEY_ID=17;
This creates table T
which is encrypted with key 17.
ALTER TABLE T ENCRYPTED=YES ENCRYPTION_KEY_ID=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 encrypted=NO;
Disables the encryption of table T
. If it was encrypted before, it's
decrypted.
Related configuration variables:
Variable | Value | Description |
---|---|---|
innodb_encrypt_tables | ON , OFF , or FORCE | Whether to encrypt tables that don't have ENCRYPTED option. If set to FORCE — prevent creation of tables with ENCRYPTED=NO |
innodb_default_encryption_key_id | Positive integer | The default value of the ENCRYPTION_KEY_ID for all tables that don't have it explicitly |
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 still have your data protected.
Thanks
- Tablespace encryption was donated to the MariaDB project by Google.
- Per-table encryption and key identifier support was donated to the MariaDB project by eperi.
We are grateful to these companies for their support of MariaDB!
See Also
- Encryption functions
- DES_DECRYPT()
- DES_encrypt()
- A Blog post about table encryption with benchmark results