SQL Server 2016 新功能搶先看 - 資料列層級安全性

發行項
09/29/2015

SQL Server 2016 搶先看第九波，這次要帶大家認識「資料列層級安全性」！

回顧系列專文：

本文將帶大家了解以下功能：

說明

Microsoft Azure SQL Database V12 推出資料列層級安全性（Row-Level Security，RLS）簡化了控管資料列存取的安全性，尤其是應用在多租戶架構下，有效的降低管理租戶之間資料隔離的複雜度，並且能夠避免開發人員不小心造成租戶存取到自己不該存取的資料。

資料列層級安全性原本只支援雲端平台上的 Azure SQL Database，如今在地端的 SQL Server 也可以使用這項功能，SQL Server 2016 已將資料層級安全性移植過來，提供企業內部部署 SQL Server 能夠集中控管資料存取安全的另一項選擇。

即使是已經上線的系統，都可以不用修改程式碼或只須小幅度修改，就可以適用資料列層級安全性。本文以一個簡單的 ASP.NET MVC 應用程式來作示範，模擬使用資料列層級安全性之後，不需修改應用程式就可以讓登入的使用者只能存取屬於自己的資料。

首先以下列 T-SQL 指令碼建立測試資料表及測試資料。

USE RLSDB

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Sales')

DROP TABLE dbo.Sales

CREATE TABLE dbo.Sales

(

OrderID int IDENTITY PRIMARY KEY,

SalesRep sysname,

Product varchar(10),

Qty int

)

INSERT INTO dbo.Sales VALUES

('Terry', 'Valve', 5),

('Terry', 'Wheel', 2),

('Terry', 'Valve', 4),

('Sky', 'Bracket', 2),

('Sky', 'Wheel', 5),

('Sky', 'Seat', 5)

接著建立 ASP.NET MVC 應用程式來對 Sales 資料表進行增刪改查（CRUD），在不修改任何程式碼情況下，不論是哪個使用者登入系統，都可以看到所有的銷售資料（如下圖所示）。

假設我們的需求是每個銷售人員只能看到自己的銷售資料，新增、刪除、修改時也只能針對自己的資料進行維護，在不修改應用程式的前提下，使用 SQL Server 2016 的資料列層級安全性來達到上述需求。

實作步驟

使用 SQL Server 2016 的資料列層級安全性，需要的步驟如下：

一、雖非必要條件，但建議建立資料列層級安全性物件專用的結構描述（包含篩選述詞函數和安全性原則）。

二、建立篩選述詞函數。

三、建立安全性原則。

不論是雲端上的 Azure SQL Database v12 或地端的 SQL Server 2016，只能以 T-SQL 指令碼的方式來建立或修改資料列層級安全性，接下來我們就來分別介紹這三個步驟所需使用的 T-SQL 指令碼。

建立結構描述

資料列層級安全性的相關物件，建議使用獨立的結構描述，以利做到所謂的權責分離。例如安全性原則管理員授予 ALTER ANY SECURITY POLICY 權限，但拒絕安全性原則所套用目的資料表的 SELECT 權限。

因此，我們以下列 T-SQL 建立結構描述。

CREATE SCHEMA rls

建立成功後可以在物件總管中看到將來在建立篩選述詞函數和安全性原則所要使用的結構描述（如下圖所示）。

建立篩選述詞函數

接下來需要建立篩選述詞函數，用以判斷目前登入的使用者身分，做為取得該使用者有權限讀取資料列的依據，有關篩選述詞函數您可以依照實際需求來撰寫，例如依照部門別、職位或權責等方式來篩選資料列，不論如何需要符合下列條件：

一、必須是嵌入資料表值函數（inline table valued function）函數，否則在建立資料列層級安全性原則時會發生錯誤。

二、必須使用 SCHEMABINDING 選項，若將未指定 WITH SCHEMABINDING 的函數指派給資料列層級安全性原則會發生錯誤。

下列 T-SQL 示範建立篩選述詞函數，其中傳入銷售人員名稱（@SalesRep）與目前資料庫使用者名稱比對，來篩選出屬於自己的銷售資料，其中回傳的資料表內容為何並不重要，主要是判斷結果集是否為空的來決定篩選結果。

CREATE FUNCTION rls.fn_filterbyusername(@SalesRep AS sysname)

RETURNS TABLE

WITH SCHEMABINDING

RETURN

SELECT @SalesRep AS result

WHERE @SalesRep = USER_NAME()

建立安全性原則

最後一個步驟就是建立安全性原則並將篩選述詞函數套用至指定的資料表。

下列 T-SQL 示範以 CREATE SECURITY POLICY 敘述建立安全性原則，其中 ADD FILTER PREDICATE 參數用來指定篩選述詞函數；ON 參數用來指訂定安全性原則所要套用的資料表；而 STATE 參數設為 ON，是用來在建立安全性原則後將之啟用。

CREATE SECURITY POLICY rls.sp_sales

ADD FILTER PREDICATE rls.fn_filterbyusername(SalesRep)

ON dbo.Sales

WITH (STATE = ON)

若要停用或啟用已經現有的安全性原則，可以使用下列 T-SQL 指令碼。

--停用安全性原則

ALTER SECURITY POLICY rls.sp_sales

WITH (STATE = OFF)

--啟用安全性原則

ALTER SECURITY POLICY rls.sp_sales

WITH (STATE = ON)

測試資料列層級安全性

在開始測試套用資料列層級安全性之後，應用程式在沒有做任何修改的條件下，會有什麼樣的改變，我們先在 Sales 控制器的Index 方法設定中斷點，查看 Entity Framework 幫我們產生什麼樣的 T-SQL 指令碼。

由下圖可見，Index 方法會直接回傳整個 Sales 類別，在下方的自動變數視窗可以看到，Entity Framework 產生的 T-SQL 指令碼並未以 WHERE 條件來篩選資料，如果未使用資料列層級安全性，則 Sales 資料表的所有內容理應會整個呈現在網頁之中。

但實際上畫面呈現出來的結果，卻會只顯示屬於登入使用者的銷售資料（如下圖所示）。

如此可見，藉由 SQL Server 2016 的資料列層級安全性功能，確實能夠依照安全性原則及篩選述詞函數來判斷哪些資料是被授權可以存取，並自動過濾掉不該被查詢的資料，即便現有的系統要使用這項功能，也不需大費周章修改應用程式的程式碼。

中介層應用程式之情境

若您的應用程式連接資料庫都是使用相同的連線字串，那麼上述做法則不適用於這種情境，應用程式必須稍作修改才可以使用資料列層級安全性所帶來的好處。

為了判斷目前執行的身份，以作為後續進行查詢時，資料列層級安全性可以自動進行資料的篩選，可以透過預存程序執行 SET CONTEXT_INFO 敘述將銷售人員名稱與工作階段進行關聯。

下列 T-SQL 指令碼示範建立預存程序來將銷售人員名稱與工作階段進行關聯。

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name=’ usp_setContextInfoAsName’)

DROP PROC usp_setContextInfoAsName

CREATE PROCEDURE usp_setContextInfoAsName(@SalesRep sysname)

DECLARE @bin VARBINARY(128) = CONVERT(VARBINARY(128), @SalesRep)

SET CONTEXT_INFO @bin

如同前一個情境所介紹，我們需要將傳入銷售人員名稱轉型成 context_info 值進行比對。

CREATE FUNCTION rls.fn_filterBySales(@SalesRep sysname)

RETURNS TABLE

WITH SCHEMABINDING

RETURN SELECT 1 AS fn_result

WHERE CONTEXT_INFO() = CONVERT(varbinary(128),@SalesRep)

最後則是建立安全性原則，例如下列的 T-SQL 指令碼。

CREATE SECURITY POLICY rls.sp_salesrep

ADD FILTER PREDICATE rls.fn_filterBySales(SalesRep)

ON dbo.Sales

WITH (STATE = ON)

我們再建立另一個預存程序，在該預存程序中先執行 usp_setContextInfoAsName 預存程序來設定工作階段，接著就能讓資料列層級安全性套用安全性原則來查詢該使用者有權限查詢的資料列。

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'usp_getSales')

DROP PROC usp_getSales

CREATE PROCEDURE usp_getSales(@SalesRep sysname)

EXECUTE usp_setContextInfoAsName @SalesRep

SELECT *

FROM dbo.Sales

然後修改 Sales 控制器的 Index 方法，改用 Database.SqlQuery 方法來執行 usp_getSales 預存程序。

重新執行應用程式，就會發現畫面上只會呈現該使用者有權限查詢的資料，代表資料列層級安全性已正確運作（如下圖所示）。

管理資料列層級安全性

安全性原則可以套用多個篩選述詞函數，您可以依照需求來訂定屬於自己的篩選述詞函數，然後用 ALTER SECURITY POLICY 敘述來加入、刪除篩選述詞函數，甚至是停用安全性原則。

● 管理安全性原則

下列 T-SQL 示範使用 ALTER SECURITY POLICY 敘述來為安全性原則移除、加入篩選述詞函數，以及停用/啟用安全性原則。

--移除篩選述詞函數

ALTER SECURITY POLICY rls.spo_GetCustomerByUserID

DROP FILTER PREDICATE ON [SalesLT].[Customer]

--加入篩選述詞函數

ALTER SECURITY POLICY rls.spo_GetCustomerByUserID

ADD FILTER PREDICATE rls.fn_securitypredicate(UserID)

ON [SalesLT].[Customer]

--停用安全性原則

ALTER SECURITY POLICY rls.spo_GetCustomerByUserID

WITH(STATE=OFF)

--啟用安全性原則

ALTER SECURITY POLICY rls.spo_GetCustomerByUserID

WITH(STATE=ON)

● 查詢篩選述詞函數和查詢安全性原則

SQL Server 2016 在系統目錄檢視中增加用來查詢篩選述詞函數和安全性原則的安全目錄檢視，提供安全性原則管理員方便管理這些資料列層級安全性的相關物件。

查詢 sys.security_predicates 目錄檢視會得到目前資料庫所有篩選述詞函數的資訊，透過和 sys.tables 系統檢視的連結，就可得知該函數是作用在哪個資料表，例如下列的 T-SQL 指令碼。

USE RLSDB

SELECT a.*,b.name target_table_name

FROM sys.security_predicates a

JOIN sys.tables b

ON a.target_object_id = b.object_id

執行結果如下圖所示，其中 predicate_definition 資料行可以看到篩選述詞函數的定義，包含結構描述、函數名稱及傳入參數等資訊。

另外，查詢 sys.security_policies 目錄檢視則會回傳目前作用中的資料庫，所有安全性原則的資訊，利用和 sys.schemas 系統檢視的連結，就可得知安全性原則所屬的結構描述名稱，例如下列的 T-SQL 指令碼。

SELECT a.*,b.name [schema_name]

FROM sys.security_policies a

JOIN sys.schemas b

ON a.schema_id = b.schema_id

執行結果如下圖所示，其中 is_enabled 資料行用來表示安全性原則的啟用狀態。

防止未經授權的新增資料

預設情況下資料列層級安全性只能控管查詢資料列的權限，但無法限制資料寫入是否合乎安全性原則，想當然這樣的邏輯並不合理，若在多租戶架構下，很可能會因此把資料寫到其他租戶可以看到的地方，要如何防止這種狀況發生？

首先可以建立一個用來檢查是否符合篩選述詞函數的純量函數，傳入銷售人員名稱來判斷要新增的資料是否符合目前工作階段。

CREATE FUNCTION rls.fnCheckFilterdSales(@SalesRep sysname)

RETURNS bit

BEGIN

IF EXISTS(SELECT * FROM rls.fn_filterBySales(@SalesRep)) RETURN 1

RETURN 0

END

接著將這個純量函數作為資料表的 CHECK 條件約束。

ALTER TABLE dbo.Sales

WITH NOCHECK

ADD CONSTRAINT chk_prvent_unauthorized_insert

CHECK(rls.fnCheckFilterdSales(SalesRep) = 1)

有了這個約束條件之後，當新增的資料與目前工作階段不符時，就會出現下列的錯誤訊息，以阻止資料寫入到其他租戶的情況發生。

若您對 SQL Server 有任何問題，歡迎點這裡提出您的疑問。

觀看最新的 SQL Server 2016 新功能課程與簡報檔請點這裡

Comments

Anonymous
October 04, 2015
SQL Server 2016 搶先看系列第十彈！

本系列文已經介紹 SQL Server 2016 在安全性、混合雲應用、效能調校及開發等方面所推出的嶄新功能或改良， SQL Server

共用方式為

SQL Server 2016 新功能搶先看 - 資料列層級安全性

Comments

其他資源