MySQL - ユーザ

概要

MySQLのユーザ管理は、データベースへのアクセス制御とセキュリティの基盤を担う重要な機能である。

MySQLのユーザアカウントは、'<ユーザ名>'@'<ホスト名>' の形式で一意に識別される。
これにより、同じユーザ名であってもアクセス元のホストによって異なる権限や認証方式を設定できる。

ユーザ名の長さ制限は、MySQL 5.7以前では16文字まで、MySQL 8.0以降では32文字までとなっている。
ホスト名は、具体的なホスト名、IPアドレス、ワイルドカード (%) を使用して指定できる。

MySQL 8.0では、セキュリティ強化のため、認証プラグインのデフォルトが caching_sha2_password に変更された。
これは、従来の mysql_native_password よりも強力なSHA-256ベースの認証を提供する。

また、MySQL 8.0では、PASSWORD 関数が削除され、SET PASSWORD の代わりに ALTER USER の使用が推奨されるようになった。
更に、パスワードの有効期限、アカウントロック、リソース制限、ログイン失敗によるアカウントロック等、高度なアカウント管理機能が追加されている。

MySQL 5.7以前との互換性を維持する必要がある場合は、mysql_native_password プラグインを使用できるが、
MySQL 8.0.34で非推奨となり、8.4で無効化、9.0で完全に削除される予定であるため、新規システムでは caching_sha2_password の使用を推奨する。

認証プラグイン

MySQLは、プラグインベースの認証システムを採用しており、ユーザごとに異なる認証方式を選択できる。

caching_sha2_password

MySQL 8.0のデフォルト認証プラグインである。

• SHA-256アルゴリズムを使用し、より強力なパスワードハッシュを提供する。
• パスワードハッシュをメモリにキャッシュすることで、認証パフォーマンスを向上させる。
• 初回接続時はSSL/TLS接続または RSA鍵ペアによる暗号化通信が必要である。
• 最新のセキュリティ標準に準拠しており、新規システムではこちらを推奨する。

mysql_native_password

MySQL 5.7以前のデフォルト認証プラグインである。

• SHA-1アルゴリズムを使用した従来の認証方式である。
• MySQL 8.0.34で非推奨となり、8.4で無効化、9.0で削除される予定である。
• 古いクライアントライブラリやアプリケーションとの互換性が必要な場合に使用される。
• セキュリティ上の理由から、新規システムでの使用は推奨されない。

認証プラグインの確認と変更

現在のユーザの認証プラグインを確認するには、以下に示すコマンドを使用する。

 # 全ユーザの認証プラグインを確認
 SELECT user, host, plugin FROM mysql.user;
 
 # 特定ユーザの認証プラグインを確認
 SELECT plugin FROM mysql.user WHERE user = '<ユーザ名>' AND host = '<ホスト名>';

認証プラグインを変更するには、ALTER USER 文を使用する。

 # 認証プラグインを変更 (パスワードも同時に設定)
 ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password BY 'password';
 
 # mysql_native_passwordに変更する場合
 ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

ユーザの作成

新しいユーザアカウントを作成するには、CREATE USER 文を使用する。

基本構文

最もシンプルなユーザ作成の構文は以下の通りである。

 # 基本的なユーザ作成
 CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
 
 # ローカルホストからのみアクセス可能なユーザ
 CREATE USER 'localuser'@'localhost' IDENTIFIED BY 'password';
 
 # 任意のホストからアクセス可能なユーザ
 CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'password';
 
 # 特定のIPアドレスからアクセス可能なユーザ
 CREATE USER 'ipuser'@'192.168.1.100' IDENTIFIED BY 'password';
 
 # 特定のサブネットからアクセス可能なユーザ
 CREATE USER 'subnetuser'@'192.168.1.%' IDENTIFIED BY 'password';

認証プラグインの指定

ユーザ作成時に認証プラグインを明示的に指定できる。

 # caching_sha2_passwordを明示的に指定
 CREATE USER 'user1'@'localhost'
    IDENTIFIED WITH caching_sha2_password BY 'password';
 
 # mysql_native_passwordを指定 (互換性が必要な場合)
 CREATE USER 'user2'@'localhost'
    IDENTIFIED WITH mysql_native_password BY 'password';

リソース制限の設定

ユーザごとにリソース使用量の制限を設定できる。

 # リソース制限を設定したユーザの作成
 CREATE USER 'limiteduser'@'localhost'
    IDENTIFIED BY 'password'
    WITH MAX_QUERIES_PER_HOUR 1000
         MAX_UPDATES_PER_HOUR 500
         MAX_CONNECTIONS_PER_HOUR 100
         MAX_USER_CONNECTIONS 5;

パスワードオプションの設定

パスワードの有効期限や初回ログイン時のパスワード変更を設定できる。

 # パスワードを90日ごとに変更する必要があるユーザ
 CREATE USER 'expireuser'@'localhost'
    IDENTIFIED BY 'password'
    PASSWORD EXPIRE INTERVAL 90 DAY;
 
 # パスワードを無期限にするユーザ
 CREATE USER 'neverexpireuser'@'localhost'
    IDENTIFIED BY 'password'
    PASSWORD EXPIRE NEVER;
 
 # 初回ログイン時にパスワード変更が必要なユーザ
 CREATE USER 'firstloginuser'@'localhost'
    IDENTIFIED BY 'temporary_password'
    PASSWORD EXPIRE;
 
 # ログイン失敗によるアカウントロックを設定 (MySQL 8.0.19以降)
 CREATE USER 'lockuser'@'localhost'
    IDENTIFIED BY 'password'
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME 2;

コメントと属性の設定

MySQL 8.0.21以降では、ユーザにコメントやカスタム属性を設定できる。

 # コメント付きユーザの作成 (MySQL 8.0.21以降)
 CREATE USER 'commentuser'@'localhost'
    IDENTIFIED BY 'password'
    COMMENT 'アプリケーション用のユーザアカウント';
 
 # カスタム属性付きユーザの作成 (MySQL 8.0.21以降)
 CREATE USER 'attributeuser'@'localhost'
    IDENTIFIED BY 'password'
    ATTRIBUTE '{"department": "IT", "employee_id": "12345"}';
 
 # アカウントロック状態で作成 (後で有効化する場合)
 CREATE USER 'lockeduser'@'localhost'
    IDENTIFIED BY 'password'
    ACCOUNT LOCK;

ユーザの変更

既存のユーザアカウントの設定を変更するには、ALTER USER 文を使用する。

パスワードの変更

ユーザのパスワードを変更する方法を以下に示す。

 # 他のユーザのパスワードを変更 (権限が必要)
 ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';
 
 # 自分自身のパスワードを変更
 ALTER USER USER() IDENTIFIED BY 'new_password';
 
 # 現在のパスワードを指定して変更 (MySQL 8.0以降)
 ALTER USER USER() IDENTIFIED BY 'new_password'
    REPLACE 'current_password';
 
 # パスワードを即座に期限切れにする
 ALTER USER 'user'@'host' PASSWORD EXPIRE;
 
 # パスワードの有効期限を設定
 ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL 180 DAY;
 
 # パスワードを無期限にする
 ALTER USER 'user'@'host' PASSWORD EXPIRE NEVER;
 
 # デフォルトのパスワード有効期限ポリシーを使用
 ALTER USER 'user'@'host' PASSWORD EXPIRE DEFAULT;

認証プラグインの変更

既存ユーザの認証プラグインを変更できる。

 # caching_sha2_passwordに変更
 ALTER USER 'user'@'host'
    IDENTIFIED WITH caching_sha2_password BY 'password';
 
 # mysql_native_passwordに変更 (互換性のため)
 ALTER USER 'user'@'host'
    IDENTIFIED WITH mysql_native_password BY 'password';

アカウントのロックとアンロック

アカウントを一時的にロックまたはアンロックできる。

 # アカウントをロック (ログイン不可にする)
 ALTER USER 'user'@'host' ACCOUNT LOCK;
 
 # アカウントをアンロック (ログイン可能にする)
 ALTER USER 'user'@'host' ACCOUNT UNLOCK;
 
 # 複数の設定を同時に変更
 ALTER USER 'user'@'host'
    IDENTIFIED BY 'new_password'
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1;

ユーザの名前変更

ユーザ名またはホスト名を変更するには、RENAME USER 文を使用する。

 # ユーザ名を変更
 RENAME USER 'oldname'@'localhost' TO 'newname'@'localhost';
 
 # ホスト名を変更
 RENAME USER 'user'@'old_host' TO 'user'@'new_host';
 
 # ユーザ名とホスト名を両方変更
 RENAME USER 'olduser'@'oldhost' TO 'newuser'@'newhost';
 
 # 複数のユーザを1度に変更
 RENAME USER
    'user1'@'localhost' TO 'newuser1'@'localhost',
    'user2'@'localhost' TO 'newuser2'@'localhost';

※注意
RENAME USER 文を実行すると、ユーザの権限は自動的に新しいアカウント名に引き継がれる。
ただし、古いユーザ名を参照しているビューやストアドプロシージャ等は手動で更新する必要がある。

ユーザ情報の確認

MySQLのユーザ情報を確認する方法を以下に示す。

mysql.userテーブル

ユーザアカウント情報は、mysql.user テーブルに格納されている。

 # 全ユーザの基本情報を表示
 SELECT Host, User, plugin, account_locked FROM mysql.user;
 
 # 特定ユーザの詳細情報を表示
 SELECT * FROM mysql.user WHERE User = 'ユーザ名'\G
 
 # パスワード有効期限の設定を確認
 SELECT User, Host, password_lifetime, password_last_changed
    FROM mysql.user;
 
 # アカウントロック状態を確認
 SELECT User, Host, account_locked FROM mysql.user;

SHOW CREATE USER

ユーザの作成文を確認するには、SHOW CREATE USER 文を使用する。

 # 特定ユーザの作成文を表示
 SHOW CREATE USER 'user'@'host';
 
 # 現在のユーザの作成文を表示
 SHOW CREATE USER CURRENT_USER;
 
 # 現在のユーザの作成文を表示 (別の方法)
 SHOW CREATE USER USER();

現在のユーザの確認

現在ログインしているユーザ情報を確認する方法を以下に示す。

 # 現在のユーザ名とホスト名を表示
 SELECT CURRENT_USER();
 
 # 接続ユーザ名を表示 (プロキシユーザの場合、元のユーザ名)
 SELECT USER();
 
 # ユーザ属性を確認 (MySQL 8.0.21以降)
 SELECT * FROM information_schema.user_attributes
    WHERE USER = 'ユーザ名' AND HOST = 'ホスト名';

リソース制限

MySQLでは、ユーザごとにリソースの使用量を制限できる。

リソース制限の種類

下表に、設定可能なリソース制限を示す。

ユーザごとのリソース制限項目

制限項目	説明	デフォルト値
MAX_QUERIES_PER_HOUR	1時間あたりに実行可能な最大クエリ数 SELECT、INSERT、UPDATE、DELETE等の合計	0 (制限なし)
MAX_UPDATES_PER_HOUR	1時間あたりに実行可能な最大更新数 INSERT、UPDATE、DELETE等の合計	0 (制限なし)
MAX_CONNECTIONS_PER_HOUR	1時間あたりに確立可能な最大接続数	0 (制限なし)
MAX_USER_CONNECTIONS	同時に確立可能な最大接続数	0 (制限なし)

リソース制限の設定例

リソース制限を設定する方法を以下に示す。

 # ユーザ作成時にリソース制限を設定
 CREATE USER 'limiteduser'@'localhost'
    IDENTIFIED BY 'password'
    WITH MAX_QUERIES_PER_HOUR 10000
         MAX_UPDATES_PER_HOUR 5000
         MAX_CONNECTIONS_PER_HOUR 1000
         MAX_USER_CONNECTIONS 10;
 
 # 既存ユーザにリソース制限を設定
 ALTER USER 'user'@'host'
    WITH MAX_QUERIES_PER_HOUR 5000
         MAX_USER_CONNECTIONS 5;
 
 # 特定のリソース制限のみを変更
 ALTER USER 'user'@'host'
    WITH MAX_QUERIES_PER_HOUR 20000;
 
 # リソース制限を解除 (0を指定)
 ALTER USER 'user'@'host'
    WITH MAX_QUERIES_PER_HOUR 0
         MAX_UPDATES_PER_HOUR 0
         MAX_CONNECTIONS_PER_HOUR 0
         MAX_USER_CONNECTIONS 0;

リソースカウンタのリセット

リソース制限のカウンタをリセットする方法を以下に示す。

 # 全ユーザのリソースカウンタをリセット
 FLUSH USER_RESOURCES;
 
 # 権限テーブルを再読み込み (リソースカウンタもリセットされる)
 FLUSH PRIVILEGES;

※注意
リソース制限は1時間ごとに自動的にリセットされる。
手動でリセットする場合は、上記のコマンドを使用する。

パスワード管理

MySQL 8.0では、パスワードの有効期限、強度検証、ログイン失敗によるアカウントロック等、高度なパスワード管理機能が提供されている。

パスワードの有効期限

パスワードの有効期限を設定することで、定期的なパスワード変更を強制できる。

グローバル設定

全ユーザに適用されるデフォルトのパスワード有効期限を設定する。

 # デフォルトのパスワード有効期限を90日に設定
 SET GLOBAL default_password_lifetime = 90;
 
 # デフォルトのパスワード有効期限を無効化
 SET GLOBAL default_password_lifetime = 0;
 
 # 設定ファイル (my.cnf) での設定
 # [mysqld]
 # default_password_lifetime = 90

ユーザ個別設定

特定のユーザに対してパスワード有効期限を設定する。

 # ユーザ作成時に有効期限を設定
 CREATE USER 'user'@'host'
    IDENTIFIED BY 'password'
    PASSWORD EXPIRE INTERVAL 180 DAY;
 
 # 既存ユーザの有効期限を変更
 ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;
 
 # パスワードを無期限にする
 ALTER USER 'user'@'host' PASSWORD EXPIRE NEVER;
 
 # デフォルトのポリシーを使用する
 ALTER USER 'user'@'host' PASSWORD EXPIRE DEFAULT;
 
 # パスワードを即座に期限切れにする (次回ログイン時に変更が必要)
 ALTER USER 'user'@'host' PASSWORD EXPIRE;

パスワードポリシー

validate_password コンポーネントを使用して、パスワードの強度を検証できる。

validate_passwordコンポーネントのインストール

 # validate_passwordコンポーネントをインストール
 INSTALL COMPONENT 'file://component_validate_password';
 
 # インストールされているコンポーネントを確認
 SELECT * FROM mysql.component;
 
 # アンインストール
 UNINSTALL COMPONENT 'file://component_validate_password';

パスワードポリシーの設定

 # パスワードポリシーのレベルを設定
 # LOW (0): 長さのみチェック
 # MEDIUM (1): 長さ、数字、大文字小文字、特殊文字をチェック (デフォルト)
 # STRONG (2): MEDIUMに加えて、辞書ファイルとのマッチングもチェック
 SET GLOBAL validate_password.policy = 'MEDIUM';
 
 # パスワードの最小長を設定 (デフォルト: 8)
 SET GLOBAL validate_password.length = 12;
 
 # 必要な数字の最小個数 (デフォルト: 1)
 SET GLOBAL validate_password.number_count = 2;
 
 # 必要な大文字と小文字の最小個数 (デフォルト: 1)
 SET GLOBAL validate_password.mixed_case_count = 1;
 
 # 必要な特殊文字の最小個数 (デフォルト: 1)
 SET GLOBAL validate_password.special_char_count = 1;
 
 # 現在の設定を確認
 SHOW VARIABLES LIKE 'validate_password%';

ログイン失敗によるアカウントロック

MySQL 8.0.19以降では、ログイン失敗回数に基づいてアカウントを自動的にロックできる。

 # ユーザ作成時に設定
 # 3回連続でログインに失敗すると、2日間アカウントがロックされる
 CREATE USER 'user'@'host'
    IDENTIFIED BY 'password'
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME 2;
 
 # 既存ユーザに設定
 ALTER USER 'user'@'host'
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1;
 
 # ログイン失敗によるロックを無効化
 ALTER USER 'user'@'host'
    FAILED_LOGIN_ATTEMPTS 0
    PASSWORD_LOCK_TIME 0;
 
 # ロック時間を無期限にする (-1を指定)
 ALTER USER 'user'@'host'
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME -1;
 
 # ログイン失敗カウンタは手動でアンロックする必要がある
 ALTER USER 'user'@'host' ACCOUNT UNLOCK;

※注意
PASSWORD_LOCK_TIME は日数単位で指定する。
-1 を指定すると、手動でアンロックするまで永続的にロックされる。

ユーザの削除

ユーザアカウントを削除するには、DROP USER 文を使用する。

 # ユーザを削除
 DROP USER 'user'@'host';
 
 # 複数のユーザを同時に削除
 DROP USER 'user1'@'host1', 'user2'@'host2', 'user3'@'localhost';
 
 # 存在する場合のみ削除 (エラーを回避)
 DROP USER IF EXISTS 'user'@'host';

※注意
DROP USER を実行すると、ユーザに付与されていた全ての権限も自動的に削除される。
ユーザが現在接続中の場合でも削除は実行されるが、既存の接続は維持される。

既存の接続を終了させるには、KILL コマンドを使用する必要がある。

 # 接続中のプロセスを確認
 SHOW PROCESSLIST;
 
 # 特定のプロセスを終了
 KILL <プロセスID>;