问题引入
这天老鸟火急火燎的跑到菜鸟旁边,想必是遇到什么难题了:“现在有这么一个场景,假如有三种角色,并且存在层级关系,他们需要访问同一个数据源表,但是需要做权限控制,使得每种角色只能看到自己及以下层级的数据。比如:公司有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