Why does OS login not exist in the user table, but I can still login to MySQL?

Question

mysql> select user, password, host from user;
+------+----------+---------------------------------------------+
| user | password | host                                        |
+------+----------+---------------------------------------------+
| root |          | localhost                                   | 
| root |          | host-1.iluruco.clients.pmedia.com | 
| root |          | 127.0.0.1                                   | 
|      |          | localhost                                   | 
|      |          | host-2.iluruco.clients.pmedia.com | 
+------+----------+---------------------------------------------+

but I can log in the MySQL server on my machine using

$ mysql

mysql> select user();
+----------------+
| user()         |
+----------------+
| qaz@localhost | 
+----------------+
1 row in set (0.00 sec)

So why qaz doesn't exist in the user table?

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

MySQL has a funny authentication style

Actually, you need to use two functions

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL

You must be logging as an anonymous user. CURRENT_USER() will tell you what mysqld allowed you to come in as.

My guess is that qaz is the OS user and it was chosen by the mysql client as the user name to login.

Your set up is not secure. Please run this:

DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.user WHERE user='root' AND host<>'localhost';
UPDATE mysql.user SET password=PASSWORD('whateverpassword')
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

This does three(3) things:

Blocks anonymous users
Keeps only root@localhost
Assigns a password to root@localhost

If you cannot log back into mysql, do the following

service mysql restart --skip-grant-tables --skip-networking
mysql -e"UPDATE mysql.user SET password=PASSWORD('whateverpassword') WHERE user='root' AND host='localhost';"
service mysql restart

I wrote earlier posts about this

If I give a password to root and forget it. What should I do? — qazwsx, May 03 '12 at 20:16
I read somewhere that service is no longer a morden way of restarting mysql server. Is it true? — qazwsx, May 08 '12 at 19:25

Why does OS login not exist in the user table, but I can still login to MySQL?

1 Answers1

Linked