Who is the culprit ORA-28000 : the account is locked

Posted by

 You will always come across situations where RCA is more important than the solution.

A similar request knocked me last week where the user needs to know what process or who (person) locked the id.

There are several ways to achieve this. In this blog, I would describe by setting up audit method.

  1.  Use a trigger to capture additional information
  2.  Set an event in the "init.ora" parameter file
  3.  Audit unsuccessful logins
  4.  Use SQLNET Tracing to gather detailed information

For rest of the methods please follow “Finding the source of failed login attempts. (Doc ID 352389.1)”

Before we dive in, a quick recap..

Status# of an account in oracle database is from 0 to 10 are the. Notice that 3 and 7 are missing. Why?

The logic behind it is that you would never end up with status EXPIRED EXPIRED (GRACE) or EXPIRED EXPIRED (GRACE) LOCKED (TIMED), which doesn’t makes sense However, you can have status# 5, 6, 9 & 10. If you sum up 4+1 you would get status#=5 i.e. EXPIRED & LOCKED (TIMED), similarly others. 

What do they mean in real life?

Account status:
  • OPEN - Account is open and accessible
  • EXPIRED - Account is expired. Access denied.
  • EXPIRED (GRACE) - Account is expired but still accessible. You will be given grace period to change the password to keep the account status to OPEN.
  • LOCKED (TIMED) - Account is locked due to unsuccessful login attempts
  • LOCKED - Account is locked by the admin.
In addition, below are the combinations of above.
  • EXPIRED & LOCKED(TIMED)
  • EXPIRED(GRACE) & LOCKED(TIMED)
  • EXPIRED & LOCKED
  • EXPIRED(GRACE) & LOCKED

What parameters govern them?

failed_login_attempts - This is the number of failed login attempts before locking the Oracle user account.  

password_grace_time - This is the grace period after the password_life_time limit is exceeded. In this example after 60 days of password life time 10 days grace period will be allowed to reset the password of the account .

password_life_time - This is how long an existing password is valid.

password_lock_time - This is the number of days that must pass after an account is locked before it is unlocked.  It specifies how long to lock the account after the failed login attempts is met.

password_reuse_time - This parameter specifies a time limit before a previous password can be re-entered. To allow unlimited use of previously used passwords, set password_reuse_time to UNLIMITED.

password_verify_function - This allows you to specify the name of a custom password verification function. Example PASSWORD_VERIFY_STRONG function uploaded in GitHub.

password_reuse_max - This is the number of times that you may reuse a password and is intended to prevent repeating password cycles. If you reset the password to same value then you defy the purpose. However, real world is not ideal and you would want to reset the same password for application schema due to hard-coding, bad policies etc.

SQL> select * from dba_profiles where profile='USERS' and resource_type='PASSWORD';
 PROFILE    RESOURCE_NAME                  RESOURCE_TYPE            LIMIT                            COMMON
---------- ------------------------------ ------------------------ ------------------------------ 
USERS      FAILED_LOGIN_ATTEMPTS          PASSWORD                 5                              NO
USERS      PASSWORD_LIFE_TIME             PASSWORD                 60                             NO
USERS      PASSWORD_REUSE_TIME            PASSWORD                 60                             NO
USERS      PASSWORD_REUSE_MAX             PASSWORD                 5                              NO
USERS      PASSWORD_VERIFY_FUNCTION       PASSWORD                 PASSWORD_VERIFY_STRONG         NO
USERS      PASSWORD_LOCK_TIME             PASSWORD                 UNLIMITED                      NO
USERS      PASSWORD_GRACE_TIME            PASSWORD                 10                             NO
 7 rows selected.
 SQL> alter user nava identified by "Password00!" ;
alter user nava identified by "Password00!"
*
ERROR at line 1:
ORA-28007: the password cannot be reused 

 To overcome ORA-28007 try to rotate the profile with DUMMY profile.

SQL> CREATE PROFILE DUMMY LIMIT
 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.

 SQL> alter user nava profile DUMMY;
 User altered.
 SQL>  alter user nava identified by "Password00!" ;
 User altered.
 SQL> alter user nava profile USERS;
 User altered.

 How to trace suspect logins that have failed with ora-1017 ?

  • Check the status from dba_users.
SQL> alter user nava account lock ;
 User altered.
 SQL> select username, account_status from dba_users where username ='NAVA';
 USERNAME   ACCOUNT_STATUS
---------- ---------------
NAVA       LOCKED

 If the ACCOUNT_STATUS for the user is LOCKED, this means the account was locked by a DBA doing ALTER USER &username ACCOUNT LOCK.

Unsuccessful connect attempts can be a nuisance, especially when a password management policy is in place that has a limit on failed_login_attempts and cause the ora-28000 "the account is locked" error.

Remember unlocking is not what they are looking for, they want to know who/what process is locking the ID .

SQL> select username, account_status from dba_users where username ='NAVA';
 USERNAME   ACCOUNT_STATUS
---------- ---------------
NAVA       LOCKED(TIMED)

 If the ACCOUNT_STATUS is LOCKED(TIMED) this means it was locked because of exceeding the number of allowed FAILED_LOGIN_ATTEMPTS, this is true even if the PASSWORD_LOCK_TIME is set to unlimited.

An account never get locked due to password expiration. That is reason we need to screen the accounts in regular audit cycle and take action such as lock explicitly.


Before the account is actually locked you can inspect the number of failed login attempts so far by checking the USER$.LCOUNT column. I have tried 5 times so LCOUNT is 5.

SQL>  select ctime,ltime,ptime,lcount from user$ where name='NAVA';
 CTIME           LTIME           PTIME               LCOUNT
--------------- --------------- --------------- ----------
13-AUG-20       13-AUG-20       13-AUG-20                5
 SQL> select username, account_status from dba_users where username ='NAVA';
 USERNAME   ACCOUNT_STATUS
---------- ---------------
NAVA       Open

 Account status is still open and one more incorrect attempt and account is LOCKED (TIMED)

SQL> select username, account_status from dba_users where username ='NAVA';
 USERNAME   ACCOUNT_STATUS
---------- ---------------
NAVA       LOCKED(TIMED)

  • Let us find the culprit and enable the audit.
 SQL> sho parameter audit_trail
 NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- --------------
audit_trail                          string                            DB
 SQL> AUDIT SESSION WHENEVER NOT SUCCESSFUL;
 Audit succeeded.

 You can also get total failed login attempts, OS user name, source of origin with date  however USER$.LCOUNT will remain same and will not increase.

  SQL>  select username,
os_username,
userhost,
trunc(timestamp),
count(*) failed_logins
from dba_audit_trail
where returncode=1017 and --1017 is invalid username/password
username='NAVA' and
timestamp > sysdate -7
group by username,os_username,userhost, client_id,trunc(timestamp);
 USERNAME   OS_USERNAME          USERHOST       TRUNC(TIMESTAMP FAILED_LOGINS
---------- -------------------- -------------   --------------- -------------
NAVA       771690               LT095983           13-AUG-20     6
SQL>

 It seems account get timed locked as user NAVA used wrong password to connect from machine LT095983 .                                           

Comments

Post a Comment