MSSQL DBA 精华总结[处理对象名称中特殊字符]

Cherish_j_wang · 发表于 2012-3-19 21:21

hwtong 发表于 2012-3-19 08:32
转账转出 pub币 -1000 向 Cherish_j_wang 进行积分转帐 2012-03-19 08:31

哈哈~~奖励收到~~谢谢啦

Cherish_j_wang · 发表于 2012-3-19 21:21

crystal123789 发表于 2012-3-19 11:03
支持！好贴

多谢支持。~~

sokoo · 发表于 2012-3-21 12:59

支持，谢谢

Cherish_j_wang · 发表于 2012-3-21 20:53

sokoo 发表于 2012-3-21 12:59
支持，谢谢

多谢支持

Cherish_j_wang · 发表于 2012-3-21 21:47

本帖最后由 Cherish_j_wang 于 2012-3-21 21:51 编辑

MSSQL DBA 精华总结

之View如何做权限控制

背景

在数据库开发的过程中，我们经常会遇到关于权限控制的下面场景：

1. 针对某个用户群体授予权限

2. 群体之前有级别高低之分，高级别的群用户可以查看低级别的数据，而反之则不行

3. 这是关于安全性的考虑

4. 比如，公司的高层可以看到基层员工信息和待遇等场景

脚本

那么这个问题，我们可以使用MSSQLServer中的View来轻松解决这个问题，简直妙不可言，参见测试方法：

请开启一个SSMS连接执行下面的代码，比如连接SPID=90

use test
go
--create two logins(employee,manager)
if exists(
select *
from sys.syslogins
where name = 'Manager')
begin
drop login Manager;
end
GO
create login Manager with password='Manager',check_policy = off;
GO
if USER_ID('Manager') is not null
drop user Manager;
GO
create user Manager for login Manager;
GO
if exists(
select *
from sys.syslogins
where name = 'employee')
begin
drop login employee;
end
GO
create login employee with password='employee',check_policy = off;
GO
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.Test_ViewPermission','u')is not null
drop table dbo.Test_ViewPermission
;
GO
create table dbo.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.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
--create views
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.Test_ViewPermission WITH(NOLOCK)
GO
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.Test_ViewPermission WITH(NOLOCK)
where level_no >= 1;
GO
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.Test_ViewPermission WITH(NOLOCK)
where level_no >= 2;
GO
--====permission init.
--deny all permission to user for table & view
deny all on dbo.Test_ViewPermission TO Manager;
deny all on dbo.Test_ViewPermission TO 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 select on dbo.v_employeeinfo_forManager TO Manager;
grant select on dbo.v_employeeinfo_forEmployee TO Manager;
grant select on dbo.v_employeeinfo_forEmployee TO employee;

复制代码

请使用manager用户开启另外一个SSMS连接，比如SPID=91,执行下面的测试代码：

use test
GO
--manager can read data from manager group
select
CURRENT_USER AS 'Who am i'
,*
from dbo.v_employeeinfo_forManager
GO
--manager can read data from employee group
select
CURRENT_USER AS 'Who am i'
,*
from dbo.v_employeeinfo_forEmployee
GO
--manager cann't read data from ceo group
select *
from v_employeeinfo_forCEO
GO
--manager cann't read data from table
select *
from dbo.Test_ViewPermission

复制代码

结果：
第一、二个查询应该有结果集，第三和第四个查询，应该报告没有权限
第一个结果集：
Who am i       name       level_no       title       viewByManager       viewByEmployee       salary
Manager       BB       1       Manager       Y       Y       100000.00
Manager       CC       2       employee       Y       Y       10000.00

第二个结果集：
Who am i       name       level_no       title       viewByEmployee       salary
Manager       CC       2       employee       Y       10000.00

第三和第四个结果，报错：
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'v_employeeinfo_forCEO', database 'test', schema 'dbo'.
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'Test_ViewPermission', database 'test', schema 'dbo'.

今天的分享至此结束。