解決 SQL Server 權限衝突三招：拒絕、撤銷、授權實戰詳解

SQL Server 採用角色基礎權限模型，使用者透過角色繼承權限。但當使用者屬於多個角色、或個別權限與角色權限不一致時，會產生權限衝突。本文透過具體情境，詳細解析 DENY (拒絕)、REVOKE (撤銷)、GRANT (授權) 三大操作的優先序與實戰用法。

權限評估邏輯核心原則

1. DENY 最高優先：任何層級 (使用者/角色/群組) 的 DENY 皆覆蓋 GRANT
2. GRANT 次之：明確授予權限
3. REVOKE 移除指定層級的 GRANT/DENY，但不阻擋從更高層級繼承的權限
4. 最終有效權限 = 所有層級 GRANT 聯集 - 所有層級 DENY 聯集

情境設定

• 角色 DE_SALES (銷售部門) 擁有 CUSTOM 表 SELECT 權限
• 使用者 SALE1 屬於 DE_SALES，但為試用期員工，不應 查詢 CUSTOM

招式一：DENY (明確拒絕) — 最高優先權

適用場景

使用者 Landy 屬於角色 SALES (擁有 Customs 表 SELECT)，因故需禁止 Landy 存取 Customs。

操作

DENY SELECT ON OBJECT::Customs TO Landy;

效果

• 即使角色 SALES 有 SELECT，Landy 仍無法存取
• DENY 適用於「個別例外限制」，靈活且即時生效

極端案例警示

若對 public 角色執行 DENY SELECT ON Customs TO public，所有使用者 將無法存取該表 (含 sysadmin)。DENY 應僅用於使用者層級，避免角色/群組層級造成廣泛封鎖。

招式二：REVOKE (撤銷) — 移除指定層級授予/拒絕

適用場景

承上例，Landy 試用期結束，需恢復正常權限 (繼承角色 SALES 的 SELECT)。

操作

REVOKE SELECT ON OBJECT::Customs TO Landy;

效果

• 移除 Landy 層級的 DENY
• 不影響角色 SALES 的 GRANT，故 Landy 恢復存取

關鍵差異：REVOKE vs DENY

實務組合：試用期員工管理

1. 入職：使用者層級 DENY 敏感表
2. 轉正：使用者層級 REVOKE 該 DENY，自動繼承角色權限
3. 離職：REVOKE 所有權限或停用登入

此模式避免建立額外角色，權限管理更彈性。

陷阱：多重角色 DENY 衝突

若 Landy 同時屬於 SALES (GRANT) 與 T_SALES (DENY Customs)：

• 使用者層級 REVOKE 僅移除使用者層級 DENY
• 角色 T_SALES 的 DENY 仍生效 → Landy 仍無法存取
• 解決：必須在 T_SALES 角色層級 REVOKE 或移除使用者該角色

招式三：GRANT (授權) — 明確賦予，覆蓋 REVOKE

適用場景

同試用期結束場景，改用 GRANT 而非 REVOKE。

操作

GRANT SELECT ON OBJECT::Customs TO Landy;

效果

• 移除使用者層級的 DENY/REVOKE
• 明確授予 SELECT，即使角色有 DENY，**使用者層級 GRANT 仍無法覆蓋角色層級 DENY** (DENY 最高優先)

GRANT vs REVOKE 差異

• GRANT：檢查權限時直接看見明確授予，審計較直觀
• REVOKE：需檢查上層角色權限才知最終結果

最佳實務建議

1. DENY 僅用於使用者層級，嚴禁用於角色/群組/public
2. 角色/群組僅授予 GRANT，建立基準權限基線
3. 例外處理用使用者層級 DENY/REVOKE/GRANT
4. 定期稽核：SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 或第三方工具
5. 文件化權限矩陣：角色定義、例外清單、變更流程

結論

理解 DENY > GRANT > REVOKE (繼承) 的優先序，善用「角色定基線、使用者做例外」模式，即可靈活解決權限衝突，同時保持權限模型清晰可控。