2019年11月5日火曜日

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

TL;DR


MySQL 8.0.18から、ランダムなパスワードを勝手に生成する RANDOM PASSWORD 構文が使えるようになった。
外部のパスワードジェネレータでいいじゃn ううんなんでもない。
CREATE USERALTER 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 があれば一発で便利だなあと思いました。
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)

0 件のコメント :

コメントを投稿