How to Turn off / Disable User Account Expiration

Oracle User Account Expired. How to disable or turn off EXPIRE
===========================================


Issue:

One of the critical User account got expired, expire to be 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-OCT-11 09-APR-11 DEFAULT


2. Confirmed with profile settings that 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-APR-11 18-OCT-11 DEFAULT


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


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 UNLIMITED

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

16 rows selected.


6. Tried resetting the profile again to user, but no luck... :-(


SQL> alter user TEST profile default;

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 EXPIRED(GRACE) 09-APR-11 18-OCT-11 DEFAULT


7. Tried setting account unlock...but still no luck....

SQL> alter user TEST account unlock;

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 EXPIRED(GRACE) 09-APR-11 18-OCT-11 DEFAULT


SOLUTION:


8. Then found that, when user is already in GRACE Period, even altering profile, will NOT take effect until the password is changed

Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low Value

Be careful if you plan to set the PASSWORD_LIFE_TIME parameter of CREATE PROFILE or ALTER PROFILE to a low value (for example, 1 day). If the user who is assigned this profile is concurrently logged in when you make this modification, then Oracle Database sets the user's account status from OPEN to EXPIRED(GRACE)with the warning error ORA-28002: the password will expire within n days. You may not be notified of this change because the user can still connect to the Oracle database. You can find the concurrently logged in users by querying the USERNAME column of the V$SESSION dynamic performance view.

Note the following:

If the user is not logged in when you set PASSWORD_LIFE_TIME to a low value, then the user's account status does not change when the user does log in.

You can set the PASSWORD_LIFE_TIME parameter to UNLIMITED, but this only affects accounts that have not entered their grace period. If the user has already entered the grace period, then he or she must change the password.


Ref: http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#i1007339



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-APR-11 DEFAULT