To quote book of all knowledge:
In 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.
A brute force attack can be launched against an user account in MySQL. MySQL replies with success/error based on supplied credentials and time required for the verification is almost the same in either cases. Hence, an attacker can launch brute force attack against a MySQL user account at a rapid rate and can try many different passwords.
Enter – The Connection_control plugin! It was introduced in MySQL 8.0 and also backported to MySQL 5.7 and MySQL 5.6. This plugin allows database administrators to introduce an increasing delay in server response to client after certain number of consecutive failed connection attempts through a user account.
The plugin provides following:
- A way to configure threshold after which an increasing delay is triggered
- Ability to configure delay in server’s reply with minimum and maximum limits
- An information schema view to see monitoring information related to failed connection attempts
In order to get things going, one has to install the plugin and corresponding information schema view.
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Once done, there are 3 system variables available to control plugin’s behavior.
- connection_control_failed_connections_threshold: To set the consecutive number of failed attempts that triggers a delay in server’s response
- connection_control_min_connection_delay: To set the minimum delay once threshold is reached
- connection_control_max_connection_delay: To set the maximum delay regardless of the consecutive failed attempts
Once set, connection_control starts monitoring all failed connection attempts and keeps track of consecutive failed connection attempts for each user. Till the time consecutive failed attempts are less than the threshold, user does not experience any delay. This should avoid delay in genuine cases where user incorrectly typed her password.
However, if for a user account, such a figure crosses the threshold, the plugin treats it as a possible attack to crack user password. It then starts with minimum delay configured by system variable. Server will delay its reply. Client does not know whether password was correct or not unless server replies. Hence attacker has to maintain such a connection active. With each subsequent failed attempts, delay is increased by 1 sec until it reaches maximum limit. Thus, if an attacker is attacking server by spawning multiple connection requests, such connections have to be active till the time server replies. Introducing a delay makes it harder for attacker because now resources are occupied to make sure that connection requests are active.
Server will continue to introduce such delay for all subsequent failed connections and first successful connection. If we do not add any delay after first successful connection, attacker will get an indication that delay implies wrong password and thus free pending connections after waiting for a specific amount of time.
Maximum delay has an upper limit of 2147483647 and thereby effectively locking the account out of server. Setting threshold value at runtime works as a reset switch and server will restart counting of consecutive failed attempts.
Some finer points about the plugin:
- If user is proxying another user, the proxying user information is used for counting failed attempts. Thus, if multiple users are proxying same account, brute force attack on one of the account does not impact or introduce delay if others try to use same proxy user.
- In case of non-proxying account, effective user name (as provided by CURRENT_USER()) is considered by the plugin. This would ensure that for accounts with wildcard characters for hostname, all failed attempts are grouped together.
- If effective user name is not available (aka account does not exist), client supplied user name is used.
It is worth noting that connection_control functionality is independent of host cache. Restriction applied through host cache such as max-connect-errors still remains effective. What’s more, such checks are performed towards the beginning of connection establishment phase and hence control may never reach connection_control if max-connect-errors is triggered and connection from certain host(s) is blocked.
As always, a big thank you for using MySQL and let us know your feedback!