Default Profile Locks Users Accounts?

For default since orcale 11 lot of UNLIMITED configurations of profile have change. Beyond version 10 FAILED_LOGIN_ATTEMPTS is set 10 as default value. In Oracle 11g onwards for DEFAULT profile PASSWORD_LOCK_TIME,PASSWORD_GRACE_TIME,PASSWORD_LIFE_TIME parameters are limited. Maybe you have suffered from and aplication that often  lock user account or maybe new instalation or upgrade make some users account locked over time.


SQL> SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES
WHERE PROFILE=’DEFAULT’ AND RESOURCE_NAME IN (‘FAILED_LOGIN_ATTEMPTS’,’PASSWORD_LOCK_TIME’);

RESOURCE_NAME LIMIT
——————————– —————————————-
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
PASSWORD_LIFE_TIME 180
FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME, these two parameters makes the account status to LOCKED(TIMED) when you try with wrong passwords.

To avoid this problem is easy as create a new profile with FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME values as UNLIMITED  and assign this new profile to the user. It is not recommended to change the resource values to the DEFAULT profile.

Create a new profile:

CREATE PROFILE default_2 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;

I write this post because is the most common small problem in new 11g/12c installations and upgrades, no to do something
with the password life time of 180 days in default profile, your users / schemas will cease to work after half an year from creation.

for solve this little issue easy:

SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;

check:
select profile, limit from dba_profiles where resource_name = ‘PASSWORD_LIFE_TIME’;

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s