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.

1 Answer 1


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
SQL> connect as a dba user
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
ORA-28002: the password will expire within 1 days
SQL> select account_status ,expiry_date, PROFILE, sysdate from user_users;
---------------- ------------------- -------------- -------------------
EXPIRED(GRACE)   03/21/2017 14:56:00 TEST_PROFILE   03/20/2017 14:56:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.