MySQL Encryption Solution keyring_file Detailed Explanation

Description

MySql Community Edition supports table-based data encryption schemes starting from 5.7. 11, with a module named keyring_file that supports encryption of entire tables. This encryption method is actually based on file encryption. After mysqld reads key and starts, it will decrypt the data of the whole table. In mysql service, the read data is decrypted, which means that it is imperceptible to the client. While the key is stored locally, the mysql service has access to read and write the key.

Generally speaking, this scheme is not very safe, because the database files are encrypted, but as long as there is an account of mysql service, the accessed data will be decrypted, and the encryption will not break itself. Moreover, the decryption key is also stored locally, and the intruder can completely take it away. This scheme can only ensure that intruders can't read the contents after dragging away the database files.

Three additional modules of enterprise MySQL

If it is the enterprise version of mysql, there are three other encryption schemes.

1.keyring_encrypted_file

It's similar to the community version I mentioned before, except for one more key. This key is used to encrypt and decrypt key for database. Security is similar.

2.keyring_okv

Compared with storing key locally, this module uses KMIP to access key, which is relatively safer.

3.keyring_aws

Integrate the key management service of aws to manage the encryption and decryption of key. Take a step forward to improve the management security of key.

Encryption types supported by 4 encryption modules

模块名	可用加密算法	密钥长度限制
keyring_encrypted_file	AES DSA RSA	无限制 无限制 无限制
keyring_file	AES DSA RSA	无限制 无限制 无限制
keyring_okv	AES	16, 24, 32
keyring_aws	AES	16, 24, 32

Summary 1, four schemes are file encryption, memory decryption scheme, the difference lies in the encryption and decryption of key storage scheme. It is recommended to use keyring_okv and keyring_aws, and ensure the security of mysql account and strictly distinguish account permissions.

The other two are not safe.

Implementation steps

OK, let's briefly talk about the simplest deployment scheme of keyring_file. It seems that windows can't use this scheme in advance, because I don't know why key for encryption can't always be generated.

1. Use the latest version of mysql 5.7. 21

Install the latest version of mysql using tools such as yum apt or download the source code to compile and install it yourself

sudo apt install mysql-5.7

2. Enable the cryptographic module

INSTALL PLUGIN keyring_file soname 'keyring_file. so';

mysql> INSTALL PLUGIN keyring_file soname 'keyring_file.so';
Query OK, 0 rows affected (0.10 sec)

3. Set the encrypted key storage path

set global keyring_file_data='/root/mysql-keyring/keyring';

mysql> set global keyring_file_data='/var/lib/mysql-keyring/keyring';
Query OK, 0 rows affected (0.00 sec)

4. Permanently enable settings

Both steps of appeal are temporary, and the service will be invalid if restarted. We write the configuration in the configuration file to ensure that it will take effect after restarting the service

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/root/mysql-keyring/keyring

5. Check the storage path of key

show global variables like '% keyring_file_data%';

mysql> show global variables like '%keyring_file_data%';
+-------------------+--------------------------------+
| Variable_name   | Value             |
+-------------------+--------------------------------+
| keyring_file_data | /var/lib/mysql-keyring/keyring |
+-------------------+--------------------------------+
1 row in set (0.00 sec)

6. View enabled modules

Check whether the next keyring_file module has been loaded.
show plugins;

mysql> show plugins;
+----------------------------+----------+--------------------+-----------------+---------+
| Name            | Status  | Type        | Library     | License |
+----------------------------+----------+--------------------+-----------------+---------+
| binlog           | ACTIVE  | STORAGE ENGINE   | NULL      | GPL   |
| mysql_native_password   | ACTIVE  | AUTHENTICATION   | NULL      | GPL   |
| sha256_password      | ACTIVE  | AUTHENTICATION   | NULL      | GPL   |
| PERFORMANCE_SCHEMA     | ACTIVE  | STORAGE ENGINE   | NULL      | GPL   |
| CSV            | ACTIVE  | STORAGE ENGINE   | NULL      | GPL   |
| MRG_MYISAM         | ACTIVE  | STORAGE ENGINE   | NULL      | GPL   |
| MyISAM           | ACTIVE  | STORAGE ENGINE   | NULL      | GPL   |
| InnoDB           | ACTIVE  | STORAGE ENGINE   | NULL      | GPL   |
| INNODB_TRX         | ACTIVE  | INFORMATION SCHEMA | NULL      | GPL   |
| INNODB_LOCKS        | ACTIVE  | INFORMATION SCHEMA | NULL      | GPL   |
| INNODB_LOCK_WAITS     | ACTIVE  | INFORMATION SCHEMA | NULL      | GPL   |
| INNODB_CMP         | ACTIVE  | INFORMATION SCHEMA | NULL      | GPL   |
| INNODB_CMP_RESET      | ACTIVE  | INFORMATION SCHEMA | NULL      | GPL   |

 . . . . . . ( Omission N Article )

| keyring_file        | ACTIVE  | KEYRING      | keyring_file.so | GPL   |
+----------------------------+----------+--------------------+-----------------+---------+
45 rows in set (0.00 sec)

7. Encrypt existing tables

alter table table encryption='Y';

mysql> create table cc (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table cc encryption='Y';
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

8. Unencrypt

alter table table encryption='N';

mysql> alter table cc encryption='N';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Official documents:

https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html