I have setup mysql and created a user 'test' and have given a password for it.
Strange thing I observed is, for test user I am able to access with out password from the machine I have installed mysql but when I try to access remotely then I need to give a password.
Why is this behaviour? any idea?
It is the way MySQL authenticates and current users installed.
When you run this command
SELECT USER(),CURRENT_USER();
you quickly learn that USER() tells you how mysqld sees you trying to login and CURRENT_USER() tells you how mysqld allowed you to login. I discussed this 3 years ago in Why does OS login not exist in the user table, but I can still login to MySQL?
My guess is that whatever CURRENT_USER() is in mysql.user has no password.
You can run SELECT CONCAT(QUOTE(user),QUOTE(host)) userhost,password FROM mysql.user; to see your users. You might note that password is blank.
To solve your issue, you must create a remote username and password.
GRANT ... TO user@localhost ...
and
GRANT ... TO user@'%' ...
are independent of each other.
Also, if the hostname is my.site.com or %.site.com or 11.22.33.44 or 11.22.33.%, the rules get more complicated. In particular some of these could refer to the same client, so it gets tricky as to which GRANT is in effect.
