Introduction

業務でMySQLの既存ユーザーの設定を反映した新しいユーザーを作成するタスクを実施していました。
既存のユーザーをSHOW CREATE USERしユーザー名とパスワードだけ変更すればいいやと思ってSQLを実行して返ってきた結果が以下でした。もちろんユーザーとパスワードは記事用に変えてします。

CREATE USER `develop-user`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 'XXXXX' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

'develop-user'@'%'がユーザーです。
この時にIDENTIFIEDから後のSQL文が一瞬わからなく、これ流すの怖くない？っと思ってSQLの意味を調べ、ついでに検証したので備忘録としてこの記事に残したいと思います。
最近、プログラミングやクラウドの構築ばかりなので、今回のようなSQLとかの基礎が抜けていたり忘れてしまっていたりするので今回いい振り返りになりました

環境

$ lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 22.04.2 LTS
Release:	22.04
Codename:	jammy

$ mysql --version
mysql  Ver 8.0.33-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

$ mysqld --version
/usr/sbin/mysqld  Ver 8.0.33-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

Setup

今回は、UbuntuのEC2を立てて検証しました。EC2にssh接続した後の操作を記載します。

インストール

まず、MySQLのクライアントとサーバーをインストールします。

$ sudo apt install mysql-server mysql-client

$ systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2023-05-28 14:47:30 UTC; 5 days ago
   Main PID: 2271 (mysqld)
     Status: "Server is operational"
      Tasks: 67 (limit: 1141)
     Memory: 451.7M
        CPU: 32min 32.928s
     CGroup: /system.slice/mysql.service
             └─2271 /usr/sbin/mysqld

MySQL USER作成

起動しているMySQLに接続します。初期設定ではrootユーザーへの接続は、sudoコマンドでパスワードは必要ありません。

$ sudo mysql -u root

rootユーザーは、root@localhostのため、-h localhostをつけても接続できます。localhostのため、EC2をパブリックアクセスできるようにしても外部からはアクセスできません。
次に検証用のユーザーを作成します。

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

mysql> SELECT Host, User FROM mysql.user WHERE User = 'develop-user';

はい、貧弱パスワードのユーザーが作成されました。次に作成したユーザーと同じ設定を作成できるSHOW CREATE USERを実行します。

mysql> SHOW CREATE USER 'develop-user'@'%';

CREATE USER `develop-user`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 'XXXXX' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

MySQL8系で出力されたSHOW CREATE USERが上記になります。MySQL5系では出力が異なります。

外部接続を可能にする

AWSを外部から接続できるようにしても外部からMySQL自体の設定で弾かれて接続できません。 MySQLの設定項目であるbind_addressを変更します。設定ファイルを変更しMySQLを再起動して設定を読み込ませます。

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
...
#bind-address            = 127.0.0.1
bind-address            = 0.0.0.0
...

$ sudo systemctl restart mysql

これで全IPからアクセスできるようになりました。いつものことですが、セキュリティには気をつけてください。記事ではガバガバの設定をしていますが、個人や仕事などで設定する際はセキュリティ設定を慎重に行ってください。
また余談になりますがbind_addressは、SQLで変更できないです

mysql> SET GLOBAL bind_address = '0.0.0.0';
ERROR 1238 (HY000): Variable 'bind_address' is a read only variable

各SQL文の説明

上記で出力されたCREATE文を意味ごとに分割すると以下に分けられますこの記事で説明する項目

CREATE USER develop-user@%
IDENTIFIED WITH ‘caching_sha2_password’ AS ‘XXXXX’
REQUIRE NONE
PASSWORD EXPIRE DEFAULT
ACCOUNT UNLOCK 次回の記事で説明する項目
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE CURRENT DEFAULT

CREATE USERはみたままですね。今回はオプション部分を説明します。
REQUIREは暗号化接続を利用するか否かです。今回の記事では証明書設定を説明すると長くなるので省きます。REQUIRE NONEは暗号化接続を利用しないという意味になります。
以下で各オプションを説明していきますが、正直公式ドキュメントに全て記載してあるので、原文がいい人はそちらをどうぞ公式がアレルギーの人はぜひ見て行ってください。

IDENTIFIED

はじめにIDENTIFIED構文を説明します。
IDENTIFIEDはユーザーにパスワードを設定する時に使用するオプションになります。

BY

一番簡単なIDENTIFIEDを使用したSQLは以下のようなBYを使用したSQLになります。Setupで作成したユーザーを更新します。

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

になります。passwordの部分は実際の設定するパスワードを入力してください。
BYは後者の文字列を暗号化してパスワードを生成します。わかりやすいように結果を縦出力にします。

mysql> SELECT
    ->  Host, User, plugin, authentication_string
    -> FROM
    ->  mysql.user
    -> WHERE
    ->  User = 'develop-user'\G
*************************** 1. row ***************************
                 Host: %
                 User: develop-user
               plugin: caching_sha2_password
authentication_string: $A$005$F!"HMEafwN2;PK19TBLFxi/jQAo2xvDJFBUSSWh5uUXI7pXdh8qMxBZ8

authentication_stringが暗号化されたパスワードです。今回で言うとpasswordをハッシュ化していますね。
BYで生成されるauthentication_stringは毎回異なります。

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

SELECT
  Host, User, plugin, authentication_string
FROM
  mysql.user
WHERE
  User = 'develop-user'\G

*************************** 1. row ***************************
                 Host: %
                 User: develop-user
               plugin: caching_sha2_password
authentication_string: $A$005$(*E;^@UO(2{ig3/5BtgS6Mbwz/M8lOm/QCz15AAqqFCmf1DS4bFUqao/

AS

全く同じハッシュ値でパスワードを設定したい場合は、ASを使用します。
ASを使用する場合は、pluginも指定してください。上記を見るとpluginはcaching_sha2_passwordですね。pluginを指定する時はWITHを使用します。

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$F!"HMEafwN2;PK19TBLFxi/jQAo2xvDJFBUSSWh5uUXI7pXdh8qMxBZ8';

ただ上記のSQLだと失敗してしまいます。ERROR 1827 (HY000): The password hash doesn't have the expected format.
公式に以下の記述があります。

MySQL 8.0.17 の時点では、ハッシュ文字列は文字列リテラルまたは 16 進数値のいずれかになります。後者は、print_identified_with_as_hex システム変数が有効になっている場合に、印刷不可能な文字を含むパスワードハッシュに対して SHOW CREATE USER によって表示される値のタイプに対応します。

要は、印刷不可能な文字が含まれていることがあり、端末ディスプレイやその他の環境で悪影響があるためprint_identified_with_as_hexをONにしてねということになります。現バージョンのデフォルトでは、OFFになっているためONにします。

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

mysql> SET GLOBAL print_identified_with_as_hex = ON;

コピーするハッシュ化されたパスワードは、SHOW CREATE USERで取得します。

mysql> SHOW CREATE USER 'develop-user'@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for develop-user@%                                                                                                                                                                                                                                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `develop-user`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035242318071B253B7664782D1401575D532E77715807566543765666364D525570446879384766457767305751722E6750784A4E416373466C45723165786C312F REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

では、ASでパスワードを設定します。
ハッシュ値の部分は'xxxxx'や"xxxx"などでクォーテーションをつけないようにしたください。

mysql> ALTER USER 'develop-user'@'%' IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035242318071B253B7664782D1401575D532E77715807566543765666364D525570446879384766457767305751722E6750784A4E416373466C45723165786C312F;
Query OK, 0 rows affected (0.00 sec)

次は問題なくできましたね。ハッシュ値が同じなので前と同じパスワードでログインできます。

mysql_native_password

MySQLは2つの認証プラグインを用意しています。
→ https://dev.mysql.com/doc/refman/8.0/ja/caching-sha2-pluggable-authentication.html
MySQL8のデフォルトはcaching_sha2_passwordになっています。

mysql> SHOW GLOBAL VARIABLES LIKE 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+

MySQL8以前は、mysql_native_passwordがデフォルトの認証プラグインなのでMySQLのバージョンアップ後のユーザー複製時は気をつけてください。
また、mysql_native_passwordはprint_identified_with_as_hexを考慮せずIDENTIFIEDのASを使用できます。

PASSWORD EXPIRE

パスワードの有効期限を決定します。
公式に記載ありますので、それでも十分わかりやすいです。一様、各SQLの説明と検証結果を記載していきます。

INTERVAL N DAY

PASSWORD EXPIRE INTERVAL N DAYでパスワード有効期限日数指定します。例としては以下になります。パスワードの有効期限は一日だけとします。

mysql> ALTER USER 'develop-user'@'%' PASSWORD EXPIRE INTERVAL 1 DAY;

mysql> SELECT
    ->  Host, User, plugin, authentication_string, password_expired, password_last_changed, password_lifetime
    -> 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-10 16:04:12
    password_lifetime: 1

有効期限日数が達した状態でUserのステータスを再度確認してみましたが、上記と同じ表示でpassword_expiredもNのままでした。
しかしログイン自体は可能ですが、有効期限は切れているのでSQLの実行はできませんでした。

NEVER

PASSWORD EXPIRE NEVERだと有効期限は設定されません。

mysql> ALTER USER 'develop-user'@'%' PASSWORD EXPIRE NEVER;

mysql> SELECT
    ->  Host, User, plugin, authentication_string, password_expired, password_last_changed, password_lifetime
    -> 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-10 16:04:12
    password_lifetime: 0

DEFAULT

PASSWORD EXPIRE DEFAULTは、MySQLの設定されているdefault_password_lifetimeで決定されます。デフォルトでは0に設定されており0は無期限を表します。そのため、default_password_lifetimeが0の状態のPASSWORD EXPIRE DEFAULTはPASSWORD EXPIRE NEVERと同じ意味になります。

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

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

mysql> SELECT
    ->  Host, User, plugin, authentication_string, password_expired, password_last_changed, password_lifetime
    -> 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-10 16:04:12
    password_lifetime: NULL

password_lifetimeがNEVERは0だったのに対し、DEFAULTは値がNULLになりdefault_password_lifetimeによって有効期限が決まります。
また、MySQL 5.7.4で導入されたdefault_password_lifetimeがじわじわくるの記事に書かれていますが、MySQL5.7.4 ~ 5.7.10までは、default_password_lifetimeはデフォルトで360に設定されているため気をつけてください。何も考えないで設定すると事故ります！

指定なし

特に引数を指定しないPASSWORD EXPIREはコマンドを実行した瞬間に有効期限切れになります。

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

mysql> SELECT
    ->  Host, User, plugin, authentication_string, password_expired, password_last_changed, password_lifetime
    -> 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: Y
password_last_changed: 2023-06-10 16:04:12
    password_lifetime: 0
1 row in set (0.00 sec)

password_expiredがYになっていることがわかりますね。

期限切れになった時の挙動

公式はこちら → 6.2.16 期限切れパスワードのサーバー処理
パスワードの期限切れ後、ログイン自体は可能ですがSQLが実行できなくなってしまいます。

$ mysql -u develop-user -ppassword
mysql> SELECT CURRENT_USER();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

MySQLWorkbenchはログイン自体ができないことがわかります。これはログイン時にデータベース情報などのデータを取得しているからと思われます。

SQLが実行できないと記載しましたが、エラー文にも記載がある通りパスワードリセットコマンドはパスワード期限切れのユーザーでも実行できます。もちろんパスワードリセット後SQLが実行できるようになります。

$ mysql -u develop-user -ppassword
mysql> ALTER USER 'develop-user'@'%' IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035242318071B253B7664782D1401575D532E77715807566543765666364D525570446879384766457767305751722E6750784A4E416373466C45723165786C312F PASSWORD EXPIRE DEFAULT;
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| develop-user@% |
+----------------+

このような挙動はサンドボックスモードというらしいです。

「「サンドボックスモード、」」では、クライアントは期限切れのパスワードのリセットに必要な操作のみを実行できます。

MySQLのパスワード切れなどは公式にも

サーバーが期限切れパスワードクライアントを切断するか、サンドボックスモードに制限するかは、クライアントとサーバーの設定の組合せによって決まります。

と記載がある通りMySQLクライアントやMySQLWorkbench、アプリケーションなどによって挙動が少し変わるため気をつけてください。

ACCOUNT

アカウントを使用不可にするか否かのコマンドです。以下はアカウントを使用不可(LOCK)にします。

mysql> ALTER USER 'develop-user'@'%' ACCOUNT LOCK;

mysql> SELECT
    ->  Host, User, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked
    -> 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-10 18:00:50
    password_lifetime: NULL
       account_locked: Y

アカウントロックしてもログイン中はSQL実行できますが、一回ログアウトするとログインできません。

mysql> ALTER USER 'develop-user'@'%' ACCOUNT LOCK;
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| develop-user@% |
+----------------+
mysql> exit;
Bye

$ mysql -u develop-user -ppassword
ERROR 3118 (HY000): Access denied for user 'develop-user'@'localhost'. Account is locked.

UNLOCKでロックアカウントを使用可能(UNLOCK)にします。
ロックユーザーはログインできないのでrootユーザーでUNLOCKします。

mysql> ALTER USER 'develop-user'@'%' ACCOUNT UNLOCK;

mysql> SELECT
    ->  Host, User, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked
    -> 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-10 18:00:50
    password_lifetime: NULL
       account_locked: N

終わりに

長くなるので、次のSQLオプションは後編に記載します。

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

目次

Introduction

環境

Setup

インストール

MySQL USER作成

外部接続を可能にする

各SQL文の説明

IDENTIFIED

BY

AS

mysql_native_password

PASSWORD EXPIRE

INTERVAL N DAY

NEVER

DEFAULT

指定なし

期限切れになった時の挙動

ACCOUNT

終わりに

参考文献

kita21

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

目次

Introduction

環境

Setup

インストール

MySQL USER作成

外部接続を可能にする

各SQL文の説明

IDENTIFIED

BY

AS

mysql_native_password

PASSWORD EXPIRE

INTERVAL N DAY

NEVER

DEFAULT

指定なし

期限切れになった時の挙動

ACCOUNT

終わりに

参考文献

kita21

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