スマートスタイル TECH BLOG|データベース&クラウドの最新技術情報を配信

MySQL8.0でパスワード再利用ポリシーを設定する

はじめに

MySQLにはパスワードを管理するためのいくつかの機能があります。
本記事ではMySQL8.0から追加されたパスワード管理機能の1つ、パスワード再利用のポリシーについて紹介します。
本記事はMySQL8.0.21を使用して検証を行います。

パスワードの再利用のポリシー

パスワードを変更する際に、同じパスワードを再利用しないように制限できる機能です。
設定項目は変更回数と経過日数の2通りあります。また、グローバル(全ユーザに適用)かユーザごとに設定するかの2通りがあります。

グローバルに設定する場合はpassword_historypassword_reuse_interval変数を使用します。
ユーザごとに設定する場合はCREATE USERALTER USERで次のようにpassword_optionのPASSWORD HISTORYPASSWORD REUSE INTERVALを使用します。

変更回数

変更回数の設定について詳しく見ていきます。グローバルに設定する場合は、サーバー変数password_historyで変更回数の閾値を設定します。
例えば、password_history = 3とした場合、これまで設定したパスワード過去3回分は設定することができません。

実際に試してみます。デフォルトではpassword_historyは0(制限なし)となっています。今回はSET PERSIST構文を使って設定します。

次にtest1@localhostユーザを作成し、パスワードを繰り返し変更します。

ここで現行のパスワード含め過去3つ前のPassword2!に変更しようとするとエラーとなります。4つ前のPassword1!は設定が可能です。

次に個別のユーザに設定する方法を確認します。ユーザ作成時にPASSWORD HISTORYを設定します。すでにグローバルに設定している場合は、個別の設定で上書きされます。

個別に設定された値はmysql.userのPassword_reuse_historyから確認できます。
グローバル設定が適用されている場合はNULLとなります。

パスワードを変更してみます。test2@localhostの変更回数は1に設定されているため、現行のパスワード含め2つ前のパスワードには設定が可能です。

経過日数

経過日数の設定について詳しく見ていきます。グローバルに設定する場合は、サーバー変数password_reuse_intervalで変更回数の閾値を設定します。
例えば、password_reuse_interval = 100とした場合、現在から100日以内に設定したパスワードは設定することができません。
なお設定は日単位となっており、検証には時間がかかるため本記事では設定方法の確認のみ行います。

デフォルトではpassword_reuse_intervalも0(制限なし)となっています。変更回数と同様にSET PERSIST構文を使って設定します。

次に個別のユーザに設定する方法を確認します。ユーザ作成時にPASSWORD REUSE INTERVALを設定します。すでにグローバルに設定している場合は、個別の設定が上書きされます。

個別に設定された値はmysql.userPassword_reuse_timeから確認できます。グローバル設定が適用されている場合はNULLとなります。

変更回数と経過日数を組み合わせて設定する

変更回数と経過日数の設定を組み合わせることも可能です。例えばpassword_history = 3password_reuse_interval=100を設定している場合は、両方の基準を満たしている必要があります。
次の例ではpassword_historyの条件は満たしていますが、password_reuse_intervalの条件を満たしていないため、ALTER USER test4@localhost identified by 'Password1!';はエラーが返されます。

パスワードの履歴について

パスワードの履歴はmysql.password_historyに保持されます。

※本検証ではdefault_authentication_pluginmysql_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@localhostnew_test@localhostRENAME USERで変更します。
mysql.password_historyも合わせて変更されていることが確認できます。

また、現在password_history=3、password_reuse_interval=0に設定されていますが、ユーザ名を変更する前の変更回数を含めてチェックされていることがわかります。
※現在、test1@localhost(new_test@localhost)はPassword1!→Password2!→Password3!→Password4!→Password1!という順番で変更されています。

ユーザを削除した場合

次にDROP USERnew_test@localhostを削除し、mysql.password_historyからもnew_test@localhostのデータ削除されることを確認します。

mysql.password_historyからデータは削除されているため再度new_test@localhostを作成する場合は、削除前の変更回数はカウントされません。

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 = 0password_reuse_interval=0を設定している状態(パスワードの再利用に関する制限がない状態)ではパスワードを変更してもmysql.password_historyには記録されません。

変更回数を設定すると、それ以降のパスワード変更履歴がmysql.password_historyに記録されます。

また、mysql.password_historyには設定された変更回数と経過日数のチェックに必要な分だけ記録されます。
例えば、test1@localhostのパスワードをさらに変更した場合、最初に設定した、’Password1!’は変更回数の制限(3回)から外れたため、mysql.password_historyからデータが削除されます。

Password_timestampを見ると一番古い2020-11-17 17:27:22.464924のデータが削除されていることがわかります。

また、ドキュメントにはパスワード変更を試みた時にこの動作が発生すると記載があります。
password_history = 1に変更して、どのタイミングでmysql.password_historyからデータが削除されるか確認します。

パスワードが変更された時点でmysql.password_historyからデータが削除されました。

まとめ

パスワードの管理はセキュリティ上重要ですが、機能の仕様をよく理解しないで有効化すると思わぬトラブルが発生してしまう可能性があります。MySQL8.0で追加されたパスワードの管理機能は他にもあるので、他の機能についても今後取り上げていきたいと思います。


MySQL

 

Return Top