Oracle 11g enabling PASSWORD EXPIRE for DEFAULT profile

Yesterday I found out when I logged into Oracle Enterprise Manager and after bringing up my Database instance, that I had to change the password for the following users: SYS, SYSTEM, SYSMAN, DBSNMP and MGMT_VIEW because they were expired.

They all have in common that they belong to the DEFAULT profile.

Checking the relevant settings of the DEFAULT profile in my Oracle 11g Database instance I have running on Solaris 10 we have:


auyantepui% uname -a
SunOS auyantepui 5.10 Generic_139555-08 sun4u sparc SUNW,Sun-Blade-1000
auyantepui% cat /etc/release
Solaris 10 5/09 s10s_u7wos_08 SPARC
Copyright 2009 Sun Microsystems, Inc. All Rights Reserved.
Use is subject to license terms.
Assembled 30 March 2009
auyantepui% id
uid=100(oracle) gid=102(oinstall)
auyantepui% sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Nov 23 15:03:08 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> CONNECT / AS SYSDBA
Connected.
SQL> set linesize 120
SQL> SELECT * FROM dba_profiles
2 WHERE resource_type='PASSWORD'
3 AND profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

7 rows selected.

SQL>


So we can see the password for a user with the DEFAULT profile expires after 180 days.

This can be a nuisance for applications running in a J2EE Application Server where you can have a Data Source configured and residing in an Oracle 11g Database. This could also represent a problem if you have an Oracle 11g Database instance managed by the Sun Cluster 3.X Data Service Agent which does some regular probing to check for the health of the Database.

The way to get rid of this PASSWORD EXPIRE would be:


SQL> ALTER PROFILE default LIMIT
2 FAILED_LOGIN_ATTEMPTS unlimited
3 PASSWORD_LIFE_TIME unlimited;

Profile altered.

SQL> SELECT * FROM dba_profiles
2 WHERE resource_type='PASSWORD'
3 AND profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

7 rows selected.

SQL>


By setting PASSWORD_LIFE_TIME to UNLIMITED we solved this nuisance of having to change the password for SYS and SYSTEM every 180 days.

Also FAILED_LOGIN_ATTEMPTS has been set to UNLIMITED because you don't want to take any chances and minimize the risk of getting an application locked out.