MySQL 8.0.4にroot@localhostでログインして
SHOW GRANTS を実行したらこうなった。mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
よく訓練されたMySQLerにはお分かりいただけると思うが、MySQL 5.7とそれ以前であればこんな感じだった。
mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
ALL修飾子（と呼ぶのか？）がなくなって、更に、SUPER権限の代替になる（らしい）
*_ADMIN* 的なやつら（ADMINって入ってないやつもあるけど）が列挙されている。
見た目はかなり違うけど、与えられている権限自体は変わらない（そりゃそうだ）
テーブル的には
mysql.user （今までと同じ）と mysql.global_grants （SUPER権限を分割したやつら）にそれぞれレコードがあった。mysql80 7> SELECT * FROM mysql.user WHERE user= 'root'\G
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: caching_sha2_password
authentication_string:
password_expired: N
password_last_changed: 2018-01-30 11:01:30
password_lifetime: NULL
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
1 row in set (0.00 sec)
mysql80 7> SELECT * FROM mysql.global_grants\G
*************************** 1. row ***************************
USER: root
HOST: localhost
PRIV: BACKUP_ADMIN
WITH_GRANT_OPTION: Y
*************************** 2. row ***************************
USER: root
HOST: localhost
PRIV: BINLOG_ADMIN
WITH_GRANT_OPTION: Y
*************************** 3. row ***************************
USER: root
HOST: localhost
PRIV: CONNECTION_ADMIN
WITH_GRANT_OPTION: Y
*************************** 4. row ***************************
USER: root
HOST: localhost
PRIV: ENCRYPTION_KEY_ADMIN
WITH_GRANT_OPTION: Y
*************************** 5. row ***************************
USER: root
HOST: localhost
PRIV: GROUP_REPLICATION_ADMIN
WITH_GRANT_OPTION: Y
*************************** 6. row ***************************
USER: root
HOST: localhost
PRIV: PERSIST_RO_VARIABLES_ADMIN
WITH_GRANT_OPTION: Y
*************************** 7. row ***************************
USER: root
HOST: localhost
PRIV: REPLICATION_SLAVE_ADMIN
WITH_GRANT_OPTION: Y
*************************** 8. row ***************************
USER: root
HOST: localhost
PRIV: RESOURCE_GROUP_ADMIN
WITH_GRANT_OPTION: Y
*************************** 9. row ***************************
USER: root
HOST: localhost
PRIV: RESOURCE_GROUP_USER
WITH_GRANT_OPTION: Y
*************************** 10. row ***************************
USER: root
HOST: localhost
PRIV: ROLE_ADMIN
WITH_GRANT_OPTION: Y
*************************** 11. row ***************************
USER: root
HOST: localhost
PRIV: SET_USER_ID
WITH_GRANT_OPTION: Y
*************************** 12. row ***************************
USER: root
HOST: localhost
PRIV: SYSTEM_VARIABLES_ADMIN
WITH_GRANT_OPTION: Y
*************************** 13. row ***************************
USER: root
HOST: localhost
PRIV: XA_RECOVER_ADMIN
WITH_GRANT_OPTION: Y
13 rows in set (0.00 sec)
ちなみに、
GRANT SUPER しようとしたらワーニングになった。mysql80 7> GRANT SUPER ON *.* TO root@127.0.0.1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql80 7> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1287 | The SUPER privilege identifier is deprecated |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)
mysql80 7> SELECT * FROM global_grants WHERE user= 'root' AND host= '127.0.0.1'\G
Empty set (0.00 sec)
mysql80 7> SHOW GRANTS FOR root@127.0.0.1;
+------------------------------------------+
| Grants for root@127.0.0.1 |
+------------------------------------------+
| GRANT SUPER ON *.* TO `root`@`127.0.0.1` |
+------------------------------------------+
1 row in set (0.00 sec)
まだSUPER権限でも管理系の権限は振るえる（今後はわからないけれども9.0かしらね）
mysql80 9> SHOW GRANTS;
+------------------------------------------+
| Grants for root@127.0.0.1 |
+------------------------------------------+
| GRANT SUPER ON *.* TO `root`@`127.0.0.1` |
+------------------------------------------+
1 row in set (0.00 sec)
mysql80 9> SHOW MASTER LOGS;
+------------+-----------+
| Log_name | File_size |
+------------+-----------+
| bin.000001 | 170 |
| bin.000002 | 39229419 |
| bin.000003 | 854 |
| bin.000004 | 791 |
+------------+-----------+
4 rows in set (0.01 sec)
mysql80 9> SET GLOBAL max_connections= 100;
Query OK, 0 rows affected (0.00 sec)
んー、変わっていくなあ。
