[SQL 2016]-新功能-Row Level Security

SQL Server 2016在安全性上做了相當多的改良,第三個就是限制使用者存取資料的Row Level Security。
這個功能首推的就是,使用者只能取得"已設定好的條件"的資料列,
也就是不同的使用者在同一個資料表,使用相同的語法,取出的資料可限制為不同。

已經看過了Table Level的Always Encrypted, 與Column Level的Dynamic Data Masking, 在SQL Server 2016上當然少不了Row Level的安全性功能囉! 不過在CP2的名稱就叫Row Level Security, 未來會不會改變還不知道, 但這個功能可是未來連DBA在存取資料上都能被安全性綁的死死的~~有好有壞啦~~ 只要權限配置的妥當, 那麼DBA就有權利說 "別叫DBA幫忙撈資料". 但是一旦沒有操控好, 那會是DBA們的一場大災難吶!!!!!

Row Level Security在SQL Server 2014時就在喊了, 但是一直沒看到什麼做為, 一直以為會在Service Pack 1時加入但是看起來是沒有完整的功能. 這個功能簡單來說, 就像是使用TRIGGER加上VIEW的整合性結果, 能夠依照登入SQL的使用者來決定可以取出什麼樣的資料~~話說SQL Server 2016裡的這個功能, 個人覺得是最適合在一些企業裡部署, 但是缺憾是目前one table on policy…… 不然就是要把想要實現的東西丟在一包裡 (很難, 大家的夢想都很大).

來看看線上叢書怎麼說吧~~

Row-Level Security
https://msdn.microsoft.com/en-us/library/dn765131.aspx

 

這個功能透過POLICY的方式可以依照指定的條件取出過濾取出的資料, 當然就包含了SELECT, UPDATE, & DELETE了. 這也說明了若是使用的當, A是不能夠碰觸B的資料的. 這也實現了許多資安上的需求.

那麼以下就用實例來看看這個功能的基本使用方式吧~

首先這個範例會展示如何限定某個使用者只能讀取限制的資料, 並且說明在定義POLICY後, 連SA (DBO) 權限之使用者也無法取出資料.

/**************************************************
建立測試資料庫: RowLevelSecurity
資料庫檔案位置: 預設路徑
**************************************************/
CREATE DATABASE RowLevelSecurity
ON  PRIMARY 
( NAME = N'RowLevelSecurity_data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\RowLevelSecurity_data.mdf')
 LOG ON 
( NAME = N'RowLevelSecurity_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\RowLevelSecurity_log.ldf')
GO

 

/**************************************************
在RowLevelSecurity資料庫下建立一個schema名為Security
**************************************************/
USE RowLevelSecurity
GO

CREATE SCHEMA Security;
GO
/**************************************************
建立測試資料表: FilterByOrder
並寫入幾筆資料進行測試用
**************************************************/
CREATE TABLE FilterByOrder
(
	sn int identity(1,1) not null,
	product_name varchar(3) not null,
	qty int not null
)
GO

INSERT INTO FilterByOrder
(
	product_name,
	qty
)
VALUES	('abc', 200),
		('def', 250),
		('ghi', 100),
		('jkl', 150),
		('mno', 300),
		('pqr',  50),
		('stu', 200)
GO

/**************************************************
此時用SA登入取出的資料預期如下
sn          product_name qty
----------- ------------ -----------
1           abc          200
2           def          250
3           ghi          100
4           jkl          150
5           mno          300
6           pqr          50
7           stu          200

(7 個資料列受到影響)
**************************************************/

 

/**************************************************
建立一個使用者名為Below150, 稍後就指定這個使用者只能讀取qty小於150的資料
同時給予這個Below150使用者SELECT的權限在FilterByOrder資料表上.
**************************************************/
CREATE USER Below150 WITHOUT LOGIN
GO
GRANT SELECT ON FilterByOrder TO Below150
GO

 

/**************************************************
在這裡是重點了, 建立一個schema為Security的FUNCTION
然後把需要的result寫入這個FUNCTION裡, 例如本例
USER_NAME為Below150的人只能看到qty小於150的資料
**************************************************/
CREATE FUNCTION Security.fn_qtyBelow150(@qty as int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN 
SELECT 1 AS fn_qtyBelow150
WHERE @qty <=150 AND USER_NAME() = 'Below150';
GO

/**************************************************
然後把剛才建立的FUNCTION設定到這張資料表的SECURITY POLICY中
**************************************************/
CREATE SECURITY POLICY qtyFilterBelow150
ADD FILTER PREDICATE Security.fn_qtyBelow150(qty) 
ON dbo.FilterByOrder
WITH (STATE = ON);

/**************************************************
這時候透過User Account "Below150"去查資料時
就只能查到qty小於150的資料了.
**************************************************/
EXECUTE AS USER = 'Below150';
SELECT * FROM FilterByOrder; 
REVERT;
/**************************************************
sn          product_name qty
----------- ------------ -----------
3           ghi          100
4           jkl          150
6           pqr          50

(3 個資料列受到影響)
**************************************************/

/**************************************************
但這時您可能會發現使用當前登入的帳號, 甚至是SA權限的帳號
進行SELECT * FROM FilterByOrder資料表, 回傳竟然是0筆
可想而知, 一旦套了POLICY, 連SA都能預防性的禁止資料取出.

sn          product_name qty
----------- ------------ -----------

(0 個資料列受到影響)
**************************************************/

 

/**************************************************
因此修正的方式 (也許不該說修正, 而是為了DBA方便好做事吧……)
是先將POLICY停用再進行SA權限的存取, 但這又卡在可能同時間
使用者跑上來查詢, 所以比較好的方式是DROP POLICY, 編寫
FUNCTION加入條件, 然後再CREATE POLICY去套用 (當然一開始
就建立的好, 就不用這麼麻煩了.
**************************************************/
DROP SECURITY POLICY qtyFilterBelow150
GO

ALTER FUNCTION Security.fn_qtyBelow150(@qty as int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN 
SELECT 1 AS fn_qtyBelow150
WHERE (@qty <=150 AND USER_NAME() = 'Below150')
		OR USER_NAME() = 'dbo'; --這是當前的USER_NAME()
GO

CREATE SECURITY POLICY qtyFilterBelow150
ADD FILTER PREDICATE Security.fn_qtyBelow150(qty) 
ON dbo.FilterByOrder
WITH (STATE = ON);

SELECT * FROM FilterByOrder;
/**************************************************
sn          product_name qty
----------- ------------ -----------
1           abc          200
2           def          250
3           ghi          100
4           jkl          150
5           mno          300
6           pqr          50
7           stu          200

(7 個資料列受到影響)
**************************************************/

 


 

第二個範例就是展示個人資料個人看, 主管才能看光光.

/**************************************************
建立測試資料表: FilterBySalesName
並寫入幾筆資料進行測試用
**************************************************/
CREATE TABLE FilterBySalesName
(
	id int not null,
	sales_name varchar(10) not null,
	product_name varchar(3) not null,
	amount int not null
);
GO

INSERT INTO FilterBySalesName
(
	id,
	sales_name,
	product_name,
	amount
)
VALUES	(1, 'James', 'abc', 10000),
		(2, 'James', 'def', 20000),
		(3, 'James', 'ghi', 30000),
		(4, 'Brown', 'jkl', 40000),
		(5, 'Brown', 'mno', 50000),
		(6, 'Brown', 'pqr', 60000);
GO

 

/**************************************************
建立三個Database User Account:
James: 假設是第一個Sales
Brown: 假設是第二個Sales
Sally: 假設是Manager可以見所有人的資料
**************************************************/
CREATE USER James WITHOUT LOGIN --First Sales Name
CREATE USER Brown WITHOUT LOGIN --Second Sales Name
CREATE USER Sally WITHOUT LOGIN --Manager

GRANT SELECT ON FilterBySalesName TO James;
GRANT SELECT ON FilterBySalesName TO Brown;
GRANT SELECT ON FilterBySalesName TO Sally;

 

/**************************************************
接著就是建立FUNCTION和POLICY, 做法和前面一樣就不多提了
**************************************************/
CREATE FUNCTION Security.fn_FilterBySalesName(@name as varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_FilterBySalesName
WHERE USER_NAME() = @name OR USER_NAME() = 'Sally'
GO

CREATE SECURITY POLICY SalesQuerySalef
ADD FILTER PREDICATE Security.fn_FilterBySalesName(sales_name) 
ON dbo.FilterBySalesName
WITH (STATE = ON);

 

/**************************************************
這時分別用三個帳號取資料的結果
**************************************************/
EXECUTE AS USER = 'James';
SELECT * FROM FilterBySalesName; 
REVERT;
/**************************************************
id          sales_name product_name amount
----------- ---------- ------------ -----------
1           James      abc          10000
2           James      def          20000
3           James      ghi          30000

(3 個資料列受到影響)
**************************************************/

EXECUTE AS USER = 'Brown';
SELECT * FROM FilterBySalesName; 
REVERT;
/**************************************************
id          sales_name product_name amount
----------- ---------- ------------ -----------
4           Brown      jkl          40000
5           Brown      mno          50000
6           Brown      pqr          60000

(3 個資料列受到影響)
**************************************************/

EXECUTE AS USER = 'Sally';
SELECT * FROM FilterBySalesName; 
REVERT;
/**************************************************
id          sales_name product_name amount
----------- ---------- ------------ -----------
1           James      abc          10000
2           James      def          20000
3           James      ghi          30000
4           Brown      jkl          40000
5           Brown      mno          50000
6           Brown      pqr          60000

(6 個資料列受到影響)
**************************************************/