第1关:数据库安全
- 任务要求
- 参考答案
- 重置记录
- 评论2
重要信息
educoder平台上mysql的版本为5.5,有下面的功能是没有的 (1)创建角色(该功能在mysql8.0中会有) (2)drop user if exists datarecoder1@localhost;(该功能在mysql5.7中会有)
任务描述
下面为某部门数据库安全策略 订单数据库中包括名为代理商,客户,产品,订单,订货项目等5个数据表。
- 数据库维护人员(1人):可对订单数据库进行任何操作。 账号名称:system_dbowner,允许任何ip通过此用户连接数据库,密码为usercode1
- 数据录入人员(2人):可对订单数据库中所有表进行插入、删除、更新操作,不能创建与修改表结构及其它授权等操作。 账号名称:datarecorder1, datarecorder2,允许任何ip通过此用户连接数据库,密码为usercode1
- 订单管理人员(2人):能对订单数据库中的订单表和项目表进行插入、删除、更新操作,其它表仅能查询。不能创建与修改表结构及其它授权等操作。 账号名称:order_1,order_2,允许任何ip通过此用户连接数据库,密码为usercode1
- 客户管理人员(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权限是创建一个用户之后的默认权限,其本身代表连接登录权限
更多权限介绍,查看 Mysql 用户权限管理 - keme - 博客园 ######(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;
/*
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;