Users are identified within the app, so all DB connections are done through 1 schema. We created a profile for this schema where the password will never expire. App support has a manual process to change the password every 60 days, but Oracle must never be allowed to expire the password automatically.
The problem is that I found the ACCOUNT_STATUS for the main schema to be EXPIRED(GRACE) and that the EXPIRY_DATE is 3 days away.
This schemas profile was changed by accident, and rectified within a few hours. But the damage was done the ACCOUNT_STATUS still showed EXPIRED(GRACE).
I found an excellent blog post by Robert Geier (http://blog.contractoracle.com/2009/11/analysis-of-oracle-password-expiry.html) to explain the situation. Here’s a abbreviation of his explanation: password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, and is reset by a password change. Changing the profile will not change aud$.ASTATUS.
SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = ‘QWERTY’;
USERNAME PROFILE ACCOUNT_STATUS
------------------ ------------------ --------------------------------
QWERTY QWERTY_NONEXPIRY EXPIRED(GRACE)
So even though the profile was changed back to QWERTY_NONEXPIRY the ACCOUNT_STATUS was still EXPIRED(GRACE). The only way to reset the ACCOUNT_STATUS back to OPEN is to change the password, because only a password change will reset aud$.ASTATUS. But a password change will give a ORA-28007 because of the current profile limits for PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME gets violated.
SQL> select password from sys.user$ where name='QWERTY';
PASSWORD
-----------------------------
87FE2DD49B2882B2
SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';
alter user QWERTY identified by values '87FE2DD49B2882B2'
*
ERROR at line 1:
ORA-28007: the password cannot be reused
So I had to create a new temporary profile with the required settings to allow me to change the password to be the same as the current password:
SQL> create profile tmtmp limit PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME UNLIMITED;
SQL> alter user QWERTY profile tmtmp;
SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';
User altered.
SQL> alter user QWERTY profile QWERTY_NONEXPIRY;
SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = 'QWERTY';
USERNAME PROFILE ACCOUNT_STATUS
------------------ ------------------ --------------------------------
QWERTY QWERTY_NONEXPIRY OPEN
