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:
1 | SQL> select username, account_status, expiry_date, profile from dba_users; |
01 | USERNAME ACCOUNT_STATUS EXPIRY_DA PROFILE |
05 | SOE OPEN 18-JAN-12 DEFAULT |
07 | BRAVO EXPIRED 30-MAY-13 DEFAULT |
09 | SCOTT EXPIRED & LOCKED 15-JUL-11 DEFAULT |
11 | ORACLE_OCM EXPIRED & LOCKED 05-SEP-10 DEFAULT |
13 | XS$NULL EXPIRED & LOCKED 05-SEP-10 DEFAULT |
15 | MDDATA EXPIRED & LOCKED 05-SEP-10 DEFAULT |
17 | DIP EXPIRED & LOCKED 05-SEP-10 DEFAULT |
19 | APEX_PUBLIC_USER EXPIRED & LOCKED 05-SEP-10 DEFAULT |
1 | 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:
1 | SQL> SELECT profile, resource_name, limit FROM dba_profiles WHERE profile='DEFAULT'; |
01 | PROFILE RESOURCE_NAME LIMIT |
05 | DEFAULT COMPOSITE_LIMIT UNLIMITED |
07 | DEFAULT SESSIONS_PER_USER UNLIMITED |
09 | DEFAULT CPU_PER_SESSION UNLIMITED |
11 | DEFAULT CPU_PER_CALL UNLIMITED |
13 | DEFAULT LOGICAL_READS_PER_SESSION UNLIMITED |
15 | DEFAULT LOGICAL_READS_PER_CALL UNLIMITED |
17 | DEFAULT IDLE_TIME UNLIMITED |
19 | DEFAULT CONNECT_TIME UNLIMITED |
21 | DEFAULT PRIVATE_SGA UNLIMITED |
23 | DEFAULT FAILED_LOGIN_ATTEMPTS 10 |
25 | DEFAULT PASSWORD_LIFE_TIME 180 |
27 | PROFILE RESOURCE_NAME LIMIT |
31 | DEFAULT PASSWORD_REUSE_TIME UNLIMITED |
33 | DEFAULT PASSWORD_REUSE_MAX UNLIMITED |
35 | DEFAULT PASSWORD_VERIFY_FUNCTION NULL |
37 | DEFAULT PASSWORD_LOCK_TIME 1 |
39 | DEFAULT PASSWORD_GRACE_TIME 7 |
5) Changing the password_life_time for unlimited
1 | SQL> alter profile default limit password_life_time unlimited; |
