users account locked.

IF your oracle user account get locked frequently here is the solution

In 11g onwards there is a small limitation of the password life time in default prfiles.
The users who use default profiles will get affected(locked) because of this.

In previous verions (<11g) is it by default set to unlimited, but in 11g onwards as a 
security measure its value limited to 180 days. Users a/c will lock when they reach 180
days after when they created. 

we can change the value as we want.


To find user and his profile
SELECT username, account_status,profile FROM dba_users WHERE username= 'APPUSR';

To see the profile contents/limitaions
SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';



All resource limits for DEFAULT profile is set to UNLIMITED,
but only for FAILED_LOGIN_ATTEPTS 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_ATTEPTS 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_ATTEPTS attribute in DEFAULT profile,
or 
create a new profile for that user with FAILED_LOGIN_ATTEPTS 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.

Creating a profile.


SQL>CREATE PROFILE APPUSR_DEFAULT LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
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.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

User altered.


later we can chenge the values like

alter profile appusr_default set failed_login_attempts = 4;


Some parameters & their descriptions
===========================
failed_login_attempts -  This is the number of failed login attempts before locking the Oracle user account.
                                 The default is three failed attempts.

password_grace_time - This is the grace period after the password_life_time limit is exceeded. 


password_life_time -    This is how long an existing password is valid. The default here forces a password        
                                    change every 60 days.


password_lock_time –  This specifies how long to lock the account after the failed login attempts is met.
                                   Most DBA’s set this value to UNLIMITED.

password_verify_function - This allows you to specify the name of a custom password verification function.
                                      
Most confusing parameters are..

password_reuse_time  &  password_reuse_max

These two parameters must be set in conjunction with each other.
PASSWORD_REUSE_TIME :specifies the number of days before which a password cannot be reused.
PASSWORD_REUSE_MAX :specifies the number of password changes required before the current   
                                                 password can be reused.

If you specify an integer for both of these parameters,

then the user cannot reuse a password until the password has been changed the password 
the number of times specified for PASSWORD_REUSE_MAX 
during the number of days specified for PASSWORD_REUSE_TIME.

For example, if you specify

PASSWORD_REUSE_TIME       to 30
and PASSWORD_REUSE_MAX to 10,

then the user can reuse the password after 30 days if the password has already been changed 10 times.

**************