实验二 数据库安全性技术（8.0）
制作不易！点个关注！给大家带来更多的价值！
目录
第1关：创建用户
任务描述
本关任务：
在给定的数据库环境中，完成用户的创建。
数据库环境
mysql8.0.28 Debian GNU/Linux 10
相关知识
创建用户
可以使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。其基本语法格式如下：
CREATE USER <用户> [ IDENTIFIED BY [ PASSWORD ] 'password' ] [ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]
参数说明如下：
1) 用户
指定创建用户账号，格式为 user_name'@'host_name。这里的user_name是用户名，host_name为主机名，即用户连接 MySQL 时所用主机的名字。如果在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为“%”，表示一组主机，即对所有主机开放权限。
2) IDENTIFIED BY子句
用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句。
3) PASSWORD ‘password’
PASSWORD 表示使用哈希值设置密码，该参数可选。如果密码是一个普通的字符串，则不需要使用 PASSWORD 关键字。‘password’ 表示用户登录时使用的密码，需要用单引号括起来。
使用 CREATE USER 语句时应注意以下几点：
1.CREATE USER 语句可以不指定初始密码。但是从安全的角度来说，不推荐这种做法。
2.使用 CREATE USER 语句必须拥有 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限。
3.使用 CREATE USER 语句创建一个用户后，MySQL 会在 mysql 数据库的 user 表中添加一条新记录。
4.CREATE USER 语句可以同时创建多个用户，多个用户用逗号隔开。
新创建的用户拥有的权限很少，它们只能执行不需要权限的操作。如登录 MySQL、使用 SHOW 语句查询所有存储引擎和字符集的列表等。如果两个用户的用户名相同，但主机名不同，MySQL 会将它们视为两个用户，并允许为这两个用户分配不同的权限集合。
例 1
使用 CREATE USER 创建一个用户，用户名是 test1，密码是 test1，主机名是 localhost。SQL 语句和执行过程如下。
mysql> CREATE USER 'test1'@'localhost' IDENTIFIED BY 'test1';
Query OK, 1 rows affected (0.06 sec)
结果显示，创建 test1 用户成功。
注意： 在MySQL 5.7.6及更高版本中，可以使用下面语句来创建用户。
CREATE USER IF NOT EXISTS 'user'@'localhost' IDENTIFIED BY 'password';
MySQL在安装时，初始用户名为root，此为系统管理员用户，其余用户均由root创建并授权，经授权的用户也可以创建用户。
在生产环境中，除了管理员，其它人应当使用由管理员创建的账户，不得使用root帐户。出于安全考虑，一般root用户也仅限在服务器本地登录，不轻易开放远程登录。
删除用户
在 MySQL 数据库中，可以使用 DROP USER 语句删除用户，也可以直接在 mysql.user 表中删除用户以及相关权限。
1. 使用 DROP USER 语句删除普通用户
使用 DROP USER 语句删除用户的语法格式如下：
DROP USER <用户1> [ , <用户2> ]…
其中，用户用来指定需要删除的用户账号。
使用 DROP USER 语句应注意以下几点：
1.DROP USER 语句可用于删除一个或多个用户，并撤销其权限。
2.使用 DROP USER 语句必须拥有 mysql 数据库的 DELETE 权限或全局 CREATE USER 权限。
3.在 DROP USER 语句的使用中，若没有明确地给出账户的主机名，则该主机名默认为“%”。
注意：用户的删除不会影响他们之前所创建的表、索引或其他数据库对象，因为 MySQL 并不会记录是谁创建了这些对象。
例 1
下面使用 DROP USER 语句删除用户’test1@‘localhost’。SQL 语句和执行过程如下。
mysql> DROP USER 'test1'@'localhost';
Query OK, 0 rows affected (0.00 sec)
在 cmd 命令行工具中，使用 test1 用户登录数据库服务器，发现登录失败，说明用户已经删除，如下所示。
C:\Users\USER>mysql -h localhost -u test1 -p
Enter password: ****
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
2. 使用DELETE语句删除普通用户
可以使用 DELETE 语句直接删除 mysql.user 表中相应的用户信息，但必须拥有 mysql.user 表的 DELETE 权限。其基本语法格式如下：
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
Host 和 User 这两个字段都是 mysql.user 表的主键。因此，需要两个字段的值才能确定一条记录。
例 2
下面使用 DELETE 语句删除用户’test2’@‘localhost’。SQL 语句和执行过程如下所示。
DELETE FROM mysql.user WHERE Host='localhost'AND User='test2';
Query OK, 1 rows affected (0.00 sec)
结果显示删除成功。可以使用 SELETE 语句查询 mysql.user 表，以确定该用户是否已经成功删除。
注意： 在MySQL 5.7.6及更高版本中，可以使用DROP USER 语句从系统数据库中删除用户。如果用户不存在，使用关键词 IF EXISTS 可避免出现警告。执行 DROP USER 语句需要拥有 CREATE USER 权限。
例3
mysql80> DROP USER IF EXISTS idontexist;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql80> SHOW WARNINGS;
+-------+------+---------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------+
| Note | 3162 | Authorization ID 'idontexist'@'%' does not exist. |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
编程要求
注意：在本实验中，所有创建用户操作不需要添加主机名。
例如：
create user 用户名 identified by ‘用户登录密码’
在右侧代码文件编辑器填写语句，完成用户的创建。
创建数据库用户test，密码为1234。
删除数据库用户test。
测试说明
平台会对你编写的代码使用下面SQL语句进行测试：
#查询是否创建test用户
select user,host from mysql.user where user not like 'mysql.%' and user <> 'root' and name = 'test';
测试输入：无；
预期输出：
user host
test %
代码如下：
/* 注意：请在 begin - end代码块中作答，其他代码不要删除。*/
#1. 创建数据库用户test，密码为1234。
/*------------begin-----------*/
CREATE USER 'test' IDENTIFIED BY '1234';
/*------------end------------*/
select user,host from mysql.user where user not like 'mysql.%' and user <> 'root' and user = 'test';
#2. 删除数据库用户test。
/*------------begin-----------*/
DROP USER 'test';
/*------------end------------*/
select user,host from mysql.user where user not like 'mysql.%' and user <> 'root' and user = 'test';
第2关：用户和权限（一）
任务描述
本关任务：
在给定的数据库环境中，创建用户，并给用户授予指定的权限。
相关知识
权限
MySQL常用的权限有：
all: 所有权限(grant option除外)
alter: alter table权限
alter routine: alter 存储过程
create: create database/table
create role: create role
create foutine: create 存储过程和函数
create user： create/alter/rename/drop user
create view: create view
delete: delete语句
drop: drop database/table
drop role: drop role
execute: 调用存储过程或函数
index：create/drop index
insert: insert语句
select: select语句
trigger: 触发器相关操作
update: update语句 等。 select,update,insert,delete还可以用在列上，如select(c_id),update(b_balance)等。
角色(Role)
角色是权限的集合。如果有一组人(承担相同职责的小组,或者説小组成员扮演相同的角色)应该被授予一组相同的权限，不妨创建一个角色，将那组权限授予该角色，然后再将角色授予该组的每个成员。这比一个个地给每个组员授予一批权限要方便得多。
创建角色的语句：
CREATE ROLE [IF NOT EXISTS] role [, role ] ...
一次可以创建多个角色。
删除角色：
DROP ROLE [IF EXISTS] role [, role ] ...
角色被删除后，拥有该角色的用户立即失去角色定义的权限组合。不过，如果用户同时拥有多个角色，两个角色代表的权限集合如果有交集，则该用户仍拥有交集代表的权限。
GRANT授权语句
以下语句授予权限给用户或角色：
grant 权限[,权限] ... on 数据库对象 to user|role,[user|role]... [with grant option]
可以同时将多个权限授予多个用户或角色。
with grant option表示被授权用户可以传播权限，即授权该用户将其拥有的权限（之前获得的权限，通过本语句获得的权限，以及今后获得的权限）再授予其它用户。
以下语句授予角色所代表的权限集给用户或角色：
GRANT role [, role] ... TO user_or_role [, user_or_role] ... [WITH ADMIN OPTION]
总之，GRANT语句可以将权限或角色（权限集合）授予用户或角色。但是不能将权限和角色混合授予用户(或角色)。不过，你可以分开用两条不同的GRANT语句来实现：直接授权语句有关键词ON，间接授权(角色代表的权限集合)语句不带ON关键词。
REVOKE收回权限语句
以下语句将对象的权限从用户或角色手中收回：
revoke 权限[,权限]... on 数据库对象 from user|role[,user|role]...
下列语句把role所代表的权限集合从用户或角色中收回：
REVOKE role [, role ] ... FROM user_or_role [, user_or_role ] ...
如果用户本身拥有多个角色所代表的权限集合，而这些集合存在交集，收回其中部分角色代表的权限集后，用户可能仍拥有那个角色所代表的部分权限(交集代表的那部分权限)。
编程要求
今有两个关系模式：
部门（部门号，部门名称）
职工（职工号，姓名，性别，工资，入职日期，部门号）
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
创建职工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dname) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',3);
在右侧代码文件编辑器填写语句，用SQL的GRANT和REVOKE语句完成以下授权定义或存取控制功能。
用户test对dept和emp表具有SELECT的权限。
注意：用户在后台已经创建好，不需要自行创建。
测试说明
平台会对你编写的代码使用下面SQL语句进行测试：
#查询wangming用户所拥有的权限
select user,table_name,table_priv,column_priv from mysql.tables_priv where db = 'demo' and user = 'test';
测试输入：无；
预期输出
user table_name table_priv column_priv
test dept Select
test emp Select
1.测试通过后可以使用下面方法自行验证权限授予情况。
2.使用root账号登录收回权限后再次测试。
开始你的任务吧，祝你成功！
代码如下：
use demo;
#1. 用户test对dept和emp表具有SELECT的权限。
#代码开始
grant select on demo.emp to test;
grant select on demo.dept to test;
#代码结束
第3关：用户和权限（二）
编程要求
今有两个关系模式：
部门（部门号，部门名称）
职工（职工号，姓名，性别，工资，入职日期，部门号）
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
# 创建职工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dname) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',3);
在右侧代码文件编辑器填写语句，用SQL的GRANT和REVOKE语句完成以下授权定义或存取控制功能。
用户wangming对职工和部门表有INSERT和DELETE权力。
**注意：用户在后台已经创建好，不需要自行创建。**
测试说明
平台会对你编写的代码使用下面SQL语句进行测试：
#查询wangming用户所拥有的权限
select user,table_name,table_priv,column_priv from mysql.tables_priv where db = 'demo' and user = 'wangming';
测试输入：无；
预期输出：
user table_name table_priv column_priv
wangming dept Insert,Delete
wangming emp Insert,Delete
1.测试通过后可以使用下面方法自行验证权限授予情况。
2.使用root账号登录收回权限后再次测试。
开始你的任务吧，祝你成功！
代码如下：
use demo;
#1. 用户wangming对职工和部门表有INSERT和DELETE权力。
#代码开始
grant INSERT,DELETE on demo.emp to wangming;
grant INSERT,DELETE on demo.dept to wangming;
#代码结束
第4关：用户和权限（三）
编程要求
今有两个关系模式：
部门（部门号，部门名称）
职工（职工号，姓名，性别，工资，入职日期，部门号）
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
# 创建职工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dname) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',3);
在右侧代码文件编辑器填写语句，用SQL的GRANT和REVOKE语句完成以下授权定义或存取控制功能。
用户liyong对职工表有SELECT权力，对工资字段具有更新权力。
注意：用户在后台已经创建好，不需要自行创建。
测试说明
平台会对你编写的代码使用下面SQL语句进行测试：
#查询liyong用户所拥有的权限
select user,table_name,table_priv,column_priv from mysql.tables_priv where db = 'demo' and user = 'liyong';
测试输入：无；
预期输出：
user table_name table_priv column_priv
liyong emp Select Update
1.测试通过后可以使用下面方法自行验证权限授予情况。
2.使用root账号登录收回权限后再次测试。
开始你的任务吧，祝你成功！
代码如下：
use demo;
#1. 用户liyong对职工表有SELECT权力，对工资字段具有更新权力。
#代码开始
grant select,update(salary) on demo.emp to liyong;
#代码结束
第5关：用户和权限（四）
编程要求
在右侧代码文件编辑器填写语句，用SQL的GRANT和REVOKE语句完成以下授权定义或存取控制功能。
用户zhouping具有对两个表所有权力(读、插、改、删数据)，并具有给其他用户授权的权力。
**提示：**所有权力为ALL PRIVILEGES，在GRANT语句中使用WITH GRANT OPTION选项，被授权的用户就具有了再次将对象权限授予其他用户的能力。
注意：用户在后台已经创建好，不需要自行创建。
测试说明
今有两个关系模式：
部门（部门号，部门名称）
职工（职工号，姓名，性别，工资，入职日期，部门号）
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
# 创建职工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dname) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',3);
平台会对你编写的代码使用下面SQL语句进行测试：
#查询zhouping用户所拥有的权限
select user,table_name,table_priv,column_priv from mysql.tables_priv where db = 'demo' and user = 'zhouping';
测试输入：无；
预期输出：
user table_name table_priv column_priv
zhouping dept Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger
zhouping emp Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger
1.测试通过后可以使用下面方法自行验证权限授予情况。
2.使用root账号登录收回权限后再次测试。
开始你的任务吧，祝你成功！
代码如下：
use demo;
#1. 用户zhouping具有对两个表所有权力(读、插、改、删数据)，并具有给其他用户授权的权力。
# 提示：所有权力为ALL PRIVILEGES，在GRANT语句中使用WITH GRANT OPTION选项，被授权的用户就具有了再次将对象权限授予其他用户的能力。
#代码开始
grant ALL PRIVILEGES on demo.emp to zhouping WITH GRANT OPTION;
grant ALL PRIVILEGES on demo.dept to zhouping WITH GRANT OPTION;
#代码结束
第6关：用户和权限（五）
编程要求
今有两个关系模式：
部门（部门号，部门名称）
职工（职工号，姓名，性别，工资，入职日期，部门号）
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
# 创建职工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dname) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',3);
在右侧代码文件编辑器填写语句，用SQL的GRANT和REVOKE语句完成以下授权定义或存取控制功能。
用户yanglan具有从每个部门职工中SELECT部门号(dep_id)，最高工资(别名maxsal)，最低工资(别名minsal)，平均工资(别名avgsal)的权力，他不能查看每个人的工资。
**提示：**首先创建视图(v_sal)查询每个部门职工中的部门编号，最高工资，最低工资和平均工资，然后授予用户查询视图的权限。
注意：用户在后台已经创建好，不需要自行创建。
测试说明
平台会对你编写的代码使用下面SQL语句进行测试：
#查询yanglan用户所拥有的权限
select user,table_name,table_priv,column_priv from mysql.tables_priv where db = 'demo' and user = 'yanglan';
测试输入：无；
预期输出：
Field Type Null Key Default Extra
dep_id int YES NULL
maxsal double YES NULL
minsal double YES NULL
avgsal double YES NULL
user table_name table_priv column_priv
yanglan v_sal Select
1.测试通过后可以使用下面方法自行验证权限授予情况。
2.使用root账号登录收回权限后再次测试。
开始你的任务吧，祝你成功！
代码如下：
use demo;
#1. 用户yanglan具有从每个部门职工中SELECT最高工资(别名maxsal)，最低工资(别名minsal)，平均工资(别名avgsal)的权力，他不能查看每个人的工资。
#提示：首先创建视图(v_sal)查询每个部门职工中的最高工资，最低工资和平均工资，然后授予用户查询视图的权限。
#步骤1：创建视图v_sal
CREATE VIEW v_sal AS SELECT dep_id,MAX(emp.`salary`)AS maxsal,MIN(emp.`salary`)AS minsal,AVG(salary) AS avgsal FROM emp,dept WHERE emp.`dep_id`=dept.`did` GROUP BY dname;
#步骤2：查看视图
DESCRIBE v_sal;
#步骤3：给用户授予查询视图的权限
grant select on v_sal to yanglan;
#代码结束
2533
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
