:

ORA-28002: the password will expire within 7 days

ORA-28002 Password Will Expire

The Problem

I was working on a project to upgrade our databases from 10g to 11g so we rolled it to one of our TEST environments in order to identify any problems. After some time using it, we came across an ORA-28002 error message. The following error is the full error shown to the user:

ERROR: ORA-28002: the password will expire within 7 days

The Cause

In Oracle 11g, by default, there are a few changes to security and password management. This is one of them. The default profile now has a more strict password life time and grace time than before. If you have a look at the view DBA_PROFILES you can find out the current value for the password_grace_time and password_life_time, among others.

The Solution

ALTER PROFILE DEFAULT LIMIT password_grace_time UNLIMITED;
ALTER PROFILE DEFAULT LIMIT password_life_time UNLIMITED;

There may be other profiles whose parameters you would like to change, but the above statement changes on the “DEFAULT” profile. You can change the a different profile by substituting the profile_name into the above statements where it currently says DEFAULT.

You can use the following query to find out what other profile settings are in place:

SELECT *
FROM dba_profiles
WHERE profile = 'DEFAULT';

Other profile settings of interested which are related to the ORA-28002 password expiring error message are:

  • FAILED_LOGIN_ATTEMPTS – The maximum number of attempts which a user can make before an account is locked
  • PASSWORD_LIFE_TIME – The length of time that one password can be in use before it has to be changed
  • PASSWORD_REUSE_TIME – The length of time that has to pass before a password can be reused
  • PASSWORD_REUSE_MAX- The number of times a password can be reused as a password
  • PASSWORD_VERIFY_FUNCTION – A user defined function to enforce password rules, such as complexity
  • PASSWORD_LOCK_TIME – Specifies in days how long the account will remain locked. You can specify smaller time periods, for example 30 minutes is 0.0208 days (30mins/1440mins in a day)
  • PASSWORD_GRACE_TIME – How long a user has to change their password after it has hit a threshold to be changed

Hopefully this has helped you to resolve this letter ORA-28002 error message. There is a similar error, the ORA-28001: the password has expired error which I have also written another article about so check that out as they are very closely related.

Like it, share it...

Category: 11g


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *