It’s a good practice to change passwords on the regular basis. Although, in certain circumstances it may cause some inconvenience.

ORA-28001 may surprise users of various software. Applications such as a WordPress, forums etc, or older enterprise software use application specific username and password to connect to a database. In the worst case, the application will suppress the original “ORA-28001: the password has expired” error. Instead, it will display some generic message like “unable to connect to database’. It may take time to figure out what did really happen.

The most annoying part is when the original application password was lost. And there is no easy way of recovering it from the application. However, there is a possibility to reset the password to the old value without knowing it. Plus, one should make changes to prevent Oracle password from expiring again in future.

How to recover from ORA-28001 assuming we don’t know the original password?

First, identify users with expired passwords. Connect to the database as SYSDBA and execute the following statement.

SELECT username, account_status, expiry_date
FROM dba_users
WHERE account_status != 'OPEN'
ORDER BY 3 DESC;

This will produce an output of all expired and/or locked accounts by expiry date in a descending order.

USERNAME                ACCOUNT_STATUS   EXPIRY_DATE
----------------------- ---------------- -------------------
APPUSER                 EXPIRED          29-NOV-13

Identify the most likely schema (username) of interest. In this example it’s APPUSER schema.
Now we have to retrieve the hash value of the original password. Issue the following statement:

SELECT name, password
FROM sys.user$
WHERE name = 'APPUSER';

The result should be similar to:

NAME                           PASSWORD
------------------------------ ------------------------------
APPUSER                        FE214E4F8379067B

To reset the password, use the following command:

ALTER USER APPUSER IDENTIFIED BY VALUES 'FE214E4F8379067B';

VALUES keyword indicates that the specified password is a hash value.

And finally verify the result using:

SELECT username, account_status
FROM dba_users
WHERE username = 'APPUSER';

The result:

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APPUSER                        OPEN

“OPEN” indicates that password was successfully reset. The application should now be working as expected. In some cases, a restart of the application server may be required.

Preventing ORA-28001

How to prevent oracle password from expiring? Oracle uses user profiles to define certain user specific rules. It includes password complexity and life time. The DEFAULT profile is assigned to most users. It enforces a 180 + 7 (grace) days password life time. After that point, user is required to change password.

To prevent password expiry, one may change the appropriate profile setting. I would not recommend changing the original DEFAULT profile. It’s better to create a new one with unlimited expiry setting. Assign the new profile to users that do not require regular password change. Note that if the password is already expired changing profile setting will not revert the account status to OPEN.
I’ll create a new profile with the name NEW_PROFILE. However, you may choose any name you like.

It’s possible to perform all operations using GUI tools. I will show a manual procedure using SQLPLUS.

First, list profiles and their password life time setting:

SELECT profile, limit
FROM dba_profiles
WHERE resource_name = 'PASSWORD_LIFE_TIME';

You will get a list that may look like

PROFILE                        LIMIT
------------------------------ ----------------------------------------
DEFAULT                        180
MONITORING_PROFILE             DEFAULT

You have the option to alter existing profile (fast but not recommended):

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;

It is better to create a custom profile. It may be a copy of the DEFAULT.

1. Get the existing configuration for the DEFAULT user profile:

set pages 20
SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT';

2. Copy the result into a text editor
3. Replace PASSWORD_LIFE_TIME value of 180 to UNLIMITED
4. Remove the header and replace it with CREATE PROFILE name LIMIT statement.
5. Add semicolon at the end.

The result should look like:

CREATE PROFILE NEW_PROFILE LIMIT
COMPOSITE_LIMIT                  UNLIMITED
SESSIONS_PER_USER                UNLIMITED
CPU_PER_SESSION                  UNLIMITED
CPU_PER_CALL                     UNLIMITED
LOGICAL_READS_PER_SESSION        UNLIMITED
LOGICAL_READS_PER_CALL           UNLIMITED
IDLE_TIME                        UNLIMITED
CONNECT_TIME                     UNLIMITED
PRIVATE_SGA                      UNLIMITED
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7;

Oracle Database SQL Reference on CREATE PROFILE

To list users and their assigned profiles and whether or not their password has expired issue:

select username, profile, account_status from dba_users ORDER BY 1;

To assign the new profile to a use use:

ALTER USER username PROFILE NEW_PROFILE;

[fb_button]