《安全与完整性》数据库实验报告

已于 2025-05-02 16:53:15 修改
阅读量976 收藏 22
点赞数 12
分类专栏： 数据库实验报告 文章标签： 数据库 sql java
于 2025-01-04 00:46:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_52381146/article/details/144920861
版权

实验报告（五）

一、实验题目

    实验五  安全与完整性

二、实验目的

1.掌握使⽤SQL语言对⽤户、角色的维护方法。

2.掌握使⽤SQL语言对权限的分配、回收操作。

3.理解约束的触发机制。

4.掌握使⽤触发器实现复杂完整性。

三、实验内容与实现

实验要求一：

  1. 创建⽤户 us1 和 ut1 ，⾃⾏指定密码。

代码：

CREATE USER us1

IDENTIFIED BY '20203723';

CREATE USER ut1

IDENTIFIED BY '20203723';

实验结果：

  1. 创建⻆⾊ rt_read 、 rt_insert 、 rt_update 。

代码：

CREATE USER us1

IDENTIFIED BY '20203723';

CREATE USER ut1

IDENTIFIED BY '20203723';

 

实验结果：

  1. 将 学⽣信息 与 开课信息 的查询权限授予 us1 。

代码：

GRANT SELECT ON edu_db.students

TO us1;

GRANT SELECT ON edu_db.course_info

TO us1;

 

实验结果：

  1. 将 教师信息 、课程信息 、开课信息 、成绩信息 的查询权限授予角色rt_read 。

代码：

GRANT SELECT ON edu_db.teachers

TO rt_read;

GRANT SELECT ON edu_db.courses

TO rt_read;

GRANT SELECT ON edu_db.course_info

TO rt_read;

GRANT SELECT ON edu_db.scores

TO rt_read;

 

实验结果：

  1. 将 成绩信息 的插⼊权限授予⻆⾊ rt_insert 。

代码：

GRANT SELECT, insert ON edu_db.scores

TO rt_insert;

 

实验结果：

  1. 将 成绩信息 分数列的更新权限授予⻆⾊ rt_update 。

代码：

GRANT SELECT, UPDATE(score) ON edu_db.scores

TO rt_update;

 

实验结果：

  1. 将⽤户 ut1 加⼊到⻆⾊ rt_read 、 rt_insert 和 rt_update 中。

代码：

GRANT rt_read,rt_insert,rt_update TO ut1;

SET DEFAULT ROLE rt_read,rt_insert,rt_update TO ut1;

 

实验结果：

  1. 验证上述权限

代码：

set global activate_all_roles_on_login=ON;

select * from classes

 

实验结果：

9. 如果要屏蔽教师查看到工资，需要在上述权限分配的基础上做哪些操作？请给出完整的操作过程及语句。

代码：

REVOKE SELECT ON edu_db.teachers

FROM rt_read;

USE edu_db;

CREATE VIEW vw_teacher

AS

SELECT id, name, sex, job_title, dept_id

FROM teachers;

GRANT SELECT ON vw_teacher

TO rt_read;

SELECT *

FROM vw_teacher;

 

实验结果：

实验要求二：

1. 根据第1部分的结构，举例说明：

  1. 主键约束的触发机制；

create table books(

book_isbn char(4) primary key,

book_name varchar(10) not null,

book_author varchar(6)

);

 

  2. 外键约束的触发机制；

/*创建触发器，实现外键约束的级联更新效果*/

DELIMITER $

DROP TRIGGER IF EXISTS trg_cascade_update_course_info $

CREATE TRIGGER `trg_cascade_update_course_info` AFTER UPDATE ON `course_info` FOR EACH ROW

BEGIN 

    /*course_info.id字段值被更新，则需要更新scores.cs_id字段*/

    UPDATE scores s SET s.cs_id=NEW.id WHERE s.cs_id=OLD.id;

END;

$

DELIMITER ;

 

/*创建触发器，实现外键约束的级联删除效果*/

DELIMITER $

DROP TRIGGER IF EXISTS trg_cascade_delete_course_info $

CREATE TRIGGER `trg_cascade_delete_course_info` AFTER DELETE ON `course_info` FOR EACH ROW

BEGIN 

    /*当course_info有记录被删除，则需要删除socres表中相应记录*/

    DELETE FROM scores WHERE cs_id=OLD.id;

END;

$

DELIMITER ;

 

 

/*创建触发器，实现插入数据时的外键约束效果*/

DELIMITER $

DROP TRIGGER IF EXISTS trg_constraint_insert_scores $

CREATE TRIGGER `trg_constraint_insert_scores` BEFORE INSERT ON `scores` FOR EACH ROW

BEGIN 

    DECLARE returned_rows INT(11);

    DECLARE msg VARCHAR(1000);

    /*有记录插入scores表之前，需要到course_info表中去检查scores.cs_id字段值在course_info.id字段中是否存在*/

    SELECT count(c.id) INTO returned_rows FROM course_info c WHERE c.id=NEW.cs_id;

    IF returned_rows <> 1 THEN 

        set msg = "Cannot add or update a child row: a foreign key constraint on scores(cs_id) fails.";

        SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = msg;  /*从mysql 5.5版本开始提供了SIGNAL方法，可以实现中断操作*/

    END IF;

END;

$

 

DELIMITER ;

/*创建触发器，实现更新数据时的外键约束效果*/

DELIMITER $

DROP TRIGGER IF EXISTS trg_constraint_update_scores $

CREATE TRIGGER `trg_constraint_update_scores` BEFORE UPDATE ON `scores` FOR EACH ROW

BEGIN 

    DECLARE returned_rows INT(11);

    DECLARE msg VARCHAR(1000);

    /*在更新scores表之前，需要到course_info表中去检查scores.cs_id字段值在course_info.id字段中是否存在*/

    SELECT count(c.id) INTO returned_rows FROM course_info c WHERE c.id=NEW.cs_id;

    IF returned_rows <> 1 THEN 

        set msg = "Cannot add or update a child row: a foreign key constraint on scores(cs_id) fails.";

        SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = msg;  /*从mysql 5.5版本开始提供了SIGNAL方法，可以实现中断操作*/

    END IF;

END;

$

DELIMITER ;

 

  3. ⾮空约束的触发机制；

ALTER TABLE tb_dept4

CHANGE COLUMN location

 location VARCHAR(50) NOT NULL;

 

2. 实现业务规则，并验证规则的有效性：

  1. 学⽣的年龄不低于15岁，不⾼于50岁。（2种⽅式实现）

代码：

方式一：

DELIMITER $

drop trigger if exists trig_studentage $

create trigger trig_studentage

before insert

on students

for each row

begin

 declare age int;

 select year(now())-year(birthday) into age

 from students

 where birthday=new.birthday;

 if(age<15 or age>50) then

  signal sqlstate '45000' set message_text = '学生的年龄不低于15岁，不高于50岁';

 end if;

end;

 #测试数据

insert into students

values('20203732', '杨佳丽', '2022-02-05', '男', '200404');

 

方式二：

DELIMITER $

drop trigger if exists trig_studentage $

create trigger trig_studentage

before insert

on students

for each row

begin

 declare age int;

 set age=year(now())-year(birthday);

 if(age<15 or age>50) then

  signal sqlstate '45000' set message_text = '学生的年龄不低于15岁，不高于50岁';

 end if;

 end; 

#测试数据

insert into students

values('202020202', '太阳花', '2022-02-05', '男', '200000');

 

实验结果：

 2. 每学期学生选课的总学分不超过10分。

代码：  

DELIMITER $

drop trigger if exists trig_student_select_credit $

create trigger trig_student_select_credit

after insert

on courses

for each row

begin

 declare credit int;

  select sum(credit) into credit

  from courses,scores,course_info

  where scores.cs_id=course_info.id

    and course_info.course_id=courses.id

 group by scores.student_id,courses.semester;

 if(credit>=10) then

  signal sqlstate '45001' set message_text = '选课的总学分不超过10分';

 end if;

end; 

 

  3. 记录 成绩表 被更改和被删除的操作记录流水（自定义流水表结构，要求记录修改的时间）。

代码:

CREATE TABLE score_change_memory (

  student_id char(8) NOT NULL,

  cs_id int NOT NULL,

  type char(4) NOT NULL,

  score decimal(5,2) DEFAULT NULL,

  action_date datetime DEFAULT NULL,

  action_type varchar(20) DEFAULT NULL,

  PRIMARY KEY (student_id,cs_id,type)

);

 

DELIMITER $

drop trigger if exists trig_update $

CREATE TRIGGER trig_update

AFTER UPDATE

ON scores

FOR EACH ROW

BEGIN

    INSERT INTO score_change_memory(student_id,cs_id,type,score,new_score,action_type)

    VALUES(new.student_id,new.cs_id,new.type,new.score,'update',now());

END

 

DELIMITER $

drop trigger if exists trig_delete $

CREATE TRIGGER trig_delete

AFTER DELETE

ON scores

FOR EACH ROW

BEGIN

    INSERT INTO score_change_memory(student_id,cs_id,type,score,score,action_type)

    VALUES(old.student_id,old.cs_id,old.type,old.score,'delete',now());

END

 

 #测试数据

UPDATE scores set score = 100 WHERE student_id = '200000' AND cs_id = 8;

 

实验结果：

四、实验问题总结与心得

问题总结：

1.在进行用户角色授权检测时要登录进具体的用户，否则测试失败。
2.测试失败时，首先刷新用户权限和数据表，进行排错。
3.触发器的设置要注意条件，在if条件句中，不要设置成相反的内容 。

实验心得：

在本次实验中完成了使用sql语言对用户，角色的维护方法，掌握了使用sql语言对权限的分配、回收操作，理解了约束的触发机制

通过本次数据库实验对触发器有了实践的经验，增强了我的动手能力，明白了触发器存在的意义和作用，同时也对sql安全与完整性有了更深的掌握和理解，通过本次实验加强了对理论知识的掌握和理解，激发了我的学习兴趣，在今后继续努力。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值