这天老鸟火急火燎的跑到菜鸟旁边,想必是遇到什么难题了:“现在有这么一个场景,假如有三种角色,并且存在层级关系,他们需要访问同一个数据源表,但是需要做权限控制,使得每种角色只能看到自己及以下层级的数据。比如:公司有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,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结果显示如下,测试结果的确与预期吻合。
预期是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查询结果展示如下,测试结果再次与预期吻合。
预期是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层级测试结果也完全满足预期。
从测试结果来看,SQL Server使用视图来做权限控制方法是相当的清爽和彻底满足老鸟预期的。于是菜鸟得意洋洋的来到老鸟办公室,霸气的展示了自己的解决方案和例子,老鸟觉得非常满意。