How to untangle a bad mysql user configuration?

Question

Situation

I have a mysql server, with a messy user configuration : there are two different entries having the same login 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)

Notice how only the second one (host 192.168.0.21) has the "grant" privilege.

For completeness, here is as view of the two users' privileges :

mysql> show grants for 'admn'@'192.168.0.0/255.255.255.0';
+------------------------------------------------------------------------------------------------+
| Grants for [email protected]/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 [email protected]                                                                        |
+-----------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admn'@'192.168.0.21' IDENTIFIED BY PASSWORD '---' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The mysql server's version is :

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

Problem

When I log in from the 192.168.0.21 machine using adm's credentials, I get identified as 'admn'@'192.168.0.0/255.255.255.0' :

mysql> select user(), current_user();
+-------------------+--------------------------------+
| user()            | current_user()                 |
+-------------------+--------------------------------+
| [email protected] | [email protected]/255.255.255.0 |
+-------------------+--------------------------------+
1 row in set (0.00 sec)

and since this account doesn't have the "grant" privilege, I'm stuck regarding privilege administration.

Question

How can I access 'admn'@'192.168.0.21' acount ?

Answer 1

You'll need to change your account that has a subnet so that it uses a wildcard, i.e. change 192.168.0.0/255.255.255.0 to 192.168.0.%.

The reason for this is that MySQL chooses users preferentially according to how specific their host is, and that specificity is the same for 192.168.0.21 and 192.168.0.0/255.255.255.0, but the wildcard version (which means the same thing as the subnetted version!) has a lower specificity.

See 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.