SQL Server 2016新特性：行级别安全控制（Row-Level Security----RLS）

行级别安全控制(Row-Level Security----RLS)能够让我们根据用户执行查询的特性，来控制对数据库表中的数据行进行访问。RSL能够简化应用程序中安全的设计与编写代码，实现对数据行的访问限制。访问限制的逻辑位于数据库层，而不是在应用程序层分离数据。比如，我们希望各部门的经理只能查看他所在部门的员工的薪资情况，医院的护士只能查看自己所负责的病人的状况等。以往像要实现这样的功能，一般要通过视图或者应用程序层去实现，在提交T-SQL之前，通过WHERE条件来实现数据过滤。而SQL Server 2016引入的RLS，则可以更加简便地实现行级别权限控制。RLS是通过创建安全策略(securitypolicy)和内联表值函数(inline table valued functions)来实现的，RLS过滤判定在功能上相当于WHERE语句。

微软的建议：

为RSL对象（判定函数及安全策略）创建单独的schema
ALTER ANYSECURITY POLICY权限专为高度特权用户（比如安全策略管理者）而设。安全策略管理者对他们所保护的表不需要SELECT权限。
为了避免潜在的运行时错误，在判定函数中要避免类型转换。
只要有可能，要避免在判定函数中使用递归，从而避免性能下降。查询优化器会尝试发现直接的递归，但不能保证发现间接的递归。
为了性能最佳化，判定函数中要避免使用过多的表连接。

下面通过MSDN上的示例，感受一下RLS：

示例1：创建3个用户，创建一张表并塞入6笔数据，然后为该表创建一个内联表值函数和一个安全策略，最后你可以看到SELECT语句是如何为不同用户过滤数据的。

1.1 创建3个用户

CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;

1.2 建表并塞入6笔数据

CREATE TABLE Sales
(
OrderID int,
SalesRep sysname,
Product varchar(10),
Qty int
);
INSERT Sales VALUES
(1, 'Sales1', 'Valve', 5),
(2, 'Sales1', 'Wheel', 2),
(3, 'Sales1', 'Valve', 4),
(4, 'Sales2', 'Bracket', 2),
(5, 'Sales2', 'Wheel', 5),
(6, 'Sales2', 'Seat', 5);

1.3 为每个用户授读权限

GRANT SELECT ON Sales TO Manager;
GRANT SELECT ON Sales TO Sales1;
GRANT SELECT ON Sales TO Sales2;

1.4 创建一个新的schema和一个内联表值函数。

CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

1.5 创建一个安全策略，把内联函数作为过滤判定，状态必须设置为ON

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);

1.6 测试select，可以看到Sales1和Sales2只能看到他们自己的数据，而Manager可以看到所有数据。

1.7 禁用策略后，Sales1和Sales2都能看到所有数据

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

示例2：该示例演示了中间层应用程序如何实现连接过滤。应用程序在连接数据库之后，在SESSION_CONTEXT里设置当前的应用程序用户ID，这样，安全策略就能过滤掉该ID不该看到的数据，也能阻止插入用户为错误的ID插入数据。

2.1 建表并塞入6笔数据

CREATE TABLE Sales (
OrderId int,
AppUserId int,
Product varchar(10),
Qty int
);
INSERT Sales VALUES
(1, 1, 'Valve', 5),
(2, 1, 'Wheel', 2),
(3, 1, 'Valve', 4),
(4, 2, 'Bracket', 2),
(5, 2, 'Wheel', 5),
(6, 2, 'Seat', 5);

2.2 创建一个低特权用户，应用程式会使用它来连接

-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;
-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;

2.3 创建一个新的schema和判定函数，该函数使用存放在SESSION_CONTEXT里的应用user ID来过滤数据行

CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO

2.4 创建安全策略

CREATE SECURITY POLICY Security.SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
ON dbo.Sales,
ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
ON dbo.Sales AFTER INSERT
WITH (STATE = ON);

2.5 模拟连接过滤，在SESSION_CONTEXT里设置不同的user ID，然后查询表Sales。实际应用中，应用程序负责在打开连接后，在SESSIION_CONTEXT里设置当前的user ID。

EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO

-- Note: @read_only prevents the value from changing again
-- until the connection is closed (returned to the connection pool)
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=0;
SELECT * FROM Sales;
GO
INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO
REVERT;
GO

(责任编辑：IT)

搜索

热门标签:

SQL Server 2016新特性：行级别安全控制（Row-Level Security----RLS）