Every so often we have a client worrying about unauthenticated users. For example, as part of the output of SHOW PROCESSLIST they will see:
+-----+----------------------+--------------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+----------------------+--------------------+------+---------+------+-------+------------------+ | 235 | unauthenticated user | 10.10.2.74:53216 | NULL | Connect | NULL | login | NULL | | 236 | unauthenticated user | 10.120.61.10:51721 | NULL | Connect | NULL | login | NULL | | 237 | user | localhost | NULL | Query | 0 | NULL | show processlist | +-----+----------------------+--------------------+------+---------+------+-------+------------------+
Who are these unauthenticated users, how do they get there, and why aren’t they authenticated?
The client-server handshake in MySQL is a 4-step process. Those familiar with mysql-proxy already know these steps, as there are four functions that a Lua script in mysql-proxy can override. The process is useful to know for figuring out exactly where a problem is when something breaks.
Step 1: Client sends connect request to server. There is no information here (as far as I can tell). However, it does mean that if you try to connect to a host and port of a mysqld server that is not available, you will get
ERROR 2003 (HY000): Can't connect to MySQL server on '[host]' (111)
Step 2: The server assigns a connection and sends back a handshake, which includes the server’s mysqld version, the thread id, the server host and port, the client host and port, and a “scramble buffer” (for salting authentication, I believe).
It is during Step 2 where the connections show up in SHOW PROCESSLIST. They have not been authenticated yet, but they are connected. If there are issues with authentication, connections will be stuck at this stage. Most often stuck connections are due to DNS not resolving properly, which the skip-name-resolve option will help with.
Step 3: Client sends authentication information, including the username, the password (salted and hashed) and default database to use. If the client sends an incorrect packet, or does not send authentication information within connect_timeout seconds, the server considers the connection aborted and increments its Aborted_connects status variable.
Step 4: Server sends back whether the authentication was successful or not. If the authentication was not successful, mysqld increments its Aborted_connects status variable and sends back an error message:
ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: [YES/NO])
Hope this helps!