Score:2

Can an unauthorized login attempt occupy a MySQL connection for a long time?

in flag

My MySQL has a limited number of concurrent connections defined by the max_connections variable by my cloud provider. Currently limited to 151 connections.

There is only one web server using this database therefore it should be more than enough. However, I was surprised to see that I currently have 30+ active connections to my server:

mysql> show status where `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 34    |
+-------------------+-------+

While there shouldn't be almost anything using it at the moment and I can verify it with show processlist: (only one connection here)

mysql> show processlist;
+--------+------------+--------------------+------------+---------+------+----------+------------------+-----------+---------------+
| Id     | User       | Host               | db         | Command | Time | State    | Info             | Rows_sent | Rows_examined |
+--------+------------+--------------------+------------+---------+------+----------+------------------+-----------+---------------+
| 272130 | ********** | ****************** | ********** | Query   |    0 | starting | show processlist |         0 |             0 |
+--------+------------+--------------------+------------+---------+------+----------+------------------+-----------+---------------+

I've been struggling to try to account for the remaining 33 connection's whereabouts and finally I realized: could those be mass brute-force attacks? Maybe 30-40 hackers are trying to guess my password and that blocks a thread per each attacker?

Is my assumption correct?

UPDATE 2021-07-07: Added more details of MySQL status

mysql> show status where `variable_name` like '%threads%' or `variable_name` like '%connection%';
+-----------------------------------+---------------------+
| Variable_name                     | Value               |
+-----------------------------------+---------------------+
| Connection_errors_accept          | 0                   |
| Connection_errors_internal        | 0                   |
| Connection_errors_max_connections | 0                   |
| Connection_errors_peer_address    | 8                   |
| Connection_errors_select          | 0                   |
| Connection_errors_tcpwrap         | 0                   |
| Connections                       | 482365              |
| Delayed_insert_threads            | 0                   |
| Max_used_connections              | 74                  |
| Max_used_connections_time         | 2021-07-05 09:10:27 |
| Slow_launch_threads               | 0                   |
| Threadpool_idle_threads           | 0                   |
| Threadpool_threads                | 0                   |
| Threads_cached                    | 5                   |
| Threads_connected                 | 36                  |
| Threads_created                   | 2882                |
| Threads_running                   | 1                   |
+-----------------------------------+---------------------+
in flag
If the connections are assigned to your account the authentication on MySQL level has already been passed so account brute force doesn't make sense. Based on your description I would assume that all connected threads belong to the one server process.
adamsfamily avatar
in flag
I'm actually not sure whether the connections are already assiged to my account. I'm wondering how can I figure this out? BTW, this is Arubacloud's 'shared' MySQL database service, so other users might be on the same server. Is it possible that I do see other authenticated connections in the `Threads_connected` that belong to other users? (I don't see it super likely that those would be connections from my server because even if there is no traffic on the server, the connections are still alive - 12hrs laters)
in flag
Assuming that the website is not critical there is one simple measure to see if the connection come from your web server: stop the web server and recheck the connections. Alternatively if the webserver is not shared you could look at the TCP connections between webserver and mysql server. Every database connection always also have one TCP connection (assuming the MYSQL connection uses TCP).
adamsfamily avatar
in flag
Tried it, shut down the web site and connections are still between 33-38. I think it has to be either 1) other users of the MySQL server I cannot see with `show processlist` or 2) unauthenticated users -- however I can't prove either...
Score:1
ua flag
  • SHOW PROCESSLIST only shows connections for the user running that command. Be sure to connect as root to get the complete list. "root" is actually more powerful than you need; there may be some other user ("admin"?) that is powerful enough. In terms of GRANT, all the 'user' needs is PROCESS.

  • Once you get the processlist, Time will indicate how many seconds since they connected.

  • SHOW STATUS LIKE 'Threads_running'; will show how many connections are currently active.

  • SHOW STATUS LIKE 'Max_used_connections'; is a high water mark. If it has not yet hit max_connections (you mentioned 151), then you have not yet (since restart) "run out of connections".

  • The timeout may be large, leading to connections hanging around.

adamsfamily avatar
in flag
Thanks Rick, unfortunately I cannot login as `root` since the managed database provider does not let me. However, I am logged in under the only user account I have therefore I should at least see my own connections. I updated the question with a more detailed output. The 'high watermark' is great, very good indicator, thanks!
ua flag
@adamsfamily - I added to my first bullet item.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.