背景

假如我們有關(guān)鍵數(shù)據(jù)存儲(chǔ)在一個(gè)表里面，比如人員表中包含員工、部門和薪水信息。只允許用戶訪問各自部門的信息，但是不能訪問其他部門。一般我們都是在程序端實(shí)現(xiàn)這個(gè)功能，而在sqlserver2016以后也可以直接在數(shù)據(jù)庫(kù)端實(shí)現(xiàn)這個(gè)功能。

解決

安全已經(jīng)是一個(gè)數(shù)據(jù)方面的核心問題，每一代的MS數(shù)據(jù)庫(kù)都有關(guān)于安全方面的新功能，那么在Sql Server 2016,也有很多這方面的升級(jí)，比如‘Row Level Security’, ‘Always Encrypted’, ‘Dynamic Data Masking’, 和‘Enhancement of Transparent Data Encryption’ 等等都會(huì)起到安全方面的作用。本篇我將介紹關(guān)于Row Level Security (RLS--行級(jí)別安全), 能夠控制表中行的訪問權(quán)限。RLS 能使我們根據(jù)執(zhí)行查詢?nèi)说膶傩詠砜刂苹A(chǔ)數(shù)據(jù)，從而幫助我們?nèi)菀椎貫椴煌脩籼嵬该鞯脑L問數(shù)據(jù)。行級(jí)安全性使客戶能夠根據(jù)執(zhí)行查詢的用戶的特性控制數(shù)據(jù)庫(kù)中的行。

為了實(shí)現(xiàn)RLS我們需要準(zhǔn)備下面三個(gè)方面:

謂詞函數(shù)

安全謂詞

安全策略

逐一描述上面三個(gè)方面

謂詞函數(shù)

謂詞函數(shù)是一個(gè)內(nèi)置的表值函數(shù)，用于檢查用戶執(zhí)行的查詢?cè)L問數(shù)據(jù)是否基于其邏輯定義。這個(gè)函數(shù)返回一個(gè)1來表示用戶可以訪問。

安全謂詞

安全謂詞就是將謂詞函數(shù)綁定到表里面，RLS提供了兩種安全謂詞：過濾謂詞和阻止謂詞。過濾謂詞就是在使用SELECT, UPDATE, 和 DELETE語(yǔ)句查詢數(shù)據(jù)時(shí)只是過濾數(shù)據(jù)但是不會(huì)報(bào)錯(cuò)。而阻止謂詞就是在使用違反謂詞邏輯的數(shù)據(jù)時(shí)，顯示地報(bào)錯(cuò)并且阻止用戶使用 AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE 等操作。

安全策略

安全策略對(duì)象專門為行級(jí)別安全創(chuàng)建，分組所有涉及謂詞函數(shù)的安全謂詞。

實(shí)例

實(shí)例中我們創(chuàng)建一個(gè)Person表和測(cè)試數(shù)據(jù)，最后我們讓不懂得用戶訪問各自部門的信息，代碼如下:

Create table dbo.Person(PersonId INT IDENTITY(1,1),PersonName varchar(100),Department varchar(100),Salary INT,User_Access varchar(50))GOINSERT INTO Person (PersonName, Department, Salary, User_Access)SELECT 'Ankit', 'CS', 40000, 'User_CS'UNION ALLSELECT 'Sachin', 'EC', 20000, 'User_EC'UNION ALLSELECT 'Kapil', 'CS', 30000, 'User_CS'UNION ALLSELECT 'Ishant', 'IT', 50000, 'User_IT'UNION ALLSELECT 'Aditya', 'EC', 45000, 'User_EC'UNION ALLSELECT 'Sunny', 'IT', 60000, 'User_IT'UNION ALLSELECT 'Rohit', 'CS', 55000, 'User_CS'GO

?

?

此時(shí)表已經(jīng)被創(chuàng)建，并且插入了測(cè)試數(shù)據(jù)，執(zhí)行下面語(yǔ)句檢索有是有的記錄:

SELECT * FROM Person

正如所示，目前有三個(gè)部門department（CS,EC,IT），并且User_Access列表示各自的用戶組。讓我們創(chuàng)建三個(gè)測(cè)試用戶數(shù)據(jù)的賬戶語(yǔ)句如下：

--For CS departmentCREATE USER User_CS WITHOUT LOGIN--For EC departmentCREATE USER User_EC WITHOUT LOGIN-- For IT DepartmentCREATE USER User_IT WITHOUT LOGIN

?

在創(chuàng)建了用戶組以后，授權(quán)讀取權(quán)限給上面是哪個(gè)新建的用戶，執(zhí)行語(yǔ)句如下:

---授予select權(quán)限給所有的用戶GRANT SELECT ON Person TO User_CSGRANT SELECT ON Person TO User_ECGRANT SELECT ON Person TO User_IT

?

現(xiàn)在我們創(chuàng)建一個(gè)謂詞函數(shù)，該函數(shù)是對(duì)于查詢用戶是不可見的。

----Create functionCREATE FUNCTION dbo.PersonPredicate( @User_Access AS varchar(50) )RETURNS TABLEWITH SCHEMABINDINGASRETURN SELECT 1 AS AccessRightWHERE @User_Access = USER_NAME()GO

?

?

這個(gè)函數(shù)是只返回行，如果正在執(zhí)行查詢的用戶的名字與User_Access 列匹配，那么用戶允許訪問指定的行。在創(chuàng)建該函數(shù)后，還需要?jiǎng)?chuàng)建一個(gè)安全策略，使用上面的謂詞函數(shù)PersonPredicate來對(duì)表進(jìn)行過濾邏輯的綁定，腳本如下:

--安全策略CREATE SECURITY POLICY PersonSecurityPolicyADD FILTER PREDICATE dbo.PersonPredicate(User_Access) ON dbo.PersonWITH (STATE = ON)

?

?

State（狀態(tài)）為ON才能是策略生效，如果打算關(guān)閉策略，你可以改變狀態(tài)為OFF。

再來看一下查詢結(jié)果：

這次查詢沒有返回任何行，這意味著謂詞函數(shù)的定義和策略的創(chuàng)建后，用戶查詢需要具有相應(yīng)權(quán)限才能返回行，接下來使用不同用戶來查詢這個(gè)數(shù)據(jù)，首先，我們用用戶User_CS來查詢一下結(jié)果:

EXECUTE AS USER = 'User_CS'SELECT * FROM dbo.PersonREVERT

?

?

正如所示，我們看到只有三行數(shù)據(jù)數(shù)據(jù)該用戶，User_CS，已經(jīng)檢索出來。因此，過濾函數(shù)將其他不屬于該用戶組的數(shù)據(jù)過濾了。

實(shí)際上這個(gè)查詢執(zhí)行的過程就是數(shù)據(jù)庫(kù)內(nèi)部調(diào)用謂詞函數(shù)，如下所示:

SELECT * FROM dbo.Person

WHERE User_Name() = 'User_CS'

其他兩組用戶的查詢結(jié)果是相似的這里就不一一演示了。

因此，我們能看到執(zhí)行查詢根據(jù)用的不同得到只屬于指定用戶組的指定數(shù)據(jù)。這就是我們要達(dá)成的目的。

到目前為止，我們已經(jīng)演示了過濾謂詞，接下來我們演示一下如何阻止謂詞。執(zhí)行如下語(yǔ)句來授權(quán)DML操作權(quán)限給用戶。

--授權(quán)DML 權(quán)限GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_CSGRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_ECGRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_IT

?

我們用用戶User_IT執(zhí)行插入語(yǔ)句，并且插入用戶組為UserCS的，語(yǔ)句如下：

EXECUTE AS USER = 'User_IT'INSERT INTO Person (PersonName, Department, Salary, User_Access)SELECT 'Soniya', 'CS', 35000, 'User_CS'REVERT

?

but，竟然沒有報(bào)錯(cuò)，插入成功了。

讓我們?cè)跈z查一下用戶數(shù)據(jù)插入的情況：

EXECUTE AS USER = 'User_IT'SELECT * FROM dbo.PersonREVERT

?

奇怪，新插入行并沒有插入到該用戶組'User_IT'中。而是出現(xiàn)在了'User_CS' 的用戶組數(shù)據(jù)中。

--插入數(shù)據(jù)出現(xiàn)在了不同的用戶組EXECUTE AS USER = 'User_CS'SELECT * FROM dbo.PersonREVERT

?

通過上面的例子我們發(fā)現(xiàn)，過濾謂詞不不會(huì)阻止用戶插入數(shù)據(jù)，因此沒有錯(cuò)誤，這是因?yàn)闆]有在安全策略中定義阻止謂詞。讓我們加入阻止謂詞來顯示報(bào)錯(cuò)，有四個(gè)阻止謂詞AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, 和 BEFORE DELETE可以使用。我們這里測(cè)試使用AFTER INSERT 謂詞。這個(gè)謂詞阻止用戶插入記錄到?jīng)]有權(quán)限查看的數(shù)據(jù)用戶組。

添加謂詞阻止的安全策略，代碼如下:

--添加阻止謂詞ALTER SECURITY POLICY PersonSecurityPolicyADD BLOCK PREDICATE dbo.PersonPredicate(User_Access)ON dbo.Person AFTER INSERT

?

現(xiàn)在我們用之前類似代碼再試一下，是否可以插入數(shù)據(jù):

EXECUTE AS USER = 'User_CS'INSERT INTO Person (PersonName, Department, Salary, User_Access)SELECT 'Sumit', 'IT', 35000, 'User_IT'REVERT

?

?

擦，果然這次錯(cuò)誤出提示出現(xiàn)了，阻止了不同權(quán)限用戶的插入。因此我們能說通過添加阻止謂詞，未授權(quán)用戶的DML操作被限制了。

注意：在例子中每個(gè)部門只有一個(gè)用戶組成。如果在一個(gè)部門包含多個(gè)用戶的情況下，我們需要?jiǎng)?chuàng)建分支登錄為每個(gè)用戶都分配需要的權(quán)限，因?yàn)橹^詞函數(shù)應(yīng)用于用戶基礎(chǔ)并且安全策略取決于謂詞函數(shù)。

?

行級(jí)別安全的限制

這里有幾個(gè)行級(jí)別安全的限制：

謂詞函數(shù)一定要帶有WITH SCHEMABINDING關(guān)鍵詞，如果函數(shù)沒有該關(guān)鍵字則綁定安全策略時(shí)會(huì)拋出異常。

在實(shí)施了行級(jí)別安全的表上不能創(chuàng)建索引視圖。

內(nèi)存數(shù)據(jù)表不支持

全文索引不支持

總結(jié)

帶有行級(jí)別安全功能的SQLServer2016,我們可以不通過應(yīng)用程序級(jí)別的代碼修改來實(shí)現(xiàn)數(shù)據(jù)記錄的權(quán)限控制。行級(jí)別安全通過使用謂詞函數(shù)和安全策略實(shí)現(xiàn)，不需要修改各種DML代碼，伴隨著現(xiàn)有代碼即可實(shí)現(xiàn)。

超強(qiáng)干貨來襲 云風(fēng)專訪：近40年碼齡，通宵達(dá)旦的技術(shù)人生

總結(jié)

以上是生活随笔為你收集整理的SQL Server 2016 行级别权限控制的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。