Oracle R12 APPS user locked

In an EBS database one of the most critical user is the ‘APPS’ user.
We have handled several R11 instances but we have’nt encountered a locked ‘APPS’ account.
(its either the 11i clients has nice(not very technical) employees nobody was trying to login as APPS, or the users are talented technical people they know how to use other accounts other than APPS can create,alter users)

 
This big EBS R12.1.2 install frequently suffers from locked ‘APPS’ user, thereby denying everyone core apps (oracle forms) access. Adding to the confusion is the fact that SSWA login is working fine. 😦

Details:
Oracle Database Version:
11g R1 (11.1.0.7) EBS R12.1.2, OEL 5.5 x86_64bit
Application User: APPS
Error: ORA-28000: the account is locked

Login as SYSDBA

SQL> conn /as sysdba

Check the APPSUSR account status.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPS’;
USERNAME ACCOUNT_STATUS PROFILE
——————– ——————– —————
APPS 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_ATTEPTS 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_ATTEPTS attribute for DEFAULT profile has been changed since 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_ATTEPTS attribute in DEFAULT profile, or create a new profile for that user with FAILED_LOGIN_ATTEPTS 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 APPS_DEFAULT LIMIT
 COMPOSITE_LIMIT UNLIMITED
 SESSIONS_PER_USER UNLIMITED
 CPU_PER_SESSION UNLIMITED
 CPU_PER_CALL UNLIMITED
 LOGICAL_READS_PER_SESSION UNLIMITED
 LOGICAL_READS_PER_CALL UNLIMITED
 IDLE_TIME UNLIMITED
 CONNECT_TIME UNLIMITED
 PRIVATE_SGA UNLIMITED
 FAILED_LOGIN_ATTEMPTS UNLIMITED
 PASSWORD_LIFE_TIME UNLIMITED
 PASSWORD_REUSE_TIME UNLIMITED
 PASSWORD_REUSE_MAX UNLIMITED
 PASSWORD_VERIFY_FUNCTION NULL
 PASSWORD_LOCK_TIME UNLIMITED
 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 apps ACCOUNT UNLOCK;

User altered.

Now check again the status of APPS user.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPS’;
USERNAME ACCOUNT_STATUS PROFILE
——————– ——————– —————
APPS OPEN APPS_DEFAULT

Advertisements

About this entry