写在前面

在实际工作中可能会遇到当管理员创建了一个用户之后，但是该用户却无法登陆系统，没有对应的权限，通过本篇文章的学习，开发者就能解决这个问题。

本文的主要内容如下：（1）MySQL权限工作原理；（2）查看所有用户；（3）创建新用户；（4）修改用户密码；（5）给用户授权；（6）查看用户权限；（7）撤销用户权限；（8）授权原则介绍；（9）文章总结。

MySQL权限工作原理

MySQL出于安全性考量，采用“主机名+用户名”这一双重验证机制来判断一个用户的身份。在互联网中，仅仅通过用户名来判断一个用户的身份，这是非常复杂的操作，但是开发者可以通过IP或者主机名来判断一台机器，且如果某位用户是通过这个机器访问的，那么就可以识别成功为一个用户。因此，当一个用户对MySQL发送指令的时候，MySQL就通过“主机名+用户名”这一机制来判断用户身份，进而验证用户权限。

MySQL权限验证分为两个阶段，第一个阶段：连接数据库，此时MySQL会根据用户名和用户来源（IP或者主机名）来判断用户是否有权限连接；第二阶段：对MySQL服务器发起请求操作，如create table、select、delete、update、create index等操作，此时MySQL会判断用户是否有权限来操作这些命令。

请注意在MySQL中，用户及权限信息放在名为mysql的数据库中，当MySQL启动时，这些内容会被读进内存并立即生效，因此开发者如果想通过直接操作这些表来修改用户及权限信息，那么就必须重启MySQL服务，或者执行flush privileges;命令来刷新配置，这样才能生效。

因为用户登录之后，MySQL会和当前用户之间建立一个连接，此时用户相关的权限信息都保存在这个连接中，存放于内存中，此时如果管理员在其他地方修改了当前用户的权限信息，那么这些变更的权限会在下一次登录时才会生效。

查看MySQL中所有的用户

前面说了用户信息及权限保存在mysql数据库中的user表内，接下使用select user,host,password_last_changed from mysql.user;命令来查看所有用户：

1
2
3
4
5
6
7
8
9
10
mysql> select user,host,password_last_changed from user;\G
+---------------+-----------+-----------------------+
| user          | host      | password_last_changed |
+---------------+-----------+-----------------------+
| root          | localhost | 2019-10-29 16:25:31   |
| mysql.session | localhost | 2019-10-29 16:21:59   |
| mysql.sys     | localhost | 2019-10-29 16:21:59   |
| root          | %         | 2019-10-30 16:21:20   |
+---------------+-----------+-----------------------+
4 rows in set (0.00 sec)

创建新用户

创建新用户的格式如下所示：

1
create user 用户名[@主机名] [identified by '密码'];

请注意，主机名默认为%，表示这个用户可以从任何一台机器连接到mysql服务器；密码可以省略，表示该用户无密码就能登录到MySQL，这个不安全，一般不建议这么操作。

例子1：不指定主机名称，表示这个用户可以从任何一台机器连接到mysql服务器：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.03 sec)


mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| envy1         | %         |
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)


mysql> exit
Bye


C:\WINDOWS\system32>mysql -uenvy1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

在上面我们创建了用户名为envy1的无密码用户，没有指定主机，使用了默认的%，这表示envy1用户可以从任何机器登录到mysql中。

例子2：指定主机名称为localhost，表示这个用户只能登录本机的mysql：

1
create user 'envy2'@'localhost' identified by 'envy123';

例子3：不指定主机名称，但设置密码，表示这个用户可以从任何机器连接到mysql服务器：

1
create user 'envy3'@'%' identified by 'envy123';

例子4：指定主机IP地址段，且设置密码，表示这个用户可以只能从指定主机IP地址段连接到mysql服务器：

1
create user 'envy4'@'192.168.73.%' identified by 'envy123';

修改用户密码

一般来说，修改用户密码有两种方式，下面分别进行学习：
（1）通过管理员来修改用户密码信息：

1
set password for '用户名'@'主机' = password('密码');

如使用set password for 'envy1'@'%'=password('envy123');来修改envy1用户的密码。

（2）通过修改mysql.user数据表来修改用户密码信息：

1
2
3
use mysql;
update user set authentication_string = password('envy123') where user = 'envy1' and host = '%';
flush privileges;

请注意，如果采用第二种方式，通过表的方式来修改用户密码，那么需要在执行命令之后执行flush privileges;命令，这样修改的信息才能生效。

还有一个问题就是在MySQL5.7之后，密码已经使用authentication_string字段来表示了，不再使用之前的password字段。

给用户授权

用户创建完成后，需要给用户授权，否则用户是无法进行任何操作的，有和没有一样。

开发者可以使用如下命令来给用户授权：

1
grant privileges ON database.table TO 'username'[@'host'] [with grant option];

简单解释一下上述参数：
（1）privileges是权限列表，可以是all，表示所有的权限，也可以是select、insert等权限，注意多个权限之间使用逗号进行分隔；
（2）ON用来指定权限针对哪些库和表，其格式为数据库.表名 ，如果后面使用*. *，则表示所有数据库所有表；
（3）TO表示将权限赋予某个用户，其格式为username@host，请注意@前面为用户名称，后面是限制的主机，可以是IP、IP段、域名或者%(%表示任何地方)。
（4）with grant option这个选项表示该用户可以将自己拥有的权限授权给别人。这一点非常重要，在实际工作中，笔者经常发现有人在创建操作用户的时候不指定with grant option选项，这样导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

需要注意的是，开发者可以使用GRANT命令来重复给用户添加权限，也就是权限叠加。如先给用户添加了select权限，之后又给它添加了insert权限，那么这个用户就同时具有了select和insert权限。

例子1：给envy1用户授权可以操作所有库的所有表的所有权限，相当于DBA角色了：

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

例子2：给envy1用户授权，使其可以对hello库中所有的表执行select操作：

1
grant select  on hello.* to 'envy1'@'%';

例子3：给envy1用户授权，使其可以对hello库中所有的表执行select和update操作：

1
grant select,update  on hello.* to 'envy1'@'%';

例子4：给envy1用户授权，使其只能对mysql库中user表的user和host字段执行select操作：

1
grant select(user,host)on mysql.user to 'envy1'@'%';

查看用户权限

前面我们给用户赋予了权限，那么接下来就学习如何查看用户的权限。

查看用户权限的语法格式如下所示：

1
show grants for 'username'[@'host'];

上面的主机host可以缺省，其默认值为%。

举个例子，查看envy1用户的权限信息：

1
2
3
4
5
6
7
8
mysql> show grants for 'envy1'@'%';
+------------------------------------------------------------+
| Grants for envy1@%                                         |
+------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'envy1'@'%'                 |
| GRANT SELECT (host, user) ON `mysql`.`user` TO 'envy1'@'%' |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

当然了，如果开发者是想查看当前登录用户的权限，可以直接使用show grants;命令，如下所示：

1
2
3
4
5
6
7
8
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

撤销用户权限

当用户身份发生变更以后，那么它所具有的权限就需要发生变更，需要撤销相应的用户权限。撤销用户权限命令的语法格式如下所示：

1
revoke privileges ON database.table FROM 'username'[@'host'];

一般在撤销用户权限之前，首先需要使用前面介绍的show grants for 'username'[@'host'];来查看用户所具有的权限，然后才使用revoke命令来撤销用户对应的权限。

举个例子，现在撤销envy1用户对于mysql数据库user表中host字段的查询权限，相应的操作如下所示：

第一步，使用如下命令查看envy1用户所具有的权限：

1
2
3
4
5
6
7
8
mysql> show grants for 'envy1'@'%';
+------------------------------------------------------------+
| Grants for envy1@%                                         |
+------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'envy1'@'%'                 |
| GRANT SELECT (host, user) ON `mysql`.`user` TO 'envy1'@'%' |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

第二步，撤销envy1用户对于mysql数据库user表中host字段的查询权限：

1
2
 revoke select(host) ON mysql.user FROM 'envy1'@'%';
Query OK, 0 rows affected (0.10 sec)

第三步，再次查看用户权限，验证撤销权限操作是否成功：

1
2
3
4
5
6
7
8
mysql> show grants for 'envy1'@'%';
+------------------------------------------------------+
| Grants for envy1@%                                   |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'envy1'@'%'           |
| GRANT SELECT (user) ON `mysql`.`user` TO 'envy1'@'%' |
+------------------------------------------------------+
2 rows in set (0.00 sec)

可以看到第二步的撤销权限操作的确操作成功了。

删除用户

有的时候，当不需要某个用户时，就需要及时将该用户删除掉，这里学习两种方式来删除用户。

方式1：使用drop方式进行删除，用户下次登录就会生效。使用命令如下所示：

1
drop user 'username'[@'host'];

举个例子，使用drop方式来删除envy1用户：

1
2
mysql> drop user 'envy1'@'%';
Query OK, 0 rows affected (0.10 sec)

方式2：通过删除数据表数据的方式来删除用户信息。请注意这种方式最后需要执行flush privileges;命令来刷新用户权限，因为前面也说过启动的时候权限存在内存中，通过修改表的方式必须刷新才能生效。

举个例子，通过删除数据表中数据方式来删除envy2用户：

1
2
3
4
5
mysql> delete from mysql.user where user='envy2' and host = '%';
Query OK, 0 rows affected (0.09 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.21 sec)

授权原则介绍

接下来笔者结合实际的工作经历，来谈一谈授权的一些原则：
（1）在创建用户的时候需要限制用户的登录主机，通常是限制IP或者IP段；
（2）初始化数据库的时候，会删除没有密码的用户；但是安装完数据库的时候，又会创建一些没有密码的用户；
（3）需要给每个用户设置不同密码复杂度的密码，并定期进行修改；
（4）在给用户授权的时候，尽量授予能满足要求的最小权限，不要过度授权，防止发生一些难以预料的问题；
（5）定期清理不需要的用户，撤销权限或者删除用户；
（6）用户权限粒度尽量设置的较为细致，不要图省事省力。

文章总结

本篇主要介绍了上述内容，下面针对一些比较容易出错的问题，再次进行梳理：
（1）在MySQL数据库中，用户及权限信息存在于mysql.user表中；
（2）本文所涉及到的携带主机的指令中，主机可以缺省，此时默认为%，表示所有机器都可以登录；
（3）MySQL出于安全性考量，采用“主机名+用户名”这一双重验证机制来判断一个用户的身份；
（4）通过命令的方式来操作用户和权限，不需要刷新，下次登录就能生效；
（5）通过操作mysql数据库中相应表的方式来操作用户和权限，必须使用flush privileges命令来刷新，之后下次登录才会生效。这两种方式的区别一定要注意。

那么这样关于用户权限管理的学习就到此为止，后续开始学习其他知识。