Security Policy：行級安全（Row-Level Security）

行級安全RLS(Row-Level Security)是在數據行級別上控制用戶的訪問，控制用戶只能訪問資料庫表的特定數據行。斷言是邏輯表達式，在SQL Server 2016中，RLS是基於安全斷言（Security Predicate）的訪問控制，Security Predicate是由內聯表值函 ...

通過授予和拒絕（Grant/Deny）命令控制用戶的許可權，只能控制用戶對資料庫對象的訪問許可權，這意味著，用戶訪問的粒度是對象整體，可以是一個數據表，或視圖等，用戶要麼能夠訪問資料庫對象，要麼沒有許可權訪問，就是說，一個資料庫對象，通過授予和拒絕用戶的許可權/角色（Permission或Role），無法使特定的數據行只允許特定身份的人訪問，但是，該需求可以使用安全策略（Security Policy）實現。

當啟用行級安全（Row-Level Security，簡稱RLS）時，Security Policy在數據行級別上控制用戶的訪問，粒度是數據行，控制用戶只能訪問數據表的特定數據行。斷言（Predicate ）是邏輯表達式，返回的結果是布爾（boolean）值：true 或false。在SQL Server 2016中，RLS是基於安全斷言（Security Predicate）的訪問控制，Security Predicate是由內聯表值函數實現的，當邏輯表達式返回結果時，安全斷言的結果是True；當邏輯表達式不返回任何結果時，安全斷言的結果是False。如果安全策略（Security Policy）被禁用，那麼用戶總是訪問所有數據行，跟數據表上不關聯任何安全策略一樣。

實現RLS，必須顯式定義三個組件：

數據表（Base Table）：用於存儲數據行，在該表上創建Security Policy，使用RLS控制用戶能夠訪問的數據行；
斷言函數（Predicate Function）：是內聯表值函數，用於執行安全斷言，Security Policy調用該函數過濾數據行或阻塞寫操作；
安全策略（Security Policy）：將數據表和斷言函數綁定，並設置安全斷言的類型；

一，內聯表值函數定義安全斷言（Security Predicate）

如果在數據表上啟用RLS，那麼一個用戶訪問數據行的許可權受到安全斷言（Security Predicate）的限制，Security Predicate 是在內聯表值函數中定義的邏輯表達式，Security Policy調用內聯表值函數，返回Security Predicate 的結果。在用戶訪問行級別數據時，SQL Server自動執行預定義的安全策略（Security Policy），僅當Security Predicate返回邏輯結果時，才允許用戶訪問指定的數據行；如果Security Predicate 不返回任何結果，那麼不允許用戶訪問數據。如果在一個數據表上創建了Security Policy，但是，安全策略（SecurityPolicy）被禁用，那麼，Security Predicate將不會過濾或阻塞任何數據行，不執行任何的Filter 或 Block操作，用戶能夠訪問所有的數據行。

下麵的示例代碼定義了安全斷言（Security Predicate），該表達式根據用戶名作為斷言控制用戶訪問的數據行：

CREATE FUNCTION rls.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';

二，過濾斷言和阻塞斷言（Filter 和 Block）

在Security Policy中，RLS支持兩種類型的安全斷言（Security Predicates）：

Filter Predicate：當用戶從基礎表讀取數據行時，Filter Predicate透明地過濾數據行，用戶只能讀取有許可權訪問的數據行；如果所有的數據行都被過濾掉，那麼返回空集給用戶；
Block Predicate：當違反斷言時，阻塞寫操作事務的提交，回滾寫操作事務；

1，過濾斷言（Filter Predicate）

當從Base Table讀取數據時，讀操作受到Filter Predicate的影響，讀取數據的操作包括：select，delete和update，用戶不能查詢，刪除和更新被過濾的數據行。

過濾斷言（Filter Predicate）定義一個Security Policy，在Base Table上執行select，update和delete命令時，Security Policy透明地過濾數據行，應用程式不會意識到Filter操作的存在；應用程式能夠插入任何數據，不管數據是否被過濾掉。

2，阻塞斷言（Block Predicate）

阻塞斷言（Block predicates）將Update操作拆分成兩個獨立的操作：Before Update 和 After Update。

Block Predicate影響所有的寫操作，有四種阻塞操作：

After Insert 斷言：阻止用戶插入違反斷言的欄位值，就是說，插入的數據必須滿足斷言；
After Update 斷言：阻止用戶將數據更新為違反斷言的欄位值，就是說，數據更新後，其值必須滿足斷言；
Before Update 斷言：只允許用戶更新符合斷言的數據行，就是說，對於符合斷言的數據行，能夠更新為任意值；
Before Delete 斷言：只允許用戶刪除符合斷言的數據行，就是說，對於符合斷言的數據行，能夠刪除；

阻塞操作有分為After 和Before選項：

After 指定：在執行Insert 或 Update操作之後，計算斷言的邏輯結果；如果邏輯結果為false，那麼回滾Insert 或 Update操作；
Before 指定：在執行Update 或Delete 操作之前，計算斷言的邏輯結果，用戶只能Update或Delete符合斷言的數據；
如果沒有指定，那麼預設會指定所有四種阻塞操作。

三，使用Security Policy控制用戶只能訪問指定的數據

1，創建數據表，並插入數據

CREATE TABLE dbo.Sales  
(  
OrderID int not null,  
SalesRep sysname,  
Product varchar(10) not null,  
Qty int not null 
);  

INSERT dbo.Sales 
VALUES   
(1, 'Sales1', 'Valve', 5),   
(2, 'Sales1', 'Wheel', 2),   
(3, 'Sales1', 'Valve', 4),  
(4, 'Sales2', 'Bracket', 2),   
(5, 'Sales2', 'Wheel', 5),   
(6, 'Sales2', 'Seat', 5);

View Code

2，創建User，並授予查詢許可權

--create user
create user Sales1 without login;
create user Manager without login;
--grant permission
GRANT SELECT ON Sales TO Manager;  
GRANT SELECT ON Sales TO Sales1;

3，創建記憶體表值函數，用於過濾數據行，返回Security Predicate 的結果

強烈推薦創建一個單獨的Schema，用於RLS對象（Predicate Function和 Security Policy），本例中創建RLS Schema。

--create schema
create schema rls;  
authorization dbo;
 
--create function
CREATE FUNCTION rls.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';

4，創建和啟動安全策略（Security Policy）

在新建的Security Policy中，將Base Table和Security Predicate 綁定，添加Filter Predicate，使用dbo.Sales作為過濾條件，啟用新建的Security Policy

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

5，測試安全策略（Security Policy）

EXECUTE AS USER = 'Sales1'; 
SELECT USER_NAME() as UserName,* 
FROM dbo.Sales;   
REVERT;  

EXECUTE AS USER = 'Manager';  
SELECT USER_NAME() as UserName,* 
FROM dbo.Sales;    
REVERT;

6，啟用或禁用安全策略（Security Policy）

--diable
ALTER SECURITY POLICY rls.SalesFilter  
WITH (STATE = OFF);  
--enable
ALTER SECURITY POLICY rls.SalesFilter  
WITH (STATE = ON);

四，維護安全策略（Security Policy）

Security Policy適用於所有的用戶，包括最高許可權角色 sysadmin 和 db_owner 的成員，以及dbo用戶，雖然這些成員擁有很高的許可權，能夠更改Security Policy的定義，甚至刪除Security Policy，但是，在訪問數據行時，仍然會受到Security Policy的影響，訪問的數據是Filter 或Block的結果。一個User要想訪問所有的數據行，必須在Predicate Function中顯式定義。一般情況下，會設置一個管理RLS的Manager用戶，用於維護Security Predicate控制的數據，必要時對數據處理進行故障排除。如果安全策略（Security Policy）被禁用，那麼，用戶在訪問數據表時，不會Filter或Block任何數據行，看到的數據表的全部數據行。

參考文檔：

CREATE SECURITY POLICY (Transact-SQL)

Row-Level Security