I have a little test site setup for Oracle PeopleSoft and I ran into an issue the other day where my sysadm account was expired. I typically have never ran across Oracle Database expiring my administrative user accounts. A little google search later and I found Amit Rath’s blog on fixing this problem. It turned out that I had a default profile that had a time limit of 180 days for the password.
SQL> select profile from dba_users;
All of my system administrative users had the profile: DEFAULT
SQL> select * from dba_profiles where profile=’DEFAULT’ and RESOURCE_NAME=’PASSWORD_LIFE_TIME’;
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
————————- —————————— ———————— ——————————
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
The password will expire every 180 days, so to change that to unlimited:
SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited;
Profile altered.
SQL> select * from dba_profiles where profile=’PROFILE’ and RESOURCE_NAME=’PASSWORD_LIFE_TIME’;
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
————————- —————————— ———————— ——————————
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
I had to actually reset my users in order to get the account opened
SQL> ALTER USER sysadm IDENTIFIED BY PassW0rd;
SQL> ALTER USER people IDENTIFIED BY PassW0rd;
SQL>select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from dba_users;
Check that the account in OPEN and the EXPIRY_DATE is NULL:
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
—————————— ————————- —————— ————————-
SYSADM OPEN DEFAULT
people OPEN DEFAULT
This solved my issue. Not sure if I missed a script on the initial setup or if this is a new function within the Oracle Database but it will cause an issue with the access profile system user account is unable to log into the database.