はじめに
MySQLにはパスワードを管理するためのいくつかの機能があります。
本記事では前回の記事に続いて、MySQL8.0から追加されたパスワード管理機能であるパスワード検証(Password Verification-Required Policy)とデュアルパスワード(Dual Password Support)を紹介します。
本記事はMySQL8.0.21を使用して検証を行います。
Password Verification-Required Policy
パスワードを変更する際に、現在のパスワードの入力を必要とする機能です。設定方法にはグローバル（全ユーザに適用）かユーザごとに設定するかの2通りがあります。
グローバルに設定する場合は password_require_current変数を使用します。ユーザごとに設定する場合はCREATE USERかALTER USERで次のようにpassword_optionのPASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]を使用します。
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE USER [user]@[host] IDENTIFIED BY 'current_Password1!' PASSWORD REQUIRE CURRENT; CREATE USER [user]@[host] IDENTIFIED BY 'current_Password1!' PASSWORD REQUIRE CURRENT DEFAULT; CREATE USER [user]@[host] IDENTIFIED BY 'current_Password1!' PASSWORD REQUIRE CURRENT OPTIONAL; |
また、このポリシーが有効である時のパスワードを変更するにはALTER USERまたはSET PASSWORDでREPLACEを使用します。
|
1 2 3 4 5 |
ALTER USER [user]@[host] IDENTIFIED BY 'new_Password1!' REPLACE 'current_Password1!'; SET PASSWORD = 'new_Password1!' REPLACE 'current_Password1!'; |
検証
まずはpassword_require_currentを有効にし、実際にパスワードを変更してみます。デフォルトでpassword_require_currentは無効となっています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> show global variables like 'password_require_current'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | password_require_current | OFF | +--------------------------+-------+ 1 row in set (0.00 sec) mysql> SET PERSIST password_require_current = ON; Query OK, 0 rows affected (0.01 sec) mysql> show global variables like 'password_require_current'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | password_require_current | ON | +--------------------------+-------+ 1 row in set (0.00 sec) mysql> CREATE USER user1@localhost IDENTIFIED BY 'current_Password1!'; Query OK, 0 rows affected (0.01 sec) |
次に作成したユーザーでMySQLに接続して、パスワードの変更を試みます。このとき、REPLACEで現在のパスワードを含めないとエラーとなります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select user(); +-----------------+ | user() | +-----------------+ | user1@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> ALTER USER user1@localhost IDENTIFIED BY 'new_Password1!'; ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it. mysql> ALTER USER user1@localhost IDENTIFIED BY 'new_Password1!' REPLACE 'current_Password1!'; Query OK, 0 rows affected (0.01 sec) |
次に個別のユーザに設定する方法を確認します。ユーザ作成時に次のいずれかを設定します。
- PASSWORD REQUIRE CURRENT:パスワード変更時に現在のパスワードが必要
- PASSWORD REQUIRE CURRENT OPTIONAL:パスワード変更時に現在のパスワードが不要
- PASSWORD REQUIRE CURRENT DEFAULT:
password_require_currentの設定に従う
各オプションを指定したユーザーを作成します。個別に設定された値はmysql.userのPassword_require_currentから確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> CREATE USER user2@localhost IDENTIFIED BY 'current_Password1!' PASSWORD REQUIRE CURRENT; Query OK, 0 rows affected (0.04 sec) mysql> CREATE USER user3@localhost IDENTIFIED BY 'current_Password1!' PASSWORD REQUIRE CURRENT OPTIONAL; Query OK, 0 rows affected (0.01 sec) mysql> CREATE USER user4@localhost IDENTIFIED BY 'current_Password1!' PASSWORD REQUIRE CURRENT DEFAULT; Query OK, 0 rows affected (0.01 sec) mysql> SELECT user ,Password_require_current FROM mysql.user WHERE user like 'user%'; +-------+--------------------------+ | user | Password_require_current | +-------+--------------------------+ | user1 | NULL | | user2 | Y | | user3 | N | | user4 | NULL | +-------+--------------------------+ 4 rows in set (0.00 sec) |
まずは、PASSWORD REQUIRE CURRENTを設定したuser2@localhostで接続して、パスワードを変更してみます。変更にはREPLACEが必要であることがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select user(); +-----------------+ | user() | +-----------------+ | user2@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> ALTER USER user2@localhost IDENTIFIED BY 'new_Password1!'; ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it. mysql> ALTER USER user2@localhost IDENTIFIED BY 'new_Password1!' REPLACE 'current_Password1!'; Query OK, 0 rows affected (0.00 sec) |
次にPASSWORD REQUIRE CURRENT OPTIONALを設定したuser3@localhostで接続して、パスワードを変更してみます。変更にREPLACEはあってもなくてもいいことが分かります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select user(); +-----------------+ | user() | +-----------------+ | user3@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> ALTER USER user3@localhost IDENTIFIED BY 'new_Password1!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER user3@localhost IDENTIFIED BY 'fresh_Password1!' REPLACE 'new_Password1!'; Query OK, 0 rows affected (0.01 sec) |
最後にPASSWORD REQUIRE CURRENT DEFAULTを設定したuser4@localhostで接続して、パスワードを変更してみます。password_require_currentがONとなっているため、REPLACEが必要です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select user(); +-----------------+ | user() | +-----------------+ | user4@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> ALTER USER user4@localhost IDENTIFIED BY 'new_Password1!'; ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it. mysql> ALTER USER user4@localhost IDENTIFIED BY 'new_Password1!' REPLACE 'current_Password1!'; Query OK, 0 rows affected (0.00 sec) |
一度password_require_currentをOFFに設定した後にREPLACEなしで変更すると変更できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> show global variables like 'password_require_current'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | password_require_current | OFF | +--------------------------+-------+ 1 row in set (0.00 sec) mysql> select user(); +-----------------+ | user() | +-----------------+ | user4@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> ALTER USER user4@localhost IDENTIFIED BY 'new_Password1!'; Query OK, 0 rows affected (0.01 sec) |
また、権限を持ったユーザー(* 1) が他のユーザーのパスワードを変更する際は、現在のパスワードは不要です。REPLACEをつけるとエラーになります。
※1 権限を持ったユーザーはCREATE USER権限もしくはmysql.*に対してUPDATE権限を持ったユーザーを指します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> ALTER USER user2@localhost IDENTIFIED BY 'current_Password1!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER user2@localhost IDENTIFIED BY 'new_Password1!' REPLACE 'current_Password1!'; ERROR 3893 (HY000): Do not specify the current password while changing it for other users. |
パスワードを変更する際にREPLACE句(現在のパスワード)が必要かどうかをまとめた表が以下となります。
| 対象 | password_require_current | password_option | REPLACE句(現在のパスワード) |
|---|---|---|---|
| 自分のパスワード | ON/OFF | PASSWORD REQUIRE CURRENT | 必要 |
| 自分のパスワード | ON/OFF | PASSWORD REQUIRE CURRENT OPTIONAL | どちらでも可 |
| 自分のパスワード | ON | PASSWORD REQUIRE CURRENT DEFAULT | 必要 |
| 自分のパスワード | OFF | PASSWORD REQUIRE CURRENT DEFAULT | どちらでも可 |
| 他ユーザーのパスワード | ON/OFF | PASSWORD REQUIRE CURRENT [DEFAULT/OPTIONAL] | 禁止 |
Dual Password
続いて、デュアルパスワード機能について紹介します。なお検証で紹介したコマンドを試す際は、前項の検証で設定したpassword_require_currentをOFFに設定しておく必要があります。
MySQL 8.0.14で導入された機能で、ユーザーはデュアルパスワードを持つことができるようになりました。
デュアルパスワードを設定する場合はALTER USERかSET PASSWORDで次のようにRETAIN CURRENT PASSWORDを使用します。
このとき変更前のパスワードがセカンダリパスワードとなり、IDENTIFIED BY '****'で設定したパスワードがプライマリパスワードとなります。
|
1 2 3 4 5 |
ALTER USER [user]@[host] IDENTIFIED BY 'new_Password1!' RETAIN CURRENT PASSWORD; SET PASSWORD = 'new_Password1!' RETAIN CURRENT PASSWORD; |
またセカンダリパスワードを破棄する際はALTER USERで次のようにDISCARD OLD PASSWORDを使用します。
|
1 |
ALTER USER [user]@[host] DISCARD OLD PASSWORD; |
検証
実際に試してみます。新たにuser5@localhostユーザーを作成し、パスワードはcurrent_Password1!とします。
つぎにALTER USERで新しいパスワードを設定します。このときにRETAIN CURRENT PASSWORD句も使用します。
|
1 2 3 4 5 6 |
mysql> CREATE USER user5@localhost IDENTIFIED BY 'current_Password1!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER user5@localhost IDENTIFIED BY 'new_Password1!' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected (0.02 sec) |
このときプライマリパスワードがnew_Password1!、セカンダリパスワードがcurrent_Password1!となっています。
current_Password1!とnew_Password1!の両方のパスワードでMySQLに接続できる事を確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$ mysql -uuser5 -pcurrent_Password1! -e "select 1" mysql: [Warning] Using a password on the command line interface can be insecure. +---+ | 1 | +---+ | 1 | +---+ $ mysql -uuser5 -pnew_Password1! -e "select 1" mysql: [Warning] Using a password on the command line interface can be insecure. +---+ | 1 | +---+ | 1 | +---+ |
セカンダリパスワードの情報はmysql.userのUser_attributesカラムに保持されます。
|
1 2 3 4 5 6 7 |
mysql> SELECT user,host, User_attributes from mysql.user where user = 'user5'; +-------+-----------+-------------------------------------------------------------------------------------------------------------------------+ | user | host | User_attributes | +-------+-----------+-------------------------------------------------------------------------------------------------------------------------+ | user5 | localhost | {"additional_password": "$A$005$,\u000bO2Mx#\u001bIF%s\u00071~P\u001cM\"\"s9SAoy80wo21X6MSleDMTH3itsxqD5SamEp20n/6YD5"} | +-------+-----------+-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
セカンダリパスワードはALTER USER [user]@[host] DISCARD OLD PASSWORDで破棄します。
|
1 2 |
mysql> ALTER USER user5@localhost DISCARD OLD PASSWORD; Query OK, 0 rows affected (0.01 sec) |
セカンダリパスワードだったcurrent_Password1!では接続できなくなっていることが確認できます。
|
1 2 3 |
$ mysql -uuser5 -pcurrent_Password1! -e "select 1" mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'user5'@'localhost' (using password: YES) |
デュアルパスワード機能の注意点
ドキュメントには以下のようなデュアルパスワードの特徴が記載されています。
1) 新しいパスワードが空の場合にRETAIN CURRENT PASSWORDを使用するとエラーとなります。
|
1 2 3 4 5 |
mysql> CREATE USER user6@localhost IDENTIFIED BY 'current_Password1!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER user6@localhost IDENTIFIED BY '' RETAIN CURRENT PASSWORD; ERROR 3895 (HY000): Current password can not be retained for user 'user6'@'localhost' because new password is empty. |
2) 現在のパスワードが空の状態でRETAIN CURRENT PASSWORDを使用するとエラーとなります。
|
1 2 3 4 5 6 |
mysql> CREATE USER user7@localhost; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER user7@localhost IDENTIFIED BY 'new_Password1!' RETAIN CURRENT PASSWORD; ERROR 3878 (HY000): Empty password can not be retained as second password for user 'user7'@'localhost'. |
3) ユーザーにセカンダリパスワードがありRETAIN CURRENT PASSWORDを使用せずにプライマリパスワードを変更した場合、セカンダリパスワードは変更されません。
|
1 2 3 4 5 6 |
mysql> CREATE USER user8@localhost IDENTIFIED BY 'current_Password1!'; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER user8@localhost IDENTIFIED BY 'new_Password1!' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected (0.01 sec) |
このときのパスワードは以下の通りです。
- プライマリパスワード:new_Password1!
- セカンダリパスワード:current_Password1!
RETAIN CURRENT PASSWORDを使用せずにパスワードを変更します。
|
1 2 3 4 5 6 |
mysql> CREATE USER user8@localhost IDENTIFIED BY 'current_Password1!'; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER user8@localhost IDENTIFIED BY 'new_Password1!' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected (0.01 sec) |
このときのパスワードは以下となります。
- プライマリパスワード:fresh_Password1!
- セカンダリパスワード:current_Password1!
実際に接続してみるとセカンダリパスワードは変更されていないことがわかります。
|
1 2 3 4 5 6 |
mysql> CREATE USER user8@localhost IDENTIFIED BY 'current_Password1!'; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER user8@localhost IDENTIFIED BY 'new_Password1!' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected (0.01 sec) |
4) ユーザーの認証プラグインを変更すると、セカンダリパスワードは破棄されます。また、認識プラグインの変更とRETAIN CURRENT PASSWORDを同時に行うと失敗します。
まずはセカンダリパスワードがあるユーザーの認証プラグインを変更してみます。
user8@localhostの認証プラグインをcaching_sha2_passwordからmysql_native_passwordに変更します。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT user, host, plugin from mysql.user WHERE user = 'user8'; +-------+-----------+-----------------------+ | user | host | plugin | +-------+-----------+-----------------------+ | user8 | localhost | caching_sha2_password | +-------+-----------+-----------------------+ 1 row in set (0.00 sec) mysql> ALTER USER user8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'fresh_Password1!'; Query OK, 0 rows affected (0.00 sec) |
プライマリパスワードでは引き続き接続できますが、セカンダリパスワードでは接続できなくなっていることがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 |
$ mysql -uuser8 -pfresh_Password1! -e "select 1" mysql: [Warning] Using a password on the command line interface can be insecure. +---+ | 1 | +---+ | 1 | +---+ $ mysql -uuser8 -pcurrent_Password1! -e "select 1" mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'user8'@'localhost' (using password: YES) |
次に認識プラグインの変更とRETAIN CURRENT PASSWORDを同時に実行してみます。
先ほど変更した認証プラグインをもう一度caching_sha2_passwordに変更してみようとするとエラーとなりました。
|
1 2 3 |
mysql> ALTER USER user8@localhost IDENTIFIED WITH 'caching_sha2_password' BY 'Password1!' RETAIN CURRENT PASSWORD; ERROR 3894 (HY000): Current password can not be retained for user 'user8'@'localhost' because authentication plugin is being changed. |
権限
デュアルパスワードの利用にはAPPLICATION_PASSWORD_ADMIN権限が必要となります。
APPLICATION_PASSWORD_ADMIN権限を持たないユーザーは自分のパスワードを変更することはできますが、デュアルパスワードを利用することはできません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> SHOW grants; +---------------------------------------------+ | Grants for user9@localhost | +---------------------------------------------+ | GRANT USAGE ON *.* TO `user9`@`localhost` | +---------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER USER user9@localhost IDENTIFIED BY 'current_Password1!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER user9@localhost IDENTIFIED BY 'new_Password1!' RETAIN CURRENT PASSWORD; ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER or APPLICATION_PASSWORD_ADMIN privilege(s) for this operation |
APPLICATION_PASSWORD_ADMIN権限を持つユーザーは自分のパスワードを変更する際にデュアルパスワードを利用できます。
|
1 2 |
mysql> GRANT APPLICATION_PASSWORD_ADMIN on *.* to user9@localhost; Query OK, 0 rows affected (0.00 sec) |
APPLICATION_PASSWORD_ADMIN権限を付与されると自身にデュアルパスワードを設定できるようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SHOW GRANTS; +----------------------------------------------------------------+ | Grants for user9@localhost | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO `user9`@`localhost` | | GRANT APPLICATION_PASSWORD_ADMIN ON *.* TO `user9`@`localhost` | +----------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> ALTER USER user9@localhost identified by 'new_Password1!' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected (0.00 sec) |
CREATE USER権限を持っていれば、APPLICATION_PASSWORD_ADMIN権限を持っていなくても他のユーザーにデュアルパスワードを設定することが可能です。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SHOW GRANTS; +------------------------------------------------------+ | Grants for user10@localhost | +------------------------------------------------------+ | GRANT CREATE USER ON *.* TO `user10`@`localhost` | +------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER USER user9@localhost identified by 'Password1!' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected (0.01 sec) |
また、ドキュメントに明記されてはいませんでしたが、mysql.*に対してUPDATE権限が付与されていれば、CREATE USER権限を付与されているときと同様他のユーザーにデュアルパスワードを設定できました。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SHOW GRANTS; +------------------------------------------------------+ | Grants for user10@localhost | +------------------------------------------------------+ | GRANT USAGE ON *.* TO `user10`@`localhost` | | GRANT UPDATE ON `mysql`.* TO `user10`@`localhost` | +------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> ALTER USER user9@localhost identified by 'new_Password1!' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected (0.01 sec) |
利用シナリオ
デュアルパスワードを有効的に利用できるシーンを考えます。複数のアプリケーションが利用するアプリ用ユーザー(app@%)のパスワードを変更する際に、アプリケーションを全て停止するのではなく、それぞれ任意のタイミングで順番に変更していくことが可能となります。
デュアルパスワードを使用しない場合は、全アプリケーションを同時に停止し、パスワードの変更作業が必要となります。
- 全アプリケーション停止
- DBのapp@%ユーザのパスワードを変更
ALTER USER app@'%' IDENTIFIED BY 'new_Password1!'; - 各アプリケーションでDB接続パスワードを変更
- 全アプリケーション再開
一方、デュアルパスワードを使用した場合は以下のように、アプリケーションごとに順番に停止し、変更することも可能です。例えば、「アプリケーションAは日中に対応したいが、アプリケーションBは深夜に対応したい。」といった状況などにも柔軟に対応できそうです。
- DBのapp@%ユーザの新しいパスワードをプライマリパスワード、古いパスワードをセカンダリパスワードに変更する
ALTER USER app@'%' IDENTIFIED BY 'new_Password1!' RETAIN CURRENT PASSWORD; - アプリケーションAを停止
- アプリケーションAのDB接続パスワードを変更
- アプリケーションAを再開
- 残りのアプリケーションも任意のタイミングで変更作業を行う
- 全てのアプリケーションの接続情報の変更作業が完了後、app@%ユーザのセカンダリパスワードを破棄する
ALTER USER app@'%' DISCARD OLD PASSWORD;
まとめ
前回に続いてMySQL8.0のパスワード管理に関する機能を紹介しました。
Password Verification-Required Policyは、MySQLログイン中の状態で離席した際に第三者に放置した端末を操作されパスワードを勝手に変更される、といった状況に対応できます。このような状況になることは考えにくいかと思いますが、リモートワークの推奨等でカフェやコワーキングスペースなど社外で勤務する機会が増えてきています。端末の置忘れなど万が一のトラブルに備え設定を検討してみてもよいかもしれません。
デュアルパスワード機能は、利用シナリオとして紹介したように、いくつかの場面で有効活用ができそうな機能です。
MySQL8.0には、その他にも比較的新しいバージョンで導入されたランダムパスワード生成機能(Random Password Generation)(MySQL8.0.18)や失敗したログインの追跡と一時的なアカウントロック機能(Failed-Login Tracking and Temporary Account Locking)(MySQL8.0.19)などもあります。
セキュアな状態を保つためにこれらのパスワード管理機能の活用を検討してみてはいかがでしょうか。
