After firing up an old database VM I noticed a change password screen appeared in OEM database control and also an ORA-28002 when logging in as system via sqlplus.
[oracle@database ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 15 19:26:24 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn system@database01
Enter password:
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL>
Checking the relevant users and profiles values shows;
SQL> select username, profile from dba_users where username in ('SYS','SYSTEM','SYSMAN','DBSNMP');
USERNAME PROFILE
------------------------------ ------------------------------
SYS DEFAULT
DBSNMP MONITORING_PROFILE
SYSMAN DEFAULT
SYSTEM DEFAULT
SQL> select resource_name,liMit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
SQL>
I change PASSWORD_LIFE_TIME for the DEFAULT profile since this is only a reference database for testing, and reset the password to stop being nagged. Note that there was no expiry date for SYS (most likely because OS Authentication is also enabled by default - too lazy to proove this today though...).
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
SQL> alter user system identified by xxxxxx;
User altered.
SQL> select username, profile, expiry_date from dba_users where username in ('SYS','SYSTEM','SYSMAN','DBSNMP');
USERNAME PROFILE EXPIRY_DA
------------------------------ ------------------------------ ---------
SYS DEFAULT
SYSTEM DEFAULT
DBSNMP MONITORING_PROFILE 22-NOV-12
SYSMAN DEFAULT 22-NOV-12
SQL> alter user sysman identified by xxxxxx;
User altered.
SQL> alter user dbsnmp identified by xxxxxx;
User altered.
SQL> select username, profile, expiry_date from dba_users where username in ('SYS','SYSTEM','SYSMAN','DBSNMP');
USERNAME PROFILE EXPIRY_DA
------------------------------ ------------------------------ ---------
DBSNMP MONITORING_PROFILE
SYSMAN DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
SQL>
Interestingly if we check the profile for DBSNMP, we see that PASSWORD_LIFE_TIME refers to the DEFAULT profile anyway.
SQL> select resource_name,liMit from dba_profiles where profile='MONITORING_PROFILE';
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME DEFAULT
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME DEFAULT
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT
16 rows selected.
SQL>
Here is an amusing
thread just in case DEFAULT wasn't obvious enough.
As usual it's all in the Doc if you look hard enough. Under
create profile in this case.
