数据库实验三-数据控制(安全性部分)

数据控制(安全性部分)

实验环境：Windows 10操作系统、MySQL 8.0、Navicat

实验内容与完成情况： 一、实验目的 （1）熟悉通过SQL对数据进行安全性控制。 （2）完成书本上习题的上机练习。 二、实验平台MySQL MySQL及其交互式查询工具Navicat。 三、实验内容和要求 使用SQL对数据进行安全性控制，包括：授权和权力回收。操作完成后查看已授权的用户是否真正具有授予的数据操作的权力；权力收回操作之后的用户是否确实丧失了收回的数据操作的权力。 四、实验步骤 1.第6题：对下列两个关系模式： 学生（学号，姓名，年龄，性别，家庭住址，班级号） 班级（班级号，班级名，班主任，班长） 【SQL语句创建关系模式】 （实验源码见文章末） 创建关系模式成功： 【使用GRANT语句完成下列授权功能】 （1）授予用户U1对两个表的所有权限，并可给其他用户授权。 （2）授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。 （3）将对班级表查看权限授予所有用户。 GRANT SELECT ON TABLE Class TO PUBLIC; 由于MySQL没有实现PUBLIC关键词。所以，要把一个表的select权限授予所有用户，需要自己遍历所有用户。 （4）将对学生表的查询、更新权限授予角色R1。 （5）将角色R1授予用户U1，并且U1可继续授权给其他角色。 【查看已授权的用户是否真正具有授予的数据操作的权力】 U1具有授予的数据操作的权力： U2具有授予的数据操作的权力： R1具有授予的数据操作的权力： 2.第7题：今有以下两个关系模式： 职工（职工号，姓名，年龄，职务，工资，部门号） 部门（部门号，名称，经理名，地址，电话号） 【SQL语句创建关系模式】 创建关系模式成功： 【创建题中用户】 【加上视图机制】 【用SQL语句的GRANT和REVOKE语句（加上视图机制）完成以下授权定义或存取控制功能】 （1）用户王明对两个表有SELECT权限。 （2）用户李勇对两个表有INSERT和DELETE权限。 （3）每个职工只对自己的记录有SELECT权限。 （4）用户刘星对职工表有SELECT权限，对工资字段具有更新权限。 （5）用户张新具有修改这两个表的结构的权限。 （6）用户周平具有对两个表的所有权限（读、插、改、删数据），并具有给其他用户授权的权限。 （7）用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。 【查看已授权的用户是否真正具有授予的数据操作的权力】 王明具有授予的数据操作的权力： 李勇具有授予的数据操作的权力： 刘星具有授予的数据操作的权力： 张新具有授予的数据操作的权力： 周平具有授予的数据操作的权力： 杨兰具有授予的数据操作的权力： 3.第8题：针对习题7中（1）～（7）的每一种情况，撤销每个用户所授予的权限。 【权力收回操作之后的用户是否确实丧失了收回的数据操作的权力】 所有用户数据操作的权力均被撤回，如下图所示：

出现的问题： 问题一 由于使用的是MySQL数据库，对所有用户进行授权操作时无法使用PUBLIC，需要自行遍历。 问题二 若使用 CREATE USER 'U1'@'localhost' IDENTIFIED BY '123456'; 命令创建用户，直接使用以下命令 GRANT ALL ON TEST3.* TO U1 WITH GRANT OPTION; 对用户授权会报错。

解决方案： 问题一 使用public是被告知不能使用grant创建用户，public似乎被解析为一个用户名，故需要通过select user,host  FROM mysql.user;将所有用户列出再进行授权操作。 问题二 修改授权命令为：GRANT ALL ON TEST3.* TO 'U1'@'localhost' WITH GRANT OPTION;

心得体会： 通过本次实验，更加理解和掌握了如何使用关系数据库标准SQL语言中的DCL语句实现数据库安全性。操作完成后查看已授权的用户是否真正具有授予的数据操作的权力时，可以直接使用图形化交互界面Navicat点击用户查看对应用户的权限，也可以通过数据库登录用户进行验证，如下图所示（R1无删除表的权限）。 同时通过对用户的建立，了解到用户组与角色的概念有着相似之处。用户组是用户的集合，一个用户组可以包括多个用户，一个用户也可以属于多个用户组；角色则是权限的集合，一个用户可以拥有多个角色的身份，多个用户也可以属于同一种角色。它们都能够实现对用户权限更加灵活方便的管理。

【实验源码-便于复制】

/* 今有以下两个关系模式：
学生（学号，姓名，年龄，性别，家庭住址，班级号）
班级（班级号，班级名，班主任，班长）*/
CREATE DATABASE IF NOT EXISTS TEST3 CHARACTER SET 'utf8';
USE TEST3;
CREATE TABLE IF NOT EXISTS Student( 
    Sno VARCHAR(9) PRIMARY KEY, 
    Sname VARCHAR(5) UNIQUE, 
    Sage INT(2), 
    Ssex VARCHAR(2),
    Shome VARCHAR(20),
    Sclass VARCHAR(9)
);
CREATE TABLE IF NOT EXISTS Class( 
    Cno VARCHAR(9) PRIMARY KEY,
    Cname VARCHAR(20) UNIQUE,
    ​Ctear VARCHAR(5),
    Cmon VARCHAR(9)
);
-- 1.授予用户U1对两个表的所有权限，并可给其他用户授权。
CREATE ROLE U1;
GRANT ALL ON TEST3.* TO U1 WITH GRANT OPTION;
-- 2.授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。
CREATE ROLE U2;
GRANT SELECT,UPDATE(Shome) ON TABLE Student TO U2;
-- 3.将对班级表查看权限授予所有用户。
GRANT SELECT ON TABLE Class TO PUBLIC;
/*MySQL没有实现public关键词。所以，要把一个表(如表table1)的select权限授予所有用户，需要自己遍历所有用户。*/
GRANT SELECT ON TABLE Class TO U1,U2;
-- 4.将对学生表的查询、更新权限授予角色R1。
CREATE ROLE R1;
GRANT SELECT,UPDATE ON TABLE Student TO R1;
-- 5.将角色R1授予用户U1，并且U1可继续授权给其他角色。
GRANT R1 TO U1 WITH ADMIN OPTION;

/* 今有以下两个关系模式：
职工（职工号，姓名，年龄，职务，工资，部门号）
部门（部门号，名称，经理名，地址，电话号）*/
CREATE DATABASE IF NOT EXISTS TEST3B CHARACTER SET 'utf8';
USE TEST3B;
create table Staff
(
    Stno char(10) PRIMARY KEY,
    Stname char(10),
    Stage int(2),
    Stjob char(5),
    Stwage int(10),
    Stdno char(5)
);
create table Department
(
    Dno char(5) PRIMARY KEY,
    Dname char(10),
    Dmname char(10),
    Daddress char(20),
    Dphone char(11)
);
-- 创建题中用户
CREATE ROLE wangming;
CREATE ROLE liyong;
CREATE ROLE liuxing;
CREATE ROLE zhangxin;
CREATE ROLE zhouping;
CREATE ROLE yanglan;
-- 加上视图机制
CREATE VIEW Staff_view AS SELECT * FROM Staff;
CREATE VIEW Department_view AS SELECT * FROM Department;
-- （1）加上视图机制，用户王明对两个表有SELECT权限。
GRANT SELECT ON Staff_view TO wangming;
GRANT SELECT ON Department_view TO wangming;
-- （2）加上视图机制，用户李勇对两个表有INSERT和DELETE权限。
GRANT INSERT,DELETE ON Staff_view TO liyong;
GRANT INSERT,DELETE ON Department_view TO liyong;
-- （3）每个职工只对自己的记录有SELECT权限。
CREATE VIEW Staff_view2 AS SELECT * FROM Staff_view WHERE concat(Stname,'@%') = USER();
GRANT SELECT ON Staff_view2 TO wangming,liyong,liuxing,zhangxin,zhouping,yanglan;
-- （4）加上视图机制，用户刘星对职工表有SELECT权限，对工资字段具有更新权限。
GRANT SELECT,UPDATE(Stwage) ON Staff_view TO liuxing;
-- （5）加上视图机制，用户张新具有修改这两个表的结构的权限。
GRANT ALTER ON Staff_view TO zhangxin;
GRANT ALTER ON Department_view TO zhangxin;
-- （6）加上视图机制，用户周平具有对两个表的所有权限（读、插、改、删数据），并具有给其他用户授权的权限。
GRANT ALL PRIVILEGES ON Staff_view TO zhouping WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON Department_view TO zhouping WITH GRANT OPTION;
-- （7）加上视图机制，用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。
CREATE VIEW Depwage_view(Dname,maxs,mins,avgs) AS SELECT Dname,max(Stwage),min(Stwage),avg(Stwage) 
FROM Staff_view,Department_view WHERE Staff_view.Stname=Department_view.Dname 
GROUP BY Staff_view.Stno,Department_view.Dno;
GRANT SELECT ON Depwage_view TO yanglan;

-- 针对上述（1）～（7）的每一种情况，使用MySQL语句撤销用户所授予的权限。

-- （1）撤销王明对两个表的SELECT权限。
REVOKE SELECT ON Staff_view FROM wangming;
REVOKE SELECT ON Department_view FROM wangming;
-- （2）撤销李勇对两个表的INSERT和DELETE权限。
REVOKE INSERT,DELETE ON Staff_view FROM liyong;
REVOKE INSERT,DELETE ON Department_view FROM liyong;
-- （3）撤销每个职工只对自己的记录有SELECT权限。
REVOKE SELECT ON Staff_view2 FROM wangming,liyong,liuxing,zhangxin,zhouping,yanglan;
-- （4）撤销刘星对职工表的SELECT权限，对工资字段具有更新权限。
REVOKE SELECT,UPDATE(Stwage) ON Staff_view FROM liuxing;
-- （5）撤销张新具有修改这两个表的结构的权限。
REVOKE ALTER ON Staff_view FROM zhangxin;
REVOKE ALTER ON Department_view FROM zhangxin;
-- （6）撤销周平具有对两个表的所有权限（读、插、改、删数据），并具有给其他用户授权的权限。
REVOKE ALL PRIVILEGES ON Staff_view FROM zhouping;
REVOKE ALL PRIVILEGES ON Department_view FROM zhouping;
-- （7）撤销杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限。
REVOKE SELECT ON Depwage_view FROM yanglan;