Day27 後半のゴール
「“ユーザー・ロール・権限”をテーブルとコードに落とせるようになる」
前半で、「認証と認可の違い」「ユーザー・ロール・権限の三層モデル」「最小権限の原則」はイメージできました。
後半では、それを実際のテーブル設計と、アプリ側のチェックの形にまで落としていきます。
ここでのゴールはこうです。
ユーザー・ロール・権限を表すテーブル構造を、自分で説明できる。
「このAPIはこの権限が必要」という形で、コード側のチェックをイメージできる。
MySQLのGRANT例を見て、「アプリ用DBユーザーの権限を絞る」感覚を持てる。
アプリ側の権限テーブル設計
「users / roles / permissions を“つなぐ”テーブルまで含めて考える」
まずは、アプリ側の権限モデルをDBに落とした形を、しっかりイメージします。
users テーブル（アプリのユーザー）
これはすでに何度も出てきた、アプリケーションのユーザーです。
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL
) ENGINE=InnoDB;
ここまではおなじみです。
roles テーブル（役割）
ユーザーに付ける「ラベル」です。
CREATE TABLE roles (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE, -- 'admin', 'member', 'operator' など
description VARCHAR(255) NULL
) ENGINE=InnoDB;
name はアプリ内で使う識別子です。
description は「管理者」「一般ユーザー」など、人間向けの説明を入れます。
permissions テーブル（権限）
「何をしていいか」を表す“操作名”を定義します。
CREATE TABLE permissions (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE, -- 'view_users', 'edit_users', 'view_orders' など
description VARCHAR(255) NULL
) ENGINE=InnoDB;
ここでの name は、コード側でも使う“権限キー”になります。
例えば、「ユーザー一覧APIを呼ぶには view_users 権限が必要」といった形で使います。
role_permissions テーブル（ロールがどの権限を持つか）
ロールと権限は多対多なので、中間テーブルでつなぎます。
CREATE TABLE role_permissions (
role_id BIGINT NOT NULL,
permission_id BIGINT NOT NULL,
PRIMARY KEY (role_id, permission_id),
CONSTRAINT fk_role_permissions_role
FOREIGN KEY (role_id) REFERENCES roles(id),
CONSTRAINT fk_role_permissions_permission
FOREIGN KEY (permission_id) REFERENCES permissions(id)
) ENGINE=InnoDB;
ここに、「admin は view_users と edit_users を持つ」「member は view_users だけ持つ」といった組み合わせを入れていきます。
user_roles テーブル（ユーザーがどのロールを持つか）
ユーザーとロールも多対多なので、同じく中間テーブルです。
CREATE TABLE user_roles (
user_id BIGINT NOT NULL,
role_id BIGINT NOT NULL,
PRIMARY KEY (user_id, role_id),
CONSTRAINT fk_user_roles_user
FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT fk_user_roles_role
FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;
これで、「ユーザーID=1 は admin ロール」「ユーザーID=2 は member ロール」といった割り当てを表現できます。
権限チェックをどうやって行うか
「“ログインユーザー → ロール → 権限”をたどる」
テーブルができたら、次は「実際にどうチェックするか」です。
流れを日本語で整理すると、こうなります。
ログイン済みのユーザーIDがある（例：user_id=1）。
user_roles から、そのユーザーが持つロールID一覧を取る。
role_permissions から、そのロールたちが持つ permission_id 一覧を取る。
permissions から、その permission_id に対応する name 一覧を取る。
「このAPIには ‘edit_users’ 権限が必要」と決めておき、その name が一覧に含まれているかをチェックする。
これを、Node.js 風の疑似コードでイメージしてみます。
async function getUserPermissions(userId, db) {
const [rows] = await db.execute(
`
SELECT p.name AS permission_name
FROM user_roles ur
JOIN roles r ON ur.role_id = r.id
JOIN role_permissions rp ON r.id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE ur.user_id = ?
`,
[userId]
);
return rows.map(row => row.permission_name); // ['view_users', 'edit_users', ...]
}
async function requirePermission(userId, requiredPermission, db) {
const permissions = await getUserPermissions(userId, db);
if (!permissions.includes(requiredPermission)) {
throw new Error('Forbidden');
}
}
APIのハンドラ側では、例えばこう使います。
// ユーザー一覧API（edit_users 権限が必要）
app.get('/api/admin/users', async (req, res) => {
try {
const userId = req.user.id; // 認証済みユーザーIDが入っている想定
await requirePermission(userId, 'view_users', pool);
const [rows] = await pool.execute('SELECT id, name, email FROM users');
res.json(rows);
} catch (err) {
if (err.message === 'Forbidden') {
return res.status(403).json({ message: 'Forbidden' });
}
console.error(err);
res.status(500).json({ message: 'Internal server error' });
}
});
ここでの重要ポイントは、「権限名（’view_users’ など）をコードとDBで共有する」ということです。
permissions.name を“権限キー”として扱い、APIごとに「このキーが必要」と決めておくと、設計がスッキリします。
ロール設計の例を具体的に考える
「“admin だけ全部OK”にしないで、役割ごとに切る」
ロールをどう分けるかは、アプリの性質によりますが、ECサイトを例にするとイメージしやすいです。
例えば、こんなロールを考えます。
member（一般会員）
operator（受注オペレーター）
admin（システム管理者）
それぞれに、どんな権限を付けるかを考えます。
member
→ view_products, create_orders, view_own_orders, edit_own_profile
operator
→ member の権限
→ view_orders, update_order_status, view_users
admin
→ operator の権限
→ edit_users, manage_roles, view_audit_logs
こうして、「ロールごとに権限のセットを定義する」ことで、
新しいユーザーを作るときは「どのロールを付けるか」を決めるだけで済みます。
もし「operator にも audit_logs を見せたい」となったら、
role_permissions に「operator × view_audit_logs」を1行追加するだけで、
全operatorに一括で権限が反映されます。
これが、「ユーザーに直接権限をベタ付けしない」メリットです。
MySQL側の権限をどう絞るか
「アプリ用DBユーザーにも“最小権限”を適用する」
次に、MySQL自体の権限の話です。
ここでは、「アプリから接続するDBユーザー」をどう作るかをイメージします。
例えば、ECアプリ用に sample_app というデータベースがあるとします。
そこに対して、アプリから読み書きするユーザー app_rw を作る例です。
CREATE USER 'app_rw'@'%' IDENTIFIED BY 'strong_password_here';
GRANT
SELECT, INSERT, UPDATE, DELETE
ON sample_app.*
TO 'app_rw'@'%';
ここでのポイントは、「必要な操作だけを許可する」ことです。
DROP, ALTER, CREATE などの権限は、アプリ用ユーザーには不要です。
それらは、管理者がメンテナンス用の別ユーザーで行うべきです。
さらに、「読み取り専用APIサーバー」を分ける場合は、
app_readonly のようなユーザーを作り、SELECT だけ許可する、という構成もよくあります。
CREATE USER 'app_readonly'@'%' IDENTIFIED BY 'another_strong_password';
GRANT
SELECT
ON sample_app.*
TO 'app_readonly'@'%';
これにより、もし app_readonly の資格情報が漏れても、
データを読まれるだけで、書き換えや削除はされない、という“被害の上限”を作れます。
アプリ側の権限管理（ユーザー・ロール・権限）と同じく、
DBユーザーにも「最小権限の原則」を適用する、という発想です。
「権限が足りないときの振る舞い」も設計の一部
「403 Forbidden と“なぜダメか”のメッセージ」
権限チェックを入れると、当然「権限が足りない」ケースが出てきます。
ここをどう扱うかも、設計の一部です。
APIであれば、HTTPステータスコード 403 Forbidden を返すのが基本です。
401 Unauthorized は「そもそも認証されていない（ログインしていない）」ときに使います。
「ログインはしているが、その操作をする権限がない」場合は 403 です。
レスポンスボディには、例えばこういったメッセージを入れます。
{
"message": "この操作を行う権限がありません"
}
内部的には、「どの権限が足りなかったか」をログに残しておくと、
運用時に「このユーザーにどのロールを付けるべきか」を判断しやすくなります。
権限管理は、「通すか・拒否するか」だけでなく、
「拒否したときにどう伝えるか」「どう運用で調整するか」まで含めて設計です。
Day27 後半のまとめ
アプリ側の権限管理は、users（人）、roles（役割）、permissions（具体的な操作）と、それらをつなぐ user_roles・role_permissions をテーブルとして用意し、「ユーザーはロールを持ち、ロールは権限を持つ」という三層モデルで、「このAPIには ‘view_users’ 権限が必要」といった形でコード側のチェックに落とし込むことで、ユーザーに直接権限をベタ付けせず、ロール定義を変えるだけで一括で権限を調整できる柔軟な設計にする。
同時に、MySQL側ではアプリ用のDBユーザー（例：app_rw や app_readonly）を作り、SELECT/INSERT/UPDATE/DELETE など必要最小限の権限だけを GRANT して「DROP/ALTER などは持たせない」という“最小権限の原則”をDBレイヤーにも適用し、アプリでは権限不足時に 403 Forbidden を返しつつ、内部ログには「どの権限が足りなかったか」を残すことで、セキュリティと運用の両面から“誰が何をしていいか”をコントロールできるようにしていく。