2018年2月26日月曜日

MySQL 8.0.4の SHOW GRANTS の結果が想像したのとちょっと違う

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)
んー、変わっていくなあ。

0 件のコメント :

コメントを投稿