MySQL のユーザー管理と権限制御
アクセス制御はデータベースセキュリティの基盤であり、各ユーザーは、アクセス権限が与えられたデータにのみアクセスできます。
このレッスンでは、ユーザーと権限の管理について解説します。
graph TB
A[MySQL Permission Levels] --> B[Global Permissions<br/>*.*]
A --> C[Database Permissions<br/>mydb.*]
A --> D[Table Permissions<br/>mydb.users]
A --> E[Column Permissions<br/>mydb.users col]
B --> B1[ALL/CREATE/RELOAD...]
C --> C1[SELECT/INSERT/UPDATE/DELETE]
D --> D1[SELECT/ALTER/INDEX...]
E --> E1[SELECT col1, col2]
1. 学習内容
- CREATE USER: ユーザーを作成する
- GRANT 権限付与
- REVOKE 権限を取り消す
- 権限レベル（グローバル／データベース／テーブル／カラム）
- ロールの管理（8.0以降）
2. 実話
(1) 課題：rootアカウントの誤操作が重大な災害を招く
開発チームはこれまで、rootアカウントを使って本番データベースに接続し続けていました。それは便利でしたが、ある日、ある開発者が誤ってDROP TABLE usersを実行してしまい、300万人のユーザーデータが一瞬にして消えてしまいました。バックアップからの復旧には4時間かかり、その間サービスは完全に停止したため、100万以上の直接的な損失が発生しました。
(2) 権限システムの解決策
最小権限の原則を適用する：各ロールには、必要な権限のみを付与する。開発者には読み取り専用アクセス権に加え、開発用リポジトリへの読み書きアクセス権が付与される。運用担当者は特定のリポジトリに対する管理権限を持つ。また、rootアクセスは、バスティオンホストを使用するDBAに限定される。
| ディメンション | 常にルート権限を使用 | 最小権限の原則 |
|---|---|---|
| 誤操作のリスク | 極めて高い（どのテーブルでもDROPが可能） | 極めて低い（権限が制限されている） |
| 災害復旧 | 困難（広範囲にわたる影響） | 容易（影響が限定的） |
| 監査証跡 | 操作者を特定できない | ユーザー別トレース |
| セキュリティレベル | ❌ | ✅ |
3. ユーザー管理
▶ 例：ユーザーの作成
SQL
-- Create a Local User
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'DevPass123!';
-- Create a user who is allowed to connect remotely
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'RemotePass123!';
-- Create a user allowed to connect from a specific IP
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPass123!';
▶ 例：ユーザーの変更と削除
SQL
-- Change Password
ALTER USER 'dev_user'@'localhost' IDENTIFIED BY 'NewPass123!';
-- Delete User
DROP USER 'dev_user'@'localhost';
-- View All Users
SELECT user, host FROM mysql.user;
4. GRANT 権限付与
▶ 例：権限管理
SQL
-- Grant All Privileges (Administrator)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
-- Grant Permissions to a Specific Database
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'dev_user'@'localhost';
-- Grant Permissions on a Specific Table
GRANT SELECT ON mydb.users TO 'readonly'@'localhost';
-- Grant Permissions on Specific Columns
GRANT SELECT (username, email) ON mydb.users TO 'limited'@'localhost';
-- Refresh Permissions
FLUSH PRIVILEGES;
5. 権限レベル
| レベル | 構文 | 説明 |
|---|---|---|
| グローバル | *.* |
すべてのデータベース内のすべてのテーブル |
| データベース | mydb.* |
特定のデータベース内のすべてのテーブル |
| テーブル | mydb.users |
特定のテーブル |
| コラム | mydb.users(email) |
特定のコラム |
6. REVOKE：権限の取り消し
SQL
-- Revoke All Permissions
REVOKE ALL PRIVILEGES ON *.* FROM 'dev_user'@'localhost';
-- Revoke Specific Permissions
REVOKE INSERT, DELETE ON mydb.* FROM 'dev_user'@'localhost';
FLUSH PRIVILEGES;
7. 閲覧権限
SQL
-- View Current User Permissions
SHOW GRANTS;
-- View a Specific User's Permissions
SHOW GRANTS FOR 'dev_user'@'localhost';
8. ユーザー管理 (MySQL 8.0 以降)
SQL
-- Create a role
CREATE ROLE 'app_read', 'app_write';
-- Grant privileges to roles
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
-- Assign Roles to Users
GRANT 'app_read', 'app_write' TO 'dev_user'@'localhost';
-- Activate role
SET DEFAULT ROLE ALL TO 'dev_user'@'localhost';
❓ よくある質問
Q
'user'@'localhost' と 'user'@'%' の違いは何ですか？A 「localhost」はローカルからの接続のみを許可しますが、「%」はどのホストからの接続も許可します。
Q
GRANT の後に FLUSH PRIVILEGES ステートメントは必要ですか？A
GRANT を直接使用する場合、必要ありません。mysql.user テーブルを直接変更する場合にのみ必要です。Q ルートパスワードを忘れてしまった場合はどうすればよいですか？
A 権限確認を bypass してシステムを起動し、パスワードを変更してから再起動してください。
Q 「WITH GRANT OPTION」とは何ですか？
A ユーザーが自身の権限を他者に付与できるようにするものです。権限の無秩序な拡大を防ぐため、この権限の付与には注意が必要です。
Q ロールとユーザーの違いは何ですか？
A ロールは権限の集合であり、それ自体では直接ログインすることはできません。ユーザーがログインした後、SET ROLE コマンドを使用してロールを有効にします。
📖 まとめ
- CREATE USER ホストとパスワードを指定してユーザーを作成します
- GRANT は権限を付与し、REVOKE は権限を取り消します
- 権限レベル：グローバル → データベース → テーブル → 列
- 役割 (8.0+) バッチ管理の権限
- FLUSH PRIVILEGES は権限を更新します
📝 練習問題
-
基本問題（難易度：⭐）：
mydbデータベースのクエリのみを実行できる読み取り専用ユーザーを作成してください。 -
上級演習（難易度：⭐⭐）：ロールを作成し、それをユーザーに割り当ててください。
-
チャレンジ問題（難易度：⭐⭐⭐）：開発者は読み取りおよび書き込みアクセス権を持ち、運用担当者は読み取り専用アクセス権を持ち、管理者はフルアクセス権を持つような権限スキームを設計してください。
