Description:
Upgrading from e.g. 8.0.15 adds TABLE_ENCRYPTION_ADMIN to users having the SUPER privilege:
-- Add the privilege TABLE_ENCRYPTION_ADMIN for every user who has the privilege SUPER
-- provided that there isn't a user who already has the privilige TABLE_ENCRYPTION_ADMIN.
SET @hadTableEncryptionAdminPriv = (SELECT COUNT(*) FROM global_grants WHERE priv = 'TABLE_ENCRYPTION_ADMIN');
INSERT INTO global_grants SELECT user, host, 'TABLE_ENCRYPTION_ADMIN', IF(grant_priv = 'Y', 'Y', 'N')
FROM mysql.user WHERE super_priv = 'Y' AND @hadTableEncryptionAdminPriv = 0;
COMMIT;
This query also updates the predefined user 'mysql.session'. However, during --initialize, this user is not granted the TABLE_ENCRYPTION_ADMIN privilege, so there is an asymmetry depending on how the 8.0.17 server is started.
How to repeat:
Initialize a clean 8.0.17 server, and you get the following:
mysql> select USER from mysql.global_grants WHERE PRIV = 'TABLE_ENCRYPTION_ADMIN';
+------+
| USER |
+------+
| root |
+------+
Start 8.0.17 on a clean data directory from 8.0.15, and you get the following:
mysql> select USER from mysql.global_grants WHERE PRIV = 'TABLE_ENCRYPTION_ADMIN';
+---------------+
| USER |
+---------------+
| mysql.session |
| root |
+---------------+
Suggested fix:
Either revoke the privilege explicitly during upgrade:
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
@@ -658,6 +658,12 @@ INSERT INTO global_grants SELECT user, host, 'TABLE_ENCRYPTION_ADMIN', IF(grant_
FROM mysql.user WHERE super_priv = 'Y' AND @hadTableEncryptionAdminPriv = 0;
COMMIT;
+-- The TABLE_ENCRYPTION_ADMIN privilege was previously granted to 'mysql.session'
+-- during upgrade. However, this user should not have this privilege, so we need
+-- to explicitly revoke it.
+DELETE FROM global_grants WHERE user = 'mysql.session' AND host = 'localhost' AND priv = 'TABLE_ENCRYPTION_ADMIN';
+
+
# Activate the new, possible modified privilege tables
# This should not be needed, but gives us some extra
Or, alternatively, grant it explicitly when the mysql.session user is created:
diff --git a/scripts/mysql_system_users.sql b/scripts/mysql_system_users.sql
@@ -38,6 +38,7 @@ GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'mysql.session'@localhost;
GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'mysql.session'@localhost;
GRANT PERSIST_RO_VARIABLES_ADMIN ON *.* TO 'mysql.session'@localhost;
GRANT SYSTEM_USER ON *.* TO 'mysql.session'@localhost;
+GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO 'mysql.session'@localhost;
-- Create an user that is definer for information_schema view
CREATE USER 'mysql.infoschema'@localhost IDENTIFIED WITH caching_sha2_password
