WARNING: Can not connect DB with URL jdbc:oracle:thin:@//db:1521/ORCLPDB
java.sql.SQLException: ORA-28001: the password has expired
You have provisioned a new SOA instance and everything was working fine, but one day the server crashes and even if you try to start it won’t. The Admin server log file is filled with ORA-28001 errors.
Security is an essential aspect of any server installation and in this scenario, Oracle product has been installed based on the default security standards where the password should expire every 90 days.
Let’s try to fix this problem from a Tactical and Strategic point-of-view.
Short term quick fix – reset the password to the original value and restart the WebLogic server.
When you are in a hurry to make things operational again because your whole production processing is impacted this option will help you, provided the current DB user PROFILE doesn’t restricts it.
Step 1) Login to the Oracle DB as sysdba user.
Step 2) Run the query below to identify the users for which passwords have expired.
select username from dba_users where account_status='EXPIRED' and username like 'DEV%';
Step 3) Generate the reset password SQL.
select 'ALTER USER ' || username ||' identified BY Your_Password ;' from dba_users where account_status='EXPIRED' and username like 'DEV%';
Step 4) Reset the password back to the old and restart the Weblogic server.
Incase the DB user PROFILE does not allows you to reset back to old password, there are two options:
- Create a custom PROFILE which allows you to reset password to the old one and attach that policy to the DB user.
- Update the password to a new value, and update the datasources with the new password.
Long term approach – Configure the database to align with your organisation security policy.
Example if the organisation security policy mandates the service account password should expire every 180 days.
CREATE PROFILE "ORG_DB_CUSTOM_PROFILE" LIMIT PASSWORD_LIFE_TIME 180
Once the profile has been created, we can attach the profile to the DB user
ALTER USER DEV_MDS PROFILE ORG_DB_CUSTOM_PROFILE;
There are several parameters that can be tweaked to meet your organisations password policy.
Example 1: Do not let the user to reuse the old password for 90 days.
ALTER PROFILE DB_CUSTOM_PROFILE LIMIT PASSWORD_REUSE_TIME 90 PASSWORD_REUSE_MAX UNLIMITED;
Example 2: Lock the user account for 1 day after 3 consecutive failed attempts
ALTER PROFILE DB_CUSTOM_PROFILE LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
Example 3: Expire password every 90 days, with a grace period of 5 days
ALTER PROFILE DB_CUSTOM_PROFILE LIMIT PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 5;