Chapter 9 Installing Connection Control Plugins

The connection-control plugin library enables administrators to introduce an increasing delay in server response to connection attempts after a configurable number of consecutive failed attempts. This capability provides a deterrent that slows 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.

To install the connection-control plugins:

1. Add these options under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):

   plugin-load-add=connection_control.so
   connection-control=FORCE_PLUS_PERMANENT
   connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT

   • 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.

2. To verify plugin installation, restart the server and examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement:

   $> systemctl restart mysqld

   $> cd /usr/local/mysql 
   $> bin/mysqladmin -u root -p version
   Enter password: (enter root password here)

   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        |
   +------------------------------------------+---------------+

• Configuring Connection Delays

• Monitoring Failed Connection Attempts

Configuring Connection Delays

Configure the server response delay for failed connection attempts using these server parameters:

• 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.

Add these options under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf) so that you can adjust them later as necessary. The default values are used in this deployment.

connection_control_failed_connections_threshold=3
connection_control_min_connection_delay=1000
connection_control_max_connection_delay=2147483647

For more information about server response delay configuration, see Connection-Control Plugin Installation.

Monitoring Failed Connection Attempts

Failed connection attempts can be monitored using these information sources:

• The Connection_control_delay_generated status variable indicates the number of times the server added a delay to its response to a failed connection attempt. This status variable does not count attempts that occur before reaching the threshold defined by the connection_control_failed_connections_threshold system variable.

• The INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table, enabled by the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin, provides information about the current number of consecutive failed connection attempts per account (user/host combination). This counts all failed attempts, regardless of whether they were delayed.

To test the connection-control plugin and view monitoring data:

1. Open a terminal and connect to the server as root:

   $> cd /usr/local/mysql 
   $> bin/mysql -u root -p
   Enter password: (enter the root password here)

2. Open a second terminal and perform four connection attempts as root, specifying an incorrect password each time. There should be a small but noticeable delay on the fourth connection attempt.

   $> cd /usr/local/mysql 
   $> bin/mysql -u root -p
   Enter password: (enter incorrect password here)

3. In the first terminal, issue this statement to view Connection_control_delay_generated status variable data. Connection attempts that exceed the connection_control_failed_connections_threshold threshold value of 3 are counted.

   mysql> SHOW STATUS LIKE 'Connection_control_delay_generated';
   +------------------------------------+-------+
   | Variable_name                      | Value |
   +------------------------------------+-------+
   | Connection_control_delay_generated | 1     |
   +------------------------------------+-------+

4. In the first terminal, issue this statement to view INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS data. All four failed connection attempts are counted.

   mysql> SELECT FAILED_ATTEMPTS FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
   +-----------------+
   | FAILED_ATTEMPTS |
   +-----------------+
   |               4 |
   +-----------------+