SQL Server 2016新特性:行级别安全控制(Row-Level Security----RLS)

原创 2016年06月02日 09:19:45

    行级别安全控制(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,可以看到Sales1Sales2只能看到他们自己的数据,而Manager可以看到所有数据。



1.7  禁用策略后,Sales1Sales2都能看到所有数据

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








SQL Server扫盲系列——安全性专题——SQL Server 2012 Security Cookbook

SQL Server扫盲系列——安全性专题
  • DBA_Huangzj
  • DBA_Huangzj
  • 2014年07月17日 15:08
  • 6849

SQL Server2012数据库之一:安全体系以及DBA如何管理

在数据库系统的日常运维中,安全性是最为重要,也是最具有挑战性的一个方面。任何一个DBA都希望服务器足够的安全,在不增加成本的情况下保证数据万无一失。但事实上是,只要用户能够访问数据,就会有安全漏洞,因...
  • hhhhbk
  • hhhhbk
  • 2014年04月01日 12:09
  • 983

SQL Server 2016 新功能之综述

SQL Server2016 新功能之综述 冬去春来,发现之前最后一篇写在2012年,又过去了5年了,时间如飞啊。那时候SQL 2012 发布让人兴奋了一把,哪知道时间如刀,刀刀催人老啊,今天SQ...
  • capsicum29
  • capsicum29
  • 2017年02月18日 15:10
  • 2575

SQL Server 2016新特性: Temporal table

曾经我们遇过,误删(或更新)数据的情况,补救措施通常是还原数据库,然后把数据还原回来,整个过程比较繁琐耗时。SQL Server 2016引入了一个新的功能,即Temporaltable,我们可以称...
  • Burgess_Liu
  • Burgess_Liu
  • 2016年06月02日 08:12
  • 2012

SQL Server 2016新特性:行级别安全控制(Row-Level Security----RLS)

行级别安全控制(Row-Level Security----RLS)能够让我们根据用户执行查询的特性,来控制对数据库表中的数据行进行访问。RSL能够简化应用程序中安全的设计与编写代码,实现对数据行的访...
  • Burgess_Liu
  • Burgess_Liu
  • 2016年06月02日 09:19
  • 1406

SQL SERVER AGENT SECURITY

非常不错的文章:APPLICATION   SECURITY,   INC.   |   WWW.APPSECINC.COM     HUNTING   FLAWS   IN   SQL   SERV...
  • superdinosaur520
  • superdinosaur520
  • 2007年05月07日 15:59
  • 936

SQL SERVER2016使用经验总结

最近重新安装了SQL SERVER 2016,现总结一下在使用过程中的相关经验。 1.在还原数据库的时候。...
  • Andrewniu
  • Andrewniu
  • 2017年11月13日 11:10
  • 207

SQL Server 2016新特性: 对JSON的支持

SQL Server 2005开始支持XML数据类型,提供原生的XML数据类型、XML索引及各种管理或输出XML格式的函数。随着JSON的流行,SQL Server2016开始支持JSON数据类型,...
  • Burgess_Liu
  • Burgess_Liu
  • 2016年06月01日 17:15
  • 4000

Content Security Policy介绍

本文介绍的是W3C的Content Security Policy,简称CSP。顾名思义,这个规范与内容安全有关,主要是用来定义页面可以加载哪些资源,减少XSS的发生。 Chrome扩展已经引入了C...
  • usoa
  • usoa
  • 2016年09月07日 16:31
  • 916

sql server系统表详细说明

sysaltfiles  主数据库 保存数据库的文件 syscharsets  主数据库字符集与排序顺序 sysconfigures 主数据库 配置选项  syscurconfigs ...
  • diligentcat
  • diligentcat
  • 2016年09月04日 08:13
  • 1363
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server 2016新特性:行级别安全控制(Row-Level Security----RLS)
举报原因:
原因补充:

(最多只允许输入30个字)