LOCKED vs LOCKED(TIMED) in ACCOUNT_STATUS in DBA_USERS

We use ACCOUNT_STATUS column value: LOCKED(TIMED) in DBA_USERS dictionary view to monitor cases where ACCOUNTS getting locked due to wrong password attempts.  When we deliberately lock a user account, status is set to LOCKED and it works for most of the cases.

I Started seeing a new behavior after 11g upgrade where ACCOUNT_STATUS for database users changed from LOCKED(TIMED) to LOCKED when user accounts is locked after too many wrong password attempts.

SQL> select username,account_status,LOCK_DATE,profile from dba_users where username='MAD_APP';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE           PROFILE

------------------------------ -------------------------------- ------------------- ------------------------------

MAD_APP                       LOCKED                           2015-02-03 12:18:28 APPLICATION_PROFILE

SQL> select username,account_status,LOCK_DATE,profile from dba_users where username='MAD_TOOL_ADM';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE           PROFILE

------------------------------ -------------------------------- ------------------- ------------------------------

MAD_TOOL_ADM                  LOCKED                           2015-02-03 12:19:33 SCHEMA_PROFILE

SQL> alter user MAD_APP account unlock;

User altered.

SQL> alter user MAD_TOOL_ADM account unlock;

User altered.

After checking at DBA_PROFILES settings for these user profiles, noticed that  PASSWORD_LOCK_TIME  is set to UNLIMITED. With this value set to unlimited, it’s an expected behavior to see only LOCKED status instead of LOCKED(TIMED). As per our organization standards it should be set to 24855.

PROFILE                               RESOURCE_NAME               RESOURCE_TYPE        LIMIT

--------------------------------- ---------------------- ---------------------- -------------

APPLICATION_PROFILE                PASSWORD_LOCK_TIME              PASSWORD        UNLIMITED

SCHEMA_PROFILE                     PASSWORD_LOCK_TIME              PASSWORD        UNLIMITED

Expected behaviour is
1. Oracle release is <= 11.1.0.7.
DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

2. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME = unlimited:
DBA_USERS.ACCOUNT_STATUS = LOCKED whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

3. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME =
DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

Note that 10.2.0.5 displays the same behavior as 11.2, because the fix that changed the behavior in 11.2 was introduced in 10.2.0.5.

So finally resolved the issue by following command

SQL> alter profile system_profile limit PASSWORD_LOCK_TIME 24855;

SQL> alter profile application_profile limit PASSWORD_LOCK_TIME 24855;

Now I could see that status changed to LOCKED(TIMED)

SQL> select username,account_status from dba_users where username in ('MAD_APP','MAD_TOOL_ADM');

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
MAD_APP                       LOCKED(TIMED)
MAD_TOOL_ADM                  LOCKED(TIMED)