【MySQL】实验07 数据库的安全性保护

(简答题, 3分) 本实验的实验目的是:

掌握视图的创建与使用，理解MySQL用户管理、权限授予与回收、角色管理。
(简答题, 4分)
创建一个视图，名称设置为view_StuInfoDno03_学号最后两位，用来查询学院编号为03的学生的所有信息，并且要求基于该视图进行更新操作时，仍需保证该视图只有该学院的学生。
提交内容：
（1）创建视图的语句；
```
USE Jxdb59;
CREATE VIEW view_StuInfoDno03_59 AS
SELECT * FROM Students59
WHERE Dno = '03'
WITH CHECK OPTION;
```
（2）查询该视图中女学生的学号，姓名，并将结果截图，图片的右下角写上学号的最后两位；
```
USE Jxdb59;
SELECT Sno, Sname FROM view_StuInfoDno03_59 WHERE Sgender = '女';
```
(简答题, 9分)

通过视图view_StuInfoDno03_学号最后两位，分别插入以下两个学生的信息。
提交内容：
（1）写出插入学生张三信息的语句；
```
USE Jxdb59;
# 插入张三（学院03）
INSERT INTO view_StuInfoDno03_59 VALUES ('202403038199', '张三', '男', '2004-05-15', '广东潮州', '03', '0303', '2024030381');
```
（2）写出在学生表中查询张三信息的语句，并将执行结果截图，图片的右下角写上学号的最后两位；
```
SELECT * FROM Students59 WHERE Sname = '张三';
```
（3）写出插入学生李四信息的语句；
```
INSERT INTO view_StuInfoDno03_59 VALUES ('202416026101', '李四', '女', '2006-05-10', '广东揭阳', '16', '1602', '2024160261');
```
（4）将执行结果截图，图片的右下角写上学号的最后两位；

（5）分析两个插入语句的执行结果。

张三插入成功，李四失败。因为 WITH CHECK OPTION 限制只能插入学院编号为03的学生。
(简答题, 4分)
创建一个视图，名称设置为view_RepInfo_学号最后两位，用来查询学生的选课信息，结果显示学号，姓名，课程号，课程名和成绩。
提交内容：
（1）创建视图的语句；
```
USE Jxdb59;
CREATE VIEW view_RepInfo_59 AS
SELECT r.Sno, s.Sname, r.Cno, c.Cname, r.Grade
FROM Reports59 r
JOIN Students59 s ON r.Sno = s.Sno
JOIN Courses59 c ON r.Cno = c.Cno
WITH CHECK OPTION;
```
（2）查询该视图中学号为202003005104的学生的选课情况，并将结果截图，图片的右下角写上学号的最后两位；
```
USE Jxdb59;
SELECT * FROM view_RepInfo_59 WHERE Sno = '202003005104';
```
(简答题, 5分)
通过视图view_RepInfo_学号最后两位，将学生202203005105选修的课程112p0024的成绩修改为76分。
提交内容：
（1）首先查询学生202203005105选修的112p0024的原成绩，将执行结果截图，图片的右下角写上学号的最后两位；
```
# 查原成绩
SELECT * FROM view_RepInfo_59 WHERE Sno = '202203005105' AND Cno = '112p0024';
```
（2）写出修改成绩的语句，将执行结果截图，图片的右下角写上学号的最后两位；
```
# 修改成绩
UPDATE view_RepInfo_59 SET Grade = 76 WHERE Sno = '202203005105' AND Cno = '112p0024';
```
（3）再查询学生202203005105选修的112p0024的新成绩，将执行结果截图，图片的右下角写上学号的最后两位

（4）分析执行结果

视图基于多表连接，但Grade字段来自单表Reports，可以更新。
(简答题, 4分)
创建一个视图，名称设置为view_StuCreditInfo_学号最后两位，用来查询学生已获学分的情况，结果显示学号，姓名和已获学分。
提交内容：
（1）创建视图的语句；（注意，已获学分需要给出字段别名）
```
CREATE VIEW view_StuCreditInfo_59 AS
SELECT s.Sno, s.Sname, SUM(IF(r.Grade >= 60, c.Ccredit, 0)) AS GotCredit
FROM Students59 s
LEFT JOIN Reports59 r ON s.Sno = r.Sno
LEFT JOIN Courses59 c ON r.Cno = c.Cno
GROUP BY s.Sno, s.Sname;
```
（2）查询该视图中学号为202116026101的学生已获学分情况，并将结果截图，图片的右下角写上学号的最后两位；
```
SELECT * FROM view_StuCreditInfo_59 WHERE Sno = '202116026101';
```
(简答题, 5分)
使用 root 用户登录MySQL服务器，然后创建一个新的本机的管理员用户，用户名设置为Man+学号最后两位，初始密码设置为本人学号，授予该用户对数据库jxdb拥有所有的权限，并且使该用户可以将权限授予其他用户。
提交内容：
（1）创建用户的语句；
```
CREATE USER 'Man59'@'localhost' IDENTIFIED BY '202411039159';
```
（2）授权语句；
```
GRANT ALL PRIVILEGES ON Jxdb59.* TO 'Man59'@'localhost' WITH GRANT OPTION;
```
（3）查询mysql.db表中的数据内容，并将对应的结果截图，图片右下角加上学号最后两位；
```
SELECT * FROM mysql.db WHERE User = 'Man59';
```
(简答题, 6分)
使用root用户创建一个教师用户，用户名设置为Tea+学号最后两位，该用户密码设置为'000000'，在命令行窗口，以Tea+学号最后两位用户登录MySQL，然后将该用户的密码设置为完整学号。
提交内容：
（1）创建用户的语句；
```
CREATE USER 'Tea59'@'localhost' IDENTIFIED BY '000000';
```
（2）在命令行窗口，以该教师用户登录MySQL，将命令及成功登录的结果截屏，图片右下角写上学号最后两位；
```
mysql -u Tea59 -p
```
（3）在登录界面，输入修改用户密码的语句，并将语句及执行结果截屏，图片右下角写上学号最后两位。
```
SET PASSWORD='202411039159';
```
(简答题, 5分)
在教师用户Tea+学号最后两位中，查询jxdb数据库中Students学生表所有信息。
提交内容：
（1）查询语句；
```
USE Jxdb59;
SELECT * FROM Students59;
```
（2）将执行结果截图，图片右下角写上学号最后两位；

（3）对执行结果进行分析。

执行失败，因为Tea59没有被授予访问数据库Jxdb59的权限。
(简答题, 5分)
使用管理员用户Man+学号最后两位登录MySQL，授予教师用户Tea+学号最后两位对数据库jxdb的学生表（Students）的SELECT、UPDATE权限，对课程表（Courses）的SELECT权限，以及对选课表（Reports）的SELECT权限以及对Grade字段的UPDATE的权限。
提交内容：
（1）授予权限的语句；
```
GRANT SELECT, UPDATE ON Jxdb59.Students59 TO 'Tea59'@'localhost';
GRANT SELECT ON Jxdb59.Courses59 TO 'Tea59'@'localhost';
GRANT SELECT, UPDATE(Grade) ON Jxdb59.Reports59 TO 'Tea59'@'localhost';
```
（2）在root用户中，查询table_priv表和columns_priv表中的相关信息，并将结果截图，图片右下角写上学号的最后两位
```
SELECT * FROM mysql.tables_priv WHERE User = 'Tea59';
SELECT * FROM mysql.columns_priv WHERE User = 'Tea59';
```
(简答题, 10分)
重新登录教师用户Tea+学号最后两位，验证教师用户的存取权限。
提交内容：
（1）查询jxdb数据库中Courses课程表中课程名包含'数据'的课程信息，将执行结果截图，图片右下角写上学号最后两位；
```
SELECT * FROM Jxdb59.Courses59 WHERE Cname LIKE '%数据%';
```
（2）通过视图view_StuInfoDno03_学号，将学号为'202403038199'的学生的姓名改为'王五'，将执行结果截图，图片右下角写上学号最后两位；
```
UPDATE Jxdb59.view_StuInfoDno03_59 SET Sname = '王五' WHERE Sno = '202403038199';
```
（3）将Reports表中学号为'202203005105'的学生选修的课程号'112p0024'的选修学年改为2018，将执行结果截图，图片右下角写上学号最后两位；
```
UPDATE Jxdb59.Reports59 SET Racademicyear = 2018 WHERE Sno = '202203005105' AND Cno = '112p0024';
```
（4）分别对以上执行结果进行分析。

(1)成功；(2)失败，没有视图view_StuInfoDno03_59的UPDATE权限；(3)失败，只有Grade列的UPDATE权限。
(简答题, 6分)
在root用户中，创建学生角色role_Stu_学号最后两位，并对学生角色授予以下权限：
（1）对Students表的学号、姓名、出生日期、专业编号和学院编号的SELECT权限；
（2）对Courses表和Reports表的SELECT权限；
提交内容：
（1）创建角色的语句；
```
CREATE ROLE 'role_Stu_59';
```
（2）对角色授权的语句；
```
GRANT SELECT(Sno, Sname, Sbirth, Mno, Dno) ON Jxdb59.Students59 TO 'role_Stu_59';
GRANT SELECT ON Jxdb59.Courses59 TO 'role_Stu_59';
GRANT SELECT ON Jxdb59.Reports59 TO 'role_Stu_59';
```
（3）查询table_priv表和columns_priv表，对相应权限进行截图，图片右下角写上学号的最后两位
```
SELECT * FROM mysql.tables_priv WHERE User = 'role_Stu_59';
SELECT * FROM mysql.columns_priv WHERE User = 'role_Stu_59';
```
(简答题, 4分)
在root用户中创建学生用户Stu+学号最后两位，密码设置为本人姓的全拼+学号最后两位，并将该用户赋予学生角色role_Stu_学号最后两位。
提交内容：
（1）创建学生用户的语句；
```
CREATE USER 'Stu59'@'localhost' IDENTIFIED BY 'li59';
```
（2）将该用户赋予学生角色的语句。
```
GRANT 'role_Stu_59' TO 'Stu59'@'localhost';
SET DEFAULT ROLE 'role_Stu_59' TO 'Stu59'@'localhost';
```
(简答题, 8分)
用学生用户Stu+学号最后两位登录MySQL服务器，并对学生的权限进行验证。
提交内容：
（1）登录语句，并将登录结果截图，图片的右下角写上学号的最后两位；
```
mysql -u Stu59 -p
```
（2）在该学生用户中，查询Students表中所有姓张的同学的信息，将结果截图，图片的右下角写上学号的最后两位；
```
SELECT * FROM Jxdb59.Students59 WHERE Sname LIKE '张%';
```
（3）在该学生用户中，查询Students表中所有姓李的同学的学号，姓名，专业号和学院号，将结果截图，图片的右下角写上学号的最后两位；
```
SELECT Sno, Sname, Mno, Dno FROM Jxdb59.Students59 WHERE Sname LIKE '李%';
```
（4）对以上两个结果进行分析。

第一个失败（SELECT *包含无权限列），第二个成功（只查有权限的列）。
(简答题, 12分)
在管理员用户Man+学号最后两位中，将视图view_StuInfoDno03_学号最后两位的SELECT权限授权角色role_Stu_学号最后两位，然后在学生用户Stu99中，对其权限进行验证。
提交内容：
（1）授权语句；
```
GRANT SELECT ON Jxdb59.view_StuInfoDno03_59 TO 'role_Stu_59';
```
（2）在学生用户Stu_学号最后两位中，利用视图view_StuInfoDno03_学号最后两位查询2004年6月1日以后出生的学生信息，将执行结果截图，图片的右下角写上学号的最后两位；
```
# 通过视图查询
SELECT * FROM Jxdb59.view_StuInfoDno03_59 WHERE Sbirth > '2004-06-01';
```
（3）在学生用户Stu_学号最后两位中，在Students表中查询2004年6月1日以后出生的学生信息，将执行结果截图，图片的右下角写上学号的最后两位；
```
# 直接查Students表全部
SELECT * FROM Jxdb59.Students59 WHERE Sbirth > '2004-06-01';
```
（4）在学生用户Stu_学号最后两位中，在Students表中查询2004年6月1日以后出生的学生的学号和姓名，将执行结果截图，图片的右下角写上学号的最后两位；
```
# 查Students表部分列
SELECT Sno, Sname FROM Jxdb59.Students59 WHERE Sbirth > '2004-06-01';
```
（5）对比分析以上三个执行结果。

视图查询成功；直接查全部列失败；查有权限的列成功。

(简答题, 3分)
在root用户中，收回教师用户Tea+学号最后两位对学生表Students的UPDATE权限。
提交内容：
（1）收回权限的语句；
```
REVOKE UPDATE ON Jxdb59.Students59 FROM 'Tea59'@'localhost';
```
（2）查询table_priv表，将相关信息截图，图片的右下角写上学号的最后两位
```
SELECT * FROM mysql.tables_priv WHERE User = 'Tea59';
```
(简答题, 6分)
在root用户中，收回学生用户Stu+学号最后两位的所有权限。
提交内容：
（1）收回权限的语句；
```
REVOKE 'role_Stu_59' FROM 'Stu59'@'localhost';
```
（2）在学生用户Stu+学号最后两位中，查询学生表Students中专业号为0303的学生的学号和姓名，将相关信息截图，图片的右下角写上学号的最后两位；
```
SELECT Sno, Sname FROM Jxdb59.Students59 WHERE Mno = '0303';
```
（3）分析执行结果。

执行失败，角色被收回后没有任何权限。

【MySQL】实验07 数据库的安全性保护

【手机编程】QFLIStudio+ 一款移动端的多语言集成开发工具

【教程篇】华为Mate10降级EMUI10教程

QFLIStudio+ 更新日志

关于魅族21的调教优化篇

【壁纸】Seewo希沃一体机壁纸合集