MySQL Tutorial: Philosophical Thoughts on unauthenticated users

MySQL Tutorial: Philosophical Thoughts on unauthenticated users

Symptom 1

In the production environment, we occasionally encounter connections such as "unauthenticated user", with load at the database server level, high sys cpu, or thread running exceptions.

1. + ----- + ---------------------- + ------------------ + ------ + --------- + ------ + ------- + -------------------- +
   
2. | Id | User | Host | db | Command | Time | State | Info |
3. + ----- + ---------------------- + ------------------ + ------ + --------- + ------ + ------- + -------------------- +
4. | 235 | unauthenticated user | 10.10.2.74: 53216 | NULL | Connect | NULL | login | NULL |
5. | 236 | unauthenticated user | 10.120.61.10: 51721 | NULL | Connect | NULL | login | NULL |
6. | 237 | user | localhost | NULL | Query | 0 | NULL | show processlist |
7. + ----- + ---------------------- + ------------------ + ------ + --------- + ------ + ------- + -------------------- +

2. Analysis

Similar to the famous question raised by the campus guard when they see a stranger entering, everyone will enter the philosophical model: Who is TA? From where? What should I do? This article answers this philosophical question from the IT technology perspective

Who is TA?

Official introduction: unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done.

Meaning: MySQL has a thread processing the client connection, but the client has not passed user verification. When show processlist is displayed, "unauthenticated user ".

From where?

Before answering this question, we should first understand the process of establishing a TCP connection between the client and MySQL (the socket is not included in the scope of the discussion). Generally, the connection between the client and MySQL is divided into four steps:

1. The client sends data packets to the MySQL server and prepares to establish a connection. If the corresponding port of the MySQL server does not run the instance, an error is returned:

ERROR 2003 (HY000): Can't connect to MySQL server on '[host]' (111)

2. the MySQL server returns the ip address, port, mysqld version, the thread id, host and port of the database server to the client. the connection has been established but authorization has not been completed yet,

"When a new client connects to mysqld, mysqld spawns a new thread to handle the request. this thread first checks whether the host name is in the host name cache. if not, the thread attempts to resolve the host name:

The thread takes the IP address and resolves it to a host name (using gethostbyaddr ()). it then takes that host name and resolves it back to the IP address (using gethostbyname () and compares to ensure it is the original IP address."

In the actual connection process, mysql allocates a new thread to process client connection requests. Check whether the client's hostname is in the cache. If not, resolve the hostname. first, reverse resolution of the Client IP address ---> the client's hostname, and then forward resolution of the client's hostname ---> Client IP address. If the results match, the authentication is valid. If the results do not match, the authentication is defined as "unauthenticated user ".

3. The client sends username/password/dbname to the MySQ database server. If the client does not send a package or send an incorrect package within the time specified by connect_timeout for some reason, the database server will disconnect the connection.

4. Verify the server and return the verification result to the client. If the verification fails, the following results are usually returned:

ERROR 1045 (28000): Access denied for user 'user' @ 'host' (using password: [YES/NO])

OK. Now, we can know that the TA is in the second stage of establishing a connection between the client and the MySQL server.

What should I do?

Apparently, you are ready to access the database to obtain or write data.

How can we avoid the emergence of such a third-party employee? Analyze the cause of the problem and introduce the solution as follows:

A adds the skip-name-resolve parameter to [mysqld] of/etc/my. cnf to disable mysql's dns anti-query. mysql uses IP address or % authorization.

B adds the correspondence between the IP address and the host name in/etc/hosts.

192.168.0.1 xxxx

However, in our production environment, if skip-name-resolve has been configured, a large number of unauthenticated user information still appears, indicating that the MySQL server does not confirm the credential for client connection requests, that is to say, MySQL cannot confirm the database account information used for these connections. MySQL waits until these connections are completed within the wait_timeout time.

For example, if I run

Run show processlist in MySQL on the target machine to display it as unauthenticated user.

Therefore, this phenomenon may not be caused by the database itself.

1. If a large number of database probes occur due to application security issues, there will be a large number of such unauthorized connections.

2. When the application service is under too much pressure, a thread exception is interrupted, resulting in a large number of abnormal database connections.

3. Application Service exceptions, resulting in a large number of abnormal database connections.

4. the MySQL client connection version is incorrect. The authentication protocol is incompatible. Pay special attention to the old-password authentication method.

5. the thread of the database server is in the queue State. Therefore, you can increase back_log and MySQL's ability to process connection requests.

The first three items need to check the system load or application status when a large number of unauthenticated users appear on the application server. The last two items need to check the system status at the database server level.

This article permanently updates the link address: