本文共 6170 字，大约阅读时间需要 20 分钟。
问题引入
这天老鸟火急火燎的跑到菜鸟旁边，想必是遇到什么难题了：“现在有这么一个场景，假如有三种角色，并且存在层级关系，他们需要访问同一个数据源表，但是需要做权限控制，使得每种角色只能看到自己及以下层级的数据。比如：公司有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;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 Server使用视图来做权限控制方法是相当的清爽和彻底满足老鸟预期的。于是菜鸟得意洋洋的来到老鸟办公室，霸气的展示了自己的解决方案和例子，老鸟觉得非常满意。
转载地址：http://unuwo.baihongyu.com/