In this blog we are going to explore about innodb tablespace encryption technique, which will be useful for securing data.

In MySQL 5.7, a new feature “Innodb Tablespace Encryption“has been added to protect the data at rest. This is a most awaited feature in security. This encryption supports all file per table tablespaces and it will not  support shared tablespace. This encryption technique works on the basis of rotating  key files. There are two types of keyring plugins available for the key management and they are given below

• keyring_file plugin – Available in all MySQL versions.

• keyring_okv plugin – Available in MySQL Enterprise Edition.

Architecture:

Innodb tablespace encryption uses two tier encryption architecture, in which it has master encryption key and Tablespace keys. The master key is used to encrypt and decrypt the tablespace keys whereas tablespace key is used to encrypt and decrypt tablespace data. Here the tablespace is very safe, as it is not viewable by any users.

Master key is stored outside the database as it can see by the users. Tablespace keys are stored in tablespace header which is protected by the Master key. For encrypted tables, tablespace key is encrypted by master key and stored in tablespace header. Here, the encryption algorithm used is AES and encryption mode is block encryption mode(CBC).

In innodb, pages are encrypted using the tablespace key and it is done at the IO layer. A page could be modified may times in the buffer and then gets flushed. So, we avoid encrypting data the page whenever it is modified, instead we used to encrypt just before writing to disk. The encryption is done by background page cleaner threads, which states the query threads don’t spend more CPU. The buffer pool remain decrypted as there is no overhead of pages accessed.

Add keyring plugin:

To use the keyring_file plugin, add early-plugin-load in my.cnf. It will load keyring plugin before innodb is loaded. This is because keyring plugin is used to decrypt tablespaces before applying the redo log. After this we have to provide the location of keyring file data, this master key file is used for encrypting tablespace keys. It should be backed up very often, as losing keyring data file will make data inaccessible.

 
[mysqld]                                                                 ealry-plugin-load=keyring_file.so                                        keyring_file_data=/var/lib/keyring/keyring
	

 
mysql> show variables like '%keyring%';
+-------------------+-------------------------+
| Variable_name     | Value                   |
+-------------------+-------------------------+
| keyring_file_data | /var/lib/keyring/keyring|
+-------------------+-------------------------+
	

After adding the keyring_file, please make sure whether it is enabled on the server.

 
mysql> show plugins;
+-----------------------------+----------+--------------------+
| Name         | Status | Type    | Library         | License |
+-----------------------------+----------+--------------------+
| keyring_file | ACTIVE | KEYRING | keyring_file.so | GPL     |
	

The keyring_file should be active to create encrypted tables.

 
mysql> SELECT TABLE_SCHEMA,TABLE_NAME,CREATE_OPTIONS FROM 
   INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------+------------+---------------------------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS                  |
+--------------+------------+---------------------------------+
| encrypt      | creative   | ENCRYPTION="Y"                  |
+--------------+------------+---------------------------------+
	

Master encryption key should be rotated periodically. Rotating the master encryption key changes only the master encryption key and re-encrypts tablespace keys. It does not decrypt or re-encrypt associated tablespace data.

Below command is used for rotating master key.

 
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;
Query OK, 0 rows affected (0.00 sec)
	

MASTER_TBSKEY – This key is master innodb encryption key. For community server, this config file will saved on the filesystem. For MySQL enterprise server, this will be in memory.

TBS#KEY – This key is hidden and used to encrypt tablespace table. This key never changes and not stored on disk. It will be in mysql internal process memory.

Creating Encrypted Tables:

Once the keyring_file is active, please create a table with encryption and check the status.

 
mysql> create table test(id int primary key, name varchar(20), age int) 
       ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

mysql> show create table test;
+-------+-----------------------------------------------------------------
| Table | Create Table                                                   |
+-------+-----------------------------------------------------------------
 `id` int(11) NOT NULL,
 `name` varchar(20) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ENCRYPTION='Y'                      |
+-------+-----------------------------------------------------------------
	

So, what to do, if we have to encrypt existing table. To do this, the table algorithm should be “COPY” as “INPLACE” algorithm is not supported by when turning encryption ON/OFF.

 
mysql> ALTER TABLE test ENCRYPTION='Y', ALGORITHM=COPY;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE test ENCRYPTION='N', ALGORITHM=COPY;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
	

The complete rebuild of table is done during encryption. Which can be costly operation in case of huge tables and there is a lack of IO.

Master key rotation:

• The master key rotation is an atomic, instance-level operation. Each time the master encryption key is rotated, all tablespace keys in the mysql instance are re-encrypted and saved back to their respective tablespace headers.

• The master encryption key only changes the master encryption key and re-encrypts tablespace keys. It does not decrypt or re-encrypt associated tablespace data.

 
mysql> ALTER Instance rotate innodb master key;
	

Note : It needs SUPER privilege

Validation ( Simple ):

Let us make a validation of encryption .

Load the data into the table. Check the lab_test table ibd file ( Tablespace ) .It contains a plain text contents.

 
[test@labs2 table_space]$ head lab_test.ibd
	

So we can encrypt is table using alter statement.

Now let’s check the lab_test.ibd file.

 
[test@labs2 table_space]$ head lab_test.ibd
	

The data file (lab_test.ibd) is encrypted .

Information Schema and Table space Encryption:

Information schema also stores the metadata information about the encrypted tables.

 
select table_schema,table_name,create_options from information_schema.tables where create_options like '%encryption="Y"%';
	

To have encryption on the percona xtradb cluster, you can visit this blogpercona xtradb cluster, you can visit this blog

Limitations:

• Shared/System tablespaces (ibdata1) is not encrypted.
• Binary, undo and redo logs are not encrypted. ( MySQL 8.0 Supports redo log & undo log encryption ).
• Advanced Encryption Standard(AES) is the only supported encryption algorithm.
• Encrypted table cannot be moved or copied from file_per_table tablespace to unsupported innodb tablespace.
• It supports only InnoDB Engine .
• In a replication topology the plugin ( keyring ) should be enabled on all nodes.