Oracle Database Password Expiry

Smart Panda - DatabaseI 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.