日々の覚書

• 旧来(MySQL 5.0, 5.1）は2種類の認証プラグイン（ mysql_native_password = 俗称41桁ハッシュ、 mysql_old_password = 俗称16桁ハッシュ） があったが、それを識別するための plugin カラムは無く、「クライアントから送られてきたダイジェストの長さ」でどちらのプラグインを使うか決めて password カラムに入っているパスワードハッシュを引き出して使っていた
• MySQL 5.5から plugin カラムが追加される。これは5.5で「認証プラグインAPI」を解放した時に一緒に実装されたのだと思う。
  • MySQL :: MySQL 5.5 Reference Manual :: 6.5.1 Authentication Plugins
  • たぶん過渡期の例外措置として、 plugin が空文字ならば旧来と同じダイジェストの長さによって mysql_native_password と mysql_old_password を打ち分ける実装になっている
  • authentication_string カラムも実装されたけど、この時点でこのカラムを使っているっぽい認証プラグインは少なくともコミュニティー版のソースコード上にはない。
• MySQL 5.6では商用版限定ながら sha256_password プラグインが登場する。
  • MySQL :: MySQL 5.6 Reference Manual :: 6.5.1.4 SHA-256 Pluggable Authentication
    • MySQL 8.0では同じ名前の sha256_password プラグインがコミュニティー版でも使えるようになった。実装まで同じかどうかは知らない。
  • MySQL 8.0の sha256_password プラグインと同じものだとすると、パスワードハッシュが $5$ から始まる66桁になるので、 Password CHAR(41) NOT NULL には入らずAuthentication_string TEXT に入れることになったのかな
    • password カラムの長さを変えるのはリスキーだなって感じで
• MySQL 8.0では sha256_password と同じく256bitのSHA2を使う cache_sha2_password がデフォルトになるのもあって、この時点までに password カラムがなくなって authentication_string に一本化されるのは確定だっただろうから、ちょっと前倒ししてMySQL 5.7で消すことにしたんじゃないかなと思う。

• デフォルトロールはログイン時に有効化
• mandatory_roles はログイン時に有効化されない、全ユーザーから SET ROLE できるロール
• ロールのホスト部は意味を持たないような気がする（ role@127.0.0.1 は 192.168.0.0 なアカウントにも割り当てができる）
• activate_all_roles_on_login = ON にすると、デフォルトロールも割り当てたロールも mandatory_roles も全部いっぺんにログイン時に有効になる

• フツーに CREATE ROLE して CREATE USER .. && GRANT <role_name> で与える場合、 GRANT したタイミングで mysql.role_edges に INSERT される
• CREATE ROLE して CREATE USER .. DEFAULT ROLE .. した場合、 CREATE USER のタイミングで mysql.role_edges に入る
• SET GLOBAL mandatory_roles .. の場合は mysql.role_edges には入らない

2018-10-29T02:23:39.444341Z 108 [Warning] [MY-010968] [Server] Can't set mandatory_role: There's no such authorization ID role@localhost.

mysql80 120> REVOKE role@localhost FROM yoku0825;
ERROR 3628 (HY000): The role `role`@`localhost` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.

mysql80 127> SET GLOBAL mandatory_roles = 'role@1.2.3.4,role@localhost';
Query OK, 0 rows affected (0.02 sec)

$ mysql80 -uyoku0825
mysql80 130> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for yoku0825@localhost                                       |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`localhost`                        |
| GRANT `role`@`1.2.3.4`,`role`@`localhost` TO `yoku0825`@`localhost` |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 130> SET ROLE role@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql80 130> SET ROLE role@1.2.3.4; <-- localhost != 1.2.3.4 だけど判定されないSET ROLEできる
Query OK, 0 rows affected (0.00 sec)

• MySQL :: MySQL 8.0 Reference Manual :: 5.1.7 Server System Variables
• MySQL :: MySQL 8.0 Reference Manual :: 6.3.4 Using Roles
• MySQL :: MySQL 8.0 Reference Manual :: 5.1.7 Server System Variables
• MySQL :: MySQL 8.0 Reference Manual :: 13.7.1.9 SET DEFAULT ROLE Syntax

• GRANT SELECT ON d1.* TO read_only_role
• SET GLOBAL mandatory_roles = read_only_role
• GRANT ALL ON d1.* TO read_write_role
• CREATE USER apuser DEFAULT ROLE read_write_role
• CREATE USER dev1 IDENTIFIED BY ..

mysql> ALTER USER yoku0825 IDENTIFIED WITH mysql_native_password BY 'new_password`;

$ mysql80 -h localhost -S /usr/mysql/8.0.13/data/mysql.sock -u yoku0825 -p ## ソケット接続
$ mysql80 -h 127.0.0.1 -u yoku0825 --ssl-mode=PREFERRED -p ## SSL接続
$ mysql80 -h 127.0.0.1 -u yoku0825 --ssl-mode=disabled -p --server-public-key-path=/usr/mysql/8.0.13/data/public_key.pem ## サーバー公開鍵を指定した非SSL接続

$ mysql80 -h 127.0.0.1 -u yoku0825 --ssl-mode=disabled -p --server-public-key-path=/usr/mysql/8.0.13/data/public_key.pem ## サーバー公開鍵を指定した非SSL接続

• sql_mode= NO_AUTO_CREATE_USER が指定されている時に CREATE USER せずに GRANT .. で直接ユーザーを作ろうとした
• SET PASSWORD FOR user@host = .. で存在しないユーザーのパスワードを変更しようとした
• mysql.user.plugin のカラムが空文字列のアカウントに GRANT や ALTER USER をかけようとした
• mysql.user テーブルに INSERT なり UPDATE なりをした後、 FLUSH PRIVILEDGES をしていないのでアカウントとして認識されていない

• sql_mode= NO_AUTO_CREATE_USER が指定されている時に CREATE USER せずに GRANT .. で直接ユーザーを作ろうとした

• SET PASSWORD FOR user@host = .. で存在しないユーザーのパスワードを変更しようとした

• mysql.user.plugin のカラムが空文字列のアカウントに GRANT や ALTER USER をかけようとした

mysql57 4> SELECT plugin FROM mysql.user WHERE user = 'yoku0825';
+--------+
| plugin |
+--------+
|        |
+--------+
1 row in set (0.00 sec)

mysql57 4> GRANT SELECT ON *.* TO yoku0825;
ERROR 1133 (42000): Can't find any matching row in the user table

mysql57 4> UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'yoku0825';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql57 4> SELECT plugin FROM mysql.user WHERE user = 'yoku0825';
+-----------------------+
| plugin                |
+-----------------------+
| mysql_native_password |
+-----------------------+
1 row in set (0.00 sec)

mysql57 4> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql57 4> GRANT SELECT ON *.* TO yoku0825;
Query OK, 0 rows affected (0.00 sec)

• mysql.user テーブルに INSERT なり UPDATE なりをした後、 FLUSH PRIVILEDGES をしていないのでアカウントとして認識されていない