What is an “unauthenticated user”?

Oct 23, 2008 / By Sheeri Cabral

Tags:

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])

5 Responses to “What is an “unauthenticated user”?”

  • Eric Day says:

    Hi Sheeri,

    You are correct in that during step one, the client doesn’t send any data. A TCP connection is established and the server takes the client source IP and compares it to the list of allowed hosts in the users table. If there is no way for the user to authenticate (no entries that could allow that host/IP), then the connection is dropped immediately with an error. So the credentials at this step are only the client’s source IP.

  • Xaprb says:

    Shameless plug: latest version of mysql-cacti-templates aggregates processlist statuses so it’s easy to see changes over time. In 99% of cases I’d say DNS is the problem, which is why I flatly recommend skip-name-resolve for EVERY production system REGARDLESS of whether “we have a really good DNS server that has never caused problems before.”

  • Sheeri Cabral says:

    Baron — plugging useful and free tools is OK :)

    Also, I agree about the skip-name-resolve, although this gets more difficult with systems such as Amazon’s EC2, because opening permissions up to “any system in the Amazon EC2 IP range” is pretty much like opening things up to the world.

  • Bhupinder says:

    We faced the same problem on one of our servers. We tried to do a reverse dns for the ip and found that it was not working. So we added the hostname of the app server to the hosts file and that resolved the problem right away. There is an interesting link for those who want to learn about it further. http://hackmysql.com/dns

  • ARK says:

    I get the same error but its happening in the localhost only….and the state is “writing to net”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>