mysql - 如何解决错误的 mysql 用户配置？

Question

情况

我有一个 mysql 服务器，用户配置很乱:有两个不同的条目具有相同的登录 admn。

mysql> select user,host,grant_priv from mysql.user where user="admn";
+------+---------------------------+------------+
| user | host                      | grant_priv |
+------+---------------------------+------------+
| admn | 192.168.0.0/255.255.255.0 | N          |
| admn | 192.168.0.21              | Y          |
+------+---------------------------+------------+
2 rows in set (0.00 sec)

请注意只有第二个(主机 192.168.0.21)具有“授予”权限。

为了完整起见，这里是两个用户权限的 View :

mysql> show grants for 'admn'@'192.168.0.0/255.255.255.0';
+------------------------------------------------------------------------------------------------+
| Grants for admn@192.168.0.0/255.255.255.0                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admn'@'192.168.0.0/255.255.255.0' IDENTIFIED BY PASSWORD '---' |
+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'admn'@'192.168.0.21';
+-----------------------------------------------------------------------------------------------------+
| Grants for admn@192.168.0.21                                                                        |
+-----------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admn'@'192.168.0.21' IDENTIFIED BY PASSWORD '---' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 服务器的版本是:

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.1.63-0+squeeze1-log |
+-----------------------+
1 row in set (0.00 sec)

问题

当我使用 adm 的凭证从 192.168.0.21 机器登录时，我被识别为 'admn'@'192.168.0.0/255.255 .255.0' :

mysql> select user(), current_user();
+-------------------+--------------------------------+
| user()            | current_user()                 |
+-------------------+--------------------------------+
| admn@192.168.0.21 | admn@192.168.0.0/255.255.255.0 |
+-------------------+--------------------------------+
1 row in set (0.00 sec)

并且由于此帐户没有“授予”权限，所以我对权限管理感到困惑。

问题

如何访问 'admn'@'192.168.0.21' 帐户？

Answer 1

您需要更改具有子网的帐户，以便它使用通配符，即将 192.168.0.0/255.255.255.0 更改为 192.168.0.%.

这是因为 MySQL 根据主机的具体程度优先选择用户，192.168.0.21 和 192.168.0.0/255.255.255.0< 的具体程度相同，但通配符版本(这与子网版本的含义相同!)具有较低的特异性。

参见 here :

The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific... The pattern '%' means “any host” and is least specific.