MySQL的账号匹配规则

引言

MySQL的账号由 '用户名'@'主机或IP' 构成，当用户连接匹配到mysql权限表中不同账号时，将被赋予对应的权限

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

现在有这样一种场景：mysql.user表中的内容如上所示，假设：

• ''@'' 账号具有A库上的ALL权限

• 'test'@'' 账号具有B库上的ALL权限

• ''@'%' 账号户具有C库上的ALL权限

• 'test'@'%' 账号具有D库上的ALL权限

• ''@'node1' 账号具有E库上的ALL权限

• 'test'@'node1' 账号具有F库上的ALL权限

此时如果使用'test'用户名从node1节点访问数据库，从账号匹配的角度来看，上述6个账号都能够被匹配到，但是只能选择其中一个，那么这个连接最终会匹配到哪个账号呢？

分析

官方文档Access Control, Stage 1: Connection Verification中有如下说明：

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

Whenever the server reads the user table into memory, it sorts the rows.

When a client attempts to connect, the server looks through the rows in sorted order.

The server uses the first row that matches the client host name and user name.

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.

大致的意思是说，MySQL会首先对mysql.user表进行排序。当client试图连接数据库时，会扫描排序后的user表，一旦有账号被匹配到，便以此账号的身份给予权限，即选择第一个被匹配到的账号。排序规则为：

select user,host from mysql.user order by host desc,user desc;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| test          | node1     |
|               | node1     |
| root          | localhost |
| mysql.sys     | localhost |
| mysql.session | localhost |
| test          | %         |
|               | %         |
| test          |           |
|               |           |
+---------------+-----------+
9 rows in set (0.00 sec)

测试

1，使用'test'用户名从node1节点连接数据库，看到current_user()函数输出为test@node1，即匹配到了'test'@'node1'账号

[root@node1 ~]# mysql -utest -p123456 -h192.168.90.110
...
mysql> select user(),current_user();
+------------+----------------+
| user()     | current_user() |
+------------+----------------+
| test@node1 | test@node1     |
+------------+----------------+
1 row in set (0.00 sec)

2，删除test@node1账号后再次尝试连接，current_user()函数输出变为@node1，即匹配到''@'node1'账号

[root@node1 ~]# mysql -uroot 
...
mysql> drop user test@node1;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

[root@node1 ~]# mysql -utest -p123456 -h192.168.90.110
...
mysql> select user(),current_user();
+------------+----------------+
| user()     | current_user() |
+------------+----------------+
| test@node1 | @node1         |
+------------+----------------+
1 row in set (0.00 sec)

3，删除''@'node1'账号后再次尝试连接，current_user()函数输出变为test@%，即匹配到'test'@'%'账号

[root@node1 ~]# mysql -uroot 
...
mysql> drop user ''@'node1';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

[root@node1 ~]# mysql -utest -p123456 -h192.168.90.110
...
mysql> select user(),current_user();
+------------+----------------+
| user()     | current_user() |
+------------+----------------+
| test@node1 | test@%         |
+------------+----------------+
1 row in set (0.00 sec)

4，重复上述步骤，最终得到的current_user()函数输出顺序为：

• test@node1

• @node1

• test@%

• @%

• test@

• @

回过头来，再次看一下mysql.user表，排除host为localhost的3个账号，其顺序与上面的输出是完全一致的

select user,host from mysql.user order by host desc,user desc;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| test          | node1     |
|               | node1     |
| root          | localhost |
| mysql.sys     | localhost |
| mysql.session | localhost |
| test          | %         |
|               | %         |
| test          |           |
|               |           |
+---------------+-----------+
9 rows in set (0.00 sec)

总结

• 当一个客户端连接能够被mysql权限表中多个账号匹配到时，将按照“order by host desc,user desc”的顺序匹配排在最前面的那个账号，进而获得该账户对应的权限
• 当数据库中存在匿名账号时，例如''@'node1'账号，它的匹配优先级是比较靠前的(比'test'@'%'账号高)，因此在使用匿名账号时要格外注意，避免用户连接被意外匹配到匿名账号上

参考

https://dev.mysql.com/doc/refman/5.7/en/connection-access.html