Mysql user accesable with out password from the host machine but not from the remote machine

Question

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?

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

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.

edited Apr 13 '17 at 12:42

Community

1

answered Jul 03 '15 at 13:25

RolandoMySQLDBA

182,700
33
317
520

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 05 '15 at 16:13
Can you login locally as root@localhost ??? – RolandoMySQLDBA Jul 05 '15 at 17:10

score 0 · Answer 2 · answered Jul 04 '15 at 02:26

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.

Mysql user accesable with out password from the host machine but not from the remote machine

2 Answers2