Introduction
- Enables data-at-rest encryption by encrypting the physical files of the database.
- Data is encrypted automatically in real time prior to writing it to disk and decrypted when reading from disk.
- No Changes in code is required and users/applications can continue to access data without any changes.
- It works only when Innodb file per table is enabled and also system tablespaces are never encrypted.
Architecture
It uses a two tier encryption key architecture, consisting of a master encryption key and tablespace keys.
When an Innodb table is encrypted,a tablespace key is generated using the master encryption key and it is stored in the tablespace header.
When data is accessed from the encrypted table,Innodb uses master encryption key to decrypt the tablespace key.
The decrypted version of tablespace key never changes and master key can be changed as and when required.
Why two keys ?
Whenever a key is compromised or key needs to be changed,all the encrypted innodb tables needs to be altered to understand new encryption key. This is very slow and table will be locked during the alter operation which is logically a downtime.
To avoid altering all the encrypted tables, a tablespace key is maintained in tablespace header which is generated from master key.The decrypted version of the tablespace key is always constant even when the master key is changed. Thus it maintains two keys(two tier encryption) to avoid altering all the encrypted Innodb tables.
How does data is encrypted ?
Innodb uses
i) ECB(Electronic Codebook) for tablespace key encryption.
ii) CBC(Cipher Block Chaining ) for data encryption.
Data is encrypted only on disk and not on Network. On network data will be in clear text format. To enbale encryption of data across the network,TLS/SSL needs to be configured.
Mysql provides tablespace encryption with the help of keyring plugin. There are dfferent varients of keyring plugin in terms of securing the key.
Installing plugin:
When the instance is started, Innodb tries to read tablespace information of all the databases that are present.
- If the tablespace is encrypted,and the plugin is not initialized early, it will be unable to decrypt the datafile which was encrypted to apply the redo logs. To avoid such errors, it is always good to initialize the plugin using early-plugin-load option.
- Once the plugin is loaded, it checks for the master key and its location depends on the type of plugin being loaded.
- keyring_file checks for file on the local storage (by default: /usr/local/mysql/keyring/keyring)
- keyring_okv checks for directory that contains ssl certs and okvclient.ora (by default:
/usr/local/mysql/mysql-keyring-okv )
Thus depending on the plugin being loaded, 2 variables needs to be defined in my.cnf file.
For keyring_file plugin
early-plugin-load=keyring_file.so
keyring_file_data=/usr/local/mysql/mysql-keyring/keyring
For keyring_okv plugin
early-plugin-load=keyring_okv.so
keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv
*** steps needs to be added for configuring Oracle keyvault.
Enabling and Disabling InnoDB Tablespace Encryption
To enable tablespace encryption,
Tables needs to be specified with 'ENCRYPTION' option while creation. (ex: ENCRYPTION='Y' )
For existing tables to enable encryption, tables can be altered by specifying 'ENCRYPTION' option ( ex: alter table <table_name> ENCRYPTION='Y'; )
To disable encryption, use 'ENCRYPTION=N' while creation or alter of the table.
mysql> create table t1(id int,name varchar(20)) encryption='Y';
Query OK, 0 rows affected (0.29 sec)
mysql> ALTER TABLE t1 ENCRYPTION='N';
Query OK, 3 rows affected (0.59 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 ENCRYPTION='Y';
Query OK, 3 rows affected (0.67 sec)
Records: 3 Duplicates: 0 Warnings: 0
|
Using TTS for Encrypted Tablespaces:
TTS can be used for encrypted tablespaces with a limitation that
An Encrypted tablespace can be imported only to another encrypted tablespace. It cannot be imported to a tablespace/instance that doesn't support encryption.
When the command flush table <table> for export is issued, a file named <table_space>.cfp is created(transfer key). This transfer key is used decrypt tablespace key.
Tablespace cannot be imported to other instance without this transfer key.
On 1st Instance:
mysql> select * from t1;
+------+---------+
| id | name |
+------+---------+
| 1 | test1 |
| 10 | test10 |
| 100 | test100 |
+------+---------+
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ENCRYPTION='Y'
mysql> flush tables t1 for export;
Query OK, 0 rows affected (0.01 sec)
[vgurram@localhost encrypt]$ ls
|
Now, Copying the files to the second instance and importing the ibd files on second database instance.
mysql> use encrypt;
Database changed
mysql> CREATE TABLE `t1` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ENCRYPTION='Y';
Query OK, 0 rows affected (0.32 sec)
mysql> alter table t1 discard tablespace;
Query OK, 0 rows affected (0.13 sec)
[vgurram@localhost encrypt]$ pwd
/home/vgurram/test_57/data/encrypt
[vgurram@localhost encrypt]$ cp t1.cfg t1.cfp t1.ibd /home/vgurram/mysql_57/data/encrypt/.
mysql> alter table t1 import tablespace;
Query OK, 0 rows affected (0.47 sec)
mysql> select * from t1;
+------+---------+
| id | name |
+------+---------+
| 1 | test1 |
| 10 | test10 |
| 100 | test100 |
+------+---------+
|
Once the files are copied to the second instance or backup has been taken, run the unlock tables command on the first instance to release the lock.
This will automatically delete the .cfp and .cfg files as well.
On 1st Instance
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
|
Identifying Tables that Use InnoDB Tablespace Encryption
Tables that use Encryption can be identified by using below command
WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| encrypt | t1 | ENCRYPTION="Y" |
+--------------+------------+----------------+
|
Master key rotation:
Master key can be rotated periodically or whenever the key gets compromised.
This key file rotation is atomic and instance level operation.
Whenever master key is rotated,all tablespace keys in the MySQL instance are re-encrypted and saved back to their respective tablespace headers.
If this re-encryption is interrupted by server failure in between,InnoDB rolls the operation forward on server restart.
Rotating the master encryption key requires the
SUPER privilege.
To rotate the master encryption key, run:
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
Limitations of Tablespace Encryption
- If master key ring file is lost, it is impossible to recover data from the Innodb tablespace.
- Altering the table to enable encryption uses "COPY" algorithm instead of "INPLACE"
- Direct migration of plugins from keyring_file to keyring_okv and viceversa is NOT supported.
- Cannot copy encrypted tablespace to another un-encrypted tablespace.
