SQL Server 2016新特性:行级别安全控制(Row-Level Security----RLS)
来源:互联网 发布:途家体验反馈数据分析 编辑:程序博客网 时间:2024/05/21 10:28
行级别安全控制(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;GOCREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname) RETURNS TABLEWITH SCHEMABINDINGAS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
1.5 创建一个安全策略,把内联函数作为过滤判定,状态必须设置为ON
CREATE SECURITY POLICY SalesFilterADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) ON dbo.SalesWITH (STATE = ON);
1.6 测试select,可以看到Sales1和Sales2只能看到他们自己的数据,而Manager可以看到所有数据。
1.7 禁用策略后,Sales1和Sales2都能看到所有数据
ALTER SECURITY POLICY SalesFilterWITH (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 demoCREATE USER AppUser WITHOUT LOGIN; GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;-- Never allow updates on this columnDENY UPDATE ON Sales(AppUserId) TO AppUser;
2.3 创建一个新的schema和判定函数,该函数使用存放在SESSION_CONTEXT里的应用user ID来过滤数据行
CREATE SCHEMA Security;GOCREATE FUNCTION Security.fn_securitypredicate(@AppUserId int) RETURNS TABLE WITH SCHEMABINDINGAS 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;GOINSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user IDGOREVERT;GO
- SQL Server 2016新特性:行级别安全控制(Row-Level Security----RLS)
- SQL优化经典案例----RLS(ROW LEVEL SECURITY)
- SQL优化经典案例----RLS(ROW LEVEL SECURITY)
- ROW LEVEL SECURITY(RLS) 性能问题
- SQL Server 2012 安全新特性:包含数据库
- 关于SQL Server 2016行级安全、数据掩码特性
- SQL Server 2016实用新特性
- SQL Server 2016新特性: Temporal table
- sql server行版本控制的隔离级别
- sql server行版本控制的隔离级别
- SQL Server 2005新特性
- SQL Server 2005新特性
- SQL Server 2005新特性
- SQL Server 2005新特性
- SQL Server 2005新特性
- sql server 2008新特性
- sql server 2008 新特性
- SQL Server 2014新特性
- 第十二周上机实践项目1(1):实现复数类中的运算符重载
- 第十二周项目训练1.1 阅读程序
- 第十 十一周点-圆-圆柱类族的设计(2)
- C/C++ 读取16进制文件
- 第十二周项目二CTime类中的运算符重载
- SQL Server 2016新特性:行级别安全控制(Row-Level Security----RLS)
- 第十四周项目1——排序函数模板
- 第九周阅读程序2
- HDU 不容易系列之一
- super关键字的使用
- Java继承,子类默认在构造函数中用super()调用父类构造函数
- Android Studio系列-签名打包
- 第十 十一周点-圆-圆柱类族的设计(3)
- 最新android studio 第三方库包导入方法jar,so,module