MySQL Connection Control

As a MySQL database administrator, have you ever faced a brute force attack on your database server or been the target of a DDOS-like connection flow on port 3306? If so, you will quickly understand why this plugin distributed with MySQL can be very useful in such situations.

Indeed, the connection-control plugin allows the administrator of a MySQL server to introduce an increasing delay in response time when a connection attempt is made and when a consecutive number of connection failures is reached.

This method, slows down the eventual attacks targeting MySQL like brute force attacks on MySQL user accounts.

Let’s test the plugin to show how it works.

The plugin library is connection_control.so that is installed in the plugin directory. On Oracle Linux/RedHat Enterprise/CentOS/Fedora the default path, if MySQL Server was installed via RPM, is: /usr/lib64/mysql/plugin/connection_control.so.

Installation

It’s possible to install the plugin in my.cnf, see the manual, but we will see how to use it interactively:

INSTALL PLUGIN CONNECTION_CONTROL
  SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
  SONAME 'connection_control.so';

Once installed, the plugin can be listed from Information_Schema:

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        |
+------------------------------------------+---------------+
2 rows in set (0.0018 sec)

Now, let’s configure the plugin. We will set the threshold of consecutive failed connection tentative to 4 and add a minimum of 1,5 seconds:

SET GLOBAL connection_control_failed_connections_threshold = 4;
SET GLOBAL connection_control_min_connection_delay = 1500;

If there are failed login attempts, we will be able to list them in Information_Schema using the table connection_control_failed_login_attempts, for the moment it’s still empty:

select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.0003 sec)

Testing

Let’s test our settings by trying to connect to our MySQL Server using MySQL Shell and an invalid password. We will try 10 times in a row and only display the real time spent by the command (the connection attempt):

$ TIMEFORMAT=%R
$ for i in `seq 1 10`
  do 
   time mysql mysql://fred:ffr@127.0.0.2 2>&1 >/dev/null | grep meh 
  done
0.747
0.749
0.731
0.735
2.265
2.753
3.744
4.757
5.750
6.741

It’s obvious that after the 4th attempt the response time kept increasing.

We can verify the status variable Connection_control_delay_generated, that contains the number of times the server added a delay to its response to a failed connection attempt:

show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 6     |
+------------------------------------+-------+

And this time the connection_control_failed_login_attempts table is not empty:

select * from information_schema.connection_control_failed_login_attempts;
+------------+-----------------+
| USERHOST   | FAILED_ATTEMPTS |
+------------+-----------------+
| 'fred'@'%' |              10 |
+------------+-----------------+
1 row in set (0.0007 sec)

To reset those counters, you just have to assign again a value to the variable connection_control_failed_connections_threshold:

SET GLOBAL connection_control_failed_connections_threshold = 4;
Query OK, 0 rows affected (0.0001 sec)

select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.0007 sec

show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0     |
+------------------------------------+-------+

Now we can also test without using MySQL Shell or any other MySQL protocol aware client:

$ for i in `seq 1 10` 
  do 
   time echo meh | nc 127.0.0.3 3306 2>&1 >/dev/null | grep meh 
  done
0.027
0.024
0.023
0.022
1.526
2.032
3.028
4.027
5.032
6.065

We can see that the response time is also increased even if there was no real MySQL authentication attempt.

We can see that the info is also available:

select * from information_schema.connection_control_failed_login_attempts;
+----------------+-----------------+
| USERHOST       | FAILED_ATTEMPTS |
+----------------+-----------------+
| ''@'localhost' |              10 |
+----------------+-----------------+
1 row in set (0.0006 sec)

show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 6     |
+------------------------------------+-------+
1 row in set (0.0021 sec)

Conclusion

MySQL Connection Control can be very useful in some environments to avoid or limit the inconvenience of a brute force attack or inappropriate TCP connections.

In MySQL HeatWave Database Service on Oracle Cloud Infrastructure, Connection Control Plugin is enabled by default.

These are the values used:

show global variables like 'connection_control_%';
+-------------------------------------------------+-------+
| Variable_name                                   | Value |
+-------------------------------------------------+-------+
| connection_control_failed_connections_threshold | 3     |
| connection_control_max_connection_delay         | 10000 |
| connection_control_min_connection_delay         | 1000  |
+-------------------------------------------------+-------+

You are not allowed to modify them.

Enjoy MySQL !