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 があれば一発で便利だなあと思いました。
- どうやら前に書いてた
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 件のコメント :
コメントを投稿