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?

Answer 1

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:

1. Blocks anonymous users
2. Keeps only root@localhost
3. 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

• Cannot drop anonymous user from mysql.user
• MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
• Secure method beside phpMyAdmin for remote mysql
• MySQL : Why are there "test" entries in mysql.db?
• mysqladmin user account not secure?