Login failure processing

If OceanBase Database is in Oracle mode, for users who have multiple failed logon attempts, the system will lock the user to prevent malicious password attacks, thereby improving database security.

The Oracle mode of OceanBase Database uses Profile to lock a user.

Concepts

Profile is a means of limiting the resources available to a database user. In the Oracle mode of OceanBase Database, the profile is used to control a user's logon verification policy. By using a user's profile file, it is possible to lock the user.

The profile file provides two parameters to lock the user after multiple consecutive logon failures:

• failed_login_attempts: the number of consecutive failed logon attempts.
• password_lock_time: the lock time, in days.

The lock and unlock policies are as follows:

• Lock policy: When there are repeated failed logon attempts due to an incorrect password, if the failure threshold is reached, the user will be locked out.
• Unlock policy: If a logged-in user is locked out, when attempting to log in again, the system will check the lock time. If the lock time has expired, the system will unlock the user and the user attempts to log in again. If the lock time has not expired, the system will return an error. You can also use SQL statements to unlock the user.

Procedure

The administrator can process logon failures by creating a profile and applying it to users, which mainly includes the following two steps:

1. Create a profile.

   Use the CREATE PROFILE statement to create a profile and specify the number of consecutive logon failures allowed for a user and the corresponding lock time.

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

   Notes:

   • Executing this statement requires the CREATE PROFILE permission.

   • FAILED_LOGIN_ATTEMPTS: specifies the number of consecutive failed logon attempts.

     If this parameter is not specified, the setting of the DEFAULT profile will be used. The value of this parameter in the DEFAULT profile is UNLIMITED, which indicates that there is no limit to the number of consecutive failed logon attempts.

   • PASSWORD_LOCK_TIME: specifies the number of days that a user is locked out after a threshold for consecutive logon failures has been reached.

     If this parameter is not specified, the setting of the DEFAULT profile will be used. The value of this parameter in the DEFAULT profile is UNLIMITED, which indicates that there is no limit to the number of days a user will be locked out.

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

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

   • PASSWORD_GRACE_TIME: After the PASSWORD_LIFE_TIME has been reached, the system issues a warning. This parameter is used to specify the number of days that users are allowed to log in after the warning has been issued.

     If this parameter is not specified, the setting of the DEFAULT profile will be used. The value of this parameter in the DEFAULT profile is UNLIMITED, which indicates that there is no limit to the number of days that a user can log in.

   • 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 of the DEFAULT profile is used.

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

2. Apply the profile to a user.

   There are two scenarios:

   • When creating a user, you can directly specify the profile file that has already been created.

     The SQL statement is as follows:

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

     If a profile is not specified when you create a user, the DEFAULT profile will be used. You can query the setting of the DEFAULT profile through the system view DBA_PROFILES.

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

   • If the user has already been created, you can apply an existing profile file to the user by modifying their profile.

     The SQL statement is as follows:

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

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

Example

1. Log on to an Oracle tenant of a cluster using the SYS user.

   obclient -usys@oracle -h127.1 -P2881 -  P***1*** 
   

2. Create a profile and set up a policy for consecutive failed logons.

   Create a profile named userprof1 that locks the user out for one day after five consecutive failed logon attempts.

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

3. Execute the following statement to check whether the setting was 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 a profile.

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

5. Verify the configuration result.

   If five consecutive incorrect passwords are entered, the user will be locked for one day.

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

   Log on to the sys tenant using the root user and view information about failed logon attempts.

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

6. Unlock the user.

   Notice:

   The operation of unlocking a user is generally performed by the administrator. If an ordinary user needs to perform lock and unlock operations, they must have global ALTER USER privileges. For more information about how to view and grant user privileges, see View user privileges and Modify user privileges.

   obclient> ALTER USER secuser ACCOUNT UNLOCK;
   

Modify the logon failure processing policy

Execute the ALTER PROFILE statement to modify an existing profile.

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