SQL Server实战5

前言：最后一个实验！

目的： 1．掌握数据库安全管理机制中的常规方法，理解登录、用户、架构、角色、权限的概念、定义及使用；

2．掌握视图、存储过程、触发器的概念、定义及如何发挥特殊的安全控制作用。

实验内容

用户与权限

⑴ 建立采油一矿的成本的视图，把该视图的查询权限授予给采油一矿的用户user11，以user11的身份查询该视图，观察执行情况；再以其他用户的身份查询该视图，观察执行情况。

create view 采油一矿_成本
as
select *
from 成本,单位
where 预算单位 = 单位代码
and 单位名称 like '采油一矿%'

-- 创建登录名
create login user01 with password = '12345'
-- 创建用户
create user user11 for login user01

grant select
on 采油一矿_成本
to user11

select *
from 采油一矿_成本

-- 在user11用户下查询成本表 发现没有权限
-- 拒绝了对对象 '成本' (数据库 'zyxt'，架构 'dbo')的 SELECT 权限。
setuser 'user11'
select *
from 成本

-- 再次setuser切换用户
setuser 
-- 即可查询成本表

setuser: 如果未指定 username，将重置模拟用户的系统管理员或数据库所有者的原始标识。

存储过程

⑵ 创建一个用户user12，以user12的身份执行实验四中所定义的存储过程，观察记录是否成功执行；然后把该存储过程的执行权限授予给user12，再次以user12的身份执行该存储过程，观察记录是否成功执行。

-- 创建登录名
create login user02 with password = '12345'
-- 创建用户
create user user12 for login user02

setuser 'user12'

execute _someDepCost @单位代码 = '1122',@起始日期 = '2016-5-1',@结束日期 = '2016-5-29'
execute _someDepCost @单位代码 = '112201',@起始日期 = '2016-5-1',@结束日期 = '2016-5-29'
execute _someDepCost @单位代码 = '112201001',@起始日期 = '2016-5-1',@结束日期 = '2016-5-29'
-- 拒绝了对对象 '_someDepCost' (数据库 'zyxt'，架构 'dbo')的 EXECUTE 权限
-- 即user12没有使用此存储过程的权限

setuser
-- 更改用户后才能进行授权

grant execute on _someDepCost
to user12

setuser 'user12'

execute _someDepCost @单位代码 = '1122',@起始日期 = '2016-5-1',@结束日期 = '2016-5-29'
execute _someDepCost @单位代码 = '112201',@起始日期 = '2016-5-1',@结束日期 = '2016-5-29'
execute _someDepCost @单位代码 = '112201001',@起始日期 = '2016-5-1',@结束日期 = '2016-5-29'

触发器

⑶ 定义触发器，实现只能在工作时间内更新“成本表”的数据，然后通过选择不同的时间进行适当的更新操作来验证。

create trigger trmytr7 
after update on 成本
as
declare @currenttime datetime
set @currenttime = getdate()
declare @current_day varchar(10)
set @current_day = convert(varchar(10),datepart(dw,@currenttime)-1)
if @current_day not between 1 and 5
begin
if @@trancount>0
begin
rollback
print'非工作日不能更新数据'
end
end
go

-- 测试
begin tran
update 成本
set 预算人 = 'test'
where 单据号 = 'zy2017001'
if @@trancount>0
begin
commit
end
go