HNU_数据库_实验2（2021级）-安全性语言实验

_蟑螂恶霸_

已于 2024-01-26 13:47:17 修改

阅读量847

点赞数 21

分类专栏： # 实验_数据库文章标签：数据库 sql 实验

于 2024-01-25 11:36:08 首次发布

本文链接：https://blog.csdn.net/qq_62323523/article/details/135832739

版权

实验_数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

《数据库》第二次实验

安全性语言实验

0.LAB2-SQL语句汇总

ALTER TABLE ST.course ADD COLUMN(TNAME CHAR(20));
SET SQL_SAFE_UPDATES=0;
UPDATE ST.COURSE SET TNAME="T_W" WHERE CNAME="数据库";
SELECT * FROM st.course;

# 创建角色之后记得激活，可以把mysql设置为自动激活
show variables like 'activate_all_roles_on_login';
set global activate_all_roles_on_login = on;
show variables like 'activate_all_roles_on_login';

# 为学生管理，教务管理的人员A_A,A_B，A_C创建用户标识和用户口令。
CREATE USER 'A_A'@'localhost' IDENTIFIED BY "123456";
CREATE USER 'A_B'@'localhost' IDENTIFIED BY "123456";
CREATE USER 'A_C'@'localhost' IDENTIFIED BY "123456";

# 创建学生X，教师W的用户标识和用户口令
CREATE USER 'S_X'@'localhost' IDENTIFIED BY "123456";
CREATE USER 'T_W'@'localhost' IDENTIFIED BY "123456"; #数据库 老师


# 创建学生角色，可以对个人信息，选课情况及课程情况进行查询
CREATE ROLE 'ROLE_STUDENT'@'localhost';
# 借助视图实现“只能查询自己的信息”
CREATE VIEW VIEW_STUDENT AS 
	SELECT * FROM ST.STUDENT WHERE CONCAT(Sname,'@localhost')=USER();
GRANT SELECT ON ST.VIEW_STUDENT TO 'ROLE_STUDENT'@'localhost';
SHOW GRANTS FOR 'ROLE_STUDENT'@'localhost';
# 借助视图实现“只能查询自己的选课信息”
CREATE VIEW VIEW_SC AS 
	SELECT * FROM ST.STUDENT NATURAL JOIN ST.SC WHERE CONCAT(Sname,'@localhost')=USER();
GRANT SELECT ON ST.VIEW_SC TO 'ROLE_STUDENT'@'localhost';
SHOW GRANTS FOR 'ROLE_STUDENT'@'localhost';
# 查询课程情况
GRANT SELECT ON ST.COURSE TO 'ROLE_STUDENT'@'localhost';
SHOW GRANTS FOR 'ROLE_STUDENT'@'localhost';

# 创建教师角色，可以查询学生信息，查询自己任课课程的选课信息和全部课程信息，可以更新选课信息中的成绩字段。
CREATE ROLE 'ROLE_TEACHER'@'LOCALHOST';
/*可以查询全部学生信息*/
GRANT SELECT ON ST.STUDENT TO 'ROLE_TEACHER'@'LOCALHOST';
/*可以查询自己任课的课程选课情况，并修改成绩，借助视图实现*/
CREATE VIEW VIEW_TSC AS 
	SELECT * FROM ST.SC NATURAL JOIN ST.COURSE WHERE CONCAT(TNAME,'@LOCALHOST')=USER();
GRANT SELECT,UPDATE ON ST.VIEW_TSC TO 'ROLE_TEACHER'@'LOCALHOST';
/*可以查询所有课程信息*/
GRANT SELECT ON ST.COURSE TO 'ROLE_TEACHER'@'LOCALHOST'; 
SHOW GRANTS FOR 'ROLE_TEACHER'@'LOCALHOST';

# 为学生管理，教务管理的人员创建角色，相应的对学生信息，选课信息和课程信息有全部的权限，对其他表有查询权限。其中，教务管理人员可以为其他人分配权限
/*学生管理角色*/
CREATE ROLE 'ROLE_SS'@'LOCALHOST';
GRANT ALL PRIVILEGES ON ST.STUDENT TO 'ROLE_SS'@'LOCALHOST';
GRANT SELECT ON ST.COURSE TO 'ROLE_SS'@'LOCALHOST';
GRANT SELECT ON ST.SC TO 'ROLE_SS'@'LOCALHOST';
SHOW GRANTS FOR 'ROLE_SS'@'LOCALHOST';
/*教务管理角色*/
CREATE ROLE 'ROLE_AS'@'LOCALHOST';
GRANT ALL PRIVILEGES ON ST.SC TO 'ROLE_AS'@'LOCALHOST' with grant option;
GRANT ALL PRIVILEGES ON ST.COURSE TO 'ROLE_AS'@'LOCALHOST' with grant option;
GRANT SELECT ON ST.STUDENT TO 'ROLE_AS'@'LOCALHOST';
SHOW GRANTS FOR 'ROLE_AS'@'LOCALHOST';

# 为用户S_X分配学生角色的权限；
GRANT 'ROLE_STUDENT'@'localhost' TO 'S_X'@'localhost';
# 为用户T_W分配教师角色的权限；
GRANT 'ROLE_TEACHER'@'LOCALHOST' TO 'T_W'@'LOCALHOST';
# 为用户A_A分配学生管理角色的权限；
GRANT 'ROLE_SS'@'LOCALHOST' TO 'A_A'@'LOCALHOST';
# 为用户A_B,A_C分配教务管理角色的权限；
GRANT 'ROLE_AS'@'LOCALHOST' TO 'A_B'@'LOCALHOST';
GRANT 'ROLE_AS'@'LOCALHOST' TO 'A_C'@'LOCALHOST';

# 回收教师查询学生信息的权限。
SHOW GRANTS FOR 'ROLE_TEACHER'@'LOCALHOST';
REVOKE SELECT ON ST.STUDENT FROM 'ROLE_TEACHER'@'LOCALHOST';
SHOW GRANTS FOR 'ROLE_TEACHER'@'LOCALHOST';
# 回收教务管理人员C的权限
SHOW GRANTS FOR 'A_C'@'LOCALHOST';
REVOKE 'ROLE_AS'@'LOCALHOST' FROM 'A_C'@'LOCALHOST';
SHOW GRANTS FOR 'A_C'@'LOCALHOST';

# **********审计实验*****
show variables like '%general_log%';
SET GLOBAL GENERAL_LOG=ON;
show variables like '%general_log%';
#  查看审计文件保存的位置
SET GLOBAL general_log_file = 'lab2.log';
show variables like '%general_log%';
SHOW VARIABLES LIKE 'datadir';

2.1 自主存取控制实验

1.实验目的

掌握自主存取控制权限的定义和维护方法。

2.实验内容和要求

定义用户、角色，分配权限给用户、角色，回收权限，以相应的用户名登录数据库验证权限分配是否正确。选择一个应用场景，使用自主存取控制机制设计权限分配。可以采用两种方案。

方案一：采用SYSTEM超级用户登录数据库，完成所有权限分配工作，然后用相应用户名登录数据库以验证权限分配正确性；

方案二：采用SYSTEM用户登录数据库创建三个部门经理用户，并分配相应的权限，然后分别用三个经理用户名登录数据库，创建相应部门的USER、ROLE,并分配相应权限。

3.实验过程

(1)修改lab1中的ST

使用lab1中建立的教务系统ST数据库模式。ST数据库模式由学生表(student)，课程表(course)，选课表(sc)三个基本表组成。

假设该系统具有学生信息查询，成绩录入，课程管理等功能，为以上三个功能使用自主存取控制机制设计一个具体的权限分配方案。

为了便于验证，为课程表添加了一个属性Tname，表示任课教师的名称，并将“数据库”课程的教师名设置为“w”。

(2)将mysql设置为自动激活

创建角色之后需要激活，可以把mysql设置为自动激活：

(3)创建用户

MYSQL的用户创建语句格式如下：

CREATE USER user_account IDENTIFIED BY password;

注：user_account的格式是username@hostname。

创建教务管理人员

创建A_A,A_B，A_C的用户标识和用户口令

创建学生，教师

创建学生S_X，教师T_W的用户标识和用户口令

(4)创建角色并分配权限

创建学生角色

权限：可以对个人信息，选课情况及课程情况进行查询

创建教师角色

权限：可以查询学生信息，查询自己任课课程的选课信息和全部课程信息，可以更新选课信息中的成绩字段。

创建管理角色

权限：为学生管理，教务管理的人员创建角色，相应的对学生信息，选课信息和课程信息有全部的权限，对其他表有查询权限。其中，教务管理人员可以为其他人分配权限。

学生管理：

教务管理：

（5）给用户分配权限

学生用户

教师用户
管理用户

学生管理角色：

教务管理角色:

（6）回收角色或用户权限

回收教师查询学生信息的权限

回收前：

回收后：

回收教务管理人员A_C的权限

回收前：

回收后：

（7）验证权限分配的正确性

学生S_X

1）登录：

① 在 MySQL Workbench 主窗口的顶部菜单中，选择 "Database"。

然后选择 "Connect to Database"。

②在 "Connect to Database" 对话框中，你将看到以下字段：

Connection Method: 通常选择 "Standard (TCP/IP)"。

Hostname: 输入数据库服务器的主机名或 IP 地址。

Port: 输入数据库服务器的端口号。默认 MySQL 端口是 3306。

Username: 输入要使用的数据库用户名。

Password: 输入相应用户名的密码。

Default Schema: 如果需要，可以选择默认使用的数据库或模式。

2）验证：

①查询个人信息：

ROOT下的student：

S_X下的student：

②查询选课信息

ROOT下的sc：

S_X下的sc：

③尝试修改自己的信息

教师T_W

①更新学生成绩：

更新前：

更新后：

②查询学生信息：

查询学生信息的权限已被收回，不能查询

学生管理A_A

①更新学生成绩：

更新前：

更新后：

②查询sc表：

教务管理A_B

①插入一门课程：

插入前：

插入后：

②教务管理A_C【权限被收回】：

4.实验总结

通过本次实验，进一步熟悉了自主存取控制权限的方法。使用SQL语句进行了角色的创建，权限的分配，以及将角色权限分配给用户。

在实验中，学习到MYSQL的角色和用户的创建的格式为username@hostname。同时学习了如如何在mysql workbench中使用其他用户访问数据库。

5.思考题

（1）分析WITH CHECK OPTION，WITH ADMIN OPTION 和 WITH GRANT OPTION 的区别和联系。

WITH CHECK OPTION：用于视图的创建，作用是在使用视图时，保证通过视图进行的修改，也能通过该视图看到修改后的结果。
WITH ADMIN OPTION语句和WITH GRANT OPTION语句：都用于授权。如果在授予用户或角色权限时添加了WITH ADMIN OPTION语句，可以将该权限再授予其他角色；如果在授予用户或角色权限时添加了WITH GRANT OPTION语句，也可以将该权限授予再授予其他用户。
1. WITH ADMIN OPTION用于系统权限授权；
2. WITH GRANT OPTION用于对象授权，而用户的系统权限被收回时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效。

在MYSQL中，只有WITH GRANT OPTION语句，没有WITH ADMIN OPTION语句。

（2）请结合上述实验示例分析使用角色进行权限分配有何优缺点

如果有许多用户都具有一类相同的权限，使用角色可以方便的批量分配权限。

但是如果各用户的权限都存在不同，使用角色就不够灵活，可能需要创建许多不同的角色，更加繁琐。