Introduction

以前の記事安心してCREATE USERを実行するためにSQL文を理解する前編の続きになります。

各SQL文の説明

この記事で説明する項目

PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE CURRENT DEFAULT

PASSWORD HISTORY

パスワードを再設定をする時にどの程度過去のパスワードを使い回しても良いかを決める設定を表しています。まず、初期パスワードを設定します

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';

N

過去のパスワード設定をする時に過去N回までのパスワードは指定できない設定を行います。
過去2回までのパスワードは指定できないように設定します。

mysql> ALTER USER 'develop-user'@'%' PASSWORD HISTORY 2;

mysql> SELECT
    -> Host, User, plugin, authentication_string, password_expired, password_last_changed,password_lifetime, account_locked, Password_reuse_history
    -> FROM
    ->  mysql.user
    -> WHERE
    -> User = 'develop-user'\G
*************************** 1. row ***************************
                  Host: %
                  User: develop-user
                plugin: caching_sha2_password
 authentication_string: $A$005$#;vdx-W]S.wqXVeCvVf6MRUpDhy8GfEwg0WQr.gPxJNAcsFlEr1exl1/
      password_expired: N
 password_last_changed: 2023-06-15 14:07:59
     password_lifetime: NULL
        account_locked: N
Password_reuse_history: 2

Password_reuse_historyが2になっていますね。では実際にどのような挙動になるか検証してみます。

# パスワードを2回変更
mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password1';
mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password2';

# 過去のパスワードを再設定
mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password2';
ERROR 3638 (HY000): Cannot use these credentials for 'develop-user@%' because they contradict the password history policy

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password1';
ERROR 3638 (HY000): Cannot use these credentials for 'develop-user@%' because they contradict the password history policy

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
ERROR 3638 (HY000): Cannot use these credentials for 'develop-user@%' because they contradict the password history policy

過去2回設定したパスワードは再設定できませんでしたが、過去3回以降のパスワードは設定できることがわかります
また過去3回目以降のパスワードを設定した場合、パスワード履歴が更新されるため再度同じパスワードでは設定できません。今回では、passwordの部分ですね。

default

こちら前の記事でもあった通りMySQLのデフォルトで設定されている値を元に挙動を決定する構文ですね。
今回はpassword_historyに設定されている値を元にパスワード再設定履歴を決定します。

mysql> SHOW GLOBAL VARIABLES LIKE 'password_history';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| password_history | 0     |
+------------------+-------+

Valueの0は、パスワード履歴の縛りをいないという意味になります。そのため、直前のパスワードでも設定可能になります。ではデフォルトの設定を行います。

mysql> ALTER USER 'develop-user'@'%' PASSWORD HISTORY DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> Host, User, plugin, authentication_string, password_expired, password_last_changed,password_lifetime, account_locked, Password_reuse_history
    -> FROM
    ->  mysql.user
    -> WHERE
    -> User = 'develop-user'\G
*************************** 1. row ***************************
                  Host: %
                  User: develop-user
                plugin: caching_sha2_password
ZMr5>entication_string: $A$005$FK2&<X
     21?bOomn.SFJrKAeDVOxVyZv1Li266iaBqLIdTLp.q4C0.
      password_expired: N
 password_last_changed: 2023-06-18 08:25:13
     password_lifetime: NULL
        account_locked: N
Password_reuse_history: NULL

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

Password_reuse_historyがNULLになっていますね。前の記事でpassword_lifetimeがNULLの時、MySQLの設定値を参照することを表していたので、今回も同じようにNULLだとMySQLの設定を参照するようです。

PASSWORD REUSE INTERVAL

こちらの設定もパスワードの再設定に関するSQLになります。
上記のパスワード履歴と違う点は、履歴の回数ではなく期間による制限になります。
パスワードの再利用間隔をN日に設定して、その日数より新しいパスワードの再利用を禁止します。

N DAY

password_lifetimeの時のN DAYと同じですね。1を指定すると同じパスワードの再設定を1日間制限します。
PASSWORD REUSE INTERVALを設定する前に登録したパスワードは、再設定可能です。いろいろややこしいと思うので検証した結果がわかりやすいと思います。

mysql> SELECT
    -> Host, User, plugin, authentication_string, password_expired, password_last_changed,password_lifetime, account_locked, Password_reuse_history, Password_reuse_time
    -> FROM
    ->  mysql.user
    -> WHERE
    -> User = 'develop-user'\G
*************************** 1. row ***************************
                  Host: %
                  User: develop-user
                plugin: caching_sha2_password
 authentication_string: $A$005$ j}AY>o%YM.M1EdqTDx6pnvlsf39hbhWEBUefFAvNX0yl4XAWPUD
      password_expired: N
 password_last_changed: 2023-06-18 10:17:04
     password_lifetime: NULL
        account_locked: N
Password_reuse_history: NULL
   Password_reuse_time: NULL

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'develop-user'@'%' PASSWORD REUSE INTERVAL 1 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> Host, User, plugin, authentication_string, password_expired, password_last_changed,password_lifetime, account_locked, Password_reuse_history, Password_reuse_time
    -> FROM
    ->  mysql.user
    -> WHERE
    -> User = 'develop-user'\G
*************************** 1. row ***************************
                  Host: %
                  User: develop-user
                plugin: caching_sha2_password
 authentication_string: $A$005$_j#FPXte:;<x~tkFk/x4o6d2PsheeA21k4WjEzuNmRQSx7tjm3g3Mu/Nd9
      password_expired: N
 password_last_changed: 2023-06-18 10:18:19
     password_lifetime: NULL
        account_locked: N
Password_reuse_history: NULL
   Password_reuse_time: 1

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
ERROR 3638 (HY000): Cannot use these credentials for 'develop-user@%' because they contradict the password history policy

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password1';
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password1';
ERROR 3638 (HY000): Cannot use these credentials for 'develop-user@%' because they contradict the password history policy

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
ERROR 3638 (HY000): Cannot use these credentials for 'develop-user@%' because they contradict the password history policy

/* １日結果 */
mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

記述が多くなってしまいましたが、大体の挙動は網羅できているのではないでしょうか？

DEFAULT

はい、またまた来ましたデフォルト設定ですね。
パスワード日数制限の変数値はpassword_reuse_intervalです。

mysql> SHOW GLOBAL VARIABLES LIKE 'password_reuse_interval';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| password_reuse_interval | 0     |
+-------------------------+-------+

mysql> ALTER USER 'develop-user'@'%' PASSWORD REUSE INTERVAL DEFAULT;

mysql> SELECT
    -> Host, User, plugin, authentication_string, password_expired, password_last_changed,password_lifetime, account_locked, Password_reuse_history, Password_reuse_time
    -> FROM
    ->  mysql.user
    -> WHERE
    -> User = 'develop-user'\G
*************************** 1. row ***************************
                  Host: %
                  User: develop-user
                plugin: caching_sha2_password
 authentication_string: $A$005$:;d3id%P#51K%>4W98qXc0sNjF2iRMoTWxbjw/pMxr.FmzJd07aMeNqBc7/
      password_expired: N
 password_last_changed: 2023-06-18 10:51:06
     password_lifetime: NULL
        account_locked: N
Password_reuse_history: NULL
   Password_reuse_time: NULL

他の構文と同じくPassword_reuse_timeがNULLにに設定しているためMySQLの設定を反映し、設定値が0のため制限しないとなります。

パスワード制限設定のASへの影響

実は、パスワード制限設定をして影響があるのはBYを使用したパスワードの変更のみです。ASはpassword_historyとpassword_reuse_intervalの影響を受けません。
まず検証するためにパスワードの設定制限をかけてパスワードを設定します

/*  */
mysql> ALTER USER 'develop-user'@'%' PASSWORD HISTORY 3;
mysql> ALTER USER 'develop-user'@'%' PASSWORD REUSE INTERVAL 3 DAY;
mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
mysql> SELECT
    -> Host, User, plugin, authentication_string, password_expired, password_last_changed,password_lifetime, account_locked, Password_reuse_history, Password_reuse_time
    -> FROM
    ->  mysql.user
    -> WHERE
    -> User = 'develop-user'\G
*************************** 1. row ***************************
                  Host: %
                  User: develop-user
                plugin: caching_sha2_password
 authentication_string: $A$005$:;d3id%P#51K%>4W98qXc0sNjF2iRMoTWxbjw/pMxr.FmzJd07aMeNqBc7/
      password_expired: N
 password_last_changed: 2023-06-18 13:01:59
     password_lifetime: NULL
        account_locked: N
Password_reuse_history: 3
   Password_reuse_time: 3

では、検証してみましょう。

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
ERROR 3638 (HY000): Cannot use these credentials for 'develop-user@%' because they contradict the password history policy

/* ハッシュ値はpasswordをハッシュ化したもの */
mysql> ALTER USER 'develop-user'@'%' IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035243B03266727393C0C1F606D3C4E73424E70377439645A5537377677576370466B314A716E70313251724764503566552F4146463348644F5434373842514339;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'develop-user'@'%' IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035243B03266727393C0C1F606D3C4E73424E70377439645A5537377677576370466B314A716E70313251724764503566552F4146463348644F5434373842514339;
Query OK, 0 rows affected (0.01 sec)

$ mysql -udevelop-user -ppassword

ASは何回やっても更新できていることがわかります。

PASSWORD REQUIRE CURRENT

アカウントパスワードの変更の試行で現在のパスワードを指定する必要があるかどうかを制御します。

指定なし

このSQLで指定しない場合は、パスワード変更時に現在のパスワード入力を求めます。

mysql> ALTER USER 'develop-user'@'%' PASSWORD REQUIRE CURRENT;

mysql> SELECT
    -> Host, User, plugin, authentication_string, password_expired, password_last_changed,password_lifetime, account_locked, Password_reuse_history, Password_reuse_time, Password_require_current
    -> FROM
    ->  mysql.user
    -> WHERE
    -> User = 'develop-user'\G
*************************** 1. row ***************************
                    Host: %
                    User: develop-user
                  plugin: caching_sha2_password
   authentication_string: $A$005$+@~UAz,;:nqY\ONa6rGd0fagMVAluF8ibUteSmsMhWOk2FbqMp6SiTpA
        password_expired: N
   password_last_changed: 2023-06-18 13:22:09
       password_lifetime: NULL
          account_locked: N
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: Y

では変更してみましょう。develop-userでログインしてください

$ mysql -udevelop-user -ppassword

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.

mysql> SET PASSWORD FOR 'develop-user'@'%' = 'password';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password' REPLACE 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> SET PASSWORD FOR 'develop-user'@'%' = 'password' REPLACE 'password';
Query OK, 0 rows affected (0.02 sec)

ALTER、SETどちらもREPLACEをつけないとエラーでパスワード再設定ができないことがわかります。

rootユーザーでの変更では、REPLACEをつけなくても変更可能です。

$ sudo mysql -uroot

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> SET PASSWORD FOR 'develop-user'@'%' = 'password';
Query OK, 0 rows affected (0.01 sec)

OPTIONAL

SQL文は

mysql> ALTER USER 'develop-user'@'%' PASSWORD REQUIRE CURRENT OPTIONAL;

mysql> SELECT
    -> Host, User, plugin, authentication_string, password_expired, password_last_changed,password_lifetime, account_locked, Password_reuse_history, Password_reuse_time, Password_require_current
    -> FROM
    ->  mysql.user
    -> WHERE
    -> User = 'develop-user'\G
*************************** 1. row ***************************
                    Host: %
                    User: develop-user
                  plugin: caching_sha2_password
   authentication_string: $A$005$+@~UAz,;:nqY\ONa6rGd0fagMVAluF8ibUteSmsMhWOk2FbqMp6SiTpA
        password_expired: N
   password_last_changed: 2023-06-18 13:22:09
       password_lifetime: NULL
          account_locked: N
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: N

こちらはREPLACEをつけなくてもパスワードの再設定が可能になります。

DEFAULT

システム変数password_require_currentに準拠します。

mysql> SHOW GLOBAL VARIABLES LIKE 'password_require_current';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_require_current | OFF   |
+--------------------------+-------+

システム変数とアカウントごとの変数の設定時の挙動を下記に記載します。公式からまんま持ってきました

アカウントごとの設定	password_require_current システム変数	パスワードの変更には現在のパスワードが必要ですか。
PASSWORD REQUIRE CURRENT	OFF	はい
PASSWORD REQUIRE CURRENT	ON	はい
PASSWORD REQUIRE CURRENT OPTIONAL	OFF	いいえ
PASSWORD REQUIRE CURRENT OPTIONAL	ON	いいえ
PASSWORD REQUIRE CURRENT DEFAULT	OFF	いいえ
PASSWORD REQUIRE CURRENT DEFAULT	ON	はい

終わりに

やっと全てのSQL構文の説明を記載しました…
記事を書いていく上で公式ドキュメントを見ながら記載してきましたが、やっぱり公式ドキュメントでほとんど事足りますね。ただ日本語が変だったり入門者の人にとってはとっつきにくかったりと人によっては読み解けない場合もあるので今回の記事が参考になれば嬉しいです。

[MySQL] 安心してCREATE USERを実行するためにSQL文を理解する後編

目次

Introduction

各SQL文の説明

PASSWORD HISTORY

N

default

PASSWORD REUSE INTERVAL

N DAY

DEFAULT

パスワード制限設定のASへの影響

PASSWORD REQUIRE CURRENT

指定なし

OPTIONAL

DEFAULT

終わりに

kita21

[MySQL] 安心してCREATE USERを実行するためにSQL文を理解する 後編

目次

Introduction

各SQL文の説明

PASSWORD HISTORY

N

default

PASSWORD REUSE INTERVAL

N DAY

DEFAULT

パスワード制限設定のASへの影響

PASSWORD REQUIRE CURRENT

指定なし

OPTIONAL

DEFAULT

終わりに

kita21

[MySQL] 安心してCREATE USERを実行するためにSQL文を理解する後編