prifile

 

Stop a users password from expiring.

Posted on June 9, 2010 by admin

In 11g, a profile password expires by default at 180 days. This can be a pain, and the means to stop this is to create another profile with unlimited password lifetime, then assign it to the user.


SQL> select profile from dba_users where username = ‘APPADMIN’;

PROFILE
——————————
DEFAULT

SQL> select resource_name, limit from dba_profiles where profile = ‘DEFAULT’;

RESOURCE_NAME 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 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

16 rows selected.

So, the limit on the password is 180 days here, after which it will expire and stop logins on the account.

select txt from (select 0 pos, ‘create profile appprofile limit’ txt from dual
union
select rownum pos, resource_name||’ ‘||limit txt from dba_profiles where profile = ‘DEFAULT’ and limit ‘UNLIMITED’
union
select 999 pos, ‘/’ txt from dual)
order by pos;

TXT
————————————————————————-
create profile appprofile limit
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
/

7 rows selected.

Change any limits you require and add others if needed. Then create the profile and assign it to the user.

SQL> create profile appprofile limit
2 FAILED_LOGIN_ATTEMPTS 10
3 PASSWORD_LIFE_TIME UNLIMITED
4 PASSWORD_VERIFY_FUNCTION NULL
5 PASSWORD_LOCK_TIME 1
6 PASSWORD_GRACE_TIME 7
7 /

Profile created.

SQL> alter user appadmin profile appprofile;

User altered.