数据库-安全设计

任务描述

下面为某部门数据库安全策略 订单数据库中包括名为代理商，客户，产品，订单，订货项目等5个数据表。

1. 数据库维护人员（1人）：可对订单数据库进行任何操作。 账号名称：system_dbowner，允许任何ip通过此用户连接数据库，密码为usercode1

2. 数据录入人员（2人）：可对订单数据库中所有表进行插入、删除、更新操作，不能创建与修改表结构及其它授权等操作。 账号名称：datarecorder1, datarecorder2，允许任何ip通过此用户连接数据库，密码为usercode1

3. 订单管理人员（2人）：能对订单数据库中的订单表和项目表进行插入、删除、更新操作，其它表仅能查询。不能创建与修改表结构及其它授权等操作。 账号名称：order_1,order_2，允许任何ip通过此用户连接数据库，密码为usercode1

4. 客户管理人员（2人）：能对订单数据库中的代理商表和客户表进行插入、删除、更新，其它表仅能查询。不能创建与修改表结构及其它授权等操作。 账号名称：customer_1, customer_2，允许任何ip通过此用户连接数据库，密码为usercode1

   ​ 要求： （1）写sql语句实现。合理分配用户权限。

   ​ （2）为了能在educoder平台上正确进行测试，所有新增用户密码统一设置为usercode1

相关知识

Mysql用户管理和权限管理

1、用户管理

(1）创建用户

CREATE USER  'user1'@'localhost' IDENTIFIED BY  'user1coder';

上面语句是创建名为user1的用户，密码为user1coder，只能在localhost登录。

create user  'user2'@'%' identified by '123';

上面语句是创建名为user2，密码为123的用户，可以通过任何ip连接数据库。

create user  user3 identified by '123';

上面的语句创建名为user3，密码为123的用户，可以通过任何ip连接数据库。

注： % 表示所有主机 192.168.2.% 表示192.168.2.0网段的所有主机 192.168.2.8(localhost) 表示指定主机。

(2）删除用户

DROP USER  'user1'@'localhost';

(3）查看用户

select user,host  from  mysql.user;

2、权限管理

grant 权限列表 on 库名.表名 to 用户名@’客户端主机’

（1）部分权限列表

all   所有权限（不包括授权权限）
Delete   权限代表允许删除行数据的权限
Insert   权限代表是否允许在表里插入数据
Select   权限代表允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select 1+1， Select PI()+2；而且select权限在执行update/delete语句中含有where条件的情况下也是需要的
Update   权限代表允许修改表中的数据的权限
Usage    权限是创建一个用户之后的默认权限，其本身代表连接登录权限

（2）库名.表名

*.*  所有数据库下的所有表
数据库.*  指定数据库下的所有表
数据库名称.表名称 指定数据库的指定表
select(col1名称),insert(col1名称,col2名称) on mydb.mytable 只能对mydb.mytable第一列有读权限，第一第二列有插入权限

例子：

（1）给user1用户所有库所有表的权限，没有授权权限，允许任何ip通过此用户连接数据库

grant all on *.* to 'user1'@'%';

（2）给user2用户所有库所有表的权限，有授权权限，相当于root用户，允许任何ip通过此用户连接数据库

grant all on *.* to 'user2'@'%'  with grant option;

（3）给user3用户db1库下所有表的权限，允许任何ip通过此用户连接数据库

grant all on db1.* to  'user3'@'%';

（4）对订单数据库中的代理商表中的代理商编号有插入权限，姓名有选择权限

例如: grant insert(代理商编号),select(姓名) on 订单数据库.代理商 to test;

（3）查看权限

show grants for 'user1'@'localhost';

（4）回收权限

revoke 权限列表 on 对象列表 from 用户列表
revoke all on mydb.* from 'user1'@'localhost';     //回收所有权限
revoke select,delete on *.* from 'user1'@'%';      //回收部分权限

（5）注意事项

（1）因为update,delete这些语句中有些也需要进行数据的查找，因此希望顺利进行更新和查找，还需要具有select权限。

（2）新建用户，修改完权限等之后，一定要刷新服务，刷新服务用：

flush privileges;

sql语句

/*
1、数据库维护人员（1人）：可对订单数据库进行任何操作。
账号名称：system_dbowner，密码为usercode1，允许任何ip通过此用户连接数据库
*/
CREATE USER 'system_dbowner'@'%' IDENTIFIED BY 'usercode1';
GRANT ALL ON 订单数据库.* TO 'system_dbowner'@'%'IDENTIFIED BY 'usercode1';


/*
2、数据录入人员（2人）：可对订单数据库中所有表进行插入、删除、更新操作，
（注意：为了顺利进行删除更新操作，也需要能有查询权限）
不能创建与修改表结构及其它授权等操作。
账号名称：datarecorder1, datarecorder2，密码为usercode1，允许任何ip通过此用户连接数据库
*/
CREATE USER 'datarecorder1'@'%' IDENTIFIED BY 'usercode1';
CREATE USER 'datarecorder2'@'%' IDENTIFIED BY 'usercode1';
GRANT SELECT, INSERT, DELETE, UPDATE
ON 订单数据库.* TO 'datarecorder1'@'%'IDENTIFIED BY 'usercode1', 
'datarecorder2'@'%'IDENTIFIED BY 'usercode1';



/*
3、订单管理人员（2人）：能对订单数据库中的订单表和订货项目表进行插入、删除、更新操作，
其它表仅能查询（注意：为了顺利进行删除更新操作，订单表和订货项目表也需要能有查询权限）。
不能创建与修改表结构及其它授权等操作。
账号名称：order_1,order_2，密码为usercode1，允许任何ip通过此用户连接数据库
*/
CREATE USER 'order_1'@'%' IDENTIFIED BY 'usercode1';
CREATE USER 'order_2'@'%' IDENTIFIED BY 'usercode1';
GRANT INSERT, DELETE, UPDATE
ON 订单数据库.订单 TO 'order_1'@'%'IDENTIFIED BY 'usercode1';
GRANT INSERT, DELETE, UPDATE
ON 订单数据库.订货项目 TO 'order_1'@'%'IDENTIFIED BY 'usercode1';
GRANT SELECT
ON 订单数据库.* TO 'order_1'@'%'IDENTIFIED BY 'usercode1';

GRANT INSERT, DELETE, UPDATE
ON 订单数据库.订单 TO 'order_2'@'%'IDENTIFIED BY 'usercode1';
GRANT INSERT, DELETE, UPDATE
ON 订单数据库.订货项目 TO 'order_2'@'%'IDENTIFIED BY 'usercode1';
GRANT SELECT
ON 订单数据库.* TO 'order_2'@'%'IDENTIFIED BY 'usercode1';



/*
4、客户管理人员（2人）：能对订单数据库中的代理商表和客户表进行插入、删除、更新，
其它表仅能查询（注意：为了顺利进行删除更新操作，代理商表和客户表也需要能有查询权限）。
不能创建与修改表结构及其它授权等操作。
账号名称：customer_1, customer_2，密码为usercode1，允许任何ip通过此用户连接数据库
*/
CREATE USER 'customer_1'@'%' IDENTIFIED BY 'usercode1';
CREATE USER 'customer_2'@'%' IDENTIFIED BY 'usercode1';
GRANT INSERT, DELETE, UPDATE
ON 订单数据库.客户 TO 'customer_1'@'%'IDENTIFIED BY 'usercode1';
GRANT INSERT, DELETE, UPDATE
ON 订单数据库.代理商 TO 'customer_1'@'%'IDENTIFIED BY 'usercode1';
GRANT SELECT
ON 订单数据库.* TO 'customer_1'@'%'IDENTIFIED BY 'usercode1';

GRANT INSERT, DELETE, UPDATE
ON 订单数据库.客户 TO 'customer_2'@'%'IDENTIFIED BY 'usercode1';
GRANT INSERT, DELETE, UPDATE
ON 订单数据库.代理商 TO 'customer_2'@'%'IDENTIFIED BY 'usercode1';
GRANT SELECT
ON 订单数据库.* TO 'customer_2'@'%'IDENTIFIED BY 'usercode1';