Locked Account

Every administrator in the working life would have definitely got request for Locked Account, usually it’s straight forward to unlock an account in an oracle database but one may ask for the reason of this account getting locked in the first instance itself.

Reasons of Accounts getting locked

Manually locking the account using alter user <username> account lock. This can be done in cases where companies have derived custom logic to suffice the requirement of restricting the access due to known reason.
Automatic locking when FAILED_LOGIN_ATTEMPTS value assigned to user profile has been breached. This will lock the account based on PASSWORD_LOCK_TIME value and unlock the account once this tenure is completed. For example with FAILED_LOGIN_ATTEMPTS value of 10 set on the user profile will lock the account after 10 invalid attempts for 1 hour when PASSWORD_LOCK_TIME value is set to 1 hour in user profile. This will show status as LOCKED(TIMED) in account_status column in dba_users.
Automatic locked when INACTIVE_ACCOUNT_TIME value assigned to user profile has been breached due to inactivity of this account on the database. This will show status as LOCKED in account_status column in dba_users. Many time administrators is not aware of this setting and get confused with invalid login attempts.

Debug Account lock Issues

By now we already know database account locking can happen due to multiple reasons and we need to find the exact reason using following sequence:

Check the status of database account from dictionary views and see whether it’s LOCKED or LOCKED(TIMED). Status LOCKED means it’s either locked manually or breach of INACTIVE_ACCOUNT_TIME.

Select username,account_status from dba_users where username='SCOTT';

For example if the status of above command comes out to be LOCKED which means either it’s locked manually or due to INACTIVE_ACCOUNT_TIME. Next we should check last_login date and see it breaches the INACTIVE_ACCOUNT_TIME.

select username,last_login from dba_users where username='SCOTT';

When last_login > than INACTIVE_ACCOUNT_TIME value, account is locked due to inactivity. In a case when last_login < than INACTIVE_ACCOUNT_TIME it could be manual lock and we should then see the audit trail for this action. In this case we assume unified audit trail is being enabled on your database:

col action_name format A16
col policy_name format A18
col system_privilege_used format A20
col EVENT_TIMESTAMP format A30
col DBUSERNAME format A30
select DBUSERNAME,ACTION_NAME,SYSTEM_PRIVILEGE_USED,EVENT_TIMESTAMP,SQL_TEXT,RETURN_CODE,AUDIT_TYPE,AUDIT_OPTION FROM unified_audit_trail
where upper(DBUSERNAME)='SCOTT' and return_code !=0 and EVENT_TIMESTAMP>sysdate-2 order by EVENT_TIMESTAMP;

Note: This will list down all the activities done by user SCOTT logged in unified audit trail in last two days where return code is not 0. We can also provide specific return code to list only failure values.

For cases where where it’s LOCKED(TIMED) we can again see the unified audit trail for failed login attempt details. This can also be done by enabling the event on the database layer.

alter system set events '1017 trace name errorstack level 10';

This will generate traces whenever there is failed login attempts

O/S info: user: userx, term: pts/1, ospid: ****, machine: *****
program: sqlplus@**** (TNS V1-V3)
application name: sqlplus@**** (TNS V1-V3), hash value=0
last wait for 'SQL*NET message from client' blocking sess=0x0 seq=2 wait_time
=5570 seconds since wait started=0

For any other reason which can’t be diagnosed kindly leave your comments.