Bug #91791 Log tables do not error attempting to encrypt them
Submitted: 25 Jul 2018 14:21 Modified: 26 Jul 2018 5:37
Reporter: Ceri Williams Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.11, 5.7.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: encrypt

[25 Jul 2018 14:21] Ceri Williams
Description:
If we attempt to encrypt a normal system table then we are informed that it is not possible:

ERROR 3183 (HY000): This tablespace can't be encrypted.

When attempting to encrypt the log tables, which of course could contain sensitive information, an ALTER TABLE will succeed and the CREATE_OPTIONS will contain ENCRYPTION="Y". This could lead to a false sense of security and should error like any other.

How to repeat:
mysql> set global log_output = "TABLE";
Query OK, 0 rows affected (0.00 sec)

mysql> alter table mysql.general_log encryption = "Y";
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from mysql.general_log;
Empty set (0.01 sec)

mysql> set global general_log = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log = OFF;
Query OK, 0 rows affected (0.01 sec)

mysql> select count(1) from mysql.general_log;
+----------+
| count(1) |
+----------+
|       45 |
+----------+
1 row in set (0.00 sec)

root@46f1faeccc47:/var/lib/mysql# head -n1 mysql/general_log.CSV
"2018-07-25 14:08:08.014179","root[root] @  [172.17.0.1]",50,3232249876,"Quit",""

mysql> select @@version, table_name, create_options from information_schema.tables where table_schema = "mysql" and create_options like '%ENCRYPT%';
+-----------+-------------+----------------+
| @@version | TABLE_NAME  | CREATE_OPTIONS |
+-----------+-------------+----------------+
| 8.0.11    | general_log | ENCRYPTION="Y" |
| 8.0.11    | slow_log    | ENCRYPTION="Y" |
+-----------+-------------+----------------+
2 rows in set (0.00 sec)

Suggested fix:
Ensure that non-InnoDB tables produce an error, such as:

ERROR 1911 (HY000): Unknown option 'encryption'
[26 Jul 2018 5:37] Umesh Shastry
Hello Ceri Williams!

Thank you for the report!

Thanks,
Umesh