2

I have removed anonymous users from mysql server but when one user trying to login from a specific host, current_user() still showing anonymous user.

mysql> select user,host from mysql.user\G  
*************************** 1. row ***************************  
user: a  
host: %  
*************************** 2. row ***************************  
user: root  
host: 127.0.0.1  
*************************** 3. row ***************************  
user: root  
host: ::1  
*************************** 4. row ***************************  
user: a  
host: hosta  
*************************** 5. row ***************************  
user: root  
host: hosta  
*************************** 7. row ***************************  
user: a  
host: localhost  
*************************** 8. row ***************************  
user: root  
host: localhost  
8 rows in set (0.00 sec)  


mysql> show grants for 'a'@'hosta';  
+-------------------------------------------------------------------------------  
| Grants for a@hosta                                                                     
+-------------------------------------------------------------------------------  
| GRANT ALL PRIVILEGES ON *.* TO 'a'@'hosta' IDENTIFIED BY PASSWORD 'xxxxxxxx'  
+-------------------------------------------------------------------------------  
1 row in set (0.00 sec)  

When I am giving correct password i am unable to login

mysql@hosta ==> mysql -ua -p -S /tmp/mysql.sock -h hosta  
Enter password:  
ERROR 1045 (28000): Access denied for user 'a'@'hosta' (using password: YES)  

but when I am giving blank password, I am able to log in:

mysql@hosta ==> mysql -ua -p -S /tmp/mysql.sock -h hosta  
Enter password:  

mysql> select user(),current_user();  
+---------------------------------------+-----------------------+  
| user()                                | current_user()        |  
+---------------------------------------+-----------------------+  
| a@hosta                               | @hosta                |  
+---------------------------------------+-----------------------+  
1 row in set (0.00 sec)  

Any idea what I am missing here ?

2

Here is what you are missing

  • Connecting with user a via a socket is the same as connecting with user a via localhost.
  • Connecting with a socket will not use TCP/IP

If you are going to explicitly using a hostname, you must use TCP/IP with port 3306 and no socket

mysql -ua -p -h hosta --protocol=tcp -P3306

I have discussed using --protocol=tcp in my earlier posts

You need to delete blank users, blank hosts, and blank passwords from mysql.user

DELETE FROM mysql.user WHERE user = '';
DELETE FROM mysql.user WHERE host = '';
DELETE FROM mysql.user WHERE password = '';
FLUSH PRIVILEGES;
  • there are no blank users,passwords and hosts in my users table. not sure why current_user is @hosta even though there are no anonymous users. – kasi Oct 14 '15 at 11:29
  • i got what wrong i am doing. please see my answer and thanks for your valuable tips Rolando. i got some valuable info from your answer. – kasi Oct 15 '15 at 5:47
1

I got the answer. I am running more than one port on server. So when i am trying to login like mysql -ua -p -S /tmp/mysql.sock -h hosta ,As there is no specific port mentioned , Its trying to login to 3306 instead of required port i.e 3311.As there are anonymous users in 3306 port, I am able to login with blank password and current_user is @hosta. So totally i am in a different server. So if you mention both socket and host ,It will use tcp protocol by default and does not use socket. And we should mention port number without fail , If you are using more than one instance on same server.

  • Still, what has priority, Socket or Host? You can't do both at the same time, can you? – Rick James Oct 21 '15 at 22:20
  • In my case it seems host has taken the priority when i have used both(socket and host) at a time – kasi Oct 26 '15 at 6:35

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.