MariaDB Data encryption at Rest

MariaDB Data at Rest Encryption

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 3-5%.

Which storage engines does MariaDB encryption support?

MariaDB encryption is fully supported for the XtraDB and InnoDB storage engines. Additionally, encryption is supported for the Aria storage engine, but only for tables created with ROW_FORMAT=PAGE (the default).

MariaDB allows the user to configure flexibly what to encrypt. In XtraDB or InnoDB, one can choose to encrypt:

  • everything — all tablespaces (with all tables)
  • individual tables
  • everything, excluding individual tables

Additionally, one can choose to encrypt XtraDB/InnoDB log files (recommended)

 

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 with FILE: the rest of the value is interpreted as a path to the file that contains the key. You will most likely want to use the form FILE:/path/to/filekey so that the actual filekey cannot be read by anyone via a SHOW command. Consequently, that file would need the proper permissions so that mysql can read it, but not unauthorized users.
  • 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 comment1;770A8A65DA156D24EE2A09327753014218;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 must always exist. Key number 2 is optional, but if it exists, it will be used for temporary tables and temporary files.

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.

`

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. The second — AES_CTR — is the recommended one, but it may not always be available (requires a recent OpenSSL). If set to AES_CBC, the plugin will use AES with 128-bit keys in the CBC mode. If set to AES_CTR, the plugin will use AES with the 128-bit keys in the CTR mode for encrypting tablespace pages (InnoDB, XtraDB, and Aria), and it will use AES in the authenticated GCM mode for temporary files (where the cyphertext is allowed to be larger than the plaintext).

Example usage in a my.cnf file:

[mysqld]file_key_management_encryption_algorithm=aes_cbcfile_key_management_filename = /home/mdb/keys.encfile_key_management_filekey = secret

 

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

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=YESoption.

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.sofile-key-managementfile-key-management-filename = /mount/usb1/keys.txtinnodb-encrypt-tablesinnodb-encrypt-loginnodb-encryption-threads=4

Specifying which tables to encrypt

You can choose which tables to encrypt. This allows you to balance security with speed. In order to use these options, innodb_file_per_table must be set to ON (the default).

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.

If innodb_file_per_table is OFF, or the ENCRYPTION_KEY_ID is not found, these statements will fail with errno: 140 “Wrong create options”.

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

 

 

Lab work out :

 

[root@dbversity.com elt_test]# pwd

/var/opt/rh/rh-mariadb101/lib/mysql/elt_test

[root@dbversity.com elt_test]#

[root@dbversity.com elt_test]# ll -lhtr

total 4.7G

-rw-rw—- 1 mysql mysql   65 May 22 10:20 db.opt

-rw-rw—- 1 mysql mysql 4.5K May 22 10:41 tera_df_to_text.frm

-rw-rw—- 1 mysql mysql  13K May 23 07:03 THRESHOLD.frm

-rw-rw—- 1 mysql mysql 1.4G May 23 07:08 tera_df_to_text.ibd

-rw-rw—- 1 mysql mysql 3.4G May 23 07:46 THRESHOLD.ibd

[root@dbversity.com elt_test]#

[root@dbversity.com elt_test]#

[root@dbversity.com elt_test]# strings  tera_df_to_text.frm  | head

>.>>

InnoDB

account_sid

account_nbr

location_cd

country_cd

lob_cd

[root@dbversity.com elt_test]#

[root@dbversity.com elt_test]# xxd tera_df_to_text.ibd  | head

0000000: f840 c33b 0000 0000 0000 0000 0000 0000  .@.;…………

0000010: 0000 0000 7cf2 5272 0008 0000 0000 0000  ….|.Rr……..

0000020: 0000 0000 0004 0000 0004 0000 0000 0001  …………….

0000030: 4f00 0001 49c0 0000 0000 0000 000d 0000  O…I………..

0000040: 0001 0001 4000 068e 0001 4000 068e 0000  ….@…..@…..

0000050: 0005 0000 4000 009e 0001 4000 009e 0000  ….@…..@…..

0000060: 0001 0000 0000 009e 0000 0000 009e 0000  …………….

0000070: 0000 0000 0003 0000 0000 ffff ffff 0000  …………….

0000080: ffff ffff 0000 0000 0001 0000 0002 0026  ……………&

0000090: 0000 0002 0026 0000 0000 0000 0000 ffff  …..&……….

[root@dbversity.com elt_test]#

[root@dbversity.com elt_test]# strings tera_df_to_text.ibd  | head

infimum

supremum

RrGU

infimum

supremum

00XXXX107170048002482210POL   ALL

008811370005065494520502MEX   BNK_CRD

00XXXX430453571284520203MEX   BNK_CRD

00XXXX430305518293520203MEX   BNK_CRD

000000666600128357120163THA   ALL

[root@dbversity.com elt_test]#

 

 

 

 

[root@dbversity.com elt_test]# openssl enc -aes-256-cbc -P -md sha1

enter aes-256-cbc encryption password:

Verifying – enter aes-256-cbc encryption password:

salt=7C9EF12FBD829D73

key=B2D1E27B4899F75F7D7EDFEB213393256A5E8873983A960C68E284BE8153675D

iv =5B5C5FA92CDCD24EF525671DCDD7F1C0

[root@dbversity.com elt_test]#

 

 

 

 

 

[root@dbversity.com ~]# cat /etc/opt/rh/rh-mariadb101/pki/key.txt

1;5B5C5FA92CDCD24EF525671DCDD7F1C0;B2D1E27B4899F75F7D7EDFEB213393256A5E8873983A960C68E284BE8153675D

[root@dbversity.com ~]#

 

 

[root@dbversity.com pki]# ll -lhtr /opt/rh/rh-mariadb101/root/usr/lib64/mysql/plugin/file_key_management.so

-rwxr-xr-x 1 root root 15K Nov 24  2016 /opt/rh/rh-mariadb101/root/usr/lib64/mysql/plugin/file_key_management.so

[root@dbversity.com pki]#

 

 

 

[root@dbversity.com ~]#

[root@dbversity.com ~]# cat /etc/opt/rh/rh-mariadb101/my.cnf

 

 

# Data Encryption at Rest

 

plugin-load                                     =       file_key_management.so

file_key_management_encryption_algorithm        =       aes_cbc

file_key_management_filename                    =       /etc/opt/rh/rh-mariadb101/pki/key.txt

 

innodb-encrypt-tables

innodb-encrypt-log

innodb-encryption-threads       =       4

innodb_file_per_table           =       ON

 

[root@dbversity.com ~]#

 

[root@dbversity.com ~]# service rh-mariadb101-mariadb restart

Stopping rh-mariadb101-mariadb:                            [  OK  ]

Enter PEM pass phrase:

Starting rh-mariadb101-mariadb:                            [  OK  ]

[root@dbversity.com ~]#

 

 

 

 

[root@dbversity.com ~]#  mysql -u dbadmin  -’Password’

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.1.19-MariaDB MariaDB Server

 

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

 

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

 

MariaDB [(none)]>  use data_encryption_at_rest

Database changed

 

MariaDB [data_encryption_at_rest]> show variables like ‘%encrypt%’;

+——————————————+———+

| Variable_name                            | Value   |

+——————————————+———+

| aria_encrypt_tables                      | OFF     |

| encrypt_binlog                           | OFF     |

| encrypt_tmp_disk_tables                  | OFF     |

| encrypt_tmp_files                        | OFF     |

| file_key_management_encryption_algorithm | aes_cbc |

| innodb_default_encryption_key_id         | 1       |

| innodb_encrypt_log                       | ON      |

| innodb_encrypt_tables                    | ON      |

| innodb_encryption_rotate_key_age         | 1       |

| innodb_encryption_rotation_iops          | 100     |

| innodb_encryption_threads                | 4       |

+——————————————+———+

11 rows in set (0.00 sec)

 

MariaDB [data_encryption_at_rest]> CREATE TABLE tbl_encrypted (id int, value varchar(255)) ENCRYPTED=YES ENCRYPTION_KEY_ID=1;

Query OK, 0 rows affected (0.05 sec)

 

MariaDB [data_encryption_at_rest]>

MariaDB [data_encryption_at_rest]> show create table tbl_encrypted\G

*************************** 1. row ***************************

Table: tbl_encrypted

Create Table: CREATE TABLE `tbl_encrypted` (

`id` int(11) DEFAULT NULL,

`value` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1

1 row in set (0.01 sec)

 

MariaDB [data_encryption_at_rest]>

 

 

 

 

MariaDB [data_encryption_at_rest]> show variables like ‘%innodb_file_per%’;

+———————–+——-+

| Variable_name         | Value |

+———————–+——-+

| innodb_file_per_table | ON    |

+———————–+——-+

1 row in set (0.00 sec)

 

MariaDB [data_encryption_at_rest]> show variables like ‘%innodb_%encrypt%’;

+———————————-+——-+

| Variable_name                    | Value |

+———————————-+——-+

| innodb_default_encryption_key_id | 1     |

| innodb_encrypt_log               | ON    |

| innodb_encrypt_tables            | ON    |

| innodb_encryption_rotate_key_age | 1     |

| innodb_encryption_rotation_iops  | 100   |

| innodb_encryption_threads        | 4     |

+———————————-+——-+

6 rows in set (0.01 sec)

 

MariaDB [data_encryption_at_rest]>

 

 

 

[root@dbversity.com data_encryption_at_rest]# strings tbl_encrypted.ibd | head

1Y<o

i330(

u~Pv

gdeT

:^3xQ

*,OK

]]B*

AQ`LK

9ng’

vu|IUr

[root@dbversity.com data_encryption_at_rest]#

[root@dbversity.com data_encryption_at_rest]#

 

 

MariaDB [data_encryption_at_rest]> alter table tbl_encrypted ENCRYPTED=NO;

Query OK, 1 row affected (0.06 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

MariaDB [data_encryption_at_rest]>

MariaDB [data_encryption_at_rest]> select * from tbl_encrypted;

+——+—————-+

| id   | value          |

+——+—————-+

|    1 | Encrypted Data |

+——+—————-+

1 row in set (0.01 sec)

 

MariaDB [data_encryption_at_rest]>

 

 

 

[root@dbversity.com data_encryption_at_rest]# strings tbl_encrypted.ibd | head

infimum

supremum

Encrypted Data

[root@dbversity.com data_encryption_at_rest]#

 

 

 

 

 

 

MariaDB [data_encryption_at_rest]> ALTER TABLE tbl_encrypted ENCRYPTED=YES;

Query OK, 1 row affected (0.08 sec)

Records: 1  Duplicates: 0  Warnings: 0

MariaDB [data_encryption_at_rest]> select * from information_schema.innodb_tablespaces_encryption where encryption_scheme=1 and name like ‘data_encryption_at_rest%’\G

*************************** 1. row ***************************

SPACE: 11

NAME: data_encryption_at_rest/tbl_encrypted

ENCRYPTION_SCHEME: 1

KEYSERVER_REQUESTS: 1

MIN_KEY_VERSION: 1

CURRENT_KEY_VERSION: 1

KEY_ROTATION_PAGE_NUMBER: NULL

KEY_ROTATION_MAX_PAGE_NUMBER: NULL

CURRENT_KEY_ID: 1

1 row in set (0.00 sec)

 

MariaDB [data_encryption_at_rest]>

 

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 of temporary files

MariaDB might create temporary files on disk. For example, temporary files are created for binary log transactional caches and for filesort. Since 10.1.5, these temporary files can also be encrypted if –encrypt-tmp-files is specified on the command line or in the my.cnf file.

Encryption of binary logs

Since 10.1.7, MariaDB can also encrypt binary logs (including relay logs). Binary logs are encrypted if –encrypt-binlog is specified on the command line or in the my.cnf file.

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.

 

 

Limitations

These limitations exist in the data-at-rest encryption implementation in MariaDB 10.1:

  • Only data and only at rest is encrypted. Metadata (for example .frm files) and data sent to the client are not encrypted (but see Secure Connections).
  • Only the MariaDB server knows how to decrypt the data, in particular
  • The disk-based Galera gcache is not encrypted (MDEV-9639).
  • The Audit plugin cannot create encrypted output. Send it to syslog and configure the protection there instead.
  • File-based general query log and slow query log cannot be encrypted (MDEV-9639).
  • The Aria log is not encrypted (MDEV-9639). This affects only non-temporary Aria tables though.
  • The MariaDB error log is not encrypted. The error log can contain query text and data in some cases, including crashes, assertion failures, and cases where InnoDB/XtraDB write monitor output to the log to aid in debugging. It can be sent to syslog too, if needed.

 

Synopsis :

 

 

Table file with Data encrypted appears as below when try to read it with commands like strings/xxd.

 

[root@dbversity.com data_encryption_at_rest]# strings tbl_encrypted.ibd | head

1Y<o

i330(

u~Pv

gdeT

:^3xQ

vu|IUr

[root@dbversity.com data_encryption_at_rest]#

 

Once I disable the encryption with  SQL command like ‘ ALTER TABLE tbl_encrypted ENCRYPTED=NO; ‘ then I can able to read the table file.

 

[root@dbversity.com data_encryption_at_rest]# strings tbl_encrypted.ibd | head

infimum

supremum

Encrypted Data

[root@dbversity.com data_encryption_at_rest]#

 

 

  • Ask Question