サクッと書けるネタだったので、久々に書きました。

リリースノートを読んでて気になった

MySQL8.0.26のリリースノートのServer Administrationの箇所にこうあった。

Setting the session value of the innodb_strict_mode system variable is now a restricted operation and the session user must have privileges sufficient to set restricted session variables.

For information about the privileges required to set restricted session variables, see System Variable Privileges. (Bug #32944980)

なになに、「システム変数 innodb_strict_mode のセッション値を設定することは、（より）制限された操作に変更され、セッションユーザーは制限されたセッション変数を設定するのに十分な特権を持っていなければならない。」と。

んで、「制限されたセッション変数を設定するために必要な権限については、「システム変数の権限」を参照してください。」と。

そして、その先のページを読むと、セッションシステム変数を変更するにはMySQL 8.0.14以降では、必要な権限は SESSION_VARIABLES_ADMIN で、それより前のバージョンだとSYSTEM_VARIABLES_ADMINまたはSUPERが必要とある。

んー？よくわからんな・・・。

何が変わったんだ？

まず、8.0.25まではinnodb_strict_mode のセッション値を変えるのにどんな権限が必要なのかわかりにくい。
- SESSION_VARIABLES_ADMINだけなの？
- SESSION_VARIABLES_ADMIN or SYSTEM_VARIABLES_ADMIN or SUPER なの？
その上で8.0.26で必要な権限が何かハッキリとわからん。
- 制限されたってのはどういうこと？
SESSION_VARIABLES_ADMINはおそらくセッション値を変えるのに必要なんだろうが、SYSTEM_VARIABLES_ADMIN、SUPERは8.0.14〜8.0.25で使える？使えない？がわからん。

ってわけで、ちとその３つ＋１（USAGE）の権限でユーザー作って8.0.25と8.0.26で確認してみることに。

検証

検証用ユーザーとその権限

SUPER権限のユーザー

CREATE USER 'strict_super'@'%' IDENTIFIED BY 'xxxxxxxx';
grant SUPER on *.* to 'strict_super'@'%';

SESSION_VARIABLES_ADMIN権限のユーザー

CREATE USER 'ss_var_admin'@'%' IDENTIFIED BY 'xxxxxxxx';
grant SESSION_VARIABLES_ADMIN on *.*  to 'ss_var_admin'@'%';

SYSTEM_VARIABLES_ADMIN権限のユーザー

CREATE USER 'sys_var_admin'@'%' IDENTIFIED BY 'xxxxxxxx';
grant SYSTEM_VARIABLES_ADMIN on *.*  to 'sys_var_admin'@'%';

USAGE権限のユーザー

CREATE USER 'usage_user'@'%' IDENTIFIED BY 'xxxxxxxx';
grant USAGE on *.* to 'usage_user'@'%';

（※パスワードのxxxxxxxxの部分は適当に入れてね）

検証作業

8.0.25

SUPER権限のユーザー

$ mysql -ustrict_super -pxxxxxxxx -h127.0.0.1 -P8025
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.25-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
Query OK, 0 rows affected (0.01 sec)

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

変更できる。（ほうほう）

SESSION_VARIABLES_ADMIN権限のユーザー

$ mysql -uss_var_admin -pxxxxxxxx -h127.0.0.1 -P8025
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.25-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

変更できる。（そらそう）

SYSTEM_VARIABLES_ADMIN権限のユーザー

$ mysql -usys_var_admin -pxxxxxxxx -h127.0.0.1 -P8025
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.25-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

変更できる。（まあ、そうか）

USAGE権限のユーザー

$ mysql -uusage_user -pxxxxxxxx -h127.0.0.1 -P8025
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.25-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

なんや、お前でもできるんやないか！？

SUPERとかなんとか言ってたのはアレ一体なんやったんや・・・。

というわけで、8.0.25でも実はSESSION_VARIABLES_ADMIN 以外にSYSTEM_VARIABLES_ADMINまたはSUPER、というかUSAGEでもセッションでの変更が可能だとわかった。

さて、問題の8.0.26である。

8.0.26

SUPER権限のユーザー

$ mysql -ustrict_super -pxxxxxxxx -h127.0.0.1 -P8026
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
mysql> set session innodb_strict_mode = OFF;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

Oh... SUPERだと無理になってる！

コメントに

you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

とあるので、その２つのどっちかなら行けるってことか。

SESSION_VARIABLES_ADMIN権限のユーザー

$ mysql -uss_var_admin -pxxxxxxxx -h127.0.0.1 -P8026
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.01 sec)

うん、OKだね。

SYSTEM_VARIABLES_ADMIN権限のユーザー

$ mysql -usys_var_admin -pxxxxxxxx -h127.0.0.1 -P8026
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.26-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

やっぱりOK

USAGE権限のユーザー

$ mysql -uusage_user -pxxxxxxxx -h127.0.0.1 -P8026
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
mysql> set session innodb_strict_mode = OFF;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

デスヨネー！

こうなってくると

気になるのは8.0.13以前。どうなっているんだ？

SESSION_VARIABLES_ADMIN権限は無いので、それ以外で試す。

8.0.13

SUPER権限のユーザー

$ mysql -ustrict_super -ppassword -h127.0.0.1 -P8013
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

変更できる。（ドキュメント通り）

SYSTEM_VARIABLES_ADMIN権限のユーザー

$ mysql -usys_var_admin -ppassword -h127.0.0.1 -P8013
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

変更できる。（ドキュメント通り）

USAGE権限のユーザー

$ mysql -uusage_user -ppassword -h127.0.0.1 -P8013
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

(　ﾟдﾟ)

(つд⊂)ｺﾞｼｺﾞｼ

(；ﾟдﾟ)

まとめ

つまりこういうこと

権限	8.0.13	8.0.25	8.0.26
SUPER	変更可	変更可	変更不可
SESSION_VARIABLES_ADMIN	変更可	変更可	変更可
SYSTEM_VARIABLES_ADMIN	変更可	変更可	変更可
USAGE	変更可	変更可	変更不可

リリースノートとドキュメントに

innodb_strict_modeのセッション値を変更する権限として「SESSION_VARIABLES_ADMIN か SYSTEM_VARIABLES_ADMIN をちゃんと指定してね」

を書いてもらいつつ、8.0.14より前の〜、のくだりは直しじゃないか。

レポート出しとくか。

ちなみに

上の話はinnodb_strict_modeでの話ですのでご注意を。

$ mysql -ustrict_super -pxxxxxxxx -h127.0.0.1 -P8026
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.26-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_parallel_read_threads = 1;
Query OK, 0 rows affected (0.00 sec)

最後に

尊敬するyoku0825さんからご指摘頂き、正確に把握できました。

本当にありがとうございます。

41から始めました

innodb_strict_mode のセッション値を変更するのに必要な権限（from 8.0.26）

リリースノートを読んでて気になった

検証

検証用ユーザーとその権限

SUPER権限のユーザー

SESSION_VARIABLES_ADMIN権限のユーザー

SYSTEM_VARIABLES_ADMIN権限のユーザー

USAGE権限のユーザー

検証作業

8.0.25

SUPER権限のユーザー

SESSION_VARIABLES_ADMIN権限のユーザー

SYSTEM_VARIABLES_ADMIN権限のユーザー

USAGE権限のユーザー

8.0.26

SUPER権限のユーザー

SESSION_VARIABLES_ADMIN権限のユーザー

SYSTEM_VARIABLES_ADMIN権限のユーザー

USAGE権限のユーザー

こうなってくると

8.0.13

SUPER権限のユーザー

SYSTEM_VARIABLES_ADMIN権限のユーザー

USAGE権限のユーザー

まとめ

ちなみに

最後に