It is the second time that I run into this problem. Therefore, it is time to write a quick note before I struggle a third time. At some point adding a new secure targets no longer works and breaks with an OAV-46599.

Initially I was a bit confused about the error. Because there haven’t been any changes on the system since the last secure target has been added. But reading the whole error message above gives the correct indication of the root cause. It’s nothing else than an ORA-28001 the password has expired. Lets see which user has an expired password.

SQL> alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';

Session altered.

SQL> set linesize 160 pagesize 200
SQL> select username,account_status,expiry_date from dba_users where account_status='EXPIRED';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ -------------------------------- -------------------
ANONYMOUS                      EXPIRED                          17.09.2011 10:21:08
AVREPORTUSER                   EXPIRED                          17.07.2013 21:25:55

It looks like the account AVREPORTUSER is expired. Because I do not know the password yet know whether it was stored somewhere, I’ll just reset the old password. For this I need both password hash’s.

SQL> col name for a15
SQL> col password for a17
SQL> col spare4 for a65
SQL> select name,password,spare4 from user$ where name='AVREPORTUSER';

NAME            PASSWORD          SPARE4
--------------- ----------------- -----------------------------------------------------------------
AVREPORTUSER    F315BBCEBB3F78E7  S:14155D035FEBAB05790EAB47CCC4ACDBD8B728C373EECDABE6EB5FAA9D03

With alter user identified by values I’m able to specify both the 10g and the 11g password hash to reset the password to the same value.

 alter user AVREPORTUSER identified by values 'S:14155D035FEBAB05790EAB47CCC4ACDBD8B728C373EECDABE6EB5FAA9D03;F315BBCEBB3F78E7';

As you can see in DBA_USERS the account has now status open again. Adding secure targets does work again.

SQL> select username,account_status,expiry_date,password_versions from dba_users where username='AVREPORTUSER';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         PASSWORD
------------------------------ -------------------------------- ------------------- --------
AVREPORTUSER                   OPEN                             11.02.2014 06:39:02 10G 11G

But how has this account become expired? The reason is quite obviously. All AV user do have the Oracle DEFAULT profile which has a limited password life time of 180 days. Therefore, the accounts expire after 180 days. And yes my AVDF test system was set up about 180 days ago. 🙂

SQL> select username,profile from dba_users where username='AVREPORTUSER';

USERNAME                       PROFILE
------------------------------ ------------------------------
AVREPORTUSER                   DEFAULT

SQL> select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180

An Oracle Bug has been opened for this issue. BUG 17078860 Relax The profile settings in the AV server’s database