数据库原理实验五（MySQL版）

实验五 安全机制

一、实验目的

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

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

二、实验学时

2学时

三、实验内容

1．理解用户、模式、角色、权限的概念，在MySQL中感知用户、模式、角色和权限。

2．完成以下操作：

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

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

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

四、实验报告

提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。

# 2.1建立采油一矿的成本的视图，把该视图的查询权限授予给采油一矿的用户user11，
create view V3 as
select * from 成本消耗表
where 预算单位 in (select 单位代码 from 单位表 where 单位名称 like '采油一矿%');
select * from V3;
 
# 以user11的身份查询该视图，观察执行情况；
create user 'user11'@'%' identified by '123456';
grant select on 金小矿.V3 to 'user11'@'%';
# 命令行使用 mysql -u user11 -p 指令进行登录，接着输入密码123456
use 金小矿; -- 链接数据库
select * from V3;
# 再以其他用户的身份查询该视图，观察执行情况。
create user 'user12'@'%' identified by '123456';
select * from V3;
 
# 2.2创建一个用户user12，以user12的身份执行实验四中所定义的存储过程，观察记录是否成功执行；
-- 授予存储过程的执行权限给用户 'user12'
call generate_cost_report('1122', '2023-05-01', '2023-05-28');
# 然后把该存储过程的执行权限授予给user12，再次以user12的身份执行该存储过程，观察记录是否成功执行。
grant execute on procedure 金小矿.generate_cost_report to 'user12'@'%';
call generate_cost_report('1122', '2023-05-01', '2023-05-28');
 
# 2.3定义触发器，实现只能在工作时间内更新“成本表”的数据，
delimiter //
drop trigger if exists update_table_time;
create trigger update_table_time after update on 成本消耗表
for each row
begin
    declare nowtime date;
    declare nowday int;
    set nowtime=now();
    set nowday=dayofweek(nowtime);
 
    if not (nowday between 1 and 5 ) then
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '非工作日不能更新数据';
    end if;
end //
delimiter ;
 
# 然后通过选择不同的时间进行适当的更新操作来验证。
begin;
update 成本消耗表 set 施工内容 = '堵漏'
where 单据号 = 'zy2023003';