Login failure processing

In the Oracle 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.

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

Concepts

Profile is a means of limiting the resources available for a database user. In the Oracle mode of OceanBase Database, the profile is used to control a user's login 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 login failures:

• failed_login_attempts: the number of consecutive failed login attempts.
• password_lock_time: the lock duration, in days.

The lock and unlock policies are as follows:

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

Procedure

The administrator can create a profile and apply it to a user. Then, login 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 login failures and the corresponding lockout duration by using the CREATE PROFILE statement.

   The SQL syntax is as follows:

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

   The syntax is described as follows:

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

   • FAILED_LOGIN_ATTEMPTS specifies the number of consecutive login 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 login failures is not limited.

   • PASSWORD_LOCK_TIME specifies the number of days for which the user will be locked after the number of consecutive login 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 in 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 login 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.

   There are two scenarios:

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

     The SQL syntax is as follows:

     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 the user has already been created, you can apply an existing profile file to the user by modifying their profile.

     The SQL syntax is as follows:

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

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

Examples

1. Log in to an Oracle tenant of a cluster as the SYS user.

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

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

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

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

3. 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 ****** 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*******;
   obclient: [Warning] Using a password on the command line interface can be insecure.
   ERROR 5039 (01007): User locked
   

   Log in to the sys 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)
   

6. 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 secuser ACCOUNT UNLOCK;
   

Modify the login failure processing policy

You can execute the ALTER PROFILE statement to modify an existing profile.

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