SQL Server的行級(jí)安全性詳解
一、前言
行級(jí)別安全性使您能夠使用組成員身份或執(zhí)行上下文來(lái)控制對(duì)數(shù)據(jù)庫(kù)表中行的訪(fǎng)問(wèn)。
行級(jí)別安全性 (RLS) 簡(jiǎn)化了應(yīng)用程序中的安全性設(shè)計(jì)和編碼。RLS 可幫助您對(duì)數(shù)據(jù)行訪(fǎng)問(wèn)實(shí)施限制。例如,您可以確保工作人員僅訪(fǎng)問(wèn)與其部門(mén)相關(guān)的數(shù)據(jù)行。另一個(gè)示例是將客戶(hù)的數(shù)據(jù)訪(fǎng)問(wèn)限制為僅與其公司相關(guān)的數(shù)據(jù)。
訪(fǎng)問(wèn)限制邏輯位于數(shù)據(jù)庫(kù)層中,而不是遠(yuǎn)離另一個(gè)應(yīng)用程序?qū)又械臄?shù)據(jù)。每次嘗試從任何層訪(fǎng)問(wèn)數(shù)據(jù)時(shí),數(shù)據(jù)庫(kù)系統(tǒng)都會(huì)應(yīng)用訪(fǎng)問(wèn)限制。這通過(guò)減少安全系統(tǒng)的表面積,使您的安全系統(tǒng)更加可靠和強(qiáng)大。
通過(guò)使用創(chuàng)建安全策略 Transact-SQL 語(yǔ)句和作為內(nèi)聯(lián)表值函數(shù)創(chuàng)建的謂詞實(shí)現(xiàn) RLS。
行級(jí)別安全性首次引入 SQL Server 2016 (13.x)。
二、描述
RLS 支持兩種類(lèi)型的安全謂詞。
- 篩選器謂詞以靜默方式篩選可用于讀取操作(選擇、更新和刪除)的行。
- 阻止謂詞顯式阻止違反謂詞的寫(xiě)入操作(插入后、更新后、更新之前、刪除之前)。
對(duì)表中行級(jí)數(shù)據(jù)的訪(fǎng)問(wèn)受定義為內(nèi)聯(lián)表值函數(shù)的安全謂詞的限制。然后,安全策略調(diào)用和強(qiáng)制執(zhí)行該函數(shù)。對(duì)于篩選器謂詞,應(yīng)用程序不知道從結(jié)果集中篩選的行。如果篩選了所有行,則將返回空集。對(duì)于塊謂詞,任何違反謂詞的操作都將失敗并顯示錯(cuò)誤。
從基表中讀取數(shù)據(jù)時(shí)應(yīng)用篩選器謂詞。它們影響所有獲取操作:選擇、刪除和更新。用戶(hù)無(wú)法選擇或刪除已篩選的行。用戶(hù)無(wú)法更新篩選的行。但是,可以更新行,以便以后對(duì)其進(jìn)行篩選。塊謂詞會(huì)影響所有寫(xiě)入操作。
- “插入后”和“更新后”謂詞可以防止用戶(hù)將行更新為違反謂詞的值。
- BEFORE UPDATE 謂詞可以阻止用戶(hù)更新當(dāng)前違反謂詞的行。
- 在刪除之前 謂詞可以阻止刪除操作。
篩選器和阻止謂詞以及安全策略都具有以下行為:
- 可以定義一個(gè)謂詞函數(shù),該函數(shù)與另一個(gè)表聯(lián)接和/或調(diào)用函數(shù)。如果使用 (默認(rèn)值) 創(chuàng)建安全策略,則可以從查詢(xún)?cè)L問(wèn)聯(lián)接或函數(shù),并按預(yù)期工作,而無(wú)需任何其他權(quán)限檢查。如果使用 創(chuàng)建安全策略,則用戶(hù)將需要對(duì)這些附加表和函數(shù)的 SELECT 權(quán)限才能查詢(xún)目標(biāo)表。如果謂詞函數(shù)調(diào)用 CLR 標(biāo)量值函數(shù),則還需要 EXECUTE 權(quán)限。
- 可以針對(duì)已定義但已禁用安全謂詞的表發(fā)出查詢(xún)。篩選或阻止的任何行不受影響。
- 如果 dbo 用戶(hù)、db_owner角色的成員或表所有者查詢(xún)已定義并啟用了安全策略的表,則會(huì)按照安全策略的定義過(guò)濾或阻止行。
- 嘗試更改由架構(gòu)綁定安全策略綁定的表的架構(gòu)將導(dǎo)致錯(cuò)誤。但是,可以更改謂詞未引用的列。
- 嘗試在已為指定操作定義謂詞的表上添加謂詞會(huì)導(dǎo)致錯(cuò)誤。無(wú)論是否啟用謂詞,都會(huì)發(fā)生這種情況。
- 嘗試修改函數(shù)(用作架構(gòu)綁定安全策略中的表的謂詞)將導(dǎo)致錯(cuò)誤。
- 定義多個(gè)包含非重疊謂詞的活動(dòng)安全策略會(huì)成功。
篩選器謂詞具有以下行為:
- 定義用于篩選表中行的安全策略。應(yīng)用程序不知道針對(duì) SELECT、UPDATE 和 DELETE 操作篩選的任何行。包括篩選掉所有行的情況。應(yīng)用程序可以插入行,即使它們將在任何其他操作期間被篩選。
塊謂詞具有以下行為:
- UPDATE 的塊謂詞被拆分為 BEFORE 和 AFTER 的單獨(dú)操作。例如,不能阻止用戶(hù)將行更新為具有高于當(dāng)前值的值。如果需要這種邏輯,則必須將觸發(fā)器與 DELETE 和 INSERT 中間表一起使用,以同時(shí)引用舊值和新值。
- 如果謂詞函數(shù)使用的列未更改,優(yōu)化程序?qū)⒉粫?huì)檢查 AFTER UPDATE 塊謂詞。
- 尚未對(duì)批量 API 進(jìn)行任何更改,包括批量插入。這意味著塊謂詞 AFTER INSERT 將應(yīng)用于批量插入操作,就像它們將常規(guī)插入操作一樣。
三、權(quán)限
創(chuàng)建、更改或刪除安全策略需要“更改任何安全策略”權(quán)限。創(chuàng)建或刪除安全策略需要對(duì)架構(gòu)具有 ALTER 權(quán)限。
此外,添加的每個(gè)謂詞都需要以下權(quán)限:
- 對(duì)用作謂詞的函數(shù)的 SELECT 和 REFERENCE 權(quán)限。
- 對(duì)綁定到策略的目標(biāo)表的 REFERENCES 權(quán)限。
- 對(duì)用作參數(shù)的目標(biāo)表中的每一列的 REFERENCES 權(quán)限。
安全策略適用于所有用戶(hù),包括數(shù)據(jù)庫(kù)中的 dbo 用戶(hù)。Dbo 用戶(hù)可以更改或刪除安全策略,但可以審核他們對(duì)安全策略的更改。如果高特權(quán)用戶(hù)(如 sysadmin 或 db_owner)需要查看所有行以排除故障或驗(yàn)證數(shù)據(jù),則必須編寫(xiě)安全策略以允許這樣做。
如果使用 創(chuàng)建安全策略,則要查詢(xún)目標(biāo)表,用戶(hù)必須對(duì)謂詞函數(shù)以及謂詞函數(shù)中使用的任何其他表、視圖或函數(shù)具有 SELECT 或 EXECUTE 權(quán)限。如果使用 (默認(rèn)值) 創(chuàng)建安全策略,則當(dāng)用戶(hù)查詢(xún)目標(biāo)表時(shí)會(huì)繞過(guò)這些權(quán)限檢查。
四、安全說(shuō)明:側(cè)信道攻擊
(1)惡意安全策略管理器。
請(qǐng)務(wù)必注意,惡意安全策略管理器具有在敏感列上創(chuàng)建安全策略的足夠權(quán)限,并有權(quán)創(chuàng)建或更改內(nèi)聯(lián)表值函數(shù),但可以與對(duì)表具有選擇權(quán)限的其他用戶(hù)串通,通過(guò)惡意創(chuàng)建旨在使用側(cè)通道攻擊推斷數(shù)據(jù)的內(nèi)聯(lián)表值函數(shù)來(lái)執(zhí)行數(shù)據(jù)泄露。此類(lèi)攻擊需要串通(或授予惡意用戶(hù)的過(guò)多權(quán)限),并且可能需要多次迭代修改策略(需要?jiǎng)h除謂詞的權(quán)限以破壞架構(gòu)綁定)、修改內(nèi)聯(lián)表值函數(shù)以及在目標(biāo)表上重復(fù)運(yùn)行 select 語(yǔ)句。我們建議您根據(jù)需要限制權(quán)限,并監(jiān)視任何可疑活動(dòng)。應(yīng)監(jiān)視活動(dòng),例如不斷更改的策略和與行級(jí)別安全性相關(guān)的內(nèi)聯(lián)表值函數(shù)。
(2)精心設(shè)計(jì)的查詢(xún)。
通過(guò)使用利用錯(cuò)誤的精心設(shè)計(jì)的查詢(xún),可能會(huì)導(dǎo)致信息泄露。
五、跨功能兼容性
- 通常,行級(jí)別安全性將跨功能按預(yù)期工作。但是,也有一些例外。本節(jié)記錄了將行級(jí)別安全性與 SQL Server 的某些其他功能結(jié)合使用的幾個(gè)注意事項(xiàng)和注意事項(xiàng)。
- DBCC SHOW_STATISTICS報(bào)告未過(guò)濾數(shù)據(jù)的統(tǒng)計(jì)信息,并可能泄露受安全策略保護(hù)的信息。因此,對(duì)查看具有行級(jí)別安全策略的表的統(tǒng)計(jì)信息對(duì)象的訪(fǎng)問(wèn)受到限制。用戶(hù)必須擁有該表,或者用戶(hù)必須是 sysadmin 固定服務(wù)器角色、db_owner固定數(shù)據(jù)庫(kù)角色或db_ddladmin固定數(shù)據(jù)庫(kù)角色的成員。
- 文件流:RLS 與文件流不兼容。
- 內(nèi)存優(yōu)化表:必須使用該選項(xiàng)定義用作內(nèi)存優(yōu)化表的安全謂詞的內(nèi)聯(lián)表值函數(shù)。使用此選項(xiàng),將禁止內(nèi)存優(yōu)化表不支持的語(yǔ)言功能,并在創(chuàng)建時(shí)發(fā)出相應(yīng)的錯(cuò)誤。
- 索引視圖:通常,可以在視圖之上創(chuàng)建安全策略,也可以在受安全策略約束的表之上創(chuàng)建視圖。但是,不能在具有安全策略的表之上創(chuàng)建索引視圖,因?yàn)橥ㄟ^(guò)索引查找行將繞過(guò)該策略。
- 變更數(shù)據(jù)捕獲:變更數(shù)據(jù)捕獲可能會(huì)泄漏應(yīng)篩選為db_owner成員或?yàn)楸韱⒂?CDC 時(shí)指定的“控制”角色成員的用戶(hù)(注意:您可以將此函數(shù)顯式設(shè)置為 NULL,以使所有用戶(hù)都能訪(fǎng)問(wèn)變更數(shù)據(jù))。實(shí)際上,db_owner和此控制角色的成員可以查看表上的所有數(shù)據(jù)更改,即使表上有安全策略也是如此。
- 更改跟蹤:更改跟蹤可能會(huì)將應(yīng)篩選的行的主鍵泄露給同時(shí)具有“選擇”和“查看更改跟蹤”權(quán)限的用戶(hù)。實(shí)際數(shù)據(jù)值不會(huì)泄露;只有 A 列被更新/插入/刪除了帶有 B 主鍵的行的事實(shí)。如果主密鑰包含機(jī)密元素(如社會(huì)保險(xiǎn)號(hào)),則會(huì)出現(xiàn)問(wèn)題。然而,在實(shí)踐中,這個(gè)CHANGETABLE幾乎總是與原始表連接,以獲得最新的數(shù)據(jù)。
- 全文搜索:使用以下全文搜索和語(yǔ)義搜索函數(shù)的查詢(xún)預(yù)計(jì)會(huì)降低性能,因?yàn)橐肓祟~外的聯(lián)接來(lái)應(yīng)用行級(jí)安全性并避免泄漏應(yīng)過(guò)濾的行的主鍵:CONTAINSTABLE、FREETEXTTABLE、semantickeyphrasetable、semanticsimilaritydetailstable、semanticsimilaritytable、semanticsimilaritytable。
- 列存儲(chǔ)索引:RLS 與聚集列存儲(chǔ)索引和非聚集列存儲(chǔ)索引兼容。但是,由于行級(jí)別安全性應(yīng)用函數(shù),因此優(yōu)化程序可能會(huì)修改查詢(xún)計(jì)劃,使其不使用批處理模式。
- 分區(qū)視圖:不能在分區(qū)視圖上定義塊謂詞,也不能在使用塊謂詞的表上創(chuàng)建分區(qū)視圖。篩選器謂詞與分區(qū)視圖兼容。
- 時(shí)態(tài)表:時(shí)態(tài)表與 RLS 兼容。但是,當(dāng)前表上的安全謂詞不會(huì)自動(dòng)復(fù)制到歷史記錄表中。要將安全策略應(yīng)用于當(dāng)前表和歷史記錄表,必須在每個(gè)表上單獨(dú)添加安全謂詞。
六、示例
向數(shù)據(jù)庫(kù)進(jìn)行身份驗(yàn)證的用戶(hù)的方案。創(chuàng)建三個(gè)用戶(hù),并創(chuàng)建并填充一個(gè)包含六行的表。然后,它為表創(chuàng)建一個(gè)內(nèi)聯(lián)表值函數(shù)和安全策略。然后,該示例演示如何為各種用戶(hù)篩選 select 語(yǔ)句。
(1)創(chuàng)建三個(gè)將演示不同訪(fǎng)問(wèn)功能的用戶(hù)帳戶(hù)。
CREATE USER Manager WITHOUT LOGIN; CREATE USER SalesRep1 WITHOUT LOGIN; CREATE USER SalesRep2 WITHOUT LOGIN; GO
(2)創(chuàng)建一個(gè)表來(lái)保存數(shù)據(jù)。
CREATE SCHEMA Sales GO CREATE TABLE Sales.Orders ( OrderID int, SalesRep nvarchar(50), Product nvarchar(50), Quantity smallint );
(3)用六行數(shù)據(jù)填充表,顯示每個(gè)銷(xiāo)售代表的三個(gè)訂單。
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)向每個(gè)用戶(hù)授予對(duì)表的讀取訪(fǎng)問(wèn)權(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í)行查詢(xún)的用戶(hù) 相同或執(zhí)行查詢(xún)的用戶(hù)是經(jīng)理用戶(hù)時(shí),該函數(shù)返回。此用戶(hù)定義的表值函數(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)允許對(duì)函數(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ù)庫(kù)清理練習(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的行級(jí)安全性詳解的文章就介紹到這了,更多相關(guān)SQL Server行級(jí)安全性?xún)?nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
SQLServer觸發(fā)器創(chuàng)建、刪除、修改、查看示例代碼
觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程﹐它不能被顯式地調(diào)用﹐而是在往表中插入記錄﹑更新記錄或者刪除記錄時(shí)被自動(dòng)地激活。所以觸發(fā)器可以用來(lái)實(shí)現(xiàn)對(duì)表實(shí)施復(fù)雜的完整性約束。2010-06-06獲取MSSQL 表結(jié)構(gòu)中字段的備注、主鍵等信息的sql
本文為大家詳細(xì)介紹下如何獲取MSSQL 表結(jié)構(gòu)中字段的備注、主鍵等信息,感興趣的朋友可以參考下2013-09-09SQL Server AlwaysOn讀寫(xiě)分離配置圖文教程
這篇文章主要介紹了SQL Server AlwaysOn讀寫(xiě)分離配置圖文教程,需要的朋友可以參考下2017-09-09SQL Server修改數(shù)據(jù)字段名的三種方法
這篇文章主要介紹了SQL Server修改數(shù)據(jù)字段名的三種方法, ALTER TABLE語(yǔ)句修改,EXEC sp_RENAME存儲(chǔ)過(guò)程修改和使用UPDATE語(yǔ)句修改,2024-03-03
文中有相關(guān)的代碼示例供大家參考,需要的朋友可以參考下sql存儲(chǔ)過(guò)程獲取漢字拼音頭字母函數(shù)
sql存儲(chǔ)過(guò)程獲取漢字拼音頭字母函數(shù),需要的朋友可以參考下。2011-10-10sqlserver查找括號(hào)()中字符串內(nèi)容的方法實(shí)現(xiàn)
本文主要介紹了sqlserver查找括號(hào)()中字符串內(nèi)容的方法實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05