数据库：头歌实验二数据库安全性技术

一、创建用户

本关任务：

在给定的数据库环境中，完成用户的创建。

编程要求

注意：在本实验中，所有创建用户操作不需要添加主机名。
例如：
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';

/* 注意：请在 begin - end代码块中作答，其他代码不要删除。*/

#1. 创建数据库用户test，密码为1234。
/*------------begin-----------*/

CREATE USER IF NOT EXISTS 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';

二、用户和权限（一）

本关任务：

在给定的数据库环境中，创建用户，并给用户授予指定的权限。

编程要求

今有两个关系模式：
部门（部门号，部门名称）
职工（职工号，姓名，性别，工资，入职日期，部门号）

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';

use demo;

 
#1. 用户test对dept和emp表具有SELECT的权限。
#代码开始

GRANT SELECT
ON dept
TO test;

GRANT SELECT
ON emp
TO test;

#代码结束

三、用户和权限（二）

编程要求

今有两个关系模式：
部门（部门号，部门名称）
职工（职工号，姓名，性别，工资，入职日期，部门号）

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';

use demo;

 
#1. 用户wangming对职工和部门表有INSERT和DELETE权力。
#代码开始
GRANT INSERT,DELETE
ON dept
TO wangming;

GRANT INSERT,DELETE
ON emp
TO wangming;


#代码结束

四、用户和权限（三）

编程要求

今有两个关系模式：
部门（部门号，部门名称）
职工（职工号，姓名，性别，工资，入职日期，部门号）

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';

use demo;

 
#1. 用户liyong对职工表有SELECT权力，对工资字段具有更新权力。
#代码开始
GRANT SELECT,UPDATE(salary)
ON emp
TO liyong;


#代码结束

五、用户和权限（四）

编程要求

在右侧代码文件编辑器填写语句，用SQL的GRANT和REVOKE语句完成以下授权定义或存取控制功能。

用户zhouping具有对两个表所有权力(读、插、改、删数据)，并具有给其他用户授权的权力。
提示：所有权力为ALL PRIVILEGES，在GRANT语句中使用WITH GRANT OPTION选项，被授权的用户就具有了再次将对象权限授予其他用户的能力。

注意：用户在后台已经创建好，不需要自行创建。

测试说明

平台会对你编写的代码使用下面SQL语句进行测试：

#查询zhouping用户所拥有的权限
select user,table_name,table_priv,column_priv from mysql.tables_priv where db = 'demo' and user = 'zhouping';

use demo;

 
#1. 用户zhouping具有对两个表所有权力(读、插、改、删数据)，并具有给其他用户授权的权力。
#   提示：所有权力为ALL PRIVILEGES，在GRANT语句中使用WITH GRANT OPTION选项，被授权的用户就具有了再次将对象权限授予其他用户的能力。
#代码开始

GRANT ALL PRIVILEGES 
ON dept
TO zhouping
WITH GRANT OPTION;
GRANT ALL PRIVILEGES 
ON emp
TO zhouping
WITH GRANT OPTION;

#代码结束

六、用户和权限（五）

编程要求

今有两个关系模式：
部门（部门号，部门名称）
职工（职工号，姓名，性别，工资，入职日期，部门号）

在右侧代码文件编辑器填写语句，用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';

use demo;

 
#1. 用户yanglan具有从每个部门职工中SELECT 部门号(dep_id)，最高工资(别名maxsal)，最低工资(别名minsal)，平均工资(别名avgsal)的权力，他不能查看每个人的工资。
#提示：首先创建视图(v_sal)查询每个部门职工中的部门号，最高工资，最低工资和平均工资，然后授予用户查询视图的权限。

#步骤1：创建视图v_sal

CREATE VIEW v_sal 
AS 
SELECT
	dep_id,
	max( salary ) AS maxsal,
	min( salary ) AS minsal,
	avg( salary ) AS avgsal 
FROM emp;


#步骤2：查看视图
DESC v_sal;



#步骤3：给用户授予查询视图的权限
GRANT SELECT
ON v_sal
TO yanglan;




#代码结束