SQL Server 列級安全實戰：透過 Table-Valued Function + View 實現資料列存取控制

痛點：SQL Server 無內建 Row-Level Security (RLS)

SQL Server 2016 前無原生 RLS (2016+ 有 `CREATE SECURITY POLICY`)。歷史版本或不想升級時，**TVF + View 模式**是經典、零侵入、高效能的替代方案。

業務場景

訂單系統：經理僅能看自己部門及子部門訂單。資料表：

• `Orders(OrderID, CustomerName, OrderTotal, Department)`
• `Departments(Department, ParentDepartment)` — 階層結構
• `UserAccess(UserID, Department)` — 使用者可存取部門 (含子部門各佔一列)

核心實作：Inline Table-Valued Function (iTVF)

CREATE FUNCTION dbo.GetOrderSummary(@User_ID VARCHAR(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT COUNT(*) AS OrderCount,
           SUM(OrderTotal) AS Receipts
    FROM dbo.Orders o
    JOIN dbo.UserAccess ua ON o.Department = ua.Department
    WHERE ua.UserID = @User_ID
);

關鍵點：

• `WITH SCHEMABINDING`：綁定結構描述，防止底表被意外修改，優化器可內聯展開，**零效能損耗** (不同於多陳述式 TVF)。
• 參數化使用者身分，邏輯集中、單一真相來源。
• 可擴充：加入 `Departments` 遞迴 CTE 處理多層階層授權。

安全暴露介面：View 封裝

CREATE VIEW dbo.OrderSummary
WITH SCHEMABINDING
AS
SELECT OrderCount, Receipts
FROM dbo.GetOrderSummary(USER_NAME()); -- 或 SUSER_SNAME() / 原生應用傳入

• 使用者**僅授予 View SELECT 權限**，**無底表、TVF 權限**。
• 應用層無需修改查詢邏輯，直接 `SELECT * FROM OrderSummary`。
• 支援任何存取路徑：SSMS、報表、ORM、連結伺服器。

進階：階層授權 (部門樹)

-- 遞迴 CTE 取得使用者可存取的所有部門 (含子孫)
WITH UserDepts AS (
    SELECT Department FROM UserAccess WHERE UserID = @User_ID
    UNION ALL
    SELECT d.Department
    FROM Departments d
    JOIN UserDepts ud ON d.ParentDepartment = ud.Department
)
SELECT COUNT(*), SUM(OrderTotal)
FROM Orders o
JOIN UserDepts ud ON o.Department = ud.Department;

企業級擴充：整合 Active Directory 群組

• `UserAccess` 表 `UserID` 欄位存 AD 群組名 (如 `DOMAIN\SalesManagers`)。
• TVF 參數改用 `IS_MEMBER('DOMAIN\SalesManagers')` 或應用層解析群組成員資格後帶入。
• 減少個別使用者維護，符合最小權限原則。

效能與維護優勢

結語

**Inline TVF + View + SCHEMABINDING** 是 SQL Server 實現列級安全的經典模式：邏輯集中於資料庫層、效能零損耗、應用零侵入、權限最小化。即使 2016+ 有原生 RLS，此模式在舊版本、跨版本相容、複雜階層授權場景下依然極具價值。核心口訣：**資料不出庫、邏輯不上層、權限不外洩**。