数据库安全性控制

重要信息

educoder平台上mysql的版本为5.5，有下面的功能是没有的 （1）创建角色（该功能在mysql8.0中会有） （2）drop user if exists datarecoder1@localhost;（该功能在mysql5.7中会有）

任务描述

下面为某部门数据库安全策略 订单数据库中包括名为代理商，客户，产品，订单，订货项目等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）创建用户

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

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

1. create user 'user2'@'%' identified by '123'

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

1. create user user3 identified by '123'

上面的语句创建名为user3，密码为123的用户，可以通过任何ip连接数据库。 注： % 表示所有主机 192.168.2.% 表示192.168.2.0网段的所有主机 192.168.2.8(localhost) 表示指定主机

(2）删除用户

1. DROP USER 'user1'@'localhost';

（3）查看用户

1. select user,host from mysql.user;

2、权限管理

grant 权限列表 on 库名.表名 to 用户名@'客户端主机' ######（1）部分权限列表

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

更多权限介绍，查看 https://www.cnblogs.com/keme/p/10288168.html ######（2）库名.表名

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

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

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

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

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

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

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

（4）对订单数据库中的代理商表中的代理商编号有插入权限，姓名有选择权限（只是为了介绍给的例子，无实际意义) grant insert(代理商编号),select(姓名) on 订单数据库.代理商 to test; ######（3）查看权限

1. show grants for 'user1'@'localhost' ;

（4）回收权限

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

（5）注意事项

（1）因为update,delete这些语句中有些也需要进行数据的查找，因此希望顺利进行更新和查找，还需要具有select权限。 （2）新建用户，修改完权限等之后，一定要刷新服务，刷新服务用：

1. flush privileges;

测试说明

平台会对你编写的代码进行测试

    /*
    1、数据库维护人员（1人）：可对订单数据库进行任何操作。
    账号名称：system_dbowner，密码为usercode1，允许任何ip通过此用户连接数据库
    */
    CREATE USER  system_dbowner IDENTIFIED BY  'usercode1';
    grant all on 订单数据库.*  to  system_dbowner;
    flush privileges;
    /*
    2、数据录入人员（2人）：可对订单数据库中所有表进行插入、删除、更新操作，
    （注意：为了顺利进行删除更新操作，也需要能有查询权限）
    不能创建与修改表结构及其它授权等操作。
    账号名称：datarecorder1, datarecorder2，密码为usercode1，允许任何ip通过此用户连接数据库
    */
    CREATE USER  datarecorder2 IDENTIFIED BY  'usercode1';
    grant select,insert,delete,update on 订单数据库.*  to  datarecorder2;
    flush privileges;
    CREATE USER  datarecorder1 IDENTIFIED BY  'usercode1';
    grant select,insert,delete,update on 订单数据库.*  to  datarecorder1;
    flush privileges;
    /*
    3、订单管理人员（2人）：能对订单数据库中的订单表和订货项目表进行插入、删除、更新操作，
    其它表仅能查询（注意：为了顺利进行删除更新操作，订单表和订货项目表也需要能有查询权限）。
    不能创建与修改表结构及其它授权等操作。
    账号名称：order_1,order_2，密码为usercode1，允许任何ip通过此用户连接数据库
    */
    CREATE USER  order_1 IDENTIFIED BY  'usercode1';
    grant select on 订单数据库.*  to  order_1;
    grant insert,delete,update on 订单数据库.订货项目 to order_1;
    grant insert,delete,update on 订单数据库.订单 to order_1;
    flush privileges;
    CREATE USER  order_2 IDENTIFIED BY  'usercode1';
    grant select on 订单数据库.*  to  order_2;
    grant insert,delete,update on 订单数据库.订单 to order_2;
    grant insert,delete,update on 订单数据库.订货项目 to order_2;
    flush privileges;
    /*
    4、客户管理人员（2人）：能对订单数据库中的代理商表和客户表进行插入、删除、更新，
    其它表仅能查询（注意：为了顺利进行删除更新操作，代理商表和客户表也需要能有查询权限）。
    不能创建与修改表结构及其它授权等操作。
    账号名称：customer_1, customer_2，密码为usercode1，允许任何ip通过此用户连接数据库
    */
    CREATE USER  customer_1 IDENTIFIED BY  'usercode1';
    grant select on 订单数据库.*  to  customer_1;
    grant insert,delete,update on 订单数据库.代理商 to customer_1;
    grant insert,delete,update on 订单数据库.客户 to customer_1;
    flush privileges;
    CREATE USER  customer_2 IDENTIFIED BY  'usercode1';
    grant select on 订单数据库.*  to  customer_2;
    grant insert,delete,update on 订单数据库.代理商 to customer_2;
    grant insert,delete,update on 订单数据库.客户 to customer_2;
    flush privileges;