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 ?

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
kasi
  • 419
  • 1
  • 7
  • 19

2 Answers2

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;
Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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 05: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.

kasi
  • 419
  • 1
  • 7
  • 19