Currently Being Moderated
neto

Oracle 11g - Password expires

Posted by neto in Databases on Aug 10, 2011 11:59:27 AM

Hi All,

 

This is neto from Brazil (in RTP)

 

How are you?

 

Different from other releases, Oracle 11g sets by default password expiration.

 

Suppose you have an user with the status "expired":

 

Please check at:

 

1) SQL> select username, account_status, expiry_date, profile from dba_users;

 

USERNAME             ACCOUNT_STATUS       EXPIRY_DA PROFILE

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

SOE                  OPEN                 18-JAN-12 DEFAULT

LOUL                 EXPIRED              18-JAN-11 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 loul identified by netapp;

 

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;

 

 

All the best my friends

 

neto

NetApp - I cannot express in words the honor of being part of this family!

Comments

Filter Blog

By date:
By tag: