'%' hostname doesn't match localhost

On Mon, May 05, 2008 at 10:49:18AM -0000, luca dep wrote:
> Public bug reported:
>
> On my Ubuntu Hardy mysql doesn't works correctly with users that are not
> root. In first instance, an user with password is not recognized, access
> is denied with correct password. Of course this is not the case of users
> without password.

How did you create the users ? Did you flush the privileges after
creation ?

> But also in this case something doesn't works: users
> with all privileges or partial privileges on some DB, can't work on the
> DBs, so the only way to manipulate the DBs is to access with root user.
>

Have you granted the necessary privileges to the users ? Which sql commands
don't work and what kind of errors do you get ?

 status incomplete

--
Mathias Gug
Ubuntu Developer http://www.ubuntu.com

The user was made by this script (where X and Y are user and password)
CREATE USER X IDENTIFIED BY 'Y';
and privileges with
GRANT ALL PRIVILEGES ON *.* TO 'X'@'%' IDENTIFIED BY 'Y' WITH GRANT OPTION;

FLUSH PRIVILEGES;
so I flushed privileges after giving them.
The error was on access:
luca@UBdp-XPS:~$ mysql -u X -p
Enter password:
ERROR 1045 (28000): Access denied for user 'X'@'localhost' (using password: YES)
I used the same script to generate the db on a gutsy, with tha same version of mysql (5.0) and got no errors there.

Confirmed.

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)

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

Forwarded to MySQL bug tracker:

http://bugs.mysql.com/36576

As explained in the upstream bug report:

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