Login failure handling

To prevent malicious password attacks and enhance database security, seekdb locks a user after multiple failed login attempts.

Login failure handling strategy

seekdb uses the connection_control_failed_connections_threshold parameter to specify the threshold for failed login attempts. When a user exceeds this threshold, the account is locked.

The connection_control_failed_connections_threshold parameter specifies the threshold for failed login attempts. The default value is 0, and the range is [0, 2147483647]. The parameter is described as follows:

• When the value is 0, the feature is disabled, and no action is taken for failed login attempts.

• When the value is not 0, the account is locked after the user exceeds the specified threshold. The lockout duration is calculated using the following formula:

  MIN(MAX((current_failed_login_num + 1 - connection_control_failed_connections_threshold) * 1000, connection_control_min_connection_delay), connection_control_max_connection_delay)

  where:

  • current_failed_login_num is the number of consecutive failed login attempts, and it is greater than or equal to connection_control_failed_connections_threshold.

  • The connection_control_min_connection_delay parameter specifies the minimum lockout duration after exceeding the threshold. The range is [1000, 2147483647], and the default value is 1000 (in milliseconds).

    For more information about the connection_control_min_connection_delay parameter, see connection_control_min_connection_delay.

  • The connection_control_max_connection_delay parameter specifies the maximum lockout duration after exceeding the threshold. The range is [1000, 2147483647], and the default value is 2147483647 (in milliseconds).

    For more information about the connection_control_max_connection_delay parameter, see connection_control_max_connection_delay.

Configuration example

1. Log in to seekdb as the root user.

   mysql -h127.0.0.1 -uroot -P2881

2. Execute the following statement to set the number of failed login attempts to 5. Specify the minimum and maximum lockout durations as 60000 and 360000 milliseconds, respectively.

   Here is an example:

   ALTER SYSTEM SET connection_control_failed_connections_threshold=5;
   
   ALTER SYSTEM SET connection_control_min_connection_delay=60000;
   
   ALTER SYSTEM SET connection_control_max_connection_delay=360000;

   For more information about the parameters, see Modify parameters.

3. Create a login user.

   CREATE USER 'test' IDENTIFIED BY '*******';

   Query OK, 0 rows affected (0.04 sec)

4. Verify whether the login failure handling strategy takes effect.

   When the user enters an incorrect password five times consecutively, the user is locked out.

   mysql -h127.0.0.1 -uroot -P2881 -p*******;
   ERROR 5039 (01007): User locked

   Log in to seekdb as the root user and view the failed login information.

   SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;

   +-------------+-----------------+
   | USERHOST    | FAILED_ATTEMPTS |
   +-------------+-----------------+
   | 'test'@'%' |               5 |
   +-------------+-----------------+
   1 row in set (0.005 sec)

5. Execute the following SQL statement to unlock the user.

   tip

   Unlocking a user is typically performed by an administrator. If a regular user needs to lock or unlock an account, they must have the global ALTER USER privilege. For information about viewing user privileges and granting privileges, see View user privileges and Directly grant privileges.

      ALTER USER test ACCOUNT UNLOCK;

      Query OK, 0 rows affected (0.03 sec)