はじめに
SQL Serverの権限管理は、シンプルに見えて実務では非常に奥が深い領域です。
- 「権限を付けたのに動かない」
- 「誰が何できるのか分からない」
- 「本番で権限事故が起きた」
こういったトラブルは、権限の仕組みを正しく理解していないことが原因であることが多いです。
この記事では、SQL Serverの権限の基本から実務での考え方までを整理します。
権限の全体像
用語整理
| 用語 | 説明 |
|---|---|
| ログイン | SQL Serverに接続するためのアカウント |
| ユーザー | データベース内のアカウント |
| ロール | 権限の集合 |
| 権限 | SELECT / INSERT などの操作権限 |
SQL Serverの権限は、次の3層構造で管理されます。
① ログイン（サーバー）
↓ 認証
② ユーザー（データベース）
↓ 紐付け
③ ロール / 権限（操作の許可）
それぞれの役割
① ログイン（Login）
SQL Serverに入るための入口（サーバーレベル）
- 接続できるかどうかを決める
- Windows認証 or SQL Server認証
CREATE LOGIN [DOMAIN\DB_Users] FROM WINDOWS;
② ユーザー（User）
どのデータベースに入れるかを決める
ログインだけではデータベースにはアクセスできません。
必ず「ユーザー」として紐付ける必要があります。
CREATE USER [DOMAIN\DB_Users] FOR LOGIN [DOMAIN\DB_Users];
③ ロール / 権限
何ができるかを決める
- SELECTできるか
- UPDATEできるか
ALTER ROLE db_datareader ADD MEMBER [DOMAIN\DB_Users];
GRANT SELECT ON dbo.Employees TO [DOMAIN\DB_Users];
認証と認可
認証（Authentication）
「あなたは誰か？」を確認する
- Windows認証
- SQL Server認証
認可（Authorization）
「何ができるか？」を決める
- SELECTできるか
- UPDATEできるか
Windows認証とグループ運用
ユーザーではなくグループに権限を付けるのが基本
メリット
- 運用が楽（ADで管理）
- 権限の一元化
- 人の入れ替えに強い
個人付与の用途
- 一時的な対応
- 特権ユーザー
- 例外的な制御
権限の付与方法
GRANT（許可）
できるようになる
GRANT SELECT ON dbo.Employees TO [UserA];
DENY（拒否）
絶対にできなくなる
DENY SELECT ON dbo.Employees TO [UserA];
REVOKE（取り消し）
GRANT / DENY を削除
REVOKE SELECT ON dbo.Employees TO [UserA];
REVOKEの挙動
REVOKEは少し誤解されやすいですが、次のような動きをします。
REVOKEは「GRANTまたはDENYを削除する」
ケース：GRANTとDENYが両方ある場合
GRANT SELECT ON dbo.Employees TO [UserA];
DENY SELECT ON dbo.Employees TO [UserA];
この状態で
REVOKE SELECT ON dbo.Employees TO [UserA];
を実行するとGRANT、DENYが共に削除されます。
その結果、SELECT権限は「未設定」の状態になります。
つまり、
- 許可もされていない
- 拒否もされていない
- 他のロールやグループの影響を受ける状態
の状態になります。
権限の優先順位
DENY ＞ GRANT（すべて合算）
ケース①：合算される
- グループ：GRANT SELECT
- 個人：GRANT UPDATE
SELECT できる
UPDATE できる
ケース②：DENYがある
- グループ：GRANT SELECT
- 個人：DENY SELECT
SELECT できない
DENYの使い道
特徴
- GRANTを無効化する
- 最優先で適用される
使いどころ
- 特定テーブルだけ禁止
- セキュリティ上の制約
基本は使わない（トラブルの元）
ロール（Role）
よく使うロール
| ロール | 内容 |
|---|---|
| db_datareader | 全テーブルSELECT |
| db_datawriter | INSERT / UPDATE / DELETE |
| db_owner | ほぼ全権限 |
付与方法
ALTER ROLE db_datareader ADD MEMBER [UserA];
ログと監査
ログに残るもの
- 実際のユーザー（個人）
個人が特定できるため、誰が実行したかは追跡可能
よくあるトラブル
- 権限あるのに実行できない
⇒ DENYの可能性 - 想定外に操作できる
⇒ グループ経由で権限が付与されている - 権限がカオス
⇒ 個人付与が増えすぎ
実務での設計指針
- グループベースで設計
⇒ 人ではなく役割で管理 - 最小権限の原則
⇒ 必要最低限のみ付与 - DENYは使わない
⇒ どうしても必要な場合のみ例外的に使う - 可視化する
⇒ 誰が何できるか確認できる状態にする
おわりに
- 権限は「ログイン → ユーザー → ロール / 権限」で構成される
- 権限は合算される
- 基本はグループ管理
SQL Serverの権限は、「とりあえず付ける」ではなく「設計するもの」 です。
このあたりを意識できると、インフラ・DBエンジニアとして一段レベルが上がります。