As a database administrator, have you ever been in a situation when your database confronted a brute force attack? A brute force attack can be launched against a user account in MySQL. MySQL replies with success or error based on supplied credentials, and the time required for the verification is almost the same in either case. Hence, an attacker can launch a brute force attack against a MySQL user account at a rapid rate and can try many different passwords.
According to cryptography, a brute-force attack consists of an attacker trying many passwords or passphrases with the hope of eventually guessing correctly. The attacker systematically checks all possible passwords and passphrases until the correct one is found.
It’s not just brute force attacks going on; the IT industry has recently seen a steady increase in distributed denial of service (DDoS) attacks. Have you also been targeted in such a type of connection flow on port 3306?
Today we would like to walk you through a special kind of plugin, i.e., the connection_control plugin! It was introduced in MySQL 8.0 and also back-ported to MySQL 5.7 and MySQL 5.6.
What are connection control plugins?
The connection control plugin library allows administrators to introduce an increasing delay in the server response to connections after a designated number of consecutive unsuccessful login attempts.
The idea behind using a connection control plugin is to configure a MySQL server so that the server will delay its response. The unauthorized user or a client does not know whether the password is correct or not unless the server replies. Thus, if an attacker attacks a server by spawning multiple connection requests, such connections have to be active until the time server replies. Introducing a delay makes it harder for attackers because now resources are occupied with ensuring connection requests are active. This technique can slow down brute force attacks against MySQL user accounts.
The plugin library contains two plugins:
- CONNECTION_CONTROL checks incoming connection attempts and adds a delay to server responses as necessary. This plugin also exposes system variables that enable its operation to be configured and a status variable that provides rudimentary monitoring information.
- CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS implements an INFORMATION_SCHEMA table that exposes more detailed monitoring information for failed connection attempts.
How to install connection control plugins
To load the plugins at runtime, use these statements, adjusting the .so suffix for your platform as necessary. Here I am going to test it with Percona Server for MySQL 5.7.36:
mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so'; Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec)
Or, you can install the plugin in my.cnf. Add these options under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):
[mysqld] plugin-load-add=connection_control.so connection-control=FORCE_PLUS_PERMANENT connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
Now let’s take a deeper look at each of these configurations options:
- plugin-load-add=connection_control.so
– Loads the connection_control.so library each time the server is started.
- connection_control=FORCE_PLUS_PERMANENT
– Prevents the server from running without the CONNECTION_CONTROL plugin, and server startup fails if the plugin does not initialize successfully.
- connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
– Prevents the server from running without the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin, and server startup fails if the plugin does not initialize successfully.
To verify plugin installation, restart the server and examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement:
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%'; +------------------------------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +------------------------------------------+---------------+ | CONNECTION_CONTROL | ACTIVE | | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE | +------------------------------------------+---------------+
Configure connection control thresholds
Now, let’s configure the server response delay for failed connection attempts using these server parameters. We will set the threshold of consecutive failed connections tentative to three and add a connection delay of a minimum of one second.
mysql> SET GLOBAL connection_control_failed_connections_threshold = 3; SET GLOBAL connection_control_min_connection_delay = 1000; SET GLOBAL connection_control_min_connection_delay = 90000;
Alternatively, to set and persist the variables at runtime, use these statements:
mysql> SET PERSIST connection_control_failed_connections_threshold = 3; SET PERSIST connection_control_min_connection_delay = 1000;
Also, you can add these options under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf) to adjust them later as necessary.
[mysqld] connection_control_failed_connections_threshold=3 connection_control_min_connection_delay=1000 connection_control_max_connection_delay=2147483647
Let’s talk about each of these variables in more detail:
- connection_control_failed_connections_threshold:
– The number of consecutive failed connection attempts permitted to accounts before the server adds a delay for subsequent connection attempts. - connection_control_min_connection_delay
– The minimum delay in milliseconds for connection failures above the threshold. - connection_control_max_connection_delay
– The maximum delay in milliseconds for connection failures above the threshold.
Testing and monitoring the connections
First terminal:
Note, here we will add a minimum one-second delay and set the failed connection threshold to three. The max connection delay is set to 90 seconds.
mysql> SET GLOBAL connection_control_failed_connections_threshold = 3; SET GLOBAL connection_control_min_connection_delay = 1000; set global connection_control_max_connection_delay=90000; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%connection_control%'; +-------------------------------------------------+-------+ | Variable_name | Value | +-------------------------------------------------+-------+ | connection_control_failed_connections_threshold | 3 | | connection_control_max_connection_delay | 90000 | | connection_control_min_connection_delay | 1000 | +-------------------------------------------------+-------+ 3 rows in set (0.00 sec)
Try to fetch the values of these variables:
mysql> select * from information_schema.connection_control_failed_login_attempts; Empty set (0.00 sec) mysql> show global status like 'connection_control_%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 0 | +------------------------------------+-------+ 1 row in set (0.00 sec)
Initially, there are no failed connection attempts and no connection delay generated since we are freshly configuring the parameters here. So, there you can see an empty set for information_schema.connection_control_failed_login_attempts and zero Connection_control_delay_generated.
Second terminal:
With the above settings, I tested brute force for 53 fake connections.
Open another terminal and perform these incorrect connections as a root user, specifying a wrong password each time.
[root@ip-xxx-xx-xx-xx ~]# for i in `seq 1 53`; do time mysql mysql -uroot -p”try_an_incorrect_password” -h xxx.xx.x.x 2>&1 >/dev/null | grep meh ; done 0.093 0.092 0.093 1.093 2.093 3.105 4.093 5.093 … … 45.092 46.093 47.093 48.093 49.092 50.093
What’s happening with these connections?
- In the MySQL processlist, each connection will be in the state of “Waiting in connection_control plugin.”
- Each connection will experience small but noticeable delays after the third connection attempt and will keep on increasing until you make the last attempt. With each subsequent failed attempt, the delay is increased by one second until it reaches the maximum limit. Meaning if the 50th connection is established after three unsuccessful logins, the 51st connection will take 51 seconds, and the 52nd connection will again take 52 seconds, and so on. This means the delay keeps on increasing until the connection_control_max_connection_delay is reached. As such, automated brute force attack tools will no longer be as useful since they will face continuous delays.
First terminal
Now switch back to the first terminal and recheck the values of the variables.
The connection_control starts monitoring all failed connection attempts and keeps track of consecutive failed connection attempts for each user.
Until consecutive failed attempts are less than the threshold, i.e., three, in our case, the user does not experience any delay. This should avoid delay in genuine cases where the user incorrectly typed his/her password.
Here you can notice that the status of Connection_control_delay_generated is now 50, and CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS is 53.
mysql> show global status like 'connection_control_%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 50 | +------------------------------------+-------+ 1 row in set (0.00 sec) mysql> SELECT FAILED_ATTEMPTS FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; +-----------------+ | FAILED_ATTEMPTS | +-----------------+ | 53 | +-----------------+ 1 row in set (0.00 sec)
What happens when you want to make a successful/genuine login?
Please note the server will continue to introduce such a delay for all subsequent failed connections and the first successful connection. Hence, the delay generated for the first successful login attempt after 53 unsuccessful logins is 53 seconds. Suppose MySQL does not add any delay after the first successful connection, in that case, the attacker will get an indication that delay implies the wrong password and thus free pending connections after waiting for a specific amount of time. So, if you try to make one successful connection after N number of unsuccessful attempts, you will surely experience a delay of N seconds for the first successful login attempt.
[root@ip-xxx-xx-xx-xx ~]# date; mysql -uroot -p’correct_password’ -hxxx.xx.x.x -e "select now();";date Tue Apr 18 06:27:36 PM UTC 2023 mysql: [Warning] Using a password on the command line interface can be insecure. +---------------------+ | now() | +---------------------+ | 2023-04-18 18:28:29 | +---------------------+ Tue Apr 18 06:28:29 PM UTC 2023
Which user caused this brute force attack?
You can also determine from which user or host these failed connection attempts were made.
mysql> select * from information_schema.connection_control_failed_login_attempts; +-----------------------+-----------------+ | USERHOST | FAILED_ATTEMPTS | +-----------------------+-----------------+ | 'root'@'xxx-xx-xx-xx' | 53 | +-----------------------+-----------------+ 1 row in set (0.00 sec)
How to reset the failed connection threshold
In case you want to reset these counters, you just have to again assign a value to the variable connection_control_failed_connections_threshold:
mysql> SET GLOBAL connection_control_failed_connections_threshold = 4; Query OK, 0 rows affected (0.00 sec) # Now you can see the values are reset! mysql> select * from information_schema.connection_control_failed_login_attempts; Empty set (0.00 sec) mysql> show global status like 'connection_control_%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 0 | +------------------------------------+-------+ 1 row in set (0.00 sec)
Conclusion
MySQL connection control can be very useful to limit the inconvenience of a brute force attack or unseemly TCP connections. Percona Server for MySQL also supports these plugins. By default, it is not enabled, but you can perform the same steps to enable the plugin and set up a secure connection for your environment.
To summarize, the plugin provides the following:
- A way to configure a threshold after which an increasing delay is triggered
- Ability to configure a delay in the server’s reply with minimum and maximum limits
- An information schema view to see monitoring information related to failed connection attempts
Please check our latest blogs on how you can keep your database secure:
Keep Your Database Secure With Percona Advisors
Improving MySQL Password Security with Validation Plugin
