当前位置：Gxlcms > 数据库问题 > SQL Server ->> Row-Level Security

SQL Server ->> Row-Level Security

时间：2021-07-01 10:21:17 帮助过：2人阅读

USER Martin WITHOUT LOGIN; CREATE USER Sara WITHOUT LOGIN; CREATE USER Amy WITHOUT LOGIN; CREATE TABLE dbo.Personnel ( EmployeeID INT, Name NVARCHAR(100), Department NVARCHAR(100), Position NVARCHAR(100), ReportTo INT, Salary FLOAT ); GO INSERT INTO dbo.Personnel ( EmployeeID, Name, Department, Position, ReportTo, Salary ) VALUES ( 1, ‘Martin‘, ‘Accounting‘, ‘Manager‘, NULL, 10000 ); GO INSERT INTO dbo.Personnel ( EmployeeID, Name, Department, Position, ReportTo, Salary ) VALUES ( 2, ‘Sara‘, ‘Accounting‘, ‘Accountant‘, 1, 5000 ); GO INSERT INTO dbo.Personnel ( EmployeeID, Name, Department, Position, ReportTo, Salary ) VALUES ( 3, ‘Amy‘, ‘Accounting‘, ‘Accountant‘, 1, 3000 ); GO

创建Schema和内联函数

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_SecurityPredicate(@Name AS SYSNAME)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_SecurityPredicate_result 
WHERE @Name = USER_NAME() OR USER_NAME() = ‘Martin‘;
GO

创建Security Policy

CREATE SECURITY POLICY SECPOL_PersonnelFilter
ADD FILTER PREDICATE Security.fn_SecurityPredicate(Name,Position) 
ON dbo.Personnel
WITH (STATE = ON);

授权给三个数据库用户访问表的权限

GRANT SELECT  ON dbo.Personnel TO [Amy]
GRANT SELECT  ON dbo.Personnel TO [Martin]
GRANT SELECT  ON dbo.Personnel TO [Sara]

开始测试用户Amy能访问多少行数据

EXECUTE AS USER = ‘Amy‘;
SELECT USER_NAME(),* FROM dbo.Personnel; 
REVERT;

结果

技术分享

测试用户Martin

EXECUTE AS USER = ‘Martin‘;
SELECT USER_NAME(),* FROM dbo.Personnel; 
REVERT;

结果

技术分享

测试用户Sara

EXECUTE AS USER = ‘Sara‘;
SELECT USER_NAME(),* FROM dbo.Personnel; 
REVERT;

结果

技术分享

RLS确实是SQL Server 2016的一个很有用处的特性。只是它身上也有一些”缺点“。以上面这个例子来讲，如果你需要实现CEO可以知道整个公司的工资，然后部门经理可以知道整个部门的员工的工资，甚至更复杂点。那么上面那个内联函数就变得复杂了，需要JOIN一些表去判定用户的职位高低来确定他的访问权限范围。而我们都知道在内联函数中加入表引用是很容易引发性能问题的。所以其实像上面这样的办法不是一个好的方案。真正解决办法其实是通过把用户的安全上下文存为一个字符串的变量的形式传入给内联函数，内联函数内部再去利用好这个安全上下文的信息去定义好数据访问的安全控制逻辑。这点在SQL Server 2016提供了一个SESSION_CONTEXT标量函数来配合实现这一方法。这种方法才可能是今后在SQL Server 2016 RTM发布后在现实中去使用RLS的Best Practices。

参考：

Row-Level Security

ALTER SECURITY POLICY (Transact-SQL)
DROP SECURITY POLICY (Transact-SQL)
sys.security_policies (Transact-SQL)
sys.security_predicates (Transact-SQL)

SQL Server ->> Row-Level Security

标签：

SQL Server ->> Row-Level Security

人气教程排行