Tuesday, February 12, 2013

About Password Expiration in Oracle Database 11g

In the Apex Installation Guide you will find the following paragraph:

"In the default profile in Oracle Database 11g, the parameter PASSWORD_LIFE_TIME is set to 180. If you are using Oracle Database 11g with Oracle Application Express, this causes the password for APEX_PUBLIC_USER to expire in 180 days. As a result, your Oracle Application Express instance will become unusable until you change the password.

To prevent this behavior, create another profile in which the PASSWORD_LIFE_TIME parameter is set to unlimited and alter the APEX_PUBLIC_USER account and assign it to the new profile."

Of course with a little help from google you can find out how to do this. I summarized it for you:

First let's see what the limits of the default profile (or the profile of your APEX_PUBLIC_USER) are:
select resource_name
      ,limit
  from dba_profiles
 where profile = 'DEFAULT';
It will result in something like this:
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
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7

There are two options. First you can change the default profile, but the profile of the other users will change accordingly and that may be a security vulnerability. But this is how you do it:
alter profile default limit
   password_life_time unlimited;

Second, and my favorite, add a new profile and link it to the APEX_PUBLIC_USER user:
create profile apex_public limit
   password_life_time unlimited;

alter user apex_public_user
   profile apex_public;
The first query will create a new profile with all limits to default except the password_life_time. The second one changes the profile of the APEX_PUBLIC_USER.

And... don't forget to change the profile for your APEX_LISTENER and APEX_REST_PUBLIC_USER.

No comments:

Post a Comment