Login failure handling

In MySQL mode of OceanBase Database, for users who have multiple failed login attempts, the system will lock the users to prevent malicious password attacks, thereby improving database security.

Login failure handling strategy

In MySQL mode of OceanBase Database, you can set the connection_control_failed_connections_threshold parameter for a tenant to specify the threshold for control over failed login attempts. If the number of consecutive failed login attempts exceeds the value of this parameter, the system locks the account.

The default value of the connection_control_failed_connections_threshold parameter is 0, and the value range is [0, 2147483647]. Take note of the following items when you specify a parameter value:

• The value 0 indicates that the feature is disabled. In this case, no action is taken against failed login attempts.

• If you set the parameter to a value other than 0, the account of a user is locked when the number of failed login attempts by the user exceeds the specified value. The lock duration is calculated by 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).

  In this formula:

  • current_failed_login_num indicates the number of consecutive failed login attempts by a user. The value of the current_failed_login_num parameter is greater than or equal to that of the connection_control_failed_connections_threshold parameter.

  • connection_control_min_connection_delay indicates the minimum lock duration in milliseconds after the number of consecutive failed login attempts exceeds the specified threshold. The value range is [1000, 2147483647]. The default value is 1000.

  • connection_control_max_connection_delay indicates the maximum lock duration in milliseconds after the number of consecutive failed login attempts exceeds the specified threshold. The value range is [1000, 2147483647]. The default value is 2147483647.

Examples

1. Log in to a MySQL tenant of a cluster as the root user.

   obclient -uroot@mysql -h127.1 -P2881 -p********
   

2. Set the threshold of failed login attempts to 5, and set the minimum lock duration and maximum lock duration after the number of consecutive failed login attempts reaches the specified threshold to 60,000 ms and 360,000 ms respectively.

   Here is an example:

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

   For more information about the parameters, see Modify cluster parameters.

3. Create a user.

   obclient> CREATE USER 'test' IDENTIFIED BY '*******';
   Query OK, 0 rows affected (0.04 sec)
   

4. Verify the login failure handling strategy. If you enter an incorrect password five consecutive times, your account will be locked.

   obclient -h127.1 -P2881 -utest@mysql -p*******;
   obclient: [Warning] Using a password on the command line interface can be insecure.
   ERROR 5039 (01007): User locked
   

   Log in to the MySQL tenant as the root user to view the login failure information.

   obclient> SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
   +-------------+-----------------+
   | USERHOST    | FAILED_ATTEMPTS |
   +-------------+-----------------+
   | 'test'@'%' |               5 |
   +-------------+-----------------+
   1 row in set (0.005 sec)
   

5. Unlock the user.

   Notice

   If you log in as the administrator, you can directly lock and unlock users. If you log in as a regular user, you must have the global ALTER USER privilege to lock and unlock users. For more information about how to view and grant user privileges, see View user privileges and Grant direct privileges.

      obclient> ALTER USER test ACCOUNT UNLOCK;
      Query OK, 0 rows affected (0.03 sec)