Oracle 11g – Password expires
Monday, June 10th, 2013 - 9:38 am - System Administration
Oracle 11g sets by default password expiration. The expired message needs to re-login and reset the old password to new one.
Suppose you have an user with the status “expired”:
You can check that by:
SQL> select username, account_status, expiry_date, profile from dba_users;
USERNAME ACCOUNT_STATUS EXPIRY_DA PROFILE -------------------- -------------------- --------- ---------- SOE OPEN 18-JAN-12 DEFAULT BRAVO EXPIRED 30-MAY-13 DEFAULT SCOTT EXPIRED & LOCKED 15-JUL-11 DEFAULT ORACLE_OCM EXPIRED & LOCKED 05-SEP-10 DEFAULT XS$NULL EXPIRED & LOCKED 05-SEP-10 DEFAULT MDDATA EXPIRED & LOCKED 05-SEP-10 DEFAULT DIP EXPIRED & LOCKED 05-SEP-10 DEFAULT APEX_PUBLIC_USER EXPIRED & LOCKED 05-SEP-10 DEFAULT
2) SQL> alter user BRAVO identified by TESTING;
3) To avoid to have users with “expired” password, you have two options:
– Create a new profile and set your own definitions about dates to expire the passwords
– Change the default profile
4) Let’s make an example how to do that:
Checking profiles:
SQL> SELECT profile, resource_name, limit FROM dba_profiles WHERE profile='DEFAULT';
PROFILE RESOURCE_NAME LIMIT ---------- -------------------------------- ---------------------------------------- DEFAULT COMPOSITE_LIMIT UNLIMITED DEFAULT SESSIONS_PER_USER UNLIMITED DEFAULT CPU_PER_SESSION UNLIMITED DEFAULT CPU_PER_CALL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION UNLIMITED DEFAULT LOGICAL_READS_PER_CALL UNLIMITED DEFAULT IDLE_TIME UNLIMITED DEFAULT CONNECT_TIME UNLIMITED DEFAULT PRIVATE_SGA UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS 10 DEFAULT PASSWORD_LIFE_TIME 180 PROFILE RESOURCE_NAME LIMIT ---------- -------------------------------- ---------------------------------------- DEFAULT PASSWORD_REUSE_TIME UNLIMITED DEFAULT PASSWORD_REUSE_MAX UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION NULL DEFAULT PASSWORD_LOCK_TIME 1 DEFAULT PASSWORD_GRACE_TIME 7
5) Changing the password_life_time for unlimited
SQL> alter profile default limit password_life_time unlimited;
