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;
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
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
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