需求:在SQL Server中,指定某个用户只能访问某个视图,但该视图中的数据来自同一服务器的多个数据库,视图里面有跨数据库访问的语句。
实现:
1、创建用户并指定该用户只能访问指定的视图,这个很容易实现,CSDN上有大量的文章,基本思路如下:
(1) 选择要操作的数据库,添加一个角色`newRole`
exec sp_addrole newRole
(2) 给`newRole`添加访问指定视图的权限
grant select on dbo.testView to newRole
(3) 添加一个登陆,登录名:newLogin;密码:123456;默认数据库:dbo.test;
exec sp_addlogin 'newLogin','123456','dbo.test'
(4) 添加一个用户,使用`newLogin`登陆,指定`newRole`角色
exec sp_adduser 'newLogin','newUser','newRole'
本步骤仅可满足该视图对本数据库表的访问,不能跨数据库访问其他数据的表。如需要整合同一服务器上多个数据库中的数据,汇总到一个视图中,则无法实现。
例如:视图中如出现select * from test1.dbo.table1,则会返回“sqlserver 服务器主体 无法在当前安全上下文下访问数据库”之类的错误。
2、在此基础上,可采用如下思路,实现整合同一服务器上多个数据库中的数据,汇总到一个视图中。
例如:需要将数据库test1中的表table1、test2中的表table2中的数据整合到数据库test中的视图testview
方法如下:
(1)在数据库test1中创建视图testview1
(2)在数据库test1中同样创建newRole角色,并给newRole角色添加访问视图testview1的权限,然后添加一个用户,即在数据库test1中执行1、中的(1)、(2)、(4)命令
(3)在数据库test2中创建视图testview2
(4)在数据库test2中同样创建newRole角色,并给newRole角色添加访问视图testview2的权限,然后添加一个用户,即在数据库test2中执行1、中的(1)、(2)、(4)命令
(5)在数据库test中创建视图testview,视图参考语句如下:
create view testview
as
select * from test1..testview1
union all
select * from test2..testview2
(6)在数据库test中同样创建newRole角色,并给newRole角色添加访问视图testview的权限,然后添加一个用户,即在数据库test中执行1、中的(1)、(2)、(4)命令
总结:针对一个视图需要跨多个数据库访问表,又不想让仅拥有该视图访问权限的用户访问实际的表。
通过给每个数据库分配同样的角色和用户,然后在每个数据库中创建视图,最后进行视图嵌套,即可满足需求。