MySQL数据库 - 授权与撤销授权

第1关：数据库授权

任务描述

本关任务：新建数据库登录用户，并给用户设置权限。

相关知识

为了完成本关任务，你需要掌握：1.用户管理；2. 数据库授权方式。

用户管理

一般安装了 MySQL 之后都只有一个超级管理权限的用户root，且root限制只能在数据库本机上使用，实际上 MySQL 是支持多用户登录的，下面我们一起来学习对用户的管理。

首先我们通过 MySQL 默认的mysql数据库中user表查询一下用户情况：

可以看到只有root用户，下面我们来看看如何新建用户以及对用户的一些常用操作：

• 创建用户

1. create user '用户名'@'允许登录的主机地址' identified by 密码;

示例：

1. #创建指定ip为 192.168.1.1 的lyn用户登录
2. create user 'lyn'@'192.168.1.1' identified by '123';
3. #创建指定ip为 192.168.1 开头的lyn用户登录
4. create user 'lyn'@'192.168.1.%' identified by '123';
5. #创建指定任何ip的lyn用户登录
6. create user 'lyn'@'%' identified by '123';

• 删除用户

1. drop user '用户名'@'允许登录的主机地址';

• 修改用户名

1. rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

• 修改密码

1. set password for '用户名'@'IP地址' = Password('新密码');

授权方式

有了新用户，我们就需要考虑权限问题了，而新创建的用户默认情况下是什么权限都没有的，想要操作数据库，就必须进行授权。

查看权限：show grants for '用户'@'IP地址'

上图中ALL表示允许做任何事，而USAGE则表示只允许登录，其他什么也做不了。下面切换为用户lyn登录，并试图创建数据库：

可以看到错误提示我们没有权限，不能操作，其实在稍微大一点的公司里面都是有着明显的权限管理。特别是操作数据库这样重要的东西，万一你要是一不小心删库了，就麻烦了，所以在公司里面一般一个项目都会创建一个专门的用户指定其权限只能控制某一个数据库进行操作，下面我们一起来为lyn用户设置权限。

MySQL 的权限命令是grant，语法为：

1. grant 权限列表 on 库.表 to 用户名@'ip' identified by "密码";

• 示例1：

上面的语句表示将所有数据库的所有权限授权给lyn这个用户，允许lyn用户在123.123.123.123这个 IP 进行远程登录，并设置lyn用户的登录密码为123456。

• 示例2：

上面语句表示将mysql_db数据库中的sys_user表的查询权限给qjh用户。最后查看权限中也表示该用户对mysql_db数据库sys_user表有查询权限。

注意：授权操作只能是root用户来操作；grant给用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效；授权后一定要刷新权限，使权限立即生效：FLUSH PRIVILEGES;

另外在使用grant给用户添加权限时，权限会自动叠加，不会覆盖之前授予的权限，比如你先给用户添加一个select权限，后来又给用户添加了一个insert权限，那么该用户就同时拥有了select和insert权限。

更多用户详情的权限列表请参考 MySQL 官网说明 。

编程要求

根据提示，在右侧编辑器中编写 SQL，将用户casual_user@'localhost'的密码修改为123456，并给其授所有数据库的“增改查”的权限。（连接数据库的用户名为：root，密码为：123123。）

测试说明

你只需做权限修改，平台会对你编写的SQL进行测试：

提示：对用户的权限控制，一条 SQL 的权限列表可含有多个，也就是可以使用一条 SQL 命令来控制用户的多个权限。

预期输出：

1. +-------------+-----------+-------------------------------------------+
2. | user | host | password |
3. +-------------+-----------+-------------------------------------------+
4. | casual_user | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
5. +-------------+-----------+-------------------------------------------+
6. +-------------------------------------------------------------------------------------------------------------------------------------+
7. | Grants for casual_user@localhost |
8. +-------------------------------------------------------------------------------------------------------------------------------------+
9. | GRANT SELECT, INSERT, UPDATE ON *.* TO 'casual_user'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
10. +-------------------------------------------------------------------------------------------------------------------------------------+

---

开始你的任务吧，祝你成功！

##########  开始编写 SQL  ##########
SET password for casual_user@'localhost' = Password('123456');
flush privileges;

GRANT SELECT,INSERT,UPDATE on *.* to casual_user@'localhost' identified
by '123456';

flush privileges;

第2关：数据库撤销权限

任务描述

本关任务：撤销用户的权限。

相关知识

为了完成本关任务，你需要掌握如何撤销权限。

撤销权限

上一章节我们学习了如何给用户授权，那么需要撤回授权，我们有没有办法呢？

MySQL 同样也提供了撤销权限的方法 ——revoke，revoke跟grant语法差不多，只需要把关键字to换成from即可，并且revoke语句中不需要跟密码设置，具体如下：

1. revoke 权限列表 on 库.表 from 用户名@'ip';

和grant一样，revoke用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

注意：revoke可以回收所有权限，也可以回收部分权限，且授权只是收回权限，用户仍可以登录。

示例：

上面的撤回语句表示将用户qjh@'localhost'在mysql_db数据库中对sys_user表的删除delete权限撤销了，即该用户不对mysql_db数据库进行删除操作了。

编程要求

在右侧编辑器中编写如下要求的 SQL：

• 将用户user1对数据库mydb1中的table1和table2的所有权限收回

• 将用户user2对数据库mydb2中的所有表（mydb2.*）的增加、查询、更新、删除、创建、修改属性权限收回

测试说明

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

预期输出：

1. +--------------------------------------------------------------------------------------------------------------+
2. | Grants for user1@localhost |
3. +--------------------------------------------------------------------------------------------------------------+
4. | GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
5. | GRANT ALL PRIVILEGES ON `mydb1`.`table3` TO 'user1'@'localhost' |
6. +--------------------------------------------------------------------------------------------------------------+
7. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8. | Grants for user2@localhost |
9. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10. | GRANT USAGE ON *.* TO 'user2'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
11. | GRANT DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mydb2`.* TO 'user2'@'localhost' |
12. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

---

开始你的任务吧，祝你成功！

##########  开始编写 SQL  ##########
REVOKE ALL on mydb1.table1 from user1@'localhost';
REVOKE ALL on mydb1.table2 from user1@'localhost';
REVOKE INSERT,UPDATE,SELECT,DELETE,ALTER,CREATE on mydb2.* from user2@'localhost';
flush privileges;