SQL Server 2016：行級安全

SQL Server 2016：行級安全

對於SQL Server，一個常見的批評是，其安全模型只能識別表和列。使用者如果希望以行為單位應用安全規則，就需要使用預存程序或資料表值函式來類比，然後找一種方法，確保它們不會被繞開。在SQL Server 2016中，那不再是個問題。

實現

SQL Server 2016（及SQL Azure）中的 行級安全 基於一個專門設計的內聯資料表值函式。該函數要麼返回一個只包含值1的行，要麼不返回結果，這取決於使用者訪問的行是否是相關行。請看下面的函數：

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

這段代碼的意思是，目前使用者必須是一名經理，或者是一名與記錄相關的推銷員。該函數沒有訪問行本身，但使用者可以使用參數傳入相應的列（比如，SalesRep）。例如：

CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) ON dbo.Sales WITH (STATE = ON);

實際效果

在使用行級安全時，使用者無法看到他們不能訪問的行。這就好像在訪問表時自動增加一個額外的、安全相關的where子句。

由於其作用像一個where子句，所以有一些局限。例如，如果使用者在那個列上使用了全文檢索搜尋索引，那麼資料就可能泄露。此外，資料庫還可能遭受旁路攻擊。微軟寫道：

通過使用精心設計的查詢，可以導致資訊泄露。例如，SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' 會讓一個惡意使用者知道John Doe的工資是10萬美元。即使有一個恰當的安全謂詞阻止惡意使用者直接查詢其它人的工資，他也可以在查詢返回“除數為0”的異常時確定工資數額。

此外，資訊也可能通過統計對象泄露。為了降低風險，查看受保護列統計資訊的使用者必須是“表的所有者，或者是伺服器固有角色sysadmin、資料庫固有角色db_owner或db_ddladmin的成員”。

中介層應用程式

截至目前，我們討論的情境是使用者以自己的身份登入。在中介層應用程式中，所有人都共用同一個資料庫賬戶，實現行級安全需要額外的步驟。

對於中介層應用程式，推薦的設計模式是將 CONTEXT_INFO 的值設定為串連開啟時使用者特定於應用程式的使用者id。然後，安全函數就可以引用CONTEXT_INFO的值。例如：

CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('dbo') -- 應用程式上下文 AND CONVERT(int, CONVERT(VARBINARY(4), CONTEXT_INFO())) = @AppUserId; -- AppUserId (int)佔4個位元組 GO CREATE SECURITY POLICY Security.SalesFilter ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId) ON dbo.Sales WITH (STATE = ON);

該方法的前提是，使用者無法執行任意SQL，因為那會讓他們可以隨意更改CONTEXT_INFO。

查看英文原文： SQL Server 2016: Row-Level Security

本文永久更新連結地址：