Resolved the User account locked(timed) issue

During changing the username and password in both application and database, we are facing issue of frequently account locked(timed).
Our account is locked frequently with error ORA-28000: the account is locked.

Cause: Our application is tried to connect with wrong password then its locked. The user profile reached to maximum number of times specified by the user’s profile parameter FAILED_LOGIN_ATTEMPTS.

Note:
1.
The PASSWORD_LOCK_TIME = value (example 1)
The table DBA_USERS.ACCOUNT_STATUS show value “LOCKED(TIMED)” whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS.
2.
The PASSWORD_LOCK_TIME = unlimited:
The table DBA_USERS.ACCOUNT_STATUS show value “LOCKED” whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS.

Solution
1. Check the User default profile

col username for a10
col account_status for a16
col profile for a20
select username,account_status,LOCK_DATE,profile from dba_users where username='HR';

USERNAME   ACCOUNT_STATUS   LOCK_DATE PROFILE
---------- ---------------- --------- --------------------
HR         OPEN                       DEFAULT

2. Check the setting of PASSWORD_LOCK_TIME and FAILED_LOGIN_ATTEMPTS in profile.

col resource_name for a23
col limit for a20
SELECT resource_name, limit FROM dba_profiles WHERE profile = 'DEFAULT' and resource_name in ('PASSWORD_LOCK_TIME','FAILED_LOGIN_ATTEMPTS');

RESOURCE_NAME           LIMIT
----------------------- --------------------
FAILED_LOGIN_ATTEMPTS   10
PASSWORD_LOCK_TIME      1

Note: By default this values are set.

3. you have two option first create new profile or change the DEFAULT Porfile.

-- I am going with changing the same profile.
alter profile default limit PASSWORD_LOCK_TIME unlimited;
alter profile default limit Failed_login_attempts unlimited;


4. Unlock the user account.

alter user HR account unlock;
User altered.


5. Hopefully, This will fixed your problem but if you want alter profile for only your user then you create new profile.

1. Let see other method of creating new profile with all unlimited limits

CREATE PROFILE USER_UNLIMITED LIMIT
COMPOSITE_LIMIT 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
FAILED_LOGIN_ATTEMPTS UNLIMITED;


2. Assign that profile to user.

Alter user HR profile USER_UNLIMITED;


3. Unlock the user account;


alter user HR account unlock;
User altered.