How to fix ORA-28001: the password has expired ORA-28002 The Password Will Expire in 7 Days Errors

ORA-28002-The-Password-Will-Expire-in-7-DaysA new day started from ORA-28002 The Password Will Expire in 7 Days message from an Oracle DB.

Let’s dig a bit how can we avoid this situation and manage our Oracle DB passwords.

When you create a database user it has a profile. Which, by default, is the “DEFAULT” profile. This defines many things. Such as CPU, I/O and memory limits (though we recommend you use the Database Resource Manager for these instead).

And the password policy.

Which expires passwords every 180 days.

To find out what policy defines rules for a particular user run:

To stop this and allow users to keep their password forever, alter the profile like so:

Or create a new policy and assign that to your users:

Once you’ve done this you may think everything’s good. So you’re surprised when you login again and still hit the ORA-28002 error.

Or, if enough time has elapsed, you now see ORA-28001:

ORA-28001: the password has expired

What’s going on here?

The problem is you’ve entered the grace period. This starts after password_life_time days have elapsed since the last password change. By default it runs for seven days. If you want to increase this time to say, two weeks, run:

During this time you can still login, but will get “the password will expire” warnings. After the number of days defined in the password_grace_time have passed, the password expires.

The only way out of either situation is to reset the password!

To reset Oracle DB user password, run:

Note you can “change” the password back to itself. Which is generally considered a bad idea. Luckily you can force new passwords by setting the password reuse limit and days.

  • password_reuse_max – the number of new passwords you must use before you return to an earlier one
  • password_reuse_time – the number of days that must elapse before you can reuse a password

If you set both of these to a value other than unlimited, users must hit both criteria to change it. So to force at least ten new passwords and one year before you can repeat a password, update the profile like so:

While you’re sorting this out, you may want to ensure people choose “strong” passwords. Oracle Database 12.2 supplies some password complexity functions:

  • ora12c_verify_function (also in 12.1)
  • ora12c_strong_verify_function (also in 12.1)
  • ora12c_stig_verify_function
  • verify_function_11G (now deprecated)

You can enable these by setting the password_verify_function in the profile.

Or you can create your own complexity function. This must accept three parameters (username, password, and old_password) and return Boolean. You must also create this function in the sys schema. One of the rare cases where you should create anything in sys!

So to ensure that all passwords are at least twenty characters long, do the following:

You can then assign it to an existing profile or create a new one like so:

Note this only affects new passwords. If your database is full of users with weak passwords you need to reset them. If you want someone else to own the password for a user, you can force them to choose a new one by expiring the current:

And they’ll be faced with an ORA-28001 error when they next login!

But managing all these password settings is a hassle. And, chances are, you already have a central place to manage users and password policies.

Active Directory.

It’d be kinda nice to authenticate against that. Which you can. Using Oracle Internet Directory.

This sits between the database and AD, mapping users between the two.

But this is another thing to setup, manage and is generally a faff.

Luckily in Oracle Database 18c this is a lot easier.

You can cut out the middleman and map database users directly to Active Directory users!

This allows you to use AD as your central user management system. So you have one place to define password policies such as how many failed logins you allow.

Want me to do this for you? Drop me a line: itgalaxyzzz {at} gmail [dot] com