Oracle 12 set user password Expiration date or EXPIRED(Grace)

oraclepassword

Is it possible to set a future expiration date on a user account in Oracle? Or to set a account status to EXPIRE(GRACE)

We want to implement a more strict password policy, and I've created the new Profile, but would like to use Oracle's password grace period function to warn users (instead of just running a script to immediately expire the user accounts).

(In short, yes I can and will send out an email telling everyone their password will expire in 10 days, but I'd rather have Oracle tell them when they log in so they can't deny seeing the warning)

I don't see an option for ALTER USER... for expiration dates.

Also when I added some users to the new profile (which has a 90 day Password Life Time and a 30 day Password Grace Time for some reason it automatically set their expiration date to 51 days and 6 hours from now, which I thought was rather odd.

Best Answer

You have to use a profile and a combination of password_life_time and password_grace_time; Lifetime will set the expiry time of password and grace_time will warn them (after expiry) as you'd expect. All you then need to do is assign the profile to user or users (using alter user).

A quick demo? 

SQL> set echo on
SQL> drop user testa cascade;
User TESTA dropped.
SQL> create user testa identified by testa;
User TESTA created.
SQL> grant create session to testa;
Grant succeeded.
SQL> prompt this connection should be successful as there is no profile attached.
this connection should be successful as there is no profile attached.
SQL> conn testa/testa
Connected.
SQL> connect as a dba user
Connected.
SQL> drop profile test_profile;
Profile TEST_PROFILE dropped.
SQL> -- using profile i am setting password to expire in about 10 seconds useful for our testing 
SQL> -- 0.0001158 is about 10 (10/86400)
SQL> -- grace time is set to 1 day
SQL> create profile test_profile limit password_life_time 0.0001158 password_grace_time 1;
Profile TEST_PROFILE created.
SQL> -- now you assign the profile to the user who should get warning .
SQL> alter user testa profile test_profile;
User TESTA altered.
SQL> exec dbms_lock.sleep(20);
PL/SQL procedure successfully completed.
SQL> prompt this connection should show a warning 
this connection should show a warning
SQL> conn testa/testa
ERROR:
ORA-28002: the password will expire within 1 days
Connected.
SQL> select account_status ,expiry_date, PROFILE, sysdate from user_users;
ACCOUNT_STATUS   EXPIRY_DATE         PROFILE        SYSDATE            
---------------- ------------------- -------------- -------------------
EXPIRED(GRACE)   03/21/2017 14:56:00 TEST_PROFILE   03/20/2017 14:56:00
Related Question