Friday, March 4, 2011

APEX_PUBLIC_USER password expires on 11g

Just read Scott Wesley's post on APEX Listener issues which was being caused by Oracle 11g automatically expiring passwords.

The cleanest solution is to use an unlimited password lifetime profile, and assign to APEX_PUBLIC_USER.

This is done simply by:

CREATE PROFILE UNLIMITED_PASSWORD_LIFETIME LIMIT
  SESSIONS_PER_USER DEFAULT
  CPU_PER_SESSION DEFAULT
  CPU_PER_CALL DEFAULT
  CONNECT_TIME DEFAULT
  IDLE_TIME DEFAULT
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL DEFAULT
  COMPOSITE_LIMIT DEFAULT
  PRIVATE_SGA DEFAULT
  FAILED_LOGIN_ATTEMPTS DEFAULT
  PASSWORD_LIFE_TIME UNLIMITED    /** this sets unlimited lifetime */
  PASSWORD_REUSE_TIME DEFAULT
  PASSWORD_REUSE_MAX DEFAULT
  PASSWORD_LOCK_TIME DEFAULT
  PASSWORD_GRACE_TIME DEFAULT
  PASSWORD_VERIFY_FUNCTION DEFAULT;


ALTER USER APEX_PUBLIC_USER
 PROFILE UNLIMITED_PASSWORD_LIFETIME
 ACCOUNT LOCK;

1 comment:

Pete Finnigan said...

Hi Mark,

Whilst your advice fixes the issue it is not what is intended by the fact a profile now expires the password.

This user password is important for the application to work but it should not be made insecure.

If you create an unlimited lifetime then you have to be sure that your password hash is never exposed and also that the password if cracking is attempted - ( whihc you would have to assume is possible in an unlimited lifetime scenario) then the cracker would never find the password in the lifetime of the system.

standard crackers exist that can test 1,600,000 hashes a second, some better ones using GPU's can do 5,000,000 a second a hardware cracker on the net can do 85,000,000 hashes a second. Your lifetime has to be calculated based on the complexity of the password keyspace and length.

cheers

pete