はじめに
MySQLにはパスワードを管理するためのいくつかの機能があります。
本記事ではMySQL8.0から追加されたパスワード管理機能の1つ、パスワード再利用のポリシーについて紹介します。
本記事はMySQL8.0.21を使用して検証を行います。
パスワードの再利用のポリシー
パスワードを変更する際に、同じパスワードを再利用しないように制限できる機能です。
設定項目は変更回数と経過日数の2通りあります。また、グローバル（全ユーザに適用）かユーザごとに設定するかの2通りがあります。
グローバルに設定する場合はpassword_historyとpassword_reuse_interval変数を使用します。
ユーザごとに設定する場合はCREATE USERかALTER USERで次のようにpassword_optionのPASSWORD HISTORYとPASSWORD REUSE INTERVALを使用します。
|
1 2 3 4 |
CREATE USER [user]@[host] IDENTIFIED BY 'Password1!' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY; |
変更回数
変更回数の設定について詳しく見ていきます。グローバルに設定する場合は、サーバー変数password_historyで変更回数の閾値を設定します。
例えば、password_history = 3とした場合、これまで設定したパスワード過去3回分は設定することができません。
実際に試してみます。デフォルトではpassword_historyは0(制限なし)となっています。今回はSET PERSIST構文を使って設定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> SHOW GLOBAL VARIABLES LIKE 'password_history'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | password_history | 0 | +------------------+-------+ 1 row in set (0.02 sec) mysql> SET PERSIST password_history = 3; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'password_history'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | password_history | 3 | +------------------+-------+ 1 row in set (0.00 sec) |
次にtest1@localhostユーザを作成し、パスワードを繰り返し変更します。
|
1 2 3 4 5 6 7 8 |
mysql> CREATE USER test1@localhost IDENTIFIED BY 'Password1!'; Query OK, 0 rows affected (0.02 sec) mysql> ALTER USER test1@localhost IDENTIFIED BY 'Password2!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER test1@localhost IDENTIFIED BY 'Password3!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER test1@localhost IDENTIFIED BY 'Password4!'; Query OK, 0 rows affected (0.02 sec) |
ここで現行のパスワード含め過去3つ前のPassword2!に変更しようとするとエラーとなります。4つ前のPassword1!は設定が可能です。
|
1 2 3 4 |
mysql> ALTER USER test1@localhost IDENTIFIED BY 'Password2!'; ERROR 3638 (HY000): Cannot use these credentials for 'test1@localhost' because they contradict the password history policy mysql> ALTER USER test1@localhost IDENTIFIED BY 'Password1!'; Query OK, 0 rows affected (0.01 sec) |
次に個別のユーザに設定する方法を確認します。ユーザ作成時にPASSWORD HISTORYを設定します。すでにグローバルに設定している場合は、個別の設定で上書きされます。
|
1 2 |
mysql> CREATE USER test2@localhost IDENTIFIED BY 'Password1!' PASSWORD HISTORY 1; |
個別に設定された値はmysql.userのPassword_reuse_historyから確認できます。
グローバル設定が適用されている場合はNULLとなります。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SELECT User, Host, Password_reuse_history FROM mysql.user WHERE User LIKE 'test%'; +-------+-----------+------------------------+ | user | host | Password_reuse_history | +-------+-----------+------------------------+ | test1 | localhost | NULL | | test2 | localhost | 1 | +-------+-----------+------------------------+ 2 rows in set (0.00 sec) |
パスワードを変更してみます。test2@localhostの変更回数は1に設定されているため、現行のパスワード含め２つ前のパスワードには設定が可能です。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> ALTER USER test2@localhost IDENTIFIED BY 'Password2!'; Query OK, 0 rows affected (0.00 sec) //現行と同じパスワードは設定できません mysql> ALTER USER test2@localhost IDENTIFIED BY 'Password2!'; ERROR 3638 (HY000): Cannot use these credentials for 'test2@localhost' because they contradict the password history policy //現行含め2つ前のパスワードは設定できます mysql> ALTER USER test2@localhost IDENTIFIED BY 'Password1!'; Query OK, 0 rows affected (0.01 sec) |
経過日数
経過日数の設定について詳しく見ていきます。グローバルに設定する場合は、サーバー変数password_reuse_intervalで変更回数の閾値を設定します。
例えば、password_reuse_interval = 100とした場合、現在から100日以内に設定したパスワードは設定することができません。
なお設定は日単位となっており、検証には時間がかかるため本記事では設定方法の確認のみ行います。
デフォルトではpassword_reuse_intervalも0(制限なし)となっています。変更回数と同様にSET PERSIST構文を使って設定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> SHOW GLOBAL VARIABLES LIKE 'password_reuse_interval'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | password_reuse_interval | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> SET PERSIST password_reuse_interval = 100; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'password_reuse_interval'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | password_reuse_interval | 100 | +-------------------------+-------+ 1 row in set (0.00 sec) |
次に個別のユーザに設定する方法を確認します。ユーザ作成時にPASSWORD REUSE INTERVALを設定します。すでにグローバルに設定している場合は、個別の設定が上書きされます。
|
1 2 |
mysql> CREATE USER test3@localhost IDENTIFIED BY 'Password1!' PASSWORD REUSE INTERVAL 365 DAY; |
個別に設定された値はmysql.userのPassword_reuse_timeから確認できます。グローバル設定が適用されている場合はNULLとなります。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT User, Host, Password_reuse_time FROM mysql.user WHERE User LIKE 'test%'; +-------+-----------+---------------------+ | user | host | Password_reuse_time | +-------+-----------+---------------------+ | test1 | localhost | NULL | | test2 | localhost | NULL | | test3 | localhost | 365 | +-------+-----------+---------------------+ 3 rows in set (0.00 sec) |
変更回数と経過日数を組み合わせて設定する
変更回数と経過日数の設定を組み合わせることも可能です。例えばpassword_history = 3、password_reuse_interval=100を設定している場合は、両方の基準を満たしている必要があります。
次の例ではpassword_historyの条件は満たしていますが、password_reuse_intervalの条件を満たしていないため、ALTER USER test4@localhost identified by 'Password1!';はエラーが返されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> SET PERSIST password_history = 3; Query OK, 0 rows affected (0.02 sec) mysql> SET PERSIST password_reuse_interval = 100; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER test4@localhost IDENTIFIED BY 'Password1!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER test4@localhost IDENTIFIED BY 'Password2!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER test4@localhost IDENTIFIED BY 'Password3!'; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER test4@localhost IDENTIFIED BY 'Password4!'; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER test4@localhost identified by 'Password1!'; ERROR 3638 (HY000): Cannot use these credentials for 'test4@localhost' because they contradict the password history policy |
パスワードの履歴について
パスワードの履歴はmysql.password_historyに保持されます。
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT * FROM mysql.password_history; +-----------+-------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +-----------+-------+----------------------------+-------------------------------------------+ | localhost | test | 2020-11-17 14:23:39.005375 | *C06327039E918D3247E4438D3785C723719DC8B5 | | localhost | test | 2020-11-17 14:23:22.061852 | *DCA60EA3BF8D1061BEA038A818448FB968A765FB | | localhost | test | 2020-11-17 14:23:16.410717 | *495696352F2CB0389759F54B729D1ADD74A64358 | | localhost | test2 | 2020-11-17 14:44:05.560754 | *27B480D87C9F65B664401AD83D2590B168BB9AEF | ... |
※本検証ではdefault_authentication_pluginをmysql_native_passwordに設定してあります。
MySQL8.0のデフォルトであるcaching_sha2_passwordに設定している場合は、Password列のハッシュ値は上記とは異なります。
ユーザごとにパスワードが変更された時間とパスワードが記録されていることが確認できます。なお、Passwordはハッシュ値で記録されます。
続いてmysql.password_historyの仕様について確認していきます。
If an account is renamed, its entries are renamed to match. If an account is dropped or its authentication plugin is changed, its entries are removed.
引用元 ： MySQL :: MySQL 8.0 Reference Manual :: 6.2.3 Grant Tables :: The password_history Grant Table
ドキュメントにはユーザ名が変更された場合はmysql.password_historyも合わせて変更され、ユーザが削除された場合はmysql.password_historyの記録も削除されると記載があります。
ユーザ名を変更した場合
まずはユーザ名を変更した場合の動作を確認してみます。
test1@localhostをnew_test@localhostにRENAME USERで変更します。
mysql.password_historyも合わせて変更されていることが確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> SELECT * FROM mysql.password_history WHERE user = 'test1'; +-----------+-------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +-----------+-------+----------------------------+-------------------------------------------+ | localhost | test1 | 2020-11-17 14:23:39.005375 | *C06327039E918D3247E4438D3785C723719DC8B5 | | localhost | test1 | 2020-11-17 14:23:22.061852 | *DCA60EA3BF8D1061BEA038A818448FB968A765FB | | localhost | test1 | 2020-11-17 14:23:16.410717 | *495696352F2CB0389759F54B729D1ADD74A64358 | +-----------+-------+----------------------------+-------------------------------------------+ 3 rows in set (0.00 sec) mysql> RENAME USER test1@localhost TO new_test@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM mysql.password_history WHERE user = 'test1'; Empty set (0.00 sec) mysql> SELECT * FROM mysql.password_history WHERE user = 'new_test'; +-----------+----------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +-----------+----------+----------------------------+-------------------------------------------+ | localhost | new_test | 2020-11-17 14:23:39.005375 | *C06327039E918D3247E4438D3785C723719DC8B5 | | localhost | new_test | 2020-11-17 14:23:22.061852 | *DCA60EA3BF8D1061BEA038A818448FB968A765FB | | localhost | new_test | 2020-11-17 14:23:16.410717 | *495696352F2CB0389759F54B729D1ADD74A64358 | +-----------+----------+----------------------------+-------------------------------------------+ 3 rows in set (0.00 sec) |
また、現在password_history=3、password_reuse_interval=0に設定されていますが、ユーザ名を変更する前の変更回数を含めてチェックされていることがわかります。
※現在、test1@localhost(new_test@localhost)はPassword1!→Password2!→Password3!→Password4!→Password1!という順番で変更されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> SHOW GLOBAL VARIABLES LIKE 'password_history'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | password_history | 3 | +------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'password_reuse_interval'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | password_reuse_interval | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> ALTER USER new_test@localhost IDENTIFIED BY 'Password4!'; ERROR 3638 (HY000): Cannot use these credentials for 'new_test@localhost' because they contradict the password history policy mysql> ALTER USER new_test@localhost IDENTIFIED BY 'Password2!'; Query OK, 0 rows affected (0.00 sec) |
ユーザを削除した場合
次にDROP USERでnew_test@localhostを削除し、mysql.password_historyからもnew_test@localhostのデータ削除されることを確認します。
|
1 2 3 4 5 |
mysql> DROP USER new_test@localhost; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM mysql.password_history WHERE user = 'new_test'; Empty set (0.01 sec) |
mysql.password_historyからデータは削除されているため再度new_test@localhostを作成する場合は、削除前の変更回数はカウントされません。
|
1 2 |
mysql> CREATE USER new_test@localhost IDENTIFIED BY 'Password2!'; Query OK, 0 rows affected (0.00 sec) |
password_history=3となっていますが、削除時点に設定されていたのと同じパスワードが設定可能です。
パスワードの再利用の制限(変更回数と経過日数)を変更した場合
mysql.password_historyのドキュメントには以下の記載があります。
The password_history table accumulates a sufficient number of nonempty passwords per account to enable MySQL to perform checks against both the account password history length and reuse interval. Automatic pruning of entries that are outside both limits occurs when password-change attempts occur.
引用元 ： MySQL :: MySQL 8.0 Reference Manual :: 6.2.3 Grant Tables :: The password_history Grant Table
mysql.password_historyは変更回数と経過日数の制限に関係ある履歴のみ保持するようです。
例えば、password_history = 0、password_reuse_interval=0を設定している状態（パスワードの再利用に関する制限がない状態）ではパスワードを変更してもmysql.password_historyには記録されません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SET PERSIST password_history = 0; Query OK, 0 rows affected (0.00 sec) mysql> SET PERSIST password_reuse_interval = 0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER test1@localhost IDENTIFIED BY 'Password1!'; Query OK, 0 rows affected (0.02 sec) mysql> ALTER USER test1@localhost IDENTIFIED BY 'Password2!'; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER test1@localhost IDENTIFIED BY 'Password3!'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM mysql.password_history WHERE user = 'test1'; Empty set (0.00 sec) |
変更回数を設定すると、それ以降のパスワード変更履歴がmysql.password_historyに記録されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> SET PERSIST password_history = 3; Query OK, 0 rows affected (0.00 sec) //password_history を変更した時点では、まだmysql.password_historyは空です。 mysql> SELECT * FROM mysql.password_history WHERE user = 'test1'; Empty set (0.00 sec) mysql> ALTER user test1@localhost IDENTIFIED BY 'Password1!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER test1@localhost IDENTIFIED BY 'Password2!'; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER test1@localhost IDENTIFIED BY 'Password3!'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM mysql.password_history WHERE user = 'test1'; +-----------+-------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +-----------+-------+----------------------------+-------------------------------------------+ | localhost | test1 | 2020-11-17 17:27:39.187054 | *495696352F2CB0389759F54B729D1ADD74A64358 | | localhost | test1 | 2020-11-17 17:27:29.467409 | *27B480D87C9F65B664401AD83D2590B168BB9AEF | | localhost | test1 | 2020-11-17 17:27:22.464924 | *C06327039E918D3247E4438D3785C723719DC8B5 | +-----------+-------+----------------------------+-------------------------------------------+ 3 rows in set (0.00 sec) |
また、mysql.password_historyには設定された変更回数と経過日数のチェックに必要な分だけ記録されます。
例えば、test1@localhostのパスワードをさらに変更した場合、最初に設定した、’Password1!’は変更回数の制限(3回)から外れたため、mysql.password_historyからデータが削除されます。
Password_timestampを見ると一番古い2020-11-17 17:27:22.464924のデータが削除されていることがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> ALTER user test1@localhost IDENTIFIED BY 'Password4!'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM mysql.password_history WHERE user = 'test1'; +-----------+-------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +-----------+-------+----------------------------+-------------------------------------------+ | localhost | test1 | 2020-11-17 17:28:08.846147 | *DCA60EA3BF8D1061BEA038A818448FB968A765FB | | localhost | test1 | 2020-11-17 17:27:39.187054 | *495696352F2CB0389759F54B729D1ADD74A64358 | | localhost | test1 | 2020-11-17 17:27:29.467409 | *27B480D87C9F65B664401AD83D2590B168BB9AEF | +-----------+-------+----------------------------+-------------------------------------------+ 3 rows in set (0.00 sec) |
また、ドキュメントにはパスワード変更を試みた時にこの動作が発生すると記載があります。
password_history = 1に変更して、どのタイミングでmysql.password_historyからデータが削除されるか確認します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SET PERSIST password_history = 1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM mysql.password_history WHERE user = 'test1'; +-----------+-------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +-----------+-------+----------------------------+-------------------------------------------+ | localhost | test1 | 2020-11-17 17:28:08.846147 | *DCA60EA3BF8D1061BEA038A818448FB968A765FB | | localhost | test1 | 2020-11-17 17:27:39.187054 | *495696352F2CB0389759F54B729D1ADD74A64358 | | localhost | test1 | 2020-11-17 17:27:29.467409 | *27B480D87C9F65B664401AD83D2590B168BB9AEF | +-----------+-------+----------------------------+-------------------------------------------+ 3 rows in set (0.00 sec) mysql> ALTER user test1@localhost IDENTIFIED BY 'Password5!'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM mysql.password_history WHERE user = 'test1'; +-----------+-------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +-----------+-------+----------------------------+-------------------------------------------+ | localhost | test1 | 2020-11-17 17:39:53.650658 | *1A97903C0A7B2A5694906E8862D8C0ABC9C70E2D | +-----------+-------+----------------------------+-------------------------------------------+ 1 row in set (0.00 sec) |
パスワードが変更された時点でmysql.password_historyからデータが削除されました。
まとめ
パスワードの管理はセキュリティ上重要ですが、機能の仕様をよく理解しないで有効化すると思わぬトラブルが発生してしまう可能性があります。MySQL8.0で追加されたパスワードの管理機能は他にもあるので、他の機能についても今後取り上げていきたいと思います。
