動的権限 / Dynamic Privilege
MySQL 8.0 で、動的権限と呼ばれる、従来より細かい範囲で操作を許可できる権限が追加されました。
gihyo.jp
バージョンアップ時に自動的に動的権限が付与される
一部の静的権限（従来の権限）を保持しているユーザには、MySQL バージョンアップ時に動的権限が自動的に付与されます。
例えば、MySQL 5.7 で作成した SUPER 権限保持ユーザの権限は、MySQL 8.0 へのアップグレードで以下のように変化します。
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)
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 |
まとめ
- バージョンアップ時に動的権限が自動的に追加されるケースがある
- バージョンアップを経たかどうかで、権限が変わる。環境ごとに権限が意図せず異っている場合、この処理が原因かも。
