MySQL Security – The Connection-Control Plugins

March 29, 2018

When thinking about security within a MySQL installation, you should consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDEAudit, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, User Account Locking, etc…

MySQL Security

An ordinary threat databases could face is an attempt to discover the password by systematically trying every possible combination (letters, numbers, symbols). This is known as a brute force attack.
In this fourth episode of the MySQL Security series, we will see how the MySQL DBA can leverage the Connection-Control Plugins to slow down brute force attacks.

The Connection-Control Plugins

The MySQL Server includes a plugin library that enables administrators to introduce an increasing delay in server response to clients after a certain number of consecutive failed connection attempts. This capability provides a deterrent that slows down brute force attacks that attempt to access MySQL user accounts.

Installation

In MySQL 5.7, the Connection-Control plugin is not installed by default :

The plugin library contains two plugins :

  • CONNECTION_CONTROL checks incoming connections and adds a delay to server responses as necessary.
  • CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS implements an INFORMATION_SCHEMA table that exposes more detailed monitoring information for failed connection attempts.

As usual, you can easily register the plugins at runtime with INSTALL PLUGIN statement :

Alternatively you can modify the configuration file (my.cnf / my.ini) and then restart the server

If the plugins have been previously registered with INSTALL PLUGIN or are loaded with plugin-load-add, you can use the connection-control and connection-control-failed-login-attempts options at server startup to control plugin activation.

e.g. to load the plugins at startup and prevent them from being removed at runtime, use these options :

Configuration

To enable you to configure its operation, the CONNECTION_CONTROL plugin exposes 3 system variables :

Note : To entirely disable checking for failed connection attempts, set connection_control_failed_connections_threshold to zero.

Default values are :

You can modify these variables at runtime with SET GLOBAL :

Indeed they can be made persistent with the configuration file :

Let’s test the Connection-Control plugin behavior with a threshold = 2 and a delay = 1 second :

Please focus on the command execution time i.e. real NmN.NNNs

Starting at the 3rd attempts the delay between each connection increase (approximately +1s (= 1000 ms) between each new failed connection attempts).

Monitoring

To monitor failed connections, use 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 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 provides information about the current number of consecutive failed connection attempts per client user/host combination. This counts all failed attempts, regardless of whether they were delayed.

You can easily monitor different user accounts :

Connection-Control failure monitoring :

Uninstalling Plugins

To remove the plugins, use the UNINSTALL PLUGIN statement :

  • UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
  • UNINSTALL PLUGIN CONNECTION_CONTROL;

Note : Update the configuration file (my.cnf / my.ini) if necessary

In order to go further

MySQL Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall
  8. Enterprise Data Masking and De-Identification

Reference Manual

MySQL Security

Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

13 Responses to “MySQL Security – The Connection-Control Plugins”

  1. […] The Connection-Control Plugins […]

  2. […] The Connection-Control Plugins […]

  3. […] Data Encryption aka TDE,  Audit, Firewall, Password Management, Password Validation Plugin, The Connection-Control Plugins, […]

  4. […] The Connection-Control Plugins […]

  5. […] The Connection-Control Plugins : Atténuer les effets d’une attaque par force brute. […]

  6. […] MySQL Security – The Connection-Control Plugins […]

  7. […] MySQL Security – The Connection-Control Plugins […]

  8. Hi ,

    Is there any way to extract date & time and program using to connect to drill down more for failed login attempts.

    Thank you.

  9. […] Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]

  10. […] Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]

  11. […] Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]

  12. […] Policy, Password Reuse Policy, Password Verification-Required Policy, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]

  13. […] Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Connection-Control Plugins, Password Validation Component, […]