One error that shows its head a lot and always seems to confuse a lot of people is the “ORA-28001 The Password Has Expired” error.
Starting with Oracle database 10g, if you do a default database installation then all passwords will default to expiring within 180 days. So what a lot of people find is that they install the Oracle database, configure their application to connect to it, everything is fine for six months and then suddenly the application stops working because the account it uses can no longer connect to the database.
How To Turn Off Password Expiration
Of course, having a robust security policy in place is always a good idea but you may not be too worried about setting passwords to expire in development or test environments. If that is the case it is a simple task to turn off the password expiration. The password expiry policy for each account is inherited from that account’s profile. You can find the account’s profile using the following SQL statement (obviously you should substitute the actual username in the Where clause):
select profile from dba_users where username = ‘USERNAME’;
You can check the profile’s password expiration policy using the following SQL statement (again, you should substitute the actual profile returned from the above SQL statement in the Where clause below):
select password_life_time from dba_profiles where profile = ‘PROFILE’;
If the password_life_time is set to anything other than “UNLIMITED’, then you have a password expiration policy set. To turn it off, use the following SQL statement:
alter profile PROFILE limit password_life_time unlimited;
If You Have Already Hit “The ORA-28001 The Password Has Expired” Error
If you have already hit the “ORA-28001 The Password Has Expired” error, then you may have user accounts that have expired. To check the status of user accounts in the database you can execute the following SQL statement:
select username, account_status, lock_date, expiry_date from dba_users order by username;
If users show up with an account_status of “EXPIRED(GRACE)”, then this means their password has expired but they are still in the grace period where they can reset it themselves as their account is not yet locked.
If the account_status is anything other than this, apart from “OPEN”, then the account has been locked and must be unlocked and the password changed. You can unlock the account using the SQL command: alter user USERNAME account unlock; Then you can reset the password as follows: alter user USERNAME identified by NEW_PASSWORD;