mysql.userテーブルの認証周りのカラムあれこれ

TL;DR

version	passwordカラム(CHAR(41) NOT NULL)	authentication_stringカラム(TEXT NULL)	pluginカラム	認証プラグインの選択
5.0.96	パスワードハッシュ	カラムなし	カラムなし	ダイジェスト長
5.1.73	パスワードハッシュ	カラムなし	カラムなし	ダイジェスト長
5.5.62	パスワードハッシュ	常に空文字	認証プラグイン	pluginカラム、空文字列の場合はダイジェスト長
5.6.42	SHA256プラグイン以外のパスワードハッシュ	SHA256プラグインの時のパスワードハッシュ	認証プラグイン	pluginカラム、空文字列の場合はダイジェスト長
5.7.24	カラムなし	パスワードハッシュ	認証プラグイン	pluginカラムのみ
8.0.13	カラムなし	パスワードハッシュ	認証プラグイン	pluginカラムのみ

---

日々の覚書: MySQL 5.7.6でmysql.userテーブルのパスワードのカラム名がなんか変わった で「MySQL 5.7ではパスワードハッシュが格納されるカラムが password から authentication_string に変更になった」としていたけれど、ちょっと調べてみたらなんかそれなりに歴史的経緯っぽいものがありました。

• 旧来(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で消すことにしたんじゃないかなと思う。

動作とかの部分はそれなりに調べたけど、経緯の部分は想像しているだけで裏付けとかはないです。

​

MySQLのロール周りのあれこれ

TL;DR

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

	ロールの有効化	ロールの作成	ロールの割り当て	割り当ての解除
ロール	SET ROLE	CREATE ROLE	GRANT	REVOKE
デフォルトロール	ログイン時	CREATE ROLE	CREATE USER .. DEFAULT ROLE または GRANT && ALTER USER .. DEFAULT ROLE	REVOKE
必須ロール	SET ROLE	CREATE ROLE	mandatory_roles を設定した時に自動で	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 には入らない

存在しないロールを SET GLOBAL mandatory_roles で指定すると↓のエラーになる（ただしステートメントはエラーにならず、エラーログにﾋﾟﾖｯと現れるだけ）

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

mandatory_roles を REVOKE したり DROP ROLE しようとしたりすると怒られる。

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.

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 ..

とかやると、 apuser はデフォルトで読み書き可能、新規追加するアカウント（たとえば dev1 の次に dev2 とか）はデフォルトで読み取りのみ可能（なロールに SET ROLE できる）、とかの表現になるのか。

あるいは、 activate_all_roles_on_login = ON しておくと SET ROLE read_only_role とかしなくてもデフォルトで読み取りだけ可能とかになる、と。

​

MySQL 8.0のcaching_sha2_password + 非SSL接続が転ける

$ mysql80 -h 127.0.0.1 -u yoku0825 --ssl-mode=disabled -p
Enter password:
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

このエラーになる条件。

• caching_sha2_password プラグインを使っているアカウント
  かつ
• まだサーバー側にSHA2キャッシュが作られていないアカウント
  かつ
• サーバーの公開鍵を指定していない非SSLのTCP接続

この条件に合致しているとログインが転けて↑のエラーを食らう。

---

解決策1. caching_sha2_password プラグインをやめて mysql_native_password プラグインを指定する

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

解決策2. 一度ログインに成功すればサーバー側にSHA2キャッシュが作成されるので、一度「エラーになる条件を満たさない」接続をしてやる

$ 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接続

解決策3. サーバーの公開鍵を指定する

↑と一緒で、 mysql コマンドラインクライアントだとこう。

$ 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接続

のいずれかをやればOK。

---

要は、 caching_sha2_password だと初回の認証成功時にパスワードキャッシュをサーバーサイドに作るらしいんだけど、そこでMITM攻撃を食らうとマズいからこんな仕様になっているらしい。

その理屈（どうして1回目だけがmysql_native_passwordとかと比べてダメなのか）はよくわからないんだけど誰か読んで教えてくだしあ（ソースを読まないとこれ以上の理解は出てこないような気もする）

MySQL :: MySQL 8.0 Reference Manual :: 6.5.1.3 Caching SHA-2 Pluggable Authentication

なお、

• mysqldの再起動
• FLUSH PRIVILEGES
• キャッシュはアカウント単位なので、 CREATE USER 直後

もこれ（SHA2キャッシュがない状態）にあたるので、おとなしくSSL接続使うようにした方が良いと思われるのでした。

なお、このネタはご覧のスポンサー（？）の提供でお送りしました

MySQL 8.0でユーザ作成後、一度もmysqlコマンドラインクライアントでログインしていない場合に、Connector/JのuseSSL=falseでの接続が失敗するという事象に当たった。事前に一度でもログインしておけば大丈夫。うーむ…

— SH2 (@sh2nd) 2018年8月10日

sha2キャッシュが計算されてないからかなとか思いましたが、mysql_native_passwordでもそうなります？

— yoku0825 (@yoku0825) 2018年8月10日

なるほどmysql_native_passwordなら一発で接続できました～

— SH2 (@sh2nd) 2018年8月10日

ドンピシャな気がしてきました！https://t.co/6NNrHpXmNdhttps://t.co/rWGbgEqzdq

— yoku0825 (@yoku0825) 2018年8月10日

​

MySQLのエラーコード1133、ER_PASSWORD_NO_MATCH "Can't find any matching row in the user table" について

TL;DR

• 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 .. で直接ユーザーを作ろうとした

昔、「MySQLのアカウントは GRANT ステートメントで作るんだよ」って教えられたような気がするんだけど、それはもう過去の話になってしまったようだ。

MySQL 5.7とそれ以降はデフォルトの sql_mode に NO_AUTO_CREATE_USER が指定されており、これが有効だと「存在しないアカウントに対する GRANT 」が転けるようになる。

先に CREATE USER でアカウントを作ってから GRANT するか、sql_modeから NO_AUTO_CREATE_USER を取り除いてやれるかばいいんだけど、MySQL 8.0.11とそれ以降では sql_mode からそもそも NO_AUTO_CREATE_USER が なくなっており 、今後常に「存在しないアカウントに対する GRANT 」は転け続けるので、sql_modeから取り除く方はお勧めしない。

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

これはエラーメッセージそのまま。ユーザーテーブル（= mysql.user のこと）に行が見つからないよ、ってこと。

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

MySQL 5.5とそれ以降では mysql.user テーブルに plugin というカラムが追加されていて、はここに「どのプラグインを使ってユーザー認証をするか」（パスワードハッシュ形式を指定したり、ソケット認証とかPAM認証とかを指定したりする）を記録する。

5.5, 5.6ではNULLABLEで空文字列許可だったけれど5.7から先はここが空っぽだとくだんのエラーで転けるようになる。
本来 plugin を変更するために使える ALTER USER ステートメントも失敗するようになるので、 mysql.user.plugin を UPDATE して FLUSH PRIVILEGES することになる。

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 をしていないのでアカウントとして認識されていない

mysqldump --all-databases からのリストアにありがちなこと。
mysql.user テーブルへの更新ステートメントはACLを更新しないので、リストアして再起動も FLUSH PRIVILEGES も叩かないと mysql.user 上には存在するけどACL上には存在しないので (；・３・) ｱﾙｪ ってなるやつになったりする。

mysql.user テーブルと、実際に認証に使われるACLの関係については↓の アカウント情報と mysql.user テーブルの同期 のあたりで詳しく（？）解説しています。

( ´-｀).oO（結構前の記事だけど割と的を射てると思う…おきにいり。

第17回　MySQLのユーザー管理について［その1］：MySQL道普請便り｜gihyo.jp … 技術評論社

​