土豆烤肉_数据库作业5（安全性保护）

土豆烤肉_
已于 2024-07-04 21:38:01 修改
阅读量131
点赞数 1
文章标签：数据库 oracle
于 2024-07-03 15:35:11 首次发布
本文链接：https://blog.csdn.net/2302_76940053/article/details/140153747
版权
/*实验07 数据库的安全性保护*/
/*T3.创建一个视图，名称设置为view_StuInfoDno11_学号最后两位，用来查询学院编号为11的学生的所有信息，并且要求基于该视图进行更新操作时，仍需保证该视图只有该学院的学生。
（1）创建视图的语句；2）查询该视图中女学生的学号，姓名，并将结果截图，*/
CREATE VIEW view_StuInfoDno11_03 AS
SELECT Sno, Sname, Sgender, Sbirth, Dno,Mno,Sclass
FROM students03
WHERE Dno = 11
WITH CHECK OPTION;

SELECT Sno, Sname
FROM view_StuInfoDno11_03
WHERE Sgender = '女'; -- 假设性别字段'女'用字符串'女'表示

/*T4.通过视图view_StuInfoDno11_03，分别插入以下两个学生的信息提交内容：
（1）写出插入学生张三信息的语句；（2）写出在学生表中查询张三信息的语句，并将执行结果截图，图片的右下角写上学号的最后两位；
（3）写出插入学生李四信息的语句；（4）将执行结果截图，图片的右下角写上学号的最后两位；（5）分析两个插入语句的执行结果*/
INSERT INTO students03 (Sno, Sname, Sgender,Sbirth,Snative, Dno,Mno,Sclass)
VALUES ('2022115199', '张三', '男', '2004-06-13','广东潮州',11,1151,'20221151');

SELECT * FROM students03 WHERE Sname = '张三';

INSERT INTO students03 (Sno, Sname, Sgender,Sbirth,Snative, Dno,Mno,Sclass)
VALUES ('2022210101', '李四', '女', '2004-05-10','广东揭阳', 21,2101,'20222101');

SELECT * FROM students03 WHERE Sname = '李四';


/*T5.创建一个视图，名称设置为view_RepInfo_学号最后两位，用来查询学生的选课信息，结果显示学号，姓名，课程号，课程名和成绩。
1）创建视图的语句；2）查询该视图中学号为2014112104的学生的选课情况*/
CREATE VIEW view_RepInfo_03 AS
SELECT   s.Sno,  s.Sname,  r.Cno,  c.Cname,  r.Grade
FROM   students03 s
JOIN   report03 r ON s.Sno = r.Sno
JOIN   courses03 c ON r.Cno = c.Cno;

SELECT *
FROM view_RepInfo_03
WHERE Sno = '2014112104';

/*T6.通过视图view_RepInfo_学号最后两位，将学生2016112105选修的课程112p0024的成绩修改为76分。
  1）写出修改成绩的语句；2）将执行结果截图5）分析执行结果*/
UPDATE view_RepInfo_03
SET Grade = 76
WHERE Sno = '2016112105' AND Cno = '112p0024';
SELECT * FROM view_RepInfo_03 WHERE Sno = '2016112105' AND Cno = '112p0024';

/*T7.创建一个视图，名称设置为view_StuCreditInfo_学号最后两位，用来查询学生已获学分的情况，结果显示学号，姓名和已获学分。
1）创建视图的语句；（注意，已获学分需要给出字段别名）2）查询该视图中学号为2015210101的学生已获学分情况*/
CREATE VIEW view_StuCreditInfo_03 AS
SELECT s.Sno AS 学号,s.Sname AS 姓名,COALESCE(SUM(c.Ccredit), 0) AS TotalCredits
FROM students03 s
LEFT JOIN report03 r ON s.Sno = r.Sno
LEFT JOIN courses03 c ON r.Cno = c.Cno
GROUP BY s.Sno, s.Sname;

SELECT *
FROM view_StuCreditInfo_03
WHERE 学号 = '2015210101';

/*T8.使用 root 用户登录MySQL服务器，然后创建一个新的本机的管理员用户，用户名设置为Man+学号最后两位，初始密码设置为2251+学号最后两位，
  授予该用户对数据库jxdb拥有所有的权限，并且使该用户可以将权限授予其他用户。
（1）创建用户的语句2）授权语句；3）查询mysql.db表中的数据内容，*/
CREATE USER 'Man03'@'localhost' IDENTIFIED BY '225103';
GRANT ALL PRIVILEGES ON jxdb03.* TO 'Man03'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SELECT * FROM mysql.db WHERE User='Man03' AND Host='localhost';
/*T9.使用root用户创建一个教师用户，用户名设置为Tea+学号最后两位，该用户密码设置为'000000'，在命令行窗口，以Tea+学号最后两位用户登录MySQL，
  然后将该用户的密码设置为完整学号。
（1）创建用户的语句；2）在命令行窗口，以该教师用户登录MySQL，将命令及成功登录的结果截屏，图片右下角写上学号最后两位；
  3）在登录界面，输入修改用户密码的语句*/
CREATE USER 'Tea03'@'localhost' IDENTIFIED BY '000000';


/*T10.在教师用户Tea+学号最后两位中，查询jxdb数据库中Students学生表所有信息。
（1）查询语句；（2）将执行结果截图，图片右下角写上学号最后两位；（3）对执行结果进行分析。*/
SELECT * FROM Students03;

/*T11.使用管理员用户Man+学号最后两位登录MySQL，授予教师用户Tea+学号最后两位对数据库jxdb的学生表（Students）的SELECT、UPDATE权限，
  对课程表（Courses）的SELECT权限，以及对选课表（Reports）的SELECT权限以及对Grade字段的UPDATE的权限。
1）授予权限的语句；（2）在root用户中，查询table_priv表和columns_priv表中的相关信息，*/
GRANT SELECT, UPDATE ON jxdb03.Students03 TO 'Tea03'@'localhost';
GRANT SELECT ON jxdb03.Courses03 TO 'Tea03'@'localhost';
GRANT SELECT, UPDATE(Grade) ON jxdb03.Report03 TO 'Tea03'@'localhost';
FLUSH PRIVILEGES;

SELECT *
FROM mysql.tables_priv
WHERE User='Tea03' AND Host='localhost' AND Db='jxdb' AND Table_name IN ('Students03', 'Courses03');

SELECT *
FROM mysql.columns_priv
WHERE User='Tea03' AND Host='localhost' AND Db='jxdb' AND Table_name='Report03'
     AND Column_name='Grade' ;


/*T12.重新登录教师用户Tea+学号最后两位，验证教师用户的存取权限。
（1）查询jxdb数据库中Courses课程表中课程名包含'数据'的课程信息，将执行结果截图，图片右下角写上学号最后两位；
（2）通过视图view_StuInfoDno11_学号，将学号为'2022115199'的学生的姓名改为'王五'，将执行结果截图，图片右下角写上学号最后两位；
（3）将Reports表中学号为'2016112105'的学生选修的课程号'112p0024'的选修学年改为2018，将执行结果截图，图片右下角写上学号最后两位；
（4）分别对以上执行结果进行分析。*/
SELECT * FROM jxdb03.courses03 WHERE Cname LIKE '%数据%';
UPDATE view_StuInfoDno11_03 SET Sname = '王五' WHERE Sno = '2022115199';
SELECT * FROM view_StuInfoDno11_03 WHERE Sno = '2022115199';

UPDATE jxdb03.report03 SET Racademicyear = 2018 WHERE Sno = '2016112105' AND Cno = '112p0024';
SELECT * FROM jxdb03.report03 WHERE Sno = '2016112105' AND Cno = '112p0024';


/*T13.在root用户中，创建学生角色role_Stu_学号最后两位，并对学生角色授予以下权限：
（1）对Students表的学号、姓名、出生日期、专业编号和学院编号的SELECT权限；
（2）对Courses表和Reports表的SELECT权限；
（1）创建角色的语句；（2）对角色授权的语句；
3）查询table_priv表和columns_priv表，对相应权限进行截图，图片右下角写上学号的最后两位*/
CREATE ROLE 'role_Stu_03'@'localhost';

GRANT SELECT (Sno, Sname, Sbirth, Mno, Dno) ON jxdb03.Students03 TO 'role_Stu_03'@'localhost';
GRANT SELECT ON jxdb03.Courses03 TO 'role_Stu_03'@'localhost';
GRANT SELECT ON jxdb03.Report03 TO 'role_Stu_03'@'localhost';

SHOW GRANTS FOR 'role_Stu_03'@'localhost';

/*T14.在root用户中创建学生用户Stu+学号最后两位，密码设置为本人姓的全拼+学号最后两位，并将该用户赋予学生角色role_Stu_学号最后两位。
（1）创建学生用户的语句；（2）将该用户赋予学生角色的语句*/
CREATE USER 'Stu03'@'localhost' IDENTIFIED BY 'duanxinyao03';
GRANT 'role_Stu_03'@'localhost' TO 'Stu03'@'localhost';
SHOW GRANTS FOR 'Stu03'@'localhost';

/*T15.用学生用户Stu+学号最后两位登录MySQL服务器，并对学生的权限进行验证。
（1）登录语句，并将登录结果截图，图片的右下角写上学号的最后两位；
（2）在该学生用户中，查询Students表中所有姓张的同学的信息，将结果截图，图片的右下角写上学号的最后两位；
（3）在该学生用户中，查询Students表中所有姓李的同学的学号，姓名，专业号和学院号，将结果截图，图片的右下角写上学号的最后两位；
（4）对以上两个结果进行分析*/
SELECT * FROM students03 WHERE Sname LIKE '张%';
SELECT Sno, Sname, Mno, Dno FROM students03 WHERE Sname LIKE '李%';

/*T16.在管理员用户Man+学号最后两位中，将视图view_StuInfoDno11_学号最后两位的SELECT权限授权角色role_Stu_学号最后两位，然后在学生用户Stu99中，对其权限进行验证。
（1）授权语句
（2）在学生用户Stu_学号最后两位中，利用视图view_StuInfoDno11_学号最后两位查询1998年6月1日以后出生的学生信息，将执行结果截图，图片的右下角写上学号的最后两位；
（3）在学生用户Stu_学号最后两位中，在Students表中查询1998年6月1日以后出生的学生信息，将执行结果截图，图片的右下角写上学号的最后两位；
（4）在学生用户Stu_学号最后两位中，在Students表中查询1998年6月1日以后出生的学生的学号和姓名，将执行结果截图，图片的右下角写上学号的最后两位；
（5）对比分析以上三个执行结果*/
-- 删除旧的视图
DROP VIEW IF EXISTS view_StuInfoDno11_03;

-- 创建新的视图，包含 Sbirth 字段
CREATE VIEW view_StuInfoDno11_03 AS
SELECT Sno, Sname, Sgender, Sbirth, Dno,Mno,Sclass
FROM students03
WHERE Dno = 11
WITH CHECK OPTION;


GRANT SELECT ON view_StuInfoDno11_03 TO 'role_Stu_03'@'localhost';
GRANT 'role_Stu_03'@'localhost' TO 'Stu03'@'localhost';
FLUSH PRIVILEGES ;

SELECT * FROM view_StuInfoDno11_03 WHERE Sbirth > '1998-06-01';
SELECT * FROM students03 WHERE Sbirth > '1998-06-01';
SELECT Sno, Sname FROM students03 WHERE Sbirth > '1998-06-01';

/*T17.在root用户中，收回教师用户Tea+学号最后两位对学生表Students的UPDATE权限。
（1）收回权限的语句；
（2）查询table_priv表，将相关信息截图，图片的右下角写上学号的最后两位*/

REVOKE UPDATE ON jxdb03.Students03 FROM 'Tea03'@'localhost';

SELECT *FROM mysql.tables_priv WHERE User='Tea03' AND Host='localhost' AND Db='jxdb03';

/*T18.在root用户中，收回学生用户Stu+学号最后两位的所有权限。
（1）收回权限的语句；
（2）在学生用户Stu+学号最后两位中，查询学生表Students中专业号为1151的学生的学号和姓名，将相关信息截图，图片的右下角写上学号的最后两位
（3）分析执行结果

  管理员用户Man03 225103
  root 123456
  Tea03 202203005103
  Stu03 duanxinyao03*/

REVOKE ALL PRIVILEGES ON *.*FROM 'Stu03'@'localhost';

DROP USER 'Stu03'@'localhost';
FLUSH PRIVILEGES;

SELECT Sno,Sname
FROM students03
WHERE Mno = 1151;