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

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

一、前言

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

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

訪問限制邏輯位于數(shù)據(jù)庫層中，而不是遠(yuǎn)離另一個應(yīng)用程序?qū)又械臄?shù)據(jù)。每次嘗試從任何層訪問數(shù)據(jù)時，數(shù)據(jù)庫系統(tǒng)都會應(yīng)用訪問限制。這通過減少安全系統(tǒng)的表面積，使您的安全系統(tǒng)更加可靠和強(qiáng)大。

通過使用創(chuàng)建安全策略 Transact-SQL 語句和作為內(nèi)聯(lián)表值函數(shù)創(chuàng)建的謂詞實現(xiàn) RLS。

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

二、描述

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

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

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

對表中行級數(shù)據(jù)的訪問受定義為內(nèi)聯(lián)表值函數(shù)的安全謂詞的限制。然后，安全策略調(diào)用和強(qiáng)制執(zhí)行該函數(shù)。對于篩選器謂詞，應(yīng)用程序不知道從結(jié)果集中篩選的行。如果篩選了所有行，則將返回空集。對于塊謂詞，任何違反謂詞的操作都將失敗并顯示錯誤。

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

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

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

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

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

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

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

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

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

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

• 嘗試修改函數(shù)（用作架構(gòu)綁定安全策略中的表的謂詞）將導(dǎo)致錯誤。

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

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

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

塊謂詞具有以下行為：

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

• 如果謂詞函數(shù)使用的列未更改，優(yōu)化程序?qū)⒉粫z查 AFTER UPDATE 塊謂詞。

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

三、權(quán)限

創(chuàng)建、更改或刪除安全策略需要“更改任何安全策略”權(quán)限。創(chuàng)建或刪除安全策略需要對架構(gòu)具有 ALTER 權(quán)限。

此外，添加的每個謂詞都需要以下權(quán)限：

• 對用作謂詞的函數(shù)的 SELECT 和 REFERENCE 權(quán)限。

• 對綁定到策略的目標(biāo)表的 REFERENCES 權(quán)限。

• 對用作參數(shù)的目標(biāo)表中的每一列的 REFERENCES 權(quán)限。

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

如果使用 創(chuàng)建安全策略，則要查詢目標(biāo)表，用戶必須對謂詞函數(shù)以及謂詞函數(shù)中使用的任何其他表、視圖或函數(shù)具有 SELECT 或 EXECUTE 權(quán)限。如果使用 （默認(rèn)值） 創(chuàng)建安全策略，則當(dāng)用戶查詢目標(biāo)表時會繞過這些權(quán)限檢查。

四、安全說明：側(cè)信道攻擊

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

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

（2）精心設(shè)計的查詢。

通過使用利用錯誤的精心設(shè)計的查詢，可能會導(dǎo)致信息泄露。

五、跨功能兼容性

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

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

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

• 內(nèi)存優(yōu)化表：必須使用該選項定義用作內(nèi)存優(yōu)化表的安全謂詞的內(nèi)聯(lián)表值函數(shù)。使用此選項，將禁止內(nèi)存優(yōu)化表不支持的語言功能，并在創(chuàng)建時發(fā)出相應(yīng)的錯誤。

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

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

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

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

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

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

• 時態(tài)表：時態(tài)表與 RLS 兼容。但是，當(dāng)前表上的安全謂詞不會自動復(fù)制到歷史記錄表中。要將安全策略應(yīng)用于當(dāng)前表和歷史記錄表，必須在每個表上單獨(dú)添加安全謂詞。

六、示例

向數(shù)據(jù)庫進(jìn)行身份驗證的用戶的方案。創(chuàng)建三個用戶，并創(chuàng)建并填充一個包含六行的表。然后，它為表創(chuàng)建一個內(nèi)聯(lián)表值函數(shù)和安全策略。然后，該示例演示如何為各種用戶篩選 select 語句。

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

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

（2）創(chuàng)建一個表來保存數(shù)據(jù)。

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

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

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）向每個用戶授予對表的讀取訪問權(quán)限。

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

（5）創(chuàng)建新架構(gòu)和內(nèi)聯(lián)表值函數(shù)。當(dāng)列中的行與執(zhí)行查詢的用戶 相同或執(zhí)行查詢的用戶是經(jīng)理用戶時，該函數(shù)返回。此用戶定義的表值函數(shù)示例可用于用作下一步中創(chuàng)建的安全策略的篩選器。

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）創(chuàng)建將函數(shù)添加為篩選器謂詞的安全策略。必須將狀態(tài)設(shè)置為ON才能啟用策略。

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

（7）允許對函數(shù)的 SELECT 權(quán)限。

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 數(shù)據(jù)庫清理練習(xí)資源。

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;

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