Expire User Password
In this post, we introduce two ways to expire an user's password in N days. The first one provides no grace time, the other provides grace time for users to consider.
Alter User Password Expire without Grace Time
"Now" means "Effective immediately". In some urgent security incident, you may intend to expire users' passwords immediately. Which means that we provide no grace time for them, they must change their own password as soon as they login to the database.
We use ALTER USER PASSWORD EXPIRE statement to expire one's password immediately.
SQL> alter user hr password expire;
User altered.
SQL> alter user oe password expire;
User altered.
SQL> alter user sh password expire;
User altered.
Affected users will receive ORA-28001: the password has expired when they login to the database. They have to change their own password on the spot, otherwise further actions can go nowhere.
Alter User Password Expire with Grace Time
For databases having many users, we should expire their passwords in a more deliberate manner for reducing complains and operational issues. The manner we recommend is using grace time to push them to change their own password gradually.
Please note that, the way we expire all user password to the same expiration date is unsupported by Oracle, you should evaluate the risk before adopting it.
In this case, we provide 3 days of grace time for users to change their own password, they can take their time to think over the new password during the grace time. The parameter of grace time set in profile is also known as PASSWORD_GRACE_TIME.
Change PASSWORD_GRACE_TIME
The step is optional. If the value of PASSWORD_GRACE_TIME in profile is already a specific number, say 7 days, then you can skip this step.
Here we change the value of PASSWORD_GRACE_TIME of their profile into 3 days.
SQL> show user
USER is "SYS"
SQL> select username, profile from dba_users where username in ('HR', 'OE', 'SH') order by 1, 2;
USERNAME PROFILE
---------- ----------
HR APP_USER
OE APP_USER
SH APP_USER
SQL> alter profile app_user limit password_grace_time 3;
Profile created.
Please remember the original value of PASSWORD_GRACE_TIME before issuing ALTER PROFILE statement in order to change it back later.
Set Expiration Date
This step is the key of whole procedure. We set users' expiration time to 3 days later from now by directly updated EXPTIME in table SYS.USER$.
SQL> update user$ set exptime = sysdate + 3, ltime = null, lcount = 0, astatus = 18, spare6 = systimestamp at time zone 'UTC' where name in ('HR', 'OE', 'SH');
3 rows updated.
SQL> commit;
Commit complete.
Don't forget to commit your change. After that, users see a warning message when they login to the database:
C:\Users\scott>sqlplus hr/hr@orclpdb
...
ERROR:
ORA-28002: the password will expire within 3 days
ORA-28002 was telling users that their password will be soon expired in 3 days.
After 3 days, the grace time has run out, they saw a different error message:
C:\Users\scott>sqlplus hr/hr@orclpdb
...
ERROR:
ORA-28001: the password has expired
Changing password for hr
New password:
ORA-28001 was telling users that their password has been expired, they should change password immediately.
Change PASSWORD_GRACE_TIME Back
If you skipped step 1, then you can also skip this step.
After 3 days, we can change the PASSWORD_GRACE_TIME back to the original limit. For example:
SQL> alter profile app_user limit password_grace_time unlimited;
Profile altered.
We've done our job!