Recently I have made a few new instances of MySQL server for my research. I have created a database and a user for the database, and I want to be able to access the database from any computer via that user. The following is the command I used.

create database Project;
create user 'proj_user' identified by 'password';
grant all on Project.* to 'proj_user';

The first line creates the database, the second line creates the user, and the third line says give all permission to ‘proj_user’ for the database ‘Project’.

Now the documentation says when host name (or host IP) is omitted, any host, which is denoted by ‘%’ is assumed for this user.

This is great and exactly what I need. But when I try to log in with my new user account, I get an error message.

mysql -u proj_user -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'proj_user'@'localhost' (using password: YES)

I’m sure I typed in the correct password, so what’s the deal here? After much research, I found that this error is one of MySQL security thing and I still don’t understand why they do it this way. Basically, I need to remove some anonymous users from the server. So I log in using root and do the following.

mysql> use mysql;
Database changed

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | proj_user        |
| 127.0.0.1 | root             |
| ::1       | root             |
| desk      |                  |
| desk      | root             |
| localhost |                  |
| localhost | debian-sys-maint |
| localhost | root             |
+-----------+------------------+
8 rows in set (0.00 sec)

From the output above, I found out that I have 2 anonymous users in the system (with blank user entry): one for host ‘desk’ and the other for ‘localhost’.

I remove the anonymous for localhost with

drop user ''@'localhost';

And I was able to log in using my new user name.


Bug #31061 Host wildcard % which is said in docs that means “all hosts” excludes localhost
http://dev.mysql.com/doc/refman/5.5/en/privilege-system.html
Securing the Initial MySQL Accounts