Friday, March 02, 2012

How to Turn off / Disable User Account Expiration


One of the DB User account got expired and needed to turned off to avoid connection issue on application connectivity

Issue Details:

1. Found User account is about to expire and it is on GRACE Period
SQL> select username,account_status,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username='TEST';

USERNAME      ACCOUNT_STATUS          LOCK_DATE EXPIRY_DATE      CREATED PROFILE
TEST               EXPIRED(GRACE)         18-FEB-12  09-MAR-12           DEFAULT

 2. Confirmed with profile settings that DEFAULT stands for 180 expiration  
SQL> select * from dba_profiles where profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

3. To disable/Turn off the expiration, we need to set profile to UNLIMITED:
SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
Profile altered.

4. While Checking the status, it still shows Expiry date as shown earlier, so, what could be cause or reason???
SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST';

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
TEST        EXPIRED(GRACE) 09-JAN-12 18-FEB-12 DEFAULT


Profile shows UNLIMITED, but why user account did not get effect???

Solution:

When user is already in GRACE Period, even altering profile, will NOT take effect until the password is changed.

SQL> alter user TEST identified by test;
User altered.

SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST'

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
TEST        OPEN                     09-JAN-12               DEFAULT

No comments: