MsSQL Server 2016行级别访问权限控制

2024-04-13 06:48

本文主要是介绍MsSQL Server 2016行级别访问权限控制,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

背景

假如我们有关键数据存储在一个表里面,比如人员表中包含员工、部门和薪水信息。只允许用户访问各自部门的信息,但是不能访问其他部门。一般我们都是在程序端实现这个功能,而在sqlserver2016以后也可以直接在数据库端实现这个功能。

解决

安全已经是一个数据方面的核心问题,每一代的MS数据库都有关于安全方面的新功能,那么在Sql Server 2016,也有很多这方面的升级,比如‘Row Level Security’, ‘Always Encrypted’, ‘Dynamic Data Masking’, 和‘Enhancement of Transparent Data Encryption’ 等等都会起到安全方面的作用。本篇我将介绍关于Row Level Security (RLS--行级别安全), 能够控制表中行的访问权限。RLS 能使我们根据执行查询人的属性来控制基础数据,从而帮助我们容易地为不同用户提透明的访问数据。行级安全性使客户能够根据执行查询的用户的特性控制数据库中的行。

为了实现RLS我们需要准备下面三个方面:

  1. 谓词函数
  2. 安全谓词
  3. 安全策略

逐一描述上面三个方面

谓词函数

谓词函数是一个内置的表值函数,用于检查用户执行的查询访问数据是否基于其逻辑定义。这个函数返回一个1来表示用户可以访问。

安全谓词

安全谓词就是将谓词函数绑定到表里面,RLS提供了两种安全谓词:过滤谓词和阻止谓词。过滤谓词就是在使用SELECT, UPDATE, 和 DELETE语句查询数据时只是过滤数据但是不会报错。而阻止谓词就是在使用违反谓词逻辑的数据时,显示地报错并且阻止用户使用 AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE 等操作。

安全策略

安全策略对象专门为行级别安全创建,分组所有涉及谓词函数的安全谓词。

实例

实例中我们创建一个Person表和测试数据,最后我们让不懂得用户访问各自部门的信息,代码如下:

复制代码

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

复制代码

 

 

此时表已经被创建,并且插入了测试数据,执行下面语句检索有是有的记录:

SELECT * FROM Person

clip_image001

正如所示,目前有三个部门department(CS,EC,IT),并且User_Access列表示各自的用户组。让我们创建三个测试用户数据的账户语句如下:

复制代码

--For CS departmentCREATE USER User_CS WITHOUT LOGIN--For EC departmentCREATE USER User_EC WITHOUT LOGIN-- For IT DepartmentCREATE USER User_IT WITHOUT LOGIN

复制代码

 

在创建了用户组以后,授权读取权限给上面是哪个新建的用户,执行语句如下:

复制代码

---授予select权限给所有的用户GRANT SELECT ON Person TO User_CSGRANT SELECT ON Person TO User_ECGRANT SELECT ON Person TO User_IT

复制代码

 

现在我们创建一个谓词函数,该函数是对于查询用户是不可见的。

复制代码

----Create functionCREATE FUNCTION dbo.PersonPredicate( @User_Access AS varchar(50) )RETURNS TABLEWITH SCHEMABINDINGASRETURN SELECT 1 AS AccessRightWHERE @User_Access = USER_NAME()GO

复制代码

 

 

这个函数是只返回行,如果正在执行查询的用户的名字与User_Access 列匹配,那么用户允许访问指定的行。在创建该函数后,还需要创建一个安全策略,使用上面的谓词函数PersonPredicate来对表进行过滤逻辑的绑定,脚本如下:

复制代码

--安全策略CREATE SECURITY POLICY PersonSecurityPolicyADD FILTER PREDICATE dbo.PersonPredicate(User_Access) ON dbo.PersonWITH (STATE = ON)

复制代码

 

 

State(状态)为ON才能是策略生效,如果打算关闭策略,你可以改变状态为OFF。

再来看一下查询结果:

clip_image002

这次查询没有返回任何行,这意味着谓词函数的定义和策略的创建后,用户查询需要具有相应权限才能返回行,接下来使用不同用户来查询这个数据,首先,我们用用户User_CS来查询一下结果:

EXECUTE AS USER = 'User_CS'SELECT * FROM dbo.PersonREVERT

 

 

正如所示,我们看到只有三行数据数据该用户,User_CS,已经检索出来。因此,过滤函数将其他不属于该用户组的数据过滤了。

实际上这个查询执行的过程就是数据库内部调用谓词函数,如下所示:

SELECT * FROM dbo.Person

WHERE User_Name() = 'User_CS'

其他两组用户的查询结果是相似的这里就不一一演示了。

因此,我们能看到执行查询根据用的不同得到只属于指定用户组的指定数据。这就是我们要达成的目的。

到目前为止,我们已经演示了过滤谓词,接下来我们演示一下如何阻止谓词。执行如下语句来授权DML操作权限给用户。

复制代码

--授权DML 权限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执行插入语句,并且插入用户组为UserCS的,语句如下:

复制代码

EXECUTE AS USER = 'User_IT'INSERT INTO Person (PersonName, Department, Salary, User_Access)SELECT 'Soniya', 'CS', 35000, 'User_CS'REVERT

复制代码

 

but,竟然没有报错,插入成功了。

让我们在检查一下用户数据插入的情况:

EXECUTE AS USER = 'User_IT'SELECT * FROM dbo.PersonREVERT

 

奇怪,新插入行并没有插入到该用户组'User_IT'中。而是出现在了'User_CS' 的用户组数据中。

复制代码

--插入数据出现在了不同的用户组EXECUTE AS USER = 'User_CS'SELECT * FROM dbo.PersonREVERT

复制代码

 

clip_image005

通过上面的例子我们发现,过滤谓词不不会阻止用户插入数据,因此没有错误,这是因为没有在安全策略中定义阻止谓词。让我们加入阻止谓词来显示报错,有四个阻止谓词AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, 和 BEFORE DELETE可以使用。我们这里测试使用AFTER INSERT 谓词。这个谓词阻止用户插入记录到没有权限查看的数据用户组。

添加谓词阻止的安全策略,代码如下:

复制代码

--添加阻止谓词ALTER SECURITY POLICY PersonSecurityPolicyADD BLOCK PREDICATE dbo.PersonPredicate(User_Access)ON dbo.Person AFTER INSERT

复制代码

 

现在我们用之前类似代码再试一下,是否可以插入数据:

复制代码

EXECUTE AS USER = 'User_CS'INSERT INTO Person (PersonName, Department, Salary, User_Access)SELECT 'Sumit', 'IT', 35000, 'User_IT'REVERT

复制代码

 

1

 

擦,果然这次错误出提示出现了,阻止了不同权限用户的插入。因此我们能说通过添加阻止谓词,未授权用户的DML操作被限制了。

注意:在例子中每个部门只有一个用户组成。如果在一个部门包含多个用户的情况下,我们需要创建分支登录为每个用户都分配需要的权限,因为谓词函数应用于用户基础并且安全策略取决于谓词函数。

 

行级别安全的限制

这里有几个行级别安全的限制:

  1. 谓词函数一定要带有WITH SCHEMABINDING关键词,如果函数没有该关键字则绑定安全策略时会抛出异常。
  2. 在实施了行级别安全的表上不能创建索引视图。
  3. 内存数据表不支持
  4. 全文索引不支持

总结

带有行级别安全功能的SQLServer2016,我们可以不通过应用程序级别的代码修改来实现数据记录的权限控制。行级别安全通过使用谓词函数和安全策略实现,不需要修改各种DML代码,伴随着现有代码即可实现。

这篇关于MsSQL Server 2016行级别访问权限控制的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/899420

相关文章

Spring Security 基于表达式的权限控制

前言 spring security 3.0已经可以使用spring el表达式来控制授权,允许在表达式中使用复杂的布尔逻辑来控制访问的权限。 常见的表达式 Spring Security可用表达式对象的基类是SecurityExpressionRoot。 表达式描述hasRole([role])用户拥有制定的角色时返回true (Spring security默认会带有ROLE_前缀),去

安卓链接正常显示,ios#符被转义%23导致链接访问404

原因分析: url中含有特殊字符 中文未编码 都有可能导致URL转换失败,所以需要对url编码处理  如下: guard let allowUrl = webUrl.addingPercentEncoding(withAllowedCharacters: .urlQueryAllowed) else {return} 后面发现当url中有#号时,会被误伤转义为%23,导致链接无法访问

两个月冲刺软考——访问位与修改位的题型(淘汰哪一页);内聚的类型;关于码制的知识点;地址映射的相关内容

1.访问位与修改位的题型(淘汰哪一页) 访问位:为1时表示在内存期间被访问过,为0时表示未被访问;修改位:为1时表示该页面自从被装入内存后被修改过,为0时表示未修改过。 置换页面时,最先置换访问位和修改位为00的,其次是01(没被访问但被修改过)的,之后是10(被访问了但没被修改过),最后是11。 2.内聚的类型 功能内聚:完成一个单一功能,各个部分协同工作,缺一不可。 顺序内聚:

控制反转 的种类

之前对控制反转的定义和解释都不是很清晰。最近翻书发现在《Pro Spring 5》(免费电子版在文章最后)有一段非常不错的解释。记录一下,有道翻译贴出来方便查看。如有请直接跳过中文,看后面的原文。 控制反转的类型 控制反转的类型您可能想知道为什么有两种类型的IoC,以及为什么这些类型被进一步划分为不同的实现。这个问题似乎没有明确的答案;当然,不同的类型提供了一定程度的灵活性,但

red5-server源码

red5-server源码:https://github.com/Red5/red5-server

深入解析秒杀业务中的核心问题 —— 从并发控制到事务管理

深入解析秒杀业务中的核心问题 —— 从并发控制到事务管理 秒杀系统是应对高并发、高压力下的典型业务场景,涉及到并发控制、库存管理、事务管理等多个关键技术点。本文将深入剖析秒杀商品业务中常见的几个核心问题,包括 AOP 事务管理、同步锁机制、乐观锁、CAS 操作,以及用户限购策略。通过这些技术的结合,确保秒杀系统在高并发场景下的稳定性和一致性。 1. AOP 代理对象与事务管理 在秒杀商品

PostgreSQL中的多版本并发控制(MVCC)深入解析

引言 PostgreSQL作为一款强大的开源关系数据库管理系统,以其高性能、高可靠性和丰富的功能特性而广受欢迎。在并发控制方面,PostgreSQL采用了多版本并发控制(MVCC)机制,该机制为数据库提供了高效的数据访问和更新能力,同时保证了数据的一致性和隔离性。本文将深入解析PostgreSQL中的MVCC功能,探讨其工作原理、使用场景,并通过具体SQL示例来展示其在实际应用中的表现。 一、

Golang进程权限调度包runtime

关于 runtime 包几个方法: Gosched:让当前线程让出 cpu 以让其它线程运行,它不会挂起当前线程,因此当前线程未来会继续执行GOMAXPROCS:设置最大的可同时使用的 CPU 核数Goexit:退出当前 goroutine(但是defer语句会照常执行)NumGoroutine:返回正在执行和排队的任务总数GOOS:目标操作系统NumCPU:返回当前系统的 CPU 核数量 p

vue2实践:el-table实现由用户自己控制行数的动态表格

需求 项目中需要提供一个动态表单,如图: 当我点击添加时,便添加一行;点击右边的删除时,便删除这一行。 至少要有一行数据,但是没有上限。 思路 这种每一行的数据固定,但是不定行数的,很容易想到使用el-table来实现,它可以循环读取:data所绑定的数组,来生成行数据,不同的是: 1、table里面的每一个cell,需要放置一个input来支持用户编辑。 2、最后一列放置两个b

android java.io.IOException: open failed: ENOENT (No such file or directory)-api23+权限受权

问题描述 在安卓上,清单明明已经受权了读写文件权限,但偏偏就是创建不了目录和文件 调用mkdirs()总是返回false. <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/><uses-permission android:name="android.permission.READ_E