Shulou(Shulou.com)06/01 Report--

Description

MySql Community Edition has supported a table-based data encryption scheme since 5.7.11, and the module is called keyring_file, which supports the encryption of the entire table. This encryption method is actually based on file encryption. Once the mysqld read key is started, the data of the entire table will be decrypted. In the mysql service, the read data is decrypted, that is, it is imperceptible to the client. The key is stored locally, and the mysql service has permission to read and write to the key.

Generally speaking, this scheme is not secure because the database files are encrypted, but as long as there is an account for mysql service, the access data is decrypted and the encryption breaks itself. And the decryption key is also stored locally, and the intruder can take it with him. This scheme can only ensure that the intruder cannot read the contents after dragging away the database files.

Three additional modules for enterprise MySQL

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

1.keyring_encrypted_file

It's similar to the community version I said before, except for an extra key. This key is used to encrypt and decrypt the key used in the database. It's all the same in terms of security.

2.keyring_okv

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

3.keyring_aws

Integrate aws's key management service to manage encrypted and decrypted key. Further improve the management security of key.

Encryption types supported by four encryption modules

Module name available encryption algorithm key length limit keyring_encrypted_fileAESDSARSA Unrestricted keyring_fileAESDSARSA Unlimited keyring_okvAES16, 24, 32keyring_awsAES16, 24, 32

To sum up, the four schemes are file encryption and memory decryption, and the difference lies in the key storage scheme of encryption and decryption. It is recommended to use keyring_okv and keyring_aws, and to ensure the security of mysql accounts and strict distinction between account permissions.

The other two are not very safe.

Implementation steps

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

1. Use the latest version of mysql 5.7.21

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

Sudo apt install mysql-5.7

two。 Enable encryption 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 storage path of encrypted key

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 of the appeal steps are temporary, and the restart service will become invalid. We will write the configuration into the configuration file to ensure that it will also take effect after restarting the service.

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

5. View 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 to see if the keyring_file module has been loaded.

Show plugins

Mysql > show plugins +-+ | Name | Status | Type | Library | License | +- -+ | 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 | 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 |. (omit N) | 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. Cancel encryption

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

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.