Account LOCKED(TIMED)

    Recently I have met several times on 10g Release 2 that some users consistently get locked with status LOCKED(TIMED). One good example is with the DBSNMP and SYSMAN users, but more critical situation is that product accounts are locked which will down your traffic.

    I have tried to unlock the account using "alter user oracle account unlock",although the SQL command run successfully, you will find the user oracle is still locked when query the status of it " select USERNAME, ACCOUNT_STATUS from dba_users where USERNAME = 'ORACLE';

   It turned out that the FAILED_LOGIN_ATTEMPTS attribute for the DEFAULT profile has been changed in 10.2.0.2 (actually 10.2.0.1 and above) from UNLIMITED to the value of 10. Well, that’s good from security point of view. On the other hand, this is really dangerous, especially during or after migrations while chances are high that some process will try to connect with wrong credentials. This can easily end up with a service outage because an application can’t connect.

    One way to resolve it is to change the DEFAULT profile. However, I would recommend leaving it 10 by default and, instead, create a new profile and assign the critical production users to this profile:

CREATE PROFILE DEFAULT_10GR1
    LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER USER [USERNAME] PROFILE DEFAULT_10GR1;

   The next step should be to review this policy with your security officer. By the way, this must be a substantive discussion – a production DBA should be keen to avoid service outages by any means, while a security officer’s priority is preventing unauthorized access.

Appenix A (Got from metalink)

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.1
Information in this document applies to any platform.

Goal

This document has been authored by an Oracle customer, and has not been subject to an independent technical review.

Authored by: Syed Sabdar Hussain
Title: Consultant – Oracle DBA & Oracle Apps DBA
Company: Saudi Hollandi Bank

An application user account, in one of our Oracle 10g Databases, is being locked every time. Below are our findings and solution to resolve the issue.

Solution

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 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