1

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?

1

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.

| improve this answer | |
  • How do I create remote user and password? Basically it has to work only with password both in local and remote machine. That is my target. – user69695 Jul 5 '15 at 16:13
  • Can you login locally as root@localhost ??? – RolandoMySQLDBA Jul 5 '15 at 17:10
0
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.