MySQL バージョンアップ時の動的権限の追加処理について理解する

動的権限 / Dynamic Privilege

MySQL 8.0 で、動的権限と呼ばれる、従来より細かい範囲で操作を許可できる権限が追加されました。

バージョンアップ時に自動的に動的権限が付与される

一部の静的権限（従来の権限）を保持しているユーザには、MySQL バージョンアップ時に動的権限が自動的に付与されます。例えば、MySQL 5.7 で作成した SUPER 権限保持ユーザの権限は、MySQL 8.0 へのアップグレードで以下のように変化します。

MySQL 5.7

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 5.7.44    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR super57@'localhost';
+---------------------------------------------+
| Grants for super57@localhost                |
+---------------------------------------------+
| GRANT SUPER ON *.* TO 'super57'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)

8.0.28 バージョンアップ後

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR super57@'localhost' \G
*************************** 1. row ***************************
Grants for super57@localhost: GRANT SUPER ON *.* TO `super57`@`localhost`
*************************** 2. row ***************************
Grants for super57@localhost: GRANT AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `super57`@`localhost`
2 rows in set (0.00 sec)

SUPER 権限は将来的に、削除が予定されています。互換性の維持のため、このように自動的に動的権限へと変換してくれるのでしょう。

バージョンアップ後に作成したユーザには動的権限の自動追加はされない

バージョンアップの過程で、動的権限の追加は行われます。同じ静的権限を付与したとしても、バージョンアップ後に改めて作成したユーザには、動的権限は追加されません。同じ GRANT 文を実行して作成したユーザでも、バージョンアップを経たかどうかで表示される権限が異なってしまいます。

MySQL 8.0.28 で新たにユーザ作成した場合

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE USER super8028@'localhost' IDENTIFIED BY 'XXXXXXXXXXXXX';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SUPER ON *.* TO super8028@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 新規に作成したSUPERを持つユーザ、動的権限は明示的に指定されない限り付与されない
mysql> SHOW GRANTS FOR super8028@'localhost' \G
*************************** 1. row ***************************
Grants for super8028@localhost: GRANT SUPER ON *.* TO `super8028`@`localhost`
1 row in set (0.00 sec)

ただ、実際に、許可されるオペレーションに差はないはずです。動的権限は基本的に、静的権限が細分化されたものであり、静的権限（例えばSUPER）を保持していれば、動的権限がなくても同様の操作が許可されているはずです。

マイナーアップデートでも発生する

MySQL 8.0 ではマイナーバージョンで追加された、動的権限があります。とみたさんの MySQL Parameters で見ると非常にわかりやすいです。

マイナーバージョンアップ時も動的権限の追加は発生します。動的権限の追加は、権限ごとに「その動的権限を保持しているユーザがいないこと」が発動（権限追加）の条件になります。

例） BINLOG_ENCRYPTION_ADMIN 権限を追加する処理

-- Add the privilege BINLOG_ENCRYPTION_ADMIN for every user who has the privilege SUPER
-- provided that there isn't a user who already has the privilige BINLOG_ENCRYPTION_ADMIN.
SET @hadBinLogEncryptionAdminPriv = (SELECT COUNT(*) FROM global_grants WHERE priv = 'BINLOG_ENCRYPTION_ADMIN');
INSERT INTO global_grants SELECT user, host, 'BINLOG_ENCRYPTION_ADMIN', IF(grant_priv = 'Y', 'Y', 'N')
FROM mysql.user WHERE super_priv = 'Y' AND @hadBinLogEncryptionAdminPriv = 0;
COMMIT;

この結果、アップグレードパスとユーザ作成のタイミングによって、権限の異なるユーザが出来てしまい、ややこしいです。例えば、MySQL 5.7 → 8.0.28 → 8.0.40 とバージョンアップし、それぞれのバージョンでユーザを追加したとします。最終的にそれぞれのユーザの権限は以下になります。

-- MySQL 5.7 時点で作成したユーザ
mysql> SHOW GRANTS FOR super57@'localhost' \G
*************************** 1. row ***************************
Grants for super57@localhost: GRANT SUPER ON *.* TO `super57`@`localhost`
*************************** 2. row ***************************
Grants for super57@localhost: GRANT AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN,TELEMETRY_LOG_ADMIN ON *.* TO `super57`@`localhost`
2 rows in set (0.00 sec)

-- MySQL 8.0.28 時点で作成したユーザ
-- AUDIT_ABORT_EXEMPT などは、super57 がすでに、保持しているため追加されない
mysql> SHOW GRANTS FOR super8028@'localhost' \G
*************************** 1. row ***************************
Grants for super8028@localhost: GRANT SUPER ON *.* TO `super8028`@`localhost`
*************************** 2. row ***************************
Grants for super8028@localhost: GRANT TELEMETRY_LOG_ADMIN ON *.* TO `super8028`@`localhost`
2 rows in set (0.00 sec)

-- MySQL 8.0.40 時点で作成したユーザ
mysql> SHOW GRANTS FOR super8040@'localhost' \G
*************************** 1. row ***************************
Grants for super8040@localhost: GRANT SUPER ON *.* TO `super8040`@`localhost`
1 row in set (0.00 sec)

追加された動的権限を剥奪してもバージョンアップで復活する

上記で記載したように、各動的権限が追加される条件は、バージョンではなく、「その動的権限を保持しているユーザがいないこと」です。そのため、静的権限だけで運用しようと自動的に追加された動的権限を剥奪したとしても、またバージョンアップをすると、復活してしまいます *1。

追加される動的権限の対応表

mysql_system_tables_fix.sql に動的権限の付与処理が書かれています。動的権限が追加される権限を整理してみました (8.0のみ、8.4 は確認してません)。

SUPER だけでなく、CREATE USER 権限や SELECT 権限に対して付与される動的権限もあります。また、SYSTEM_VARIABLES_ADMIN → AUTHENTICATION_POLICY_ADMIN のような、動的権限 → 動的権限の対応も存在しているようです。

権限を管理するツールを自前で開発していると、この自動追加処理の内容を加味して実装する必要があり面倒でした。個人的には、この処理は、MySQLのバージョンアップ時に強制せず、任意のタイミングで管理者が別途、実行できるほうが良かったように思います。

※赤字はDynamic Privilege

対象となる権限	追加される Dynamic Privilege
SUPER	AUDIT_ADMIN BACKUP_ADMIN BINLOG_ADMIN BINLOG_ENCRYPTION_ADMIN CLONE_ADMIN CONNECTION_ADMIN ENCRYPTION_KEY_ADMIN GROUP_REPLICATION_ADMIN INNODB_REDO_LOG_ENABLE PERSIST_RO_VARIABLES_ADMIN REPLICATION_APPLIER REPLICATION_SLAVE_ADMIN RESOURCE_GROUP_ADMIN RESOURCE_GROUP_USER ROLE_ADMIN SERVICE_CONNECTION_ADMIN SESSION_VARIABLES_ADMIN SET_USER_ID SYSTEM_USER SYSTEM_VARIABLES_ADMIN TABLE_ENCRYPTION_ADMIN TELEMETRY_LOG_ADMIN XA_RECOVER_ADMIN
RELOAD	INNODB_REDO_LOG_ARCHIVE FLUSH_OPTIMIZER_COSTS FLUSH_STATUS FLUSH_USER_RESOURCES FLUSH_TABLES
CREATE USER	APPLICATION_PASSWORD_ADMIN PASSWORDLESS_USER_ADMIN
SET_USER_ID	SYSTEM_USER
SELECT	SHOW_ROUTINE
SYSTEM_VARIABLES_ADMIN	AUTHENTICATION_POLICY_ADMIN
SYSTEM_USER	AUDIT_ABORT_EXEMPT FIREWALL_EXEMPT
SYSTEM_VARIABLES_ADMIN	SENSITIVE_VARIABLES_OBSERVER

まとめ

バージョンアップ時に動的権限が自動的に追加されるケースがある
バージョンアップを経たかどうかで、権限が変わる。環境ごとに権限が意図せず異っている場合、この処理が原因かも。

*1:一部の権限は mysql.session などのシステムユーザにも付与されるため、復活しないものもあると思います

mita2 database life

主にMySQLに関するメモです