MySQL8.0.14のリリースノートに以下の様に1つのアカウントに２つのパスワードを持つ事が出来る様になったとの記載があったので軽くレビューしてみました。オフィシャルマニュアルに記載がある様に定期的なパスワードは必要ですが、これまではまとめて１回のメンテナンスで全てのアプリケーションの接続で利用されているパスワードを変更するか、同じ権限でアカウントをもう１つ用意して、１つ１つ入れ替える事が必要でしたが、パスワードを２つ持てるのであれば、タイミングを見て複数のアプリケーションのパスワードを段階的に変更する事が出来るので有難い選択肢ですね。
“定期的な資格証明の変更は、アプリケーションがサーバーに接続するために使用するアカウントに対して行う必要があります。”
Account Management Notes: Previously, each MySQL user account was permitted to have a single password. MySQL now permits an account to have dual passwords, designated as primary and secondary passwords. This capability enables phased password changes to be performed seamlessly in complex multiple-server systems, without downtime. To support dual-password capability, the ALTER USER and SET PASSWORD statements now have a RETAIN CURRENT PASSWORD clause that saves the current password as the secondary password when you assign an account a new primary password. ALTER USER also has a DISCARD OLD PASSWORD clause to discard a secondary password that is no longer needed. See Password Management.
Changes in MySQL 8.0.14 (2019-01-21, General Availability)
MySQL8.0.14以降におけるDual Passwordの確認
STEP1: Create Account for testing
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| admin | % |
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
mysql> CREATE USER 'jeffrey'@'%' identified by 'Password20221024';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL ON POC.* to 'jeffrey'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| admin | % |
| jeffrey | % |
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)
STEP2: Login with the newly created test account.
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -u jeffrey -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT user(),current_user();
+--------------------+----------------+
| user() | current_user() |
+--------------------+----------------+
| jeffrey@172.18.0.1 | jeffrey@% |
+--------------------+----------------+
1 row in set (0.00 sec)STEP3: Change Password with RETAIN CURRENT PASSWORDオプション
mysql> ALTER USER 'jeffrey'@'%'
-> IDENTIFIED BY 'Password20221024-2'
-> RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)
STEP4: 古いパスワード、新しいパスワードどちらでもログイン出来る事を確認。
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -u jeffrey -pPassword20221024
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT user(),current_user();
+--------------------+----------------+
| user() | current_user() |
+--------------------+----------------+
| jeffrey@172.18.0.1 | jeffrey@% |
+--------------------+----------------+
1 row in set (0.00 sec)
mysql> \q
Bye
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -u jeffrey -pPassword20221024-2
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT user(),current_user();
+--------------------+----------------+
| user() | current_user() |
+--------------------+----------------+
| jeffrey@172.18.0.1 | jeffrey@% |
+--------------------+----------------+
1 row in set (0.00 sec)
STEP5: 古いパスワードをDISCARD
mysql> ALTER USER 'jeffrey'@'%' DISCARD OLD PASSWORD;
Query OK, 0 rows affected (0.07 sec)
古いパスワードではログイン出来ない様になっている事を確認。
備考： MySQLにおけるランダムパスワードの生成
The CREATE USER, ALTER USER, and SET PASSWORD statements now have the capability of generating random passwords for user accounts, as an alternative to requiring explicit administrator-specified literal passwords. See Password Management.
Changes in MySQL 8.0.18 (2019-10-14, General Availability)
MySQL8.0.18では上記の様にアカウントに対してランダムなパスワードを付与出来る様になった様です。
mysql> select user,host,left(authentication_string,10) from mysql.user;
+------------------+-----------+--------------------------------+
| user | host | left(authentication_string,10) |
+------------------+-----------+--------------------------------+
| admin | % | *2470C0C06 |
| jeffrey | % | *B9A41EDD6 |
| root | % | *2470C0C06 |
| mysql.infoschema | localhost | $A$005$THI |
| mysql.session | localhost | $A$005$THI |
| mysql.sys | localhost | $A$005$THI |
| root | localhost | $A$005$%U |
+------------------+-----------+--------------------------------+
7 rows in set (0.00 sec)
mysql> CREATE USER 'RANDOM_PASSWORD_USER_TEST'@'%' IDENTIFIED BY RANDOM PASSWORD;
+---------------------------+------+----------------------+-------------+
| user | host | generated password | auth_factor |
+---------------------------+------+----------------------+-------------+
| RANDOM_PASSWORD_USER_TEST | % | HwAO9}FQgwK3c<M}}CK3 | 1 |
+---------------------------+------+----------------------+-------------+
1 row in set (0.05 sec)ログインして確認
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -P 13306 -u RANDOM_PASSWORD_USER_TEST -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user();
+--------------------------------------+
| user() |
+--------------------------------------+
| RANDOM_PASSWORD_USER_TEST@172.18.0.1 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql>