当前位置：首页 > news >正文

实验题目：授权及收回授权、约束、触发器

news 来源：原创 2025/8/22 15:05:55

一、实验环境

实验使用普通PC机一台，MySQL数据库版本8.0.36，使用Navicat Premium 16提供图形化界面。

二、实验内容

1、数据库的账号、用户的建立、删除以及授权机制

2、数据库中数据完整性约束控制技术

3、触发器

三、具体完成情况（提供截图和文字说明）

1、数据库的账号、用户的建立、删除以及授权机制

1）对于系统中的每一个用户，MySQL 都为其建立特定的用户账号。用户账号管理是 MySQL 实现安全性的核心。本实验要求实践以下内容：创建用户、修改用户的设置及口令、锁定和解锁用户、删除用户。

A、创建用户语法：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

B、修改用户的设置及口令、锁定和解锁用户

SET PASSWORD FOR '用户名'@'localhost' = '新密码';

ALTER USER '用户名'@'localhost' ACCOUNT LOCK;

ALTER USER '用户名'@'localhost' ACCOUNT UNLOCK;

C、删除用户

DROP USER '用户名'@'localhost';

注：受篇幅限制，下述步骤仅供参考，完整操作截图的word版本可见上方本博文免费资源绑定。

2）MySQL 定义了一整套丰富、完整的权限，通过授予或撤销用户适当的权限，实现了安全的数据库访问控制。在 MySQL 数据库中，有两类权限，即对象权限和系统权限。对象级别权限是由用户赋予的访问或操作数据库对象的权限。

A、系统权限不是控制对指定数据库对象的访问，而是用来许可对各种特性的访问，或许可 MySQL 数据库中的特定任务。

授予用户系统权限的基本语法格式如下：

-- 授权用户访问权限

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'localhost' WITH GRANT OPTION;

B、对象权限可以用来控制对指定数据库对象的访问。

授予用户对象权限的语法格式如下：

GRANT object_privilege ON object_name TO 'username'@'host' [WITH GRANT OPTION];

3）为了简化权限管理并提高效率，可以使用角色，角色是具有名称的一组系统权限和对象权限的集合。

创建数据库角色的基本语法格式如下：

CREATE ROLE 'role01';

GRANT ALL PRIVILEGES ON database_name.* TO 'role01';

GRANT 'role01' TO 'user01'@'localhost';

4）收回用户所拥有的权限

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';

2、数据库中数据完整性约束控制技术

1）创建一个学生信息表，要求学生学号为主码（primary key），学生姓名不能取空值，并要求学生姓名取值唯一，性别只能取“男”、“女”，默认值为“男”，年龄在 15 到 40 岁之间。

CREATE TABLE student(

sno VARCHAR(10) PRIMARY KEY,

sname VARCHAR(30) UNIQUE NOT NULL,

ssex ENUM('男', '女') DEFAULT '男',

sage INT CHECK(sage BETWEEN 15 AND 40),

sdept VARCHAR(30)

);

2）创建一个课程信息表，要求课程号为主码，课程名不能为空，取值唯一，前修课为外键，参照课程表的课程号。

CREATE TABLE course(

cno VARCHAR(10) PRIMARY KEY,

cname VARCHAR(30) UNIQUE NOT NULL,

cpno VARCHAR(10),

ccredit DECIMAL(4,2),

FOREIGN KEY(cpno) REFERENCES course(cno) ON DELETE CASCADE

);

3、触发器

是否对数据库进行了安全性控制和完整性控制，用户就可以自由地对数据进行操作而不受任何限制了？

举例 1：修改记录只能在正常工作时间段内（非星期六和星期日的 8 点到 18 点）。

举例 2：在签订一份订单时，货物的库存量应减少。怎么办？

使用触发器可以解决上述问题。

1）定义触发器的语法如下：

DELIMITER //

CREATE TRIGGER `<触发器名>`

{BEFORE|AFTER|INSTEAD OF} <触发事件> ON `<表名>`

FOR EACH ROW

BEGIN

-- 可选的触发条件

IF <触发条件> THEN

-- 触发动作体

<触发动作体>

END IF;

END; //

DELIMITER ;

2）定义一个 BEFORE 行级触发器，为学生表 student 定义完整性规则“如果学生为女生，则学生的年龄不得低于 15 岁，如果低于 15 岁，自动改为 15 岁”。

CREATE TRIGGER Insert_Or_Update_sage

BEFORE INSERT OR UPDATE ON student

FOR EACH ROW

BEGIN

IF NEW.ssex = '女' AND NEW.sage < 15 THEN

SET NEW.sage = 15;

END IF;

END;

3）定义一个 BEFORE 语句级触发器，为学生表 student 定义以下限制：不能在周末或非工作时间修改数据（非星期六和星期日的 8 点到 18 点）。

CREATE TRIGGER operate_student

BEFORE INSERT OR UPDATE OR DELETE ON student

FOR EACH ROW

BEGIN

IF DAYOFWEEK(NOW()) IN (1, 7) OR HOUR(NOW()) NOT BETWEEN 8 AND 18 THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能在非工作日或工作时间外修改数据库';

END IF;

END;

4）删除触发器。

DROP TRIGGER trigger_name;

5）练习以下内容：

1、定义 AFTER 行级触发器，当学生表 Student 的年龄发生变化后就自动在年龄变化表sage_log 中增加一条相应记录

DELIMITER //

CREATE TRIGGER after_age_change

AFTER UPDATE ON Student

FOR EACH ROW

BEGIN

IF OLD.age != NEW.age THEN

INSERT INTO sage_log (student_id, old_age, new_age, change_time)

VALUES (OLD.id, OLD.age, NEW.age, NOW());

END IF;

END;

DELIMITER ;

2、定义 AFTER 行级触发器，当删除学生表 Student 的一个学生信息后，同时删除学生选课表中该学生的所有选课记录

DELIMITER //

CREATE TRIGGER delete_student_trigger

AFTER DELETE ON Student

FOR EACH ROW

BEGIN

DELETE FROM student_courses WHERE student_id = OLD.id; -- 假设Student表的主键是id

END;

DELIMITER ;

3、需要对在表上进行 DML 操作的用户进行安全检查，看是否具有合适的特权

SELECT *

FROM information_schema.TABLE_PRIVILEGES

WHERE GRANTEE = 'actual_user_name'@'actual_host'

AND TABLE_SCHEMA = 'actual_database_name'

AND TABLE_NAME = 'actual_table_name';

四、出现的问题

在构造SQL查询时，我们最初没有正确地将占位符（如user_name、host、your_database_name和your_table_name）替换为实际的值，导致查询无法正确执行。这提醒我们在编写查询时需要仔细核对并替换所有的占位符。

五、解决方案

在编写SQL查询时，我们仔细检查了所有的占位符，并确保每一个都被正确地替换为了实际的值。为了避免遗漏或错误，我们使用了文本编辑器的查找和替换功能，逐一检查并替换了user_name、host、your_database_name和your_table_name等占位符。

六、实验总结

在本次MySQL实验中，我深入学习了数据库用户管理、权限分配、数据完整性控制以及触发器的应用。通过实际操作，更加熟悉了MySQL数据库的基本操作，并掌握了如何创建、修改和删除用户，以及如何为用户分配适当的权限，从而确保数据库的安全性。此外，还学习了如何通过设置数据完整性约束来保证数据的准确性和一致性，这在实际应用中至关重要。触发器的使用则让我了解到如何在特定数据库事件发生时自动执行某些操作，这大大提升了数据库管理的自动化程度。总的来说，这次实验不仅提升了我的数据库操作技能，还加深了我对数据库安全性和完整性的理解。

相关文章：