MySQL 8.0.18からCREATE USER, ALTER USER, SET PASSWORDで "RANDOM" を指定できるようになった

TL;DR

• MySQL :: MySQL 8.0 Reference Manual :: 6.2.15 Password Management の “Random Password Generation” のあたりに全部書いてある。
• MySQL 8.0.18から。

---

MySQL 8.0.18から、ランダムなパスワードを勝手に生成する RANDOM PASSWORD 構文が使えるようになった。
外部のパスワードジェネレータでいいじゃｎ ううんなんでもない。

CREATE USER と ALTER USER は、本来パスワード文字列を渡すところにそのまま RANDOM PASSWORD と置き換えると使える。

mysql80 18> CREATE USER b IDENTIFIED WITH mysql_native_password BY RANDOM PASSWORD;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| b    | %    | CYvTzZuEK</1JwfFrFkM |
+------+------+----------------------+
1 row in set (0.01 sec)

mysql80 18> SHOW CREATE USER b;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for b@%                                                                                                                                                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'b'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*B463EC3F855577E3EC49BC205D66ECEAF7ABC106' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 18> ALTER USER b IDENTIFIED BY RANDOM PASSWORD;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| b    | %    | HnZwYKlnl]*b1lJ>JxQt |
+------+------+----------------------+
1 row in set (0.02 sec)

mysql80 18> SHOW CREATE USER b;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for b@%                                                                                                                                                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'b'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*15B4BD0DD178CB3C258B8867A1BB570A7CF9E799' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SET PASSWORD だけ、 = 'パスワード文字列' だったところが TO RANDOM になる。
こういうところ妙に英語っぽくするよねSQLって（偏見）

mysql80 18> SET PASSWORD FOR b TO RANDOM;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| b    | %    | 6jKNjZBhz2fJHLM@N13* |
+------+------+----------------------+
1 row in set (0.01 sec)

mysql80 18> SHOW CREATE USER b;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for b@%                                                                                                                                                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'b'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E0D266D8CA4F993339B63CB8C3D7AB103B6D08B5' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ちなみにランダムパスワードの長さは generated_random_password_length で指定できるらしい。

完全な余談だけど、ドキュメントを見ずにテキトーな値を突っ込んだらワーニングになって、

mysql80 16> SET SESSION generated_random_password_length = 1024;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql80 16> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect generated_random_password_length value: '1024' |
+---------+------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

こんな時MySQL 8.0の performance_schema.variables_info があれば一発で便利だなあと思いました。

• どうやら前に書いてた
  • 日々の覚書: MySQL 8.0.0時代のmy.cnfの探り方

mysql80 16> SELECT * FROM performance_schema.variables_info WHERE variable_name = 'generated_random_password_length';
+----------------------------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+
| VARIABLE_NAME                    | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME                   | SET_USER | SET_HOST  |
+----------------------------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+
| generated_random_password_length | DYNAMIC         |               | 5         | 255       | 2019-11-05 15:56:14.070703 | root     | localhost |
+----------------------------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+
1 row in set (0.01 sec)

​