DBA: DC City Stories: Oracle 11g Password Expiry

Don't be caught out with 11g's new DEFAULT profile password policy. The 11G DEFAULT profile has the following:

• 6 month password expiry
• 10 failed login attempts before lockout

Both will cause havoc when they're service accounts.


To unlock an account:


ALTER USER SCOTT ACCOUNT UNLOCK;

To find the profile settings:

col profile for a20
col resource_name for a25
col limit for a15
select profile,resource_name,limit
from dba_profiles
where resource_type='PASSWORD'
order by profile;

PROFILE    RESOURCE_NAME             LIMIT

---------- ------------------------- ---------------

DEFAULT    PASSWORD_REUSE_TIME       UNLIMITED

DEFAULT    PASSWORD_LOCK_TIME        1

DEFAULT    FAILED_LOGIN_ATTEMPTS     UNLIMITED

DEFAULT    PASSWORD_VERIFY_FUNCTION  NULL

DEFAULT    PASSWORD_LIFE_TIME        UNLIMITED

DEFAULT    PASSWORD_REUSE_MAX        UNLIMITED

DEFAULT    PASSWORD_GRACE_TIME       7

To find out the status of the accounts:

set pagesize 100
set wrap off
col USERNAME for a25
col PROFILE for a15
col ACCOUNT_STATUS for a20
col EXPIRY_DATE for a15

select USERNAME, PROFILE, ACCOUNT_STATUS, EXPIRY_DATE
from DBA_USERS;

USERNAME                  PROFILE    ACCOUNT_STATUS       EXPIRY_DATE

------------------------- ---------- -------------------- ---------------

CORPDIR_USER              DEFAULT    OPEN

MMS_USER                  DEFAULT    OPEN

NIZAR_RO                  DEFAULT    OPEN

SS_USER                   DEFAULT    OPEN

SCOTT                     DEFAULT    EXPIRED & LOCKED     17-MAR-11

ORACLE_OCM                DEFAULT    EXPIRED & LOCKED     15-AUG-09

XS$NULL                   DEFAULT    EXPIRED & LOCKED     15-AUG-09

MDDATA                    DEFAULT    EXPIRED & LOCKED     15-AUG-09

DIP                       DEFAULT    EXPIRED & LOCKED     15-AUG-09

APEX_PUBLIC_USER          DEFAULT    EXPIRED & LOCKED     15-AUG-09

SPATIAL_CSW_ADMIN_USR     DEFAULT    EXPIRED & LOCKED     15-AUG-09

SPATIAL_WFS_ADMIN_USR     DEFAULT    EXPIRED & LOCKED     15-AUG-09

DBSNMP                    MONITORING EXPIRED              20-SEP-11

To change the DEFAULT profile password policy:

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;