Bug #72709 Access from localhost denied when user is created with '%' syntax
Submitted: 21 May 2014 14:35 Modified: 21 May 2014 14:50
Reporter: rayne noonan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[21 May 2014 14:35] rayne noonan
Description:
I created a database called database_name, then created a user called database_name by using the following command:

grant all on *.* to 'database_name'@'%' identified by 'database_name';

I then tried to access the database from another server (http server) and was successful:

[root@http_002 ~]# mysql -u database_name -pdatabase_name -h 192.168.56.197
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

I then unsuccessfully tried to access the database from the host upon which the mysql server resides using the following commands:

[root@mysql_001 ~]# mysql -u database_name -pdatabase_name database_name
ERROR 1045 (28000): Access denied for user 'fconnect_prod'@'localhost' (using password: YES)

[root@mysql_001 ~]# mysql -u database_name -pdatabase_name database_name -h localhost
ERROR 1045 (28000): Access denied for user 'database_name'@'localhost' (using password: YES)

[root@mysql_001 ~]# mysql -u database_name -pdatabase_name database_name -h 127.0.0.1
ERROR 1045 (28000): Access denied for user 'database_name'@'localhost' (using password: YES)

The strange thing is that I WAS able to access the database using the ip address of the host upon which the mysql server resides:

[root@mysql_001 ~]# mysql -u database_name -pdatabase_name database_name -h 192.168.56.197
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

I also appear to be able to access the database using ANY random IP address from any server, even the host of the mysql server, as long I don't specify either '127.0.0.1' or 'localhost'.

[root@mysql_001 ~]# mysql -u fconnect_prod -pfconnect_prod fconnect_prod -h 127.0.1.1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

How to repeat:
1.) create database database_name;
2.) grant all on *.* to 'database_name'@'%' identified by 'database_name';
3.) Try to connect from remote host:
     mysql -u database_name -pdatabase_name -h 192.168.56.197
6.) Try to connect from mysql server's localhost using the host address:
     mysql -u database_name -pdatabase_name database_name -h 192.168.56.197
4.) Try to connect from mysql server's localhost using localhost or 127.0.0.1:
     mysql -u database_name -pdatabase_name database_name -h localhost
5.) Try to connect from mysql server's localhost using some arbitrary address:
     mysql -u database_name -pdatabase_name database_name -h 127.0.1.1

Suggested fix:
Allow '%' to include localhost and 127.0.0.1
[21 May 2014 14:50] MySQL Verification Team
Thank you for the bug report. Duplicate of http://bugs.mysql.com/bug.php?id=69570 .