权限控制 SQL 视图
来源:互联网 发布:mac os x 11.镜像下载 编辑:程序博客网 时间:2024/06/07 10:05
Q:
“现在有这么一个场景,假如有三种角色,并且存在层级关系,他们需要访问同一个数据源表,但是需要做权限控制,使得每种角色只能看到自己及以下层级的数据。比如:公司有CEO,Manger和普通的employee三种角色,CEO可以查看CEO、Manager和employee层级的数据;Manger只能查看Manger和employee的数据,不能查看CEO层级;而employee只能查看employee的数据,不能查看CEO和Manager级别的数据。
--1--
创建测试数据库Test,接着创建三个用户CEO,Manager和employee,然后创建测试表tb_Test_ViewPermission,最后插入三条测试数据,每个层级一条数据。
IF DB_ID('Test') IS NULL CREATE DATABASE Test;GOUSE TestGO--create three logins(CEO, manager, employee)--create login CEOIF EXISTS( SELECT * FROM sys.syslogins WHERE name = 'CEO')BEGIN DROP LOGIN CEO;ENDGOCREATE LOGIN CEO with password='CEODbo',check_policy = off;GO--create user CEOIF USER_ID('CEO') is not null DROP USER CEO;GOCREATE USER CEO FOR LOGIN CEO;GO--create login ManagerIF EXISTS( SELECT * FROM sys.syslogins WHERE name = 'Manager')BEGIN DROP LOGIN Manager;ENDGOCREATE LOGIN Manager with password='ManagerDbo',check_policy = off;GO--create user managerIF USER_ID('Manager') is not null DROP USER Manager;GOCREATE USER Manager FOR LOGIN Manager;GO--create login employeeIF EXISTS( SELECT * FROM sys.syslogins WHERE name = 'employee')BEGIN DROP LOGIN employee;ENDGOCREATE LOGIN employee with password='employeeDbo',check_policy = off;GO--create user employeeIF USER_ID('employee') is not null DROP USER employeeGOCREATE USER employee FOR LOGIN employee;GO--create basic TABLEIF OBJECT_ID('dbo.tb_Test_ViewPermission','u')is not null DROP TABLE dbo.tb_Test_ViewPermission;GOCREATE 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_ViewPermissionSELECT 'AA',0,'CEO','Y','Y','Y',1000000.0union allSELECT 'BB',1,'Manager','Y','Y','Y',100000.0union allSELECT 'CC',2,'employee','Y','Y','Y',10000.0;GO
创建三个视图
表对象和数据准备完毕后,接着我们建立三个视图,分别过滤出自己所在层级及以下层级的数据。比如,CEO包含CEO、Manager和employee层级数据;Manger包含Manger和employee层级数据;employee仅包含employee层级数据。
USE TestGO--create views for CEO querying, CEO can get all the dataIF OBJECT_ID('dbo.v_employeeinfo_forCEO','v')is not null DROP VIEW dbo.v_employeeinfo_forCEO;GOCREATE VIEW dbo.v_employeeinfo_forCEOASSELECT *FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)WHERE level_no >= 0;GO--create views for Manager querying, Manger can get manger group & employee group dataIF OBJECT_ID('dbo.v_employeeinfo_forManager','v')is not null DROP VIEW dbo.v_employeeinfo_forManager;GoCREATE VIEW dbo.v_employeeinfo_forManagerASSELECT name ,level_no ,title ,viewByManager ,viewByEmployee ,salaryFROM dbo.tb_Test_ViewPermission WITH(NOLOCK)WHERE level_no >= 1; GO--create views for Employee querying, employee just can get employee group dataIF OBJECT_ID('dbo.v_employeeinfo_forEmployee','v')is not null DROP VIEW dbo.v_employeeinfo_forEmployee;GOCREATE VIEW dbo.v_employeeinfo_forEmployeeASSELECT name ,level_no ,title ,viewByEmployee ,salaryFROM dbo.tb_Test_ViewPermission WITH(NOLOCK)WHERE level_no >= 2;GO
权限设置
所有视图创建完毕后,接下来是最为关键的步骤,就是对视图权限的设置。基本的思路是:拿掉所有用户对于基表的权限,对于视图需要拿掉自己以下层级用户权限,然后给予视图自己层级用户的查看权限。比如:Manager层级视图dbo.v_employeeinfo_forManager需要拿掉employee的权限,授予Manager查询权限。
USE TestGO--====permission init.--deny all permission to user for TABLEDENY 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 & employeeDENY 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 & EmployeeGRANT 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 testUSE testGO--CEO cann't read data from basic tableSELECT *FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)--CEO all read the data from CEO groupSELECT CAST(CURRENT_USER AS VARCHAR(10)) AS 'Who am i' ,*FROM v_employeeinfo_forCEO WITH(NOLOCK)GO
结果显示如下,测试结果的确与预期吻合。
Manager权限测试
预期是Manger对基表没有访问权限,也没有CEO视图的访问权限,但是可以查看到Manger和普通employee的数据,也就是会返回两条数据。
--Manager query testuse testGO--Manager cann't read data from basic tableSELECT *FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)--Manager can't read the data from CEO groupSELECT *FROM v_employeeinfo_forCEO WITH(NOLOCK)GO--manager can read data from manager groupSELECT CAST(CURRENT_USER AS VARCHAR(10)) AS 'Who am i' ,*FROM dbo.v_employeeinfo_forManager WITH(NOLOCK)GO
查询结果展示如下,测试结果再次与预期吻合。
Employee权限测试
预期是employee没有基表权限,没有CEO视图查看权限,也没有Manager视图查询权限,只能看到employee层级数据,也就是会返回一条数据。
--Employee query testUSE testGO--Employee cann't read data from basic tableSELECT *FROM dbo.tb_Test_ViewPermission WITH(NOLOCK)--Employee can't read the data from CEO groupSELECT *FROM v_employeeinfo_forCEO WITH(NOLOCK)GO--Employee can't read data from manager groupSELECT 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 groupSELECT CAST(CURRENT_USER AS VARCHAR(10)) AS 'Who am i' ,*FROM dbo.v_employeeinfo_forEmployee WITH(NOLOCK)GO
结果显示如下,employee层级测试结果也完全满足预期。
- 权限控制 SQL 视图
- Liferay 用权限控制视图
- 控制视图页面的访问权限
- 控制视图页面的访问权限
- SQL 2005 权限控制粒度
- asp.net core mvc权限控制:在视图中控制操作权限
- asp.net core mvc权限控制:在视图中控制操作权限
- 视图中的调用者权限控制bequeath current_user
- 教程:基于访问控制的ABAP CDS视图权限
- 教程:基于访问控制的ABAP CDS视图权限
- 用SQL语句做权限控制
- SQL SERVER权限控制参数TrustWorthy
- SQL Server2008角色和权限控制
- SQL SERVER2008 存储过程、表、视图、函数的权限
- SQL SERVER2008 存储过程、表、视图、函数的权限
- SQL SERVER2008 存储过程、表、视图、函数的权限
- 数据库笔记(SQL之视图、事务、约束以及权限)
- 权限控制
- mysql完全卸载与重装
- Visio2010建立ER图并直接导出为SQL语句
- STM32学习笔记之摄像头实验(OV2640)
- hdu 1402 A * B Problem Plus(FFT)
- 3dsMax学习笔记07_用VRayMtl材质制作水晶素材
- 权限控制 SQL 视图
- 【Java】之 简易Stack
- UOJ#8. Quine
- VR系列——Oculus Publishing文档:六、商业和授权检查
- qemu 下运行lk
- mysql基础语句
- Java环境变量配置
- oj2449: 刑警的射击成绩
- Linux查看CPU和内存使用情况