この記事の要点

MySQL のユーザー作成: CREATE USER 'name'@'host' IDENTIFIED BY 'pass'; 権限付与: GRANT ALL PRIVILEGES ON db.* TO 'name'@'host'; → FLUSH PRIVILEGES; ホスト指定: localhost (UNIX socket) / % (任意) / 192.168.1.% (サブネット) 認証プラグイン: 8.0 デフォルト caching_sha2_password、旧互換 mysql_native_password ロール (8.0+) でまとめて権限管理、SHOW GRANTS で現状確認

MySQL のユーザー

MySQL のユーザーは OS ユーザーとは別物で、MySQL サーバ内部の mysql.user テーブルに格納されます。識別子は 'ユーザー名'@'ホスト' の組で、同じ名前でもホストが違えば別ユーザー扱いです。たとえば app@'%' と app@'localhost' は別ユーザーで、それぞれに権限を付与できます。

基本: CREATE USER

-- 任意ホストから接続可能なユーザー (運用本番では推奨しない)
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass!23';

-- ローカル接続のみ
CREATE USER 'app'@'localhost' IDENTIFIED BY 'StrongPass!23';

-- 社内サブネットからのみ
CREATE USER 'app'@'192.168.1.%' IDENTIFIED BY 'StrongPass!23';

-- 認証プラグイン明示 (旧クライアント互換)
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'OldPass';

-- 確認
SELECT user, host, plugin FROM mysql.user;

権限の付与 (GRANT)

-- 指定 DB のすべての操作
GRANT ALL PRIVILEGES ON shopdb.* TO 'app'@'%';

-- 必要最小限 (推奨)
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'app'@'%';

-- 指定テーブルのみ
GRANT SELECT, INSERT ON shopdb.orders TO 'reader'@'%';

-- カラムレベル
GRANT SELECT (id, name), UPDATE (name) ON shopdb.users TO 'reader'@'%';

-- 全 DB に対する SUPER 権限 (DBA)
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

-- レプリケーション用
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';

-- 確認 / 反映
SHOW GRANTS FOR 'app'@'%';
FLUSH PRIVILEGES;   -- 直接 mysql.user を変更した場合に必須。GRANT/REVOKE 後は通常不要

主な権限一覧

権限	意味
SELECT	参照
INSERT, UPDATE, DELETE	データ更新
CREATE, DROP, ALTER	DDL (テーブル定義)
INDEX	インデックス作成・削除
EXECUTE	ストアド実行
CREATE VIEW, SHOW VIEW	ビュー操作
LOCK TABLES	テーブルロック
RELOAD	FLUSH 系コマンド
REPLICATION SLAVE/CLIENT	レプリケーション
SUPER (8.0 で分割)	ROLE_ADMIN, BINLOG_ADMIN 等の細粒度権限へ
ALL PRIVILEGES	当該スコープのすべて

ホスト指定の注意

ホスト指定	意味
localhost	UNIX ドメインソケット経由のみ
127.0.0.1	TCP のループバック
%	任意ホスト (危険)
192.168.1.%	192.168.1.0/24 相当
app.example.com	逆引きで一致するホスト
空文字 (なし)	localhost と同義 (古いバージョン)

localhost と 127.0.0.1 は別物: クライアントが -h 127.0.0.1 で接続すると TCP 扱いになり、localhost 限定のユーザーでは認証失敗します。

認証プラグイン

プラグイン	説明
caching_sha2_password	MySQL 8.0 既定。SHA-256 ベース、強固
mysql_native_password	旧来 (5.7 以前の既定)、古いクライアント互換
auth_socket	OS 認証 (UNIX socket)
sha256_password	SHA-256 (caching 無し)
authentication_ldap_simple/sasl	LDAP 連携 (Enterprise)

-- 既存ユーザーの認証プラグイン変更 (古いアプリ互換)
ALTER USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY 'NewPass';

-- サーバ既定のプラグインを変更 (my.cnf)
-- [mysqld]
-- default_authentication_plugin = mysql_native_password

ロール (Role) で権限管理 (8.0+)

-- ロール作成
CREATE ROLE 'app_read', 'app_write';

-- ロールに権限を集める
GRANT SELECT ON shopdb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON shopdb.* TO 'app_write';

-- ユーザーにロール付与
GRANT 'app_read', 'app_write' TO 'app'@'%';

-- ログイン時に自動有効化
SET DEFAULT ROLE ALL TO 'app'@'%';

-- 確認
SHOW GRANTS FOR 'app'@'%' USING 'app_read', 'app_write';
SELECT CURRENT_ROLE();

パスワード関連

-- パスワード変更
ALTER USER 'app'@'%' IDENTIFIED BY 'NewStrongPass!';

-- 自分のパスワード変更
SET PASSWORD = 'NewStrongPass!';

-- 有効期限を強制 (90 日)
ALTER USER 'app'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 次回ログイン時に変更強制
ALTER USER 'app'@'%' PASSWORD EXPIRE;

-- アカウントロック / 解除
ALTER USER 'app'@'%' ACCOUNT LOCK;
ALTER USER 'app'@'%' ACCOUNT UNLOCK;

-- パスワードポリシー (validate_password コンポーネント)
SHOW VARIABLES LIKE 'validate_password%';
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = MEDIUM;

ユーザー削除・REVOKE

-- 権限を取り消し
REVOKE INSERT, UPDATE ON shopdb.* FROM 'app'@'%';

-- 全権限剥奪
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';

-- ユーザー削除
DROP USER 'app'@'%';

-- 複数ホストの同名ユーザーをまとめて
DROP USER 'app'@'%', 'app'@'localhost';

リソース制限

-- 接続数・クエリ数・更新数の制限
CREATE USER 'limited'@'%' IDENTIFIED BY 'pass'
  WITH MAX_QUERIES_PER_HOUR 1000
       MAX_UPDATES_PER_HOUR 100
       MAX_CONNECTIONS_PER_HOUR 10
       MAX_USER_CONNECTIONS 5;

-- 後から変更
ALTER USER 'app'@'%' WITH MAX_USER_CONNECTIONS 20;

Laravel での運用

Laravel の .env に書く DB 情報用にユーザーを作る典型例:

CREATE DATABASE laravel_app DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'laravel'@'%' IDENTIFIED BY 'LaravelPass!23';
GRANT ALL PRIVILEGES ON laravel_app.* TO 'laravel'@'%';
FLUSH PRIVILEGES;

-- .env
-- DB_CONNECTION=mysql
-- DB_HOST=127.0.0.1
-- DB_DATABASE=laravel_app
-- DB_USERNAME=laravel
-- DB_PASSWORD=LaravelPass!23

FAQ

Q: Access denied for user が出る
A: ① ホスト指定が一致していない (例: localhost と 127.0.0.1 の違い)、② パスワード不一致、③ 認証プラグイン非対応の古いクライアントを caching_sha2_password ユーザーで使っている、のいずれか。

Q: 本番運用での権限ベストプラクティス
A: アプリ用ユーザーには 必要最小限の権限のみ、DBA 用ユーザーは別、SUPER は使わず細粒度権限 (BINLOG_ADMIN 等) を必要分だけ付ける。

Q: 接続元 IP が動的で固定できない
A: VPN や踏み台サーバ経由にして固定 IP を確保、または SSH トンネル + localhost 限定ユーザーが安全。