A 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:
|
1 |
select profile from DBA_USERS where username = '<username>'; |
To stop this and allow users to keep their password forever, alter the profile like so:
|
1 2 |
alter profile "DEFAULT" limit password_life_time unlimited; |
Or create a new policy and assign that to your users:
|
1 2 3 4 |
create profile unlimited_pwd_prof limit password_life_time unlimited; alter user 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:
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:
|
1 2 |
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 reset Oracle DB user password, run:
|
1 |
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 onepassword_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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 |
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:
|
1 |
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.
