SQL Server使用视图做权限控制-阿里云开发者社区

开发者社区> 风移> 正文

SQL Server使用视图做权限控制

简介: # 问题引入 这天老鸟火急火燎的跑到菜鸟旁边,想必是遇到什么难题了:“现在有这么一个场景,假如有三种角色,并且存在层级关系,他们需要访问同一个数据源表,但是需要做权限控制,使得每种角色只能看到自己及以下层级的数据。比如:公司有CEO,Manger和普通的employee三种角色,CEO可以查看CEO、Manager和employee层级的数据;Manger只能查看Manger和employee的
+关注继续查看

问题引入

这天老鸟火急火燎的跑到菜鸟旁边,想必是遇到什么难题了:“现在有这么一个场景,假如有三种角色,并且存在层级关系,他们需要访问同一个数据源表,但是需要做权限控制,使得每种角色只能看到自己及以下层级的数据。比如:公司有CEO,Manger和普通的employee三种角色,CEO可以查看CEO、Manager和employee层级的数据;Manger只能查看Manger和employee的数据,不能查看CEO层级;而employee只能查看employee的数据,不能查看CEO和Manager级别的数据。这个在SQL Server有比较简单清爽的实现方法吗?”。老鸟这个问题的确问得非常有水准,这个场景也非常普遍,菜鸟顿时陷入了无边的困境。

问题分析

在关系型数据库SQL Server中,权限的确不能达到行列级别这么细粒度的控制,这也是菜鸟为什么陷入困境的原因。但是,突然菜鸟灵魂出窍,灵光一现,像是被雷劈中一般的感觉:虽然SQL Server基于表无法达到那么细粒度的权限控制,但是我们可以建立视图(VIEW),用视图来建立正式表的行、列过滤,然后在视图对象上做权限控制,最终达到对三个层级的权限控制的目的,想到这里菜鸟立马赫然开朗。

解决问题

菜鸟越想越激动,说打就打,说干就干,于是开始了万里长征。

测试环境准备

创建测试数据库Test,接着创建三个用户CEO,Manager和employee,然后创建测试表tb_Test_ViewPermission,最后插入三条测试数据,每个层级一条数据。

IF DB_ID('Test') IS NULL
    CREATE DATABASE Test;
GO

USE Test
GO

--create three logins(CEO, manager, employee)
--create login CEO
IF EXISTS(
            SELECT *
            FROM sys.syslogins
            WHERE name = 'CEO')
BEGIN
      DROP LOGIN CEO;
END
GO
CREATE LOGIN CEO with password='CEODbo',check_policy = off;
GO

--create user CEO
IF USER_ID('CEO') is not null
        DROP USER CEO;
GO
CREATE USER CEO FOR LOGIN CEO;
GO

--create login Manager
IF EXISTS(
            SELECT *
            FROM sys.syslogins
            WHERE name = 'Manager')
BEGIN
      DROP LOGIN Manager;
END
GO
CREATE LOGIN Manager with password='ManagerDbo',check_policy = off;
GO

--create user manager
IF USER_ID('Manager') is not null
        DROP USER Manager;
GO
CREATE USER Manager FOR LOGIN Manager;
GO
       
--create login employee
IF EXISTS(
            SELECT *
            FROM sys.syslogins
            WHERE name = 'employee')
BEGIN
        DROP LOGIN employee;
END
GO
CREATE LOGIN employee with password='employeeDbo',check_policy = off;
GO

--create user employee
IF USER_ID('employee') is not null
        DROP USER employee
GO
CREATE USER employee FOR LOGIN employee;
GO

--create basic TABLE
IF OBJECT_ID('dbo.tb_Test_ViewPermission','u')is not null
    DROP TABLE dbo.tb_Test_ViewPermission
;
GO
CREATE TABLE dbo.tb_Test_ViewPermission
(
        id int identity(1,1) not null primary key
        ,name varchar(20) not null
        ,level_no int not null
        ,title varchar(20) null
        ,viewByCEO char(1) not null
        ,viewByManager char(1) not null
        ,viewByEmployee char(1) not null
        ,salary decimal(9,2) not null
);

--data init.
INSERT INTO dbo.tb_Test_ViewPermission
SELECT 'AA',0,'CEO','Y','Y','Y',1000000.0
union all
SELECT 'BB',1,'Manager','Y','Y','Y',100000.0
union all
SELECT 'CC',2,'employee','Y','Y','Y',10000.0
;
GO

创建三个视图

表对象和数据准备完毕后,接着我们建立三个视图,分别过滤出自己所在层级及以下层级的数据。比如,CEO包含CEO、Manager和employee层级数据;Manger包含Manger和employee层级数据;employee仅包含employee层级数据。

USE Test
GO
--create views for CEO querying, CEO can get all the data
IF OBJECT_ID('dbo.v_employeeinfo_forCEO','v')is not null
        DROP VIEW dbo.v_employeeinfo_forCEO
;
GO
CREATE VIEW dbo.v_employeeinfo_forCEO
AS
SELECT *
FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)
WHERE level_no >= 0;
GO

--create views for Manager querying, Manger can get manger group & employee group data
IF OBJECT_ID('dbo.v_employeeinfo_forManager','v')is not null
        DROP VIEW dbo.v_employeeinfo_forManager
;
Go
CREATE VIEW dbo.v_employeeinfo_forManager
AS
SELECT 
        name
        ,level_no
        ,title
        ,viewByManager
        ,viewByEmployee
        ,salary
FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)
WHERE level_no >= 1; 
GO

--create views for Employee querying, employee just can get employee group data
IF OBJECT_ID('dbo.v_employeeinfo_forEmployee','v')is not null
        DROP VIEW dbo.v_employeeinfo_forEmployee
;
GO
CREATE VIEW dbo.v_employeeinfo_forEmployee
AS
SELECT 
        name
        ,level_no
        ,title
        ,viewByEmployee
        ,salary
FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)
WHERE  level_no >= 2;
GO

权限设置

所有视图创建完毕后,接下来是最为关键的步骤,就是对视图权限的设置。基本的思路是:拿掉所有用户对于基表的权限,对于视图需要拿掉自己以下层级用户权限,然后给予视图自己层级用户的查看权限。比如:Manager层级视图dbo.v_employeeinfo_forManager需要拿掉employee的权限,授予Manager查询权限。

USE Test
GO
--====permission init.
--deny all permission to user for TABLE
DENY ALL ON dbo.tb_Test_ViewPermission TO CEO;
DENY ALL ON dbo.tb_Test_ViewPermission TO Manager;
DENY ALL ON dbo.tb_Test_ViewPermission TO employee;

--deny permission for Manager & employee
DENY ALL ON dbo.v_employeeinfo_forCEO TO Manager;
DENY ALL ON dbo.v_employeeinfo_forCEO TO employee;

DENY ALL ON dbo.v_employeeinfo_forManager TO employee;

--Grant query permission for CEO & Manager & Employee
GRANT SELECT ON dbo.v_employeeinfo_forCEO TO CEO;

GRANT SELECT ON dbo.v_employeeinfo_forManager TO Manager;

GRANT SELECT ON dbo.v_employeeinfo_forEmployee TO employee;
GO

权限测试

以上所有工作准备完毕后,接下来就是最紧张的权限验证环节了,时间才是检验真理的唯一标准。

CEO权限测试

按照预期,CEO应该不能访问基表数据,会报告异常,但是可以查询CEO,manager和employee层级数据,总共三条。测试语句如下,将SSMS的结果显示切换为text模式,或者直接快捷键ctrl + t。

--CEO query test
USE test
GO

--CEO cann't read data from basic table
SELECT *
FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)

--CEO all read the data from CEO group
SELECT 
        CAST(CURRENT_USER AS VARCHAR(10)) AS 'Who am i'
        ,*
FROM v_employeeinfo_forCEO WITH(NOLOCK)
GO

结果显示如下,测试结果的确与预期吻合。
01.png

Manager权限测试

预期是Manger对基表没有访问权限,也没有CEO视图的访问权限,但是可以查看到Manger和普通employee的数据,也就是会返回两条数据。

--Manager query test
use test
GO

--Manager cann't read data from basic table
SELECT *
FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)

--Manager can't read the data from CEO group
SELECT *
FROM v_employeeinfo_forCEO WITH(NOLOCK)
GO

--manager can read data from manager group
SELECT 
        CAST(CURRENT_USER AS VARCHAR(10)) AS 'Who am i'
        ,*
FROM dbo.v_employeeinfo_forManager WITH(NOLOCK)
GO

查询结果展示如下,测试结果再次与预期吻合。
02.png

Employee权限测试

预期是employee没有基表权限,没有CEO视图查看权限,也没有Manager视图查询权限,只能看到employee层级数据,也就是会返回一条数据。

--Employee query test
USE test
GO

--Employee cann't read data from basic table
SELECT *
FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)

--Employee can't read the data from CEO group
SELECT *
FROM v_employeeinfo_forCEO WITH(NOLOCK)
GO

--Employee can't read data from manager group
SELECT 
        CAST(CURRENT_USER AS VARCHAR(10)) AS 'Who am i'
        ,*
FROM dbo.v_employeeinfo_forManager WITH(NOLOCK)
GO

--Employee just can read data from employee group
SELECT 
        CAST(CURRENT_USER AS VARCHAR(10)) AS 'Who am i'
        ,*
FROM dbo.v_employeeinfo_forEmployee  WITH(NOLOCK)
GO

结果显示如下,employee层级测试结果也完全满足预期。
03.png

写在最后

从测试结果来看,SQL Server使用视图来做权限控制方法是相当的清爽和彻底满足老鸟预期的。于是菜鸟得意洋洋的来到老鸟办公室,霸气的展示了自己的解决方案和例子,老鸟觉得非常满意。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle学习笔记之与权限相关的视图
Oracle学习笔记之与权限相关的视图
106 0
MySQL 基础---视图
视图: 本质上是一种虚拟表,其内容与真实表相似,包含一系列带有名称的列和行数据。 视图的特点如下: 视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系。
661 0
使用SQL Server 2005作业设置定时任务
原文:使用SQL Server 2005作业设置定时任务 公司有一个老项目由于直接把终端拍摄的图片以二进制的形式保存到数据库中,数据库比较大所以需要经常删除这些冗余数据,手动删除费时费力,项目组长让我把这些操作变成自动的,每天执行一次,只保留最近两个月的图片数据。
619 0
mysql 系统用户最大文件打开数限制
mysql 系统用户最大文件打开数限制 纸上得来终觉浅,绝知此事多宕机...记录一下自己很蠢的一次故障处理过程。   上周的时候,一个刚上线的系统又开始反映登不上了,因为最近这个系统也老是出现这个问题,开发也一直在找问题中,所以也没太在意。
945 0
iOS系统菜单控制器UIMenuController使用简介(一)
iOS系统菜单控制器UIMenuController使用简介
12 0
SQL Server修改表结构后批量更新所有视图
最近修改了数据库表结构,数据同步的时候出了问题,发现很多数据明明已经修改,但是通过视图筛选出来的还是原来的数据,所以怀疑应该是视图缓存了数据,在园子里找到下面的博文,在这里做个记录备忘。   原文链接:http://www.cnblogs.com/yashen/archive/2004/12/23/81000.html   我们在使用SqlServer时经常遇到这种情况,当修改某个表的结构后,相关的视图就不对了而导致程序错误,因此就有个下面这个存储过程。
870 0
SSM框架之关于使用JSP作为视图展示问题解决方案
JSP作为视图层展示数据,已经有很长一段时间了,不管是在校学习还是企业工作,总会或多或少接触这个。特别是对于一些传统中小型或者一些几年前的企业而言,有很多使用JSP作为视图展示层。 JSP本质是就是Servlet,Servlet可以说也是JSP。
1521 0
+关注
风移
阿里云数据库专家,负责SQL Server数据库产品线。SQL Server从业10年,经历过SQL 2000、SQL 2005、SQL 2008、SQL 2008R2、SQL 2012、SQL 2014、SQL 2016和SQL on Linux各个版本。
75
文章
46
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载