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