SQL Server 2016 行级别权限控制
背景
假如我們有關(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è)方面:
逐一描述上面三個(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í)別安全的限制:
總結(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)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 把日出拍成大片的手机!大光圈+黑白彩色双
- 下一篇: golang大量字符串拼接方法