X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • SQL |
    March 29, 2018

How to Fix ORA-28002 The Password Will Expire in 7 Days Errors

Chris Saxon
Developer Advocate

The weekend is over. You’re back at work. You login to the database to start coding.

Only to be hit with:

ORA-28002: the password will expire in 7 days

Gaaaaaahh!

Not again. It’s time for the bi-annual trip to the DBA to get them to change the password.

It’d be kinda nice to avoid this and use the same password forever.

But how?

Database User Profiles

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 stop this and allow users to keep their password forever, alter the profile like so:

alter profile "DEFAULT" limit 
  password_life_time unlimited;

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

create profile unlimited_pwd_prof limit
  password_life_time unlimited;

alter user <username> profile unlimited_pwd_prof;

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:

alter profile unlimited_pwd_prof limit  
  password_grace_time 14;

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 do so, run:

alter user <username> identified by <password>;

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:

alter profile unlimited_pwd_prof limit  
  password_reuse_max 10 
  password_reuse_time 365;

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:

create or replace function verify_password_length (
  username varchar2, password varchar2, old_password varchar2
) return boolean as
begin
  return ( length ( password ) >= 20 );
end verify_password_length;
/

grant execute on verify_password_length to public;
You can then assign it to an existing profile or create a new one like so:
create profile long_password limit 
  password_verify_function verify_password_length;

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:

alter user <username> password expire;

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.


How do you manage passwords in Oracle Database? Any other gotchas to be aware of? Let us know in the comments!

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services