11g Password Expiry

Since 11gR1 Oracle have implemented good security practice by enforcing regular updating of passwords through a default password expiry of 180 days.  

11gR1 Parameters Used to Secure User Accounts 

11gR2 Parameters Used to Secure User Accounts 


As good as this sounds may spell a time bomb (sleeper) for some applications with hard coded passwords. For example Application Servers with no procedures in place for regular password changes. Some may not even deal with the grace period message warning of an impending lock out.

When logging into an account that has entered a GRACE period the following message is displayed

SQL> connect username/password
ERROR:
ORA-28002: the password will expire within 3 days

When logging into an account that has passed the EXPIRED period the following message is displayed

SQL> connect username
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for username
New password:
Retype new password:
Password changed
Connected.
SQL>

Finding user accounts that have expired or grace passwords:

select username, account_status, expiry_date from dba_users

USERNAME        ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ --------------------------------
USER1           EXPIRED                          11-JAN-12
USER2           EXPIRED(GRACE)                   01-FEB-12
USER3           EXPIRED(GRACE)                   02-FEB-12

The defaults are defined in the user's PROFILE. See these default LIMIT column values:

select * from dba_profiles where resource_name in ('PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME');

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

To change the default profile settings (not recommended):

alter profile default limit password_life_time unlimited;
alter profile default limit password_grace_time unlimited;

To un-expire an already expired where the password is not known in 11g+ use the following:

alter user identified by values ‘FD8C3D14F6B60015’;

As of 11g the DBA_USERS table no longer contains encrypted passwords. Use the SYS.USER$ table and the NAME, PASSWORD columns. 

select name, password, ltime from user$ order by name, exptime;

Verify the account status in the DBA_USERS table:

select username, account_status, expiry_date from dba_users where expiry_date is null;

USERNAME            ACCOUNT_STATUS                   EXPIRY_DATE
------------------- -------------------------------- -----------
USER1               OPEN
USER2               OPEN
USER3               OPEN