Bug #96977 MySQL community edition table space encryption allows restore of DB without key
Submitted: 23 Sep 13:44 Modified: 2 Oct 16:40
Reporter: seyar lawrin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Security: Encryption Severity:S3 (Non-critical)
Version:5.7.27 OS:Ubuntu (16.04.1)
Assigned to: CPU Architecture:Other
Tags: MySQL community table space encryption, mysql encryption, mysql table space encryption

[23 Sep 13:44] seyar lawrin
Description:
I encrypted database tables using MySQL 5.7.27 community edition table space encryption. When i take a dump of the database, and restore it to a different server, it doesn't ask me for the encryption key. I can restore the DB and encrypted tables without providing the encryption key used to encrypt the tables. 

How to repeat:
1. load the keyring plugin in mysqld section of .cnf file. 
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

2. restart mysql services. 
3. check the plugin is active. 
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE 'keyring%';
4. check InnoDB file per table is on.
SHOW VARIABLES LIKE 'innodb_file_per_table';

5. encrypt the tables. 
mysql> ALTER TABLE target1 ENCRYPTION='Y';

6. check the table is encrypted. 
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
       WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
7. take a dump of the DB that has encrypted table. 

mysqldump -u root -p  --databases testDB>testDb.sql
8. copy the dump file to another server. 
scp testDb.sql MySQLtest@10.263.28.128:

9. perform steps 1-4 to load MySQL encryption keyring plugin in destination server where you will restore the encrypted table. 

Note: I am not loading the keying file that was used to encrypt the table initially on the source server. I am just loading the keyring plugin. if the keyring plugin is not loaded the restore fails.  

10. restore the dump of the DB. 
  
mysql-u root -p >testDb.sql

The database is restored, and the encrypted tables can be read.
[23 Sep 16:10] Sinisa Milivojevic
Hi Mr. lawrin,

Thank you for your bug report.

We do need to know some more info, before we proceed .......

First of all, if you the target server with the same keyring file as the source server, does it get encrypted correctly ???

Second, have you tried the same operation with latest 8.0 ???

Third, if you query I_S for keyring file(s) on both servers, what are the results that you get.

Fourth, is the table on the target server encrypted at all ???
[24 Sep 14:00] seyar lawrin
Hi Sinisa,

1. No, my target and source server have completely different keys. In fact, I tested the encryption in multiple servers, including test in an isolated environment. Yes, the tables seems to get encrypted properly. when i select encrypted tables from I_S, i get all tables i encrypted. 
 
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
    ->        WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------------+---------------------------+----------------+
| TABLE_SCHEMA       | TABLE_NAME                | CREATE_OPTIONS |
+--------------------+---------------------------+----------------+
| Test_development   | Test_1_encrypt            | ENCRYPTION="Y" |
| Test_development   | Test_encryp_2             | ENCRYPTION="Y" |
+--------------------+---------------------------+----------------+
2 rows in set (0.02 sec)

2. No, I have not tried the same operation in MySQL 8.0. Our environment is  MySQL 5.7.27. I wanna be focused on this specific version. 

3. Here is the result i get when i query I_S for keyring file. 

source Server: 

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

target Server: 
 
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

4. Yes, when i select encrypted tables in source and target server it shows as encrypted. 

source server:

 SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES  WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------------+----------------+----------------+
| TABLE_SCHEMA       | TABLE_NAME     | CREATE_OPTIONS |
+--------------------+----------------+----------------+
| Test_development   | Test_1_encrypt | ENCRYPTION="Y" |
| Test_development   | Test_encryp_2  | ENCRYPTION="Y" |
+--------------------+----------------+----------------+
2 rows in set (0.01 sec)

Target server:

 SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES  WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------------+----------------+----------------+
| TABLE_SCHEMA       | TABLE_NAME     | CREATE_OPTIONS |
+--------------------+----------------+----------------+
| Test_development   | Test_1_encrypt | ENCRYPTION="Y" |
| Test_development   | Test_encryp_2  | ENCRYPTION="Y" |
+--------------------+----------------+----------------+
2 rows in set (0.01 sec)
[24 Sep 14:04] Sinisa Milivojevic
Hence, I do not see what is the bug here ????
[24 Sep 14:19] seyar lawrin
So, MySQL allows you to restore encrypted table(S) without encryption key that was used to encrypt the table at the source server?
[25 Sep 11:30] Sinisa Milivojevic
Hi,

Yes, that is true. Especially when you dump the table. There are other options, in the version 8.0, that provide more security. However, dumping the table in 5.7 will restore on other server without a problem. Version 8.0 has introduced many new restrictions, which are increasing security.

Do note that first objective of encryption is to prevent the reading of the tablespace file, for those users that have only the access to the filesystem.

Not a bug.
[25 Sep 14:02] seyar lawrin
Thank you very much,  Sinisa. 
I am very surprised because there is no mention of this anywhere in MySQL DOCs. So does MySQL 8.0  prevent this behavior?
[25 Sep 14:06] Sinisa Milivojevic
Hi Mr. lawrin,

Not in the way that you presume. Read our 8.0 Reference Manual ........
[25 Sep 16:25] seyar lawrin
Thank you very much,  Sinisa. 
I am very surprised because there is no mention of this anywhere in MySQL DOCs. So does MySQL 8.0  prevent this behavior?
[26 Sep 12:11] Sinisa Milivojevic
Hi Mr. lawrin,

My answer is still the same. It does prevent, but not in the way that you imagine. Protection in 8.0 is very stratified.

Read the chapter on InnoDB table encryption in our 8.0 Reference Manual.
[27 Sep 16:29] seyar lawrin
Hi Sinisa, 
I tested MySQL 8.0 table space encryption. The behavior is the same as the MySQL 5.7. I am able to restore encrypted table (s) to a different server without its original key. The only difference I noticed turning on the table space encryption is that I could not read .ibd files from file directory.
[30 Sep 11:59] Sinisa Milivojevic
Hi,

Exactly my point.

This is fully described in our Reference Manual, chapter 15.6 and A.17.
[2 Oct 16:40] seyar lawrin
Thank you, Sinisa. 
Is this concept the same in MySQL enterprise edition?
[3 Oct 11:38] Sinisa Milivojevic
Hi Mr. Lawrin,

The answer to your questions is that it has many more features. Most of it is described in the Reference Manual.

However, the key point is using dump (or pump). It rans lots of SELECTs and makes a script with many INSERTs. So, how to prevent SELECT to run ....... Only by the existing privilege system.