MySQL Bugs: #36576: '%' hostname doesn't match localhost

Bug #36576	'%' hostname doesn't match localhost
Submitted:	7 May 2008 19:24	Modified:	9 Aug 2008 5:52
Reporter:	Mathias Gug	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.51	OS:	Linux (Ubuntu Hardy)
Assigned to:		CPU Architecture:	Any

Description:
Using '%' as the host part doesn't match localhost. 

5.0.45 (gutsy version) was matching localhost.

Original bug report in Ubuntu:

https://bugs.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+bug/226851

How to repeat:
TESTCASE:

  * Create a test user from mysql shell:

CREATE USER X IDENTIFIED BY 'Y';
GRANT ALL PRIVILEGES ON *.* TO 'X'@'%' IDENTIFIED BY 'Y' WITH GRANT OPTION;
FLUSH PRIVILEGES;

  * From the shell, try to connect to the server:
        mysql -uX -pY

Gutsy result: access is granted:

gutsy:~$ mysql -uX -pY
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Hardy result: access is denied:
hardy:~$ mysql -uX -pY
ERROR 1045 (28000): Access denied for user 'X'@'localhost' (using password: YES)

Suggested fix:
A workaround is to create another privilege for 'X'@'localhost' in addition to 'X'@'%'.

Thank you for a problem report. Please, send the results of:

select current_user();
show grants;

after successful connection on Gutsy.

On gutsy:

mathiaz@test-mysql:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE USER X IDENTIFIED BY 'Y';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'X'@'%' IDENTIFIED BY 'Y' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> 
Bye
mathiaz@test-mysql:~$ 
mathiaz@test-mysql:~$ mysql -uX -pY
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| X@%            | 
+----------------+
1 row in set (0.00 sec)

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

mysql>

I had a similar problem.  I fixed it by removing the two auto-created anonymous localhost users.  These had higher precedence in the MySQL user-matching ordering, because 'localhost' is more specific than '%'.

References:
Anonymous users are created by default:
http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

Order matching:
http://dev.mysql.com/doc/refman/5.0/en/connection-access.html
(It is a common misconception to think that, for a given username, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is simply not true....)

So, this is not a bug, but a result of a documented behaviour, as explained in the last comment.