Handle logon failures

OceanBase Database in Oracle mode provides password-based logon verification strategies in the profile to implement user lockout in case of logon failures.

Background

The profile limits the resources used by database users. In OceanBase Database in Oracle mode, the profile controls logon verification strategies for users. You can use the profile to implement user lockout.

The profile provides two password parameters to implement user lockout:

• FAILED_LOGIN_ATTEMPTS: specifies the number of consecutive logon failures that triggers user lockout.

• PASSWORD_LOCK_TIME: specifies the lockout duration in days.

User lockout and unlock strategies:

• User lockout: The logon of a user fails when an incorrect password is entered. If the number of logon failures reaches the threshold, the user is locked.

• User unlock: If a user is locked out, the system checks the lockout duration when the user attempts to log on again. If the lockout duration has expired, the system unlocks the user, and the user can attempt to log on again. If the lockout duration has not expired, the system returns an error, and you can unlock the user by using an SQL statement.

Implementation

The administrator can create a profile and apply it to a user. Then, logon failures of the user will be handled based on the profile. The procedure includes the following two steps:

1. Create a profile.

   You can create a profile and specify the number of consecutive logon failures and the corresponding lockout duration by using the CREATE PROFILE statement.

   SQL syntax:

   CREATE PROFILE "profile_name" LIMIT
     { FAILED_LOGIN_ATTEMPTS
       | PASSWORD_LOCK_TIME
       | PASSWORD_LIFE_TIME
       | PASSWORD_GRACE_TIME
       }
       { integer | UNLIMITED | DEFAULT };
   

   Notes:

   • To execute this statement, you must have the CREATE PROFILE privilege.

   • FAILED_LOGIN_ATTEMPTS specifies the number of consecutive logon failures that triggers user lockout.

     If this parameter is not specified, the setting in the DEFAULT profile is used. The value of this parameter in the DEFAULT profile is UNLIMITED, which indicates that the number of consecutive logon failures is not limited.

   • PASSWORD_LOCK_TIME specifies the number of days for which the user will be locked after the number of consecutive logon failures reaches the threshold.

     If this parameter is not specified, the setting in the DEFAULT profile is used. The value of this parameter in the DEFAULT profile is UNLIMITED, which indicates that the number of days for which the user will be locked is not limited.

   • PASSWORD_LIFE_TIME specifies the number of days during which the same password can be used for authentication.

     If this parameter is not specified, the setting in the DEFAULT profile is used. The value of this parameter in the DEFAULT profile is UNLIMITED, which indicates that the number of days during which the same password can be used for authentication is not limited.

   • PASSWORD_GRACE_TIME specifies the number of days of the grace period during which the user is still allowed to log on after a warning is issued upon the expiration of the password lifetime.

     If this parameter is not specified, the setting in the DEFAULT profile is used. The value of this parameter in the DEFAULT profile is UNLIMITED, which indicates that the number of days during which the user logon is valid after the password lifetime expires is not limited.

   • integer | UNLIMITED | DEFAULT specifies the value range of a parameter.

     • integer indicates that the value of the parameter is an integer.

     • UNLIMITED indicates that the value of the parameter is not restricted.

     • DEFAULT indicates that the setting in the DEFAULT profile is used.

   For more information about the CREATE PROFILE statement, see CREATE PROFILE.

2. Apply the profile to a user.

   Scenarios:

   • When you create a user, directly specify the created profile for the user.

     SQL syntax:

     obclient> CREATE USER user_name IDENTIFIED BY password PROFILE
     {"profile_name" | default};
     

     By default, if you do not specify a profile when you create a user, the DEFAULT profile is used. You can query the DBA_PROFILES view for the settings of the DEFAULT profile.

     For more information about the CREATE USER statement, see CREATE USER.

   • If a user has been created, modify the profile of the user to apply the created profile to the user.

     SQL syntax:

     obclient> ALTER USER user_name PROFILE {"profile_name" | default};
     

     For more information about the ALTER USER statement, see ALTER USER.

Examples

1. Log on to an Oracle tenant as the administrator.

2. Create a profile and configure a handling strategy regarding consecutive logon failures.

   Create a profile named userprof1 and configure a strategy to lock the user for one day upon five consecutive logon failures.

   obclient> CREATE PROFILE "userprof1" LIMIT FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LOCK_TIME 1;
   

3. Execute the following statement to check whether the settings are successful:

   obclient> SELECT * FROM DBA_PROFILES;
   +-----------+--------------------------+---------------+-------------+
   | PROFILE   | RESOURCE_NAME            | RESOURCE_TYPE | LIMIT       |
   +-----------+--------------------------+---------------+-------------+
   | DEFAULT   | FAILED_LOGIN_ATTEMPTS    | PASSWORD      | UNLIMITED   |
   | DEFAULT   | PASSWORD_GRACE_TIME      | PASSWORD      | UNLIMITED   |
   | DEFAULT   | PASSWORD_LIFE_TIME       | PASSWORD      | UNLIMITED   |
   | DEFAULT   | PASSWORD_LOCK_TIME       | PASSWORD      | 86400000000 |
   | DEFAULT   | PASSWORD_VERIFY_FUNCTION | PASSWORD      | NULL        |
   | userprof1 | FAILED_LOGIN_ATTEMPTS    | PASSWORD      | 5           |
   | userprof1 | PASSWORD_GRACE_TIME      | PASSWORD      | DEFAULT     |
   | userprof1 | PASSWORD_LIFE_TIME       | PASSWORD      | DEFAULT     |
   | userprof1 | PASSWORD_LOCK_TIME       | PASSWORD      | 86400000000 |
   | userprof1 | PASSWORD_VERIFY_FUNCTION | PASSWORD      | DEFAULT     |
   +-----------+--------------------------+---------------+-------------+
   10 rows in set
   

4. Create a user and specify the profile for the user.

   obclient> CREATE USER secuser IDENTIFIED BY ****** PROFILE "userprof1";
   
   obclient> GRANT all privileges ON *.* to secuser;
   

5. Verify the settings.

   If the user enters an incorrect password five consecutive times, the user will be locked for one day.

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

   Log on to the sys tenant as the root user and view the logon failure information.

   obclient> SELECT user_name,gmt_modified,failed_login_attempts,last_failed_login_svr_ip FROM
   oceanbase.__all_virtual_tenant_user_failed_login_stat WHERE user_name='SECUSER';
   +-----------+----------------------------+-----------------------+--------------------------+
   | user_name | gmt_modified               | failed_login_attempts | last_failed_login_svr_ip |
   +-----------+----------------------------+-----------------------+--------------------------+
   | SECUSER   | 2021-11-25 16:08:24.911700 |                     5 | xxx.xx.xxx.xxx           |
   +-----------+----------------------------+-----------------------+--------------------------+
   1 row in set
   

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

   Notice

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

   obclient> ALTER USER secuser ACCOUNT UNLOCK;