How We Resolved the Account Locked (Timed) issue in Oracle Database 10g

Details: Oracle Database Version: 10g R2 (10.2.0.1)
Application User: APPUSR
Error: ORA-28000: the account is locked

Login as SYSDBA
SQL> conn /as sysdba

Check the APPSUSR account status.
SQL> SELECT username, account_status, profile FROM dba_users WHERE username= ‘APPUSR’; USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR LOCKED(TIMED) DEFAULT

Here we can see the account status is LOCKED (TIMED) and the default user’s profile is DEFAULT.

Check the resource limits of DEFAULT profile.
SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';
RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD 10 PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED

All resource limits for DEFAULT profile is set to UNLIMITED, but only for FAILED_LOGIN_ATTEMPTS attribute, it’s set to some value (10). Due to this the user account keeps getting locked(timed).When we check in the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEMPTS attribute for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10.

What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEMPTS attribute in DEFAULT profile, or create a new profile for that user with FAILED_LOGIN_ATTEMPTS attribute value set to UNLIMITED. But for security reasons, we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign that profile to the user.

Create a profile.

SQL> CREATE PROFILE APPUSR_DEFAULT LIMIT
2 COMPOSITE_LIMIT UNLIMITED
3 SESSIONS_PER_USER UNLIMITED
4 CPU_PER_SESSION UNLIMITED
5 CPU_PER_CALL UNLIMITED
6 LOGICAL_READS_PER_SESSION UNLIMITED
7 LOGICAL_READS_PER_CALL UNLIMITED
8 IDLE_TIME UNLIMITED
9 CONNECT_TIME UNLIMITED
10 PRIVATE_SGA UNLIMITED
11 FAILED_LOGIN_ATTEMPTS UNLIMITED
12 PASSWORD_LIFE_TIME UNLIMITED
13 PASSWORD_REUSE_TIME UNLIMITED
14 PASSWORD_REUSE_MAX UNLIMITED
15 PASSWORD_VERIFY_FUNCTION NULL
16 PASSWORD_LOCK_TIME UNLIMITED
17 PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

User altered. Unlock the user account:

SQL> ALTER USER appusr ACCOUNT UNLOCK;
User altered.
Now check again the status of APPUSR user.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’; USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR OPEN APPUSR_DEFAULT