Oracle: After password expiry, need to reset password

Here is the summary:



If the account status already “expired” or “exired(grace)”, updating profile will not change account_status and expiry date.

If the account status is open (means not expired), updating profile will change the expiry date for users as well.



After user changing password, it will use new profile.



The difference between expired and expired(grace) is expired(grace) allow user to change the password after login.


SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';



Session altered.



SQL> create profile passwd_life_5_min limit password_life_time 5/1440; <- Create a profile to expire password in 5 minutes



Profile created.



SQL> create user donghua identified by donghua profile passwd_life_5_min; <- user Donghua has this profile



User created.





SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA';



CREATED EXPIRY_DATE ACCOUNT_STATUS

-------------------- -------------------- --------------------------------

2011-MAR-30 18:49:54 2011-MAR-30 18:54:54 OPEN



SQL> create user donghua1 identified by donghua profile passwd_life_5_min;



User created.



SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA1';



CREATED EXPIRY_DATE ACCOUNT_STATUS

-------------------- -------------------- --------------------------------

2011-MAR-30 18:52:49 2011-MAR-30 18:57:49 OPEN



SQL> select sysdate from dual;



SYSDATE

--------------------

2011-MAR-30 18:52:59



SQL> select sysdate from dual;



SYSDATE

--------------------

2011-MAR-30 18:56:02







SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA';



CREATED EXPIRY_DATE ACCOUNT_STATUS

-------------------- -------------------- --------------------------------

2011-MAR-30 18:49:54 2011-APR-06 18:56:18 EXPIRED(GRACE)



SQL> alter profile passwd_life_5_min limit password_life_time 180; <- change password policy to 180 days



Profile altered.



SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA';



CREATED EXPIRY_DATE ACCOUNT_STATUS

-------------------- -------------------- --------------------------------

2011-MAR-30 18:49:54 2011-APR-06 18:56:18 EXPIRED(GRACE) <- Since it’s already expired(grace), the expiry date not updated.



SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA1';



CREATED EXPIRY_DATE ACCOUNT_STATUS

-------------------- -------------------- --------------------------------

2011-MAR-30 18:52:49 2011-SEP-26 18:52:49 OPEN <- because this account ‘DONGHUA1’ still open, so the new profile changes apply.





Only Donghua’s password change, it will use the new profile:



SQL> alter user donghua identified by newpassword; <- change password here



User altered.



SQL> select created,expiry_date,account_status from dba_users where username='DONGHUA';



CREATED EXPIRY_DATE ACCOUNT_STATUS

-------------------- -------------------- --------------------------------

2011-MAR-30 18:49:54 2011-SEP-26 19:02:54 OPEN