SQL?Server的行級安全性是什么

這篇文章主要介紹了SQL Server的行級安全性是什么的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇SQL Server的行級安全性是什么文章都會有所收獲，下面我們一起來看看吧。

一、前言

行級別安全性使您能夠使用組成員身份或執行上下文來控制對數據庫表中行的訪問。

行級別安全性 （RLS） 簡化了應用程序中的安全性設計和編碼。RLS 可幫助您對數據行訪問實施限制。例如，您可以確保工作人員僅訪問與其部門相關的數據行。另一個示例是將客戶的數據訪問限制為僅與其公司相關的數據。

訪問限制邏輯位于數據庫層中，而不是遠離另一個應用程序層中的數據。每次嘗試從任何層訪問數據時，數據庫系統都會應用訪問限制。這通過減少安全系統的表面積，使您的安全系統更加可靠和強大。

通過使用創建安全策略 Transact-SQL 語句和作為內聯表值函數創建的謂詞實現 RLS。

行級別安全性首次引入 SQL Server 2016 （13.x）。

二、描述

RLS 支持兩種類型的安全謂詞。

• 篩選器謂詞以靜默方式篩選可用于讀取操作（選擇、更新和刪除）的行。

• 阻止謂詞顯式阻止違反謂詞的寫入操作（插入后、更新后、更新之前、刪除之前）。

對表中行級數據的訪問受定義為內聯表值函數的安全謂詞的限制。然后，安全策略調用和強制執行該函數。對于篩選器謂詞，應用程序不知道從結果集中篩選的行。如果篩選了所有行，則將返回空集。對于塊謂詞，任何違反謂詞的操作都將失敗并顯示錯誤。

從基表中讀取數據時應用篩選器謂詞。它們影響所有獲取操作：選擇、刪除和更新。用戶無法選擇或刪除已篩選的行。用戶無法更新篩選的行。但是，可以更新行，以便以后對其進行篩選。塊謂詞會影響所有寫入操作。

• “插入后”和“更新后”謂詞可以防止用戶將行更新為違反謂詞的值。

• BEFORE UPDATE 謂詞可以阻止用戶更新當前違反謂詞的行。

• 在刪除之前 謂詞可以阻止刪除操作。

篩選器和阻止謂詞以及安全策略都具有以下行為：

• 可以定義一個謂詞函數，該函數與另一個表聯接和/或調用函數。如果使用 （默認值） 創建安全策略，則可以從查詢訪問聯接或函數，并按預期工作，而無需任何其他權限檢查。如果使用 創建安全策略，則用戶將需要對這些附加表和函數的 SELECT 權限才能查詢目標表。如果謂詞函數調用 CLR 標量值函數，則還需要 EXECUTE 權限。

• 可以針對已定義但已禁用安全謂詞的表發出查詢。篩選或阻止的任何行不受影響。

• 如果 dbo 用戶、db_owner角色的成員或表所有者查詢已定義并啟用了安全策略的表，則會按照安全策略的定義過濾或阻止行。

• 嘗試更改由架構綁定安全策略綁定的表的架構將導致錯誤。但是，可以更改謂詞未引用的列。

• 嘗試在已為指定操作定義謂詞的表上添加謂詞會導致錯誤。無論是否啟用謂詞，都會發生這種情況。

• 嘗試修改函數（用作架構綁定安全策略中的表的謂詞）將導致錯誤。

• 定義多個包含非重疊謂詞的活動安全策略會成功。

篩選器謂詞具有以下行為：

• 定義用于篩選表中行的安全策略。應用程序不知道針對 SELECT、UPDATE 和 DELETE 操作篩選的任何行。包括篩選掉所有行的情況。應用程序可以插入行，即使它們將在任何其他操作期間被篩選。

塊謂詞具有以下行為：

• UPDATE 的塊謂詞被拆分為 BEFORE 和 AFTER 的單獨操作。例如，不能阻止用戶將行更新為具有高于當前值的值。如果需要這種邏輯，則必須將觸發器與 DELETE 和 INSERT 中間表一起使用，以同時引用舊值和新值。

• 如果謂詞函數使用的列未更改，優化程序將不會檢查 AFTER UPDATE 塊謂詞。

• 尚未對批量 API 進行任何更改，包括批量插入。這意味著塊謂詞 AFTER INSERT 將應用于批量插入操作，就像它們將常規插入操作一樣。

三、權限

創建、更改或刪除安全策略需要“更改任何安全策略”權限。創建或刪除安全策略需要對架構具有 ALTER 權限。

此外，添加的每個謂詞都需要以下權限：

• 對用作謂詞的函數的 SELECT 和 REFERENCE 權限。

• 對綁定到策略的目標表的 REFERENCES 權限。

• 對用作參數的目標表中的每一列的 REFERENCES 權限。

安全策略適用于所有用戶，包括數據庫中的 dbo 用戶。Dbo 用戶可以更改或刪除安全策略，但可以審核他們對安全策略的更改。如果高特權用戶（如 sysadmin 或 db_owner）需要查看所有行以排除故障或驗證數據，則必須編寫安全策略以允許這樣做。

如果使用 創建安全策略，則要查詢目標表，用戶必須對謂詞函數以及謂詞函數中使用的任何其他表、視圖或函數具有 SELECT 或 EXECUTE 權限。如果使用 （默認值） 創建安全策略，則當用戶查詢目標表時會繞過這些權限檢查。

四、安全說明：側信道攻擊

（1）惡意安全策略管理器。

請務必注意，惡意安全策略管理器具有在敏感列上創建安全策略的足夠權限，并有權創建或更改內聯表值函數，但可以與對表具有選擇權限的其他用戶串通，通過惡意創建旨在使用側通道攻擊推斷數據的內聯表值函數來執行數據泄露。此類攻擊需要串通（或授予惡意用戶的過多權限），并且可能需要多次迭代修改策略（需要刪除謂詞的權限以破壞架構綁定）、修改內聯表值函數以及在目標表上重復運行 select 語句。我們建議您根據需要限制權限，并監視任何可疑活動。應監視活動，例如不斷更改的策略和與行級別安全性相關的內聯表值函數。

（2）精心設計的查詢。

通過使用利用錯誤的精心設計的查詢，可能會導致信息泄露。

五、跨功能兼容性

• 通常，行級別安全性將跨功能按預期工作。但是，也有一些例外。本節記錄了將行級別安全性與 SQL Server 的某些其他功能結合使用的幾個注意事項和注意事項。

• DBCC SHOW_STATISTICS報告未過濾數據的統計信息，并可能泄露受安全策略保護的信息。因此，對查看具有行級別安全策略的表的統計信息對象的訪問受到限制。用戶必須擁有該表，或者用戶必須是 sysadmin 固定服務器角色、db_owner固定數據庫角色或db_ddladmin固定數據庫角色的成員。

• 文件流：RLS 與文件流不兼容。

• 內存優化表：必須使用該選項定義用作內存優化表的安全謂詞的內聯表值函數。使用此選項，將禁止內存優化表不支持的語言功能，并在創建時發出相應的錯誤。

• 索引視圖：通常，可以在視圖之上創建安全策略，也可以在受安全策略約束的表之上創建視圖。但是，不能在具有安全策略的表之上創建索引視圖，因為通過索引查找行將繞過該策略。

• 變更數據捕獲：變更數據捕獲可能會泄漏應篩選為db_owner成員或為表啟用 CDC 時指定的“控制”角色成員的用戶（注意：您可以將此函數顯式設置為 NULL，以使所有用戶都能訪問變更數據）。實際上，db_owner和此控制角色的成員可以查看表上的所有數據更改，即使表上有安全策略也是如此。

• 更改跟蹤：更改跟蹤可能會將應篩選的行的主鍵泄露給同時具有“選擇”和“查看更改跟蹤”權限的用戶。實際數據值不會泄露;只有 A 列被更新/插入/刪除了帶有 B 主鍵的行的事實。如果主密鑰包含機密元素（如社會保險號），則會出現問題。然而，在實踐中，這個CHANGETABLE幾乎總是與原始表連接，以獲得最新的數據。

• 全文搜索：使用以下全文搜索和語義搜索函數的查詢預計會降低性能，因為引入了額外的聯接來應用行級安全性并避免泄漏應過濾的行的主鍵：CONTAINSTABLE、FREETEXTTABLE、semantickeyphrasetable、semanticsimilaritydetailstable、semanticsimilaritytable、semanticsimilaritytable。

• 列存儲索引：RLS 與聚集列存儲索引和非聚集列存儲索引兼容。但是，由于行級別安全性應用函數，因此優化程序可能會修改查詢計劃，使其不使用批處理模式。

• 分區視圖：不能在分區視圖上定義塊謂詞，也不能在使用塊謂詞的表上創建分區視圖。篩選器謂詞與分區視圖兼容。

• 時態表：時態表與 RLS 兼容。但是，當前表上的安全謂詞不會自動復制到歷史記錄表中。要將安全策略應用于當前表和歷史記錄表，必須在每個表上單獨添加安全謂詞。

六、示例

向數據庫進行身份驗證的用戶的方案。創建三個用戶，并創建并填充一個包含六行的表。然后，它為表創建一個內聯表值函數和安全策略。然后，該示例演示如何為各種用戶篩選 select 語句。

（1）創建三個將演示不同訪問功能的用戶帳戶。

CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GO

（2）創建一個表來保存數據。

CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
    (
    OrderID int,
    SalesRep nvarchar(50),
    Product nvarchar(50),
    Quantity smallint
    );

（3）用六行數據填充表，顯示每個銷售代表的三個訂單。

INSERT INTO Sales.Orders  VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders  VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders  VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders  VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders  VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders  VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales.Orders;

（4）向每個用戶授予對表的讀取訪問權限。

GRANT SELECT ON Sales.Orders TO Manager;
GRANT SELECT ON Sales.Orders TO SalesRep1;
GRANT SELECT ON Sales.Orders TO SalesRep2;
GO

（5）創建新架構和內聯表值函數。當列中的行與執行查詢的用戶 相同或執行查詢的用戶是經理用戶時，該函數返回。此用戶定義的表值函數示例可用于用作下一步中創建的安全策略的篩選器。

CREATE SCHEMA Security;
GO
  
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

（6）創建將函數添加為篩選器謂詞的安全策略。必須將狀態設置為ON才能啟用策略。

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);
GO

（7）允許對函數的 SELECT 權限。

GRANT SELECT ON Security.tvf_securitypredicate TO Manager;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2;

（8）更改安全策略以禁用該策略。

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

（9）連接到 SQL 數據庫清理練習資源。

DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;

關于“SQL Server的行級安全性是什么”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“SQL Server的行級安全性是什么”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。