MySQL用户授权与访问控制

这本来是一篇讲述怎么在Linux上完成MySQL的安装、新建用户并授权的博文，后来查阅了不少资料，看到一篇有意思的文章，思绪就开始泛滥了。

mysql> FLUSH PRIVILEGES;

也许你看到大多数讲解MySQL授权的文章最后都让你使用上面的命令来刷新MySQL的权限，但很多情况下可能都是毫无意义的(文末讲这个问题)。不求甚解的求知习惯是危险的，如果对一项技术有追求，应该花时间去了解背后的原理和逻辑。查看更新请访问我的 个人博客网站。

MySQL安装

参考 https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-16-04

$ sudo apt-get update

$ sudo apt-get install mysql-server

通过mysql_secure_installation安全向导修改root用户密码，设置是否允许root远程登陆以及决定是否删除匿名账号和测试数据库等。[MySQL开发者文档的传送门]:

$ sudo mysql_secure_installation

在生产环境中，安全起见，建议设置root用户不允许远程登陆，如果只是学习测试用就无所谓了。此外，匿名账号最好删掉，不然容易出现奇奇怪怪的问题，如*中提到的这个已授权的用户本地登陆后却出现MySQL Access Denied，高票答案中对这个问题有很详细的解答，感兴趣的读者可以打开看看。后来在MySQL的开发者文档中也看到了关于这个问题的说明: 传送门。这个问题总结起来就是：匿名账号由于指定了具体的host(localhost)，比使用通配符(%)的账号拥有了优先权(在user表中排在前面因此先被检查)，导致实名用户在本地登陆时实际上是以匿名用户身份登陆的。host是特定值的账号之所以在user表中排在前面，是因为这张表遵循了以下排序规则Access Control, Stage 1: Connection Verification:

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 198.51.100.13 and 198.51.100.0/255.255.255.0 are considered equally specific.) The pattern ‘%’ means “any host” and is least specific. The empty string ” also means “any host” but sorts after ‘%’. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For rows with equally-specific Host and User values, the order is nondeterministic.

扯远了，MySQL安装完之后一般就启动了，通过下面的命令检查MySQL服务的状态:

$ systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2018-08-14 10:14:28 CST; 2min 0s ago
  Process: 990 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS)
  Process: 976 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 989 (mysqld)
   CGroup: /system.slice/mysql.service
           └─989 /usr/sbin/mysqld

若MySQL没启动，通过以下命令启动:

$ sudo systemctl start mysql

创建新用户并授权

我们直接看看数据库中的user表的结构:

mysql> describe mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
......[此处省略多行]......
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+

由Host和User是主键可知，一个MySQL账号是由Host和User共同决定的。
参考 MySQL开发者文档：Adding User Accounts，下面是开发者文档中给出的创建用户并授权的例子，虽然在下面的例子中，授权总是紧跟着创建用户的命令，你完全可以先创建用户但暂时不做任何授权。

mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

解释一下上面例子中的字符含义: localhost表示这个账号只能在本机登陆，%是通配符，表示可以在任意host登陆。由于我们已经知道一个账号是由User和Host共同决定的，因此上面例子中创建的’finley’@’localhost’和’finley’@’%’其实是两个不同的账号。*.*表示任意数据库的所有权限，WITH GRANT OPTION表示这个账号可以将具有的权限授予其他账号。可见，上面例子创建的这两个名为finley的用户都是超级用户。官方文档还给出了几个具体授权的例子:

mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO 'custom'@'localhost';
mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON expenses.*
    ->     TO 'custom'@'host47.example.com';
mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON customer.*
    ->     TO 'custom'@'%.example.com';

SHOW GRANTS命令查看用户的权限

mysql> SHOW GRANTS FOR 'admin'@'localhost';

[注: SHOW GRANTS FOR CURRENT_USER可以查看当前用户的权限]

创建一个名为cheung的MySQL用户并授权:

mysql> CREATE USER 'cheung'@'%' IDENTIFIED BY 'password-for-cheung';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'cheung'@'%';

命令行可成功登陆:

$ mysql -u cheung -p

尝试在Windows中通过Navicat远程连接，报错2003 - Can't connect to MySQL server on '192.168.118.123' (10038)
如果在Linux上远程连接会得到如下错误提示[192.168.118.123是要连接的数据库所在的host]:

$ mysql -h 192.168.118.123 -u cheung -p
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.118.123' (111)

上面的命令实际上无需在另外一台Linux服务器上运行，就在MySQL所在的服务器运行也会得到一样的错误结果。因为使用ip值192.168.118.123作为host已经不是本地连接[经过了路由]。
通过mysql -h 127.0.0.1 -u cheung -p或者mysql -h localhost -u cheung -p则可以成功连接并登陆。
由于'cheung'@'%'，基本可以确定这是MySQL只绑定了本机回环地址(loopback address)导致的问题。

[email protected]:~$ netstat -anp |grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      -

$ cd /etc/mysql
$ grep -rn "127.0.0.1"
mysql.conf.d/mysqld.cnf:43:bind-address     = 127.0.0.1

通过上面的grep命令查找到了MySQL的监听设置，bind-address设置为127.0.0.1的话，就只能从本机连接MySQL了，所有远程连接都不被允许，因此注释掉这一行:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address       = 127.0.0.1

重启MySQL服务:

$ sudo service mysql restart

再次查看3306端口:

$ netstat -anp |grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      -

这个问题终于解决了，网上所有的教程都是这么做的。如果你了解Linux的回环地址和socket连接的原理的话，几乎可以马上反应过来，我们还可以有别的修改方式！我们可以将bind-address的值更换为本机IP(我这里具体就是192.168.118.123) [注: 如果机器的ip地址变了，就要再次修改bind-address了，因此这种方法还是有缺陷的。]

#bind-address       = 127.0.0.1
bind-address       = 192.168.118.123

这样的话，通过mysql -u cheung -p/mysql -h 192.168.118.123 -u cheung -p/mysql -h localhost -u cheung -p都可以成功连接，但使用mysql -h 127.0.0.1 -u cheung -p则会报错ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)

删除用户

> DROP USER 'smithj'@'localhost';

或者

> DROP USER IF EXISTS 'smithj'@'localhost';

不要滥用FLUSH PRIVILEGES

FLUSH PRIVILEGES的作用是Reload grant tables
滥用FLUSH PRIVILEGES命令的教程太多，一篇国外的文章对此做了详细的说明，传送门: Stop using FLUSH PRIVILEGES
总结一句就是，除非涉及直接更改grant tables(如使用INSERT, UPDATE, or DELETE对这张表进行修改)，其他情况下使用FLUSH PRIVILEGES都是毫无意义的。使用GRANT, REVOKE, SET PASSWORD, or RENAME USER等命令时，MySQL server都会立即监听到这些变化并重新加载grant tables到内存。

需要使用FLUSH PRIVILEGES的一个例子，参见: ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;

参考文献:
[1] MySQL Documentation
[2] MySQL用户授权不当引起的问题
[3] Stop using FLUSH PRIVILEGES
[4] Privileges Provided by MySQL