SQL Server 資料列層級安全性實作指南：以 UserAccess 映射表實現行級存取控制

前言：為何需要資料列層級安全性？

在企業資料庫環境中，資料安全早已非選配項目。法規遵循（如 SOX、HIPAA）、頻傳的資安事件，皆要求我們做到「最小權限原則」：正確的資料只給正確的使用者。傳統的資料表/資料行權限（GRANT/DENY）粒度不夠細，無法限制「同一張表中，不同使用者只能看特定列（Row）」。SQL Server 2016 前並無內建 Row-Level Security (RLS)，必須自行實作。

核心設計：映射表 + 檢視表/預存程序

最通用且不侵入應用層的做法，是建立一張使用者-部門映射表，搭配檢視表或內嵌資料表值函數，在查詢時動態過濾。

1. 建立映射表

CREATE TABLE dbo.UserAccess (
    UserID     varchar(20) NOT NULL,
    Department varchar(50) NOT NULL,
    CONSTRAINT PK_UserAccess PRIMARY KEY (UserID, Department)
);

2. 模擬業務表

CREATE TABLE dbo.Orders (
    OrderID    int NOT NULL PRIMARY KEY,
    DeptName   varchar(50) NOT NULL,
    OrderDate  datetime NOT NULL,
    Amount     money NOT NULL
);

3. 建立安全檢視表

利用 SUSER_SNAME() 或 CONTEXT_INFO 取得目前登入者，自動套用過濾：

CREATE VIEW dbo.vw_SecureOrders
WITH SCHEMABINDING
AS
SELECT o.OrderID, o.DeptName, o.OrderDate, o.Amount
FROM dbo.Orders AS o
JOIN dbo.UserAccess AS ua
  ON ua.Department = o.DeptName
 WHERE ua.UserID = SUSER_SNAME(); -- 或搭配應用層設定的 CONTEXT_INFO

4. 授權策略

• 回收 dbo.Orders 的直接 SELECT 權限。
• 僅授予 dbo.vw_SecureOrders 的 SELECT 權限給應用角色/使用者。
• 所有報表、查詢、應用程式一律存取檢視表，零程式碼修改。

進階：支援階層式部門（遞迴 CTE）

若部門有父子層級，映射表只存「最上層管理部門」，查詢時以遞迴 CTE 展開所有子部門：

WITH DeptTree AS (
    SELECT DepartmentID, DepartmentName FROM dbo.Departments WHERE DepartmentID = @ManagedDept
    UNION ALL
    SELECT d.DepartmentID, d.DepartmentName
    FROM dbo.Departments d JOIN DeptTree dt ON d.ParentID = dt.DepartmentID
)
SELECT o.* FROM dbo.Orders o JOIN DeptTree dt ON o.DeptName = dt.DepartmentName;


優缺點總結

優點
缺點/注意事項
不修改業務表結構，零侵入應用層
檢視表需 WITH SCHEMABINDING 才能建立索引
集中管理權限邏輯，易於稽核
大量資料下 JOIN 映射表可能影響效能，需適當索引
相容所有 SQL Server 版本（2000+）
若使用 CONTEXT_INFO，連線池重用需小心清除


結語
在 SQL Server 2016 之前，透過「映射表 + 安全檢視表」是實現行級安全最成熟、最可維護的方案。升級至 2016+ 後，建議改用內建 CREATE SECURITY POLICY + INLINE TABLE-VALUED FUNCTION，效能與維護性更佳。