Oracle 11g – Password expires

Posted: 10th June 2013 by Babar Shafiq in System Administration
Tags: , , , ,

 

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;