CODEWITHSUNDEEP
mysqlencryption
Vivek Tankaria
Vivek Tankaria

Reputation: 1311

How does Encryption on MySQL work MySQL version 5.7?

Following steps are followed to enable MySQL encryption.

  1. Mysql version 5.7 is installed on apache server. So by default keyring_file.so is available at following path: /usr/lib64/mysql/plugin/keyring_file.

  2. In /etc/my.cnf below 2 code is added and MySQL is restarted.

early-plugin-load=keyring_file.so

keyring_file_data=/var/lib/mysql-keyring/keyring
  1. When below query is executed to check if keyring plugin is active. It outputs as active

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
  1. Encryption is enabled on table level (on table author of DB testDB), It can be checked using below query
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';

+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| testDB       | author     | ENCRYPTION="Y" |
+--------------+------------+----------------+
  1. Data is inserted in author table as "plain text". However, though the table is encrypted.
select * from author;

+------+----------+-------------+
| id   | name     | email       |
+------+----------+-------------+
|    1 | PQR      | [email protected] |
|    1 | XYZ      | [email protected] |
|    1 | SSSS     | [email protected] |
|    1 | dfdfdf   | [email protected] |
+------+----------+-------------+

What needs to be done to enable encryption on MySQL table?

Upvotes: 1

Views: 1100

Answers (1)

Shadow
Shadow

Reputation: 34285

What you enabled was innodb data at rest encryption, which is a transparent encryption technique, meaning authenticated and authorised users will not even notice it. As the mysql FAQ says on decryption:

InnoDB data-at-rest encryption is designed to transparently apply encryption within the database without impacting existing applications. Returning data in encrypted format would break most existing applications. InnoDB data-at-rest encryption provides the benefit of encryption without the overhead associated with traditional database encryption solutions, which would typically require expensive and substantial changes to applications, database triggers, and views.

Upvotes: 2

Related Questions