TL;DR

オンライン変更は SYSTEM_VARIABLES_ADMIN 権限。 SUPER は非推奨。
SET PERSIST_ONLY は SYSTEM_VARIABLES_ADMIN 権限 && PERSIST_RO_VARIABLES_ADMIN 権限

ふとコードを読んでいる時に SET PERSIST_ONLY には SUPER 権限は絡んでなさそうなことに気が付いたので試してみる。

  /* for dynamic variables user needs SUPER_ACL or SYSTEM_VARIABLES_ADMIN */
  if (!static_variable) {
    if (!sctx->check_access(SUPER_ACL) &&
        !(sctx->has_global_grant(STRING_WITH_LEN("SYSTEM_VARIABLES_ADMIN"))
              .first)) {
      my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0),
               "SUPER or SYSTEM_VARIABLES_ADMIN");
      return 1;
    }
  } else {
    /*
     for static variables user needs both SYSTEM_VARIABLES_ADMIN and
     PERSIST_RO_VARIABLES_ADMIN
    */
    if (!(sctx->has_global_grant(STRING_WITH_LEN("SYSTEM_VARIABLES_ADMIN"))
              .first &&
          sctx->has_global_grant(STRING_WITH_LEN("PERSIST_RO_VARIABLES_ADMIN"))
              .first)) {
      my_error(ER_PERSIST_ONLY_ACCESS_DENIED_ERROR, MYF(0),
               "SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN");
      return 1;
    }
  }

https://github.com/mysql/mysql-server/blob/mysql-8.0.18/sql/set_var.cc#L172-L194

こういう「なんの権限が必要なの」系は、何も権限のついてないアカウント実際に叩くのが一番検証が楽。

mysql80 100> SHOW GRANTS;
+--------------------------------------+
| Grants for yoku0825@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)

mysql80 100> SET GLOBAL innodb_buffer_pool_size = 128 * 1024 * 1024;ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

mysql80 100> SET PERSIST innodb_buffer_pool_size = 128 * 1024 * 1024;ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

mysql80 100> SET PERSIST_ONLY innodb_buffer_pool_size = 128 * 1024 * 1024;
ERROR 3630 (42000): Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation

8.0導入の新しい権限の方は、権限違反の時にエラー1227じゃなくて3630になるのね。

コードのコメントには "static variables" とは書いてあるけど、ダイナミック変更可能なやつも PERSIST_ONLY ならこっちを通る。

mysql80 102> SHOW GRANTS;
+--------------------------------------+
| Grants for yoku0825@%                |
+--------------------------------------+
| GRANT SUPER ON *.* TO `yoku0825`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)

mysql80 102> SET GLOBAL innodb_buffer_pool_size = 128 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql80 102> SET PERSIST innodb_buffer_pool_size = 128 * 1024 * 1024;
Query OK, 0 rows affected (0.02 sec)

mysql80 102> SET PERSIST_ONLY innodb_buffer_pool_size = 128 * 1024 * 1024;
ERROR 3630 (42000): Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation

mysql80 104> SHOW GRANTS;
+-------------------------------------------------------+
| Grants for yoku0825@%                                 |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%`                  |
| GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO `yoku0825`@`%` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 104> SET GLOBAL innodb_buffer_pool_size = 128 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql80 104> SET PERSIST innodb_buffer_pool_size = 128 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql80 104> SET PERSIST_ONLY innodb_buffer_pool_size = 128 * 1024 * 1024;
ERROR 3630 (42000): Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation

mysql80 106> SHOW GRANTS;
+-----------------------------------------------------------+
| Grants for yoku0825@%                                     |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%`                      |
| GRANT PERSIST_RO_VARIABLES_ADMIN ON *.* TO `yoku0825`@`%` |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 106> SET GLOBAL innodb_buffer_pool_size = 128 * 1024 * 1024;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

mysql80 106> SET PERSIST innodb_buffer_pool_size = 128 * 1024 * 1024;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

mysql80 106> SET PERSIST_ONLY innodb_buffer_pool_size = 128 * 1024 * 1024;
ERROR 3630 (42000): Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation

mysql80 109> SHOW GRANTS;
+----------------------------------------------------------------------------------+
| Grants for yoku0825@%                                                            |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%`                                             |
| GRANT PERSIST_RO_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `yoku0825`@`%` |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 109> SET GLOBAL innodb_buffer_pool_size = 128 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql80 109> SET PERSIST innodb_buffer_pool_size = 128 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql80 109> SET PERSIST_ONLY innodb_buffer_pool_size = 128 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql80 111> SHOW GRANTS;
+-----------------------------------------------------------+
| Grants for yoku0825@%                                     |
+-----------------------------------------------------------+
| GRANT SUPER ON *.* TO `yoku0825`@`%`                      |
| GRANT PERSIST_RO_VARIABLES_ADMIN ON *.* TO `yoku0825`@`%` |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 111> SET GLOBAL innodb_buffer_pool_size = 128 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql80 111> SET PERSIST innodb_buffer_pool_size = 128 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql80 111> SET PERSIST_ONLY innodb_buffer_pool_size = 128 * 1024 * 1024;
ERROR 3630 (42000): Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation

確かにそうなった。
個人的には SET PERSIST_ONLY だけじゃなくて SET PERSIST も SUPER だけでできないようにしてくれると嬉しかったりするけれども。

監視用アカウントに SUPER がついて運用しているとこういう権限分離は嬉しい…。

日々の覚書

GA

2019/12/13

MySQL 8.0時代のSET GLOBALとかSET PERSISTに必要な権限

TL;DR

0 件のコメント :

コメントを投稿