Friday, April 1, 2011

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

13 comments:

  1. You made your every effort to recover the password but none is doing its bid. Now you have to do it on your own.reset windows 10 password

    ReplyDelete
  2. I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
    Cyber Security Projects for Final Year

    JavaScript Training in Chennai

    Project Centers in Chennai

    JavaScript Training in Chennai

    ReplyDelete