MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
Prevent and Detect Rapid Fire Password Attacks on MySQL
Posted on Sep 24, 2024 by Mike Frank
Category: Security
Tags: security

Introduction

What is a rapid fire brute force attack?

It is a trial-and-error approach used to obtain login passwords.  A hacker tries a multitude of username and passwords typically using code that tests a wide range of combinations until they find a login user and password combination that works. At that point the hacker is "in" and your database is "owned". This is one of the oldest style attacks, which keeps on working - and that's why it remains popular and you need protection.

Why should be very worried about rapid fire brute force styles attacks?

If successful they typically lead to:

  • Stolen data
  • Ransomware - once in they can encrypt your data.
  • Data distruction
  • Malware
  • Disruption of Service

Resulting

  • Loss of business
  • Loss of reputation
  • Expenses
  • Spying
  • Loss of Intellectual Property
  • Fines by regulators

Ounce of Prevention

Using “failed login” policies, MySQL DBAs can reduce the risk of a successful brute force rapid fire attacks. By leveraging a technique know as “login throttling”, DBAs thwarts  “rapid fire” login attempt attacks by adding increasing delays for each failed login attempt.  Installing and defining login throttling policies is simple and straightforward.

In just a matter of minutes you can, install the two MySQL plugins and set 3 variables, you can enable both defense and detection of rapid fire and other attacks on usernames and passwords.

FYI - In addition to below - also check out my youtube video:  Prevent and Detect Rapid Fire Password Attacks on MySQL

Installation

First simply run 2 commands to install the 2 mysql plugins.

CONNECTION_CONTROL_PLUGIN

Enforces your policy and adds appropriate delays.

mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
Query OK, 0 rows affected (0.27 sec)

CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS

This plugin creates the INFORMATION_SCHEMA table CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS

This table provides detailed monitoring information related to all  failed connection attempts.

mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Query OK, 0 rows affected (0.00 sec)

Check to confirm connection_control plugins were properly installed run

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

Now that our plugins are properly installed we can inspect the new variables used to define our policy.

mysql> show variables like 'CONNECTION_CONTROL%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 1000       |
+-------------------------------------------------+------------+
3 rows in set (0.00 sec)

Next we look at these variables and see how their settings enforce a policy to handle attacks.

Variables

CONNECTION_CONTROL_FAILED_CONNECTIONS_THRESHOLD

The first thing to consider is how many failed login attempts to allow before adding time delays.  Its typical for users to mistype their passwords, so best to give them several chances before adding details.  We set that threshold with this setting.

The MySQL default value for CONNECTION_CONTROL_FAILED_CONNECTIONS_THRESHOLD is 3. In general “3” is probably a good default, however if you have internal policies or regulatory specifics, you can change this value to meet your specific needs. A value of 0 will disable failed connection counting altogether.

Here we increase tries without a delay to 4.

mysql> set persist connection_control_failed_connections_threshold=4;
Query OK, 0 rows affected (0.00 sec)

CONNECTION_CONTROL_MIN_CONNECTION_DELAY

Next we define our minimum delay.  This delay increases with each failure once the number of fails passes our threshold and is set in milliseconds. The minimum number of milliseconds for this value is also 1000 (one second).

For example to increase the delay by half a second to 1.5 seconds.

mysql> set persist connection_control_min_connection_delay=1500;
Query OK, 0 rows affected (0.00 sec)

CONNECTION_CONTROL_MAX_CONNECTION_DELAY

Lastly set our maximum delay setting.  This is the maximum amount of time the minimum will climb to in intervals of the minimum connection delay. Saying this another way its the maximum a delay can reach. 

For example – setting the value to 15 seconds.

mysql> set persist connection_control_max_connection_delay=15000;
Query OK, 0 rows affected (0.01 sec)

Test Drive

For this example - we create the user – badtypist.  

mysql> create user badtypist identified by RANDOM PASSWORD;
+-----------+------+----------------------+-------------+
| user      | host | generated password   | auth_factor |
+-----------+------+----------------------+-------------+
| badtypist | %    | x8omoSa_[.boUA9L9lh- |           1 |
+-----------+------+----------------------+-------------+
1 row in set (0.01 sec)

Check initial state of things.

We can see that no delays have been added so far

mysql> show status like 'connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0     |
+------------------------------------+-------+
1 row in set (0.00 sec)

For more details on specific users login failures we run 

mysql> select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
Empty set (0.00 sec)

At this point there are no failures thus 0 and Empty Set are returned above.

Review our settings

mysql> show variables like 'CONNECTION_CONTROL%';
+-------------------------------------------------+-------+
| Variable_name                                   | Value |
+-------------------------------------------------+-------+
| connection_control_failed_connections_threshold | 4     |
| connection_control_max_connection_delay         | 15000 |
| connection_control_min_connection_delay         | 1500  |
+-------------------------------------------------+-------+

This policy shows - 4 tries, once failures exceed 4, we increase delays by 1.5 seconds for each failure up to a max delay of 15 seconds.

Run one failed login attempt (by typing in the wrong password)

% /usr/local/mysql/bin/mysql -u badtypist -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'badtypist'@'localhost' (using password: YES)

mysql> select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+-----------------+-----------------+
| USERHOST        | FAILED_ATTEMPTS |
+-----------------+-----------------+
| 'badtypist'@'%' |               1 |
+-----------------+-----------------+
1 row in set (0.00 sec)

 

Continue and fail to login 5 more times. Tries 4 and 5 will result in 1.5 and 3 second delays respectively.  

Our delayed connections count is now 2.

mysql> show status like 'connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 2     |
+------------------------------------+-------+
1 row in set (0.00 sec)

Running more  failed login attempts followed by at last by a successful attempt.

Tue Sep  3 <strong>15:35:34 </strong>CDT 2024
% /usr/local/mysql/bin/mysql -u badtypist -p < now.sql 
Enter password: 
ERROR 1045 (28000): Access denied for user 'badtypist'@'localhost' (using password: YES)
% /usr/local/mysql/bin/mysql -u badtypist -p < now.sql
Enter password: 
ERROR 1045 (28000): Access denied for user 'badtypist'@'localhost' (using password: YES)
% /usr/local/mysql/bin/mysql -u badtypist -p < now.sql
Enter password: 
ERROR 1045 (28000): Access denied for user 'badtypist'@'localhost' (using password: YES)
% /usr/local/mysql/bin/mysql -u badtypist -p < now.sql
Enter password: 
ERROR 1045 (28000): Access denied for user 'badtypist'@'localhost' (using password: YES)
% /usr/local/mysql/bin/mysql -u badtypist -p < now.sql
Enter password: 
ERROR 1045 (28000): Access denied for user 'badtypist'@'localhost' (using password: YES)
% /usr/local/mysql/bin/mysql -u badtypist -p < now.sql
Enter password: 
ERROR 1045 (28000): Access denied for user 'badtypist'@'localhost' (using password: YES)
Finally badtypist gets things right and provides the correct password.
% /usr/local/mysql/bin/mysql -u badtypist -p < now.sql
Enter password: 
now()
<strong>2024-09-03 15:36:35</strong>

With delays – the above took around a minute, versus a few seconds if these plugins were not in place.

This greatly slowed down attempts. 

Viewing the current status 

mysql> select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
Empty set (0.00 sec)

Nothing is returned – as the user badtypist – finally provided the correct password thus they are removed from our failed users list.

However the count of the number of delays generated – continued to increase.

mysql> show status like 'connection_control%generated';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 6     |
+------------------------------------+-------+
1 row in set (0.00 sec)

Running this login attempt 8 more times with a bad password.  Our status provides more detail. You would be able to see someone might be starting to run a rapid fire password attack on badtypists account.

mysql> show status like 'connection_control%generated';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 10    |
+------------------------------------+-------+
1 row in set (0.00 sec)

mysql> select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+-----------------+-----------------+
| USERHOST        | FAILED_ATTEMPTS |
+-----------------+-----------------+
| 'badtypist'@'%' |               8 |
+-----------------+-----------------+
1 row in set (0.00 sec)

 

As the user badtypist login attempts never succeeded – we see the account’s total number of failed attempts continue to increase.

Conclusion

If you are using MySQL username/password for authentication, using connection controls significantly improves security by thwarting attacks with delays as well as tracking who made the actual failed login attempts.  

This protection along with

  • Defining strong minimum password complexity policies
  • Secure client-side configuration management for authentication credentials
  • Banning shared accounts
  • Prompt removal of inactive/unneeded accounts (former employees/consultants)

Greatly improves the security on your MySQL server user accounts.

Finally, longer term, look beyond passwords.

MySQL provides various options for authentication

  1. Multi-factor Authentication
  2. LDAP and Active Directory Authentication
  3. Native Kerberos Authentication
  4. Password-less Authentication - FIDO (Fast Identity Online) WebAuthn

As always,

Thank you for using MySQL!

For more details see
https://dev.mysql.com/blog-archive/mysql-enterprise-security-4-new-authentication-methods/
https://dev.mysql.com/doc/refman/8.4/en/security.html

 Search
Categories