[Oracle11g] Set Oracle Database User Account and Password to Never Expire

Here is a small tip on how to create a new user in Oracle 11g and set the password to never expire. This would come in handy when you are creating new metadata schema's for OBIEE, when for long time these schema's are not in use they tend to get locked and OBIEE services will fail to start.

So in order to avoid this scenario it is better to create a new Oracle user profile and set the parameters to unlimited. Please follow the steps below

After you create a new Oracle user, say for example OBIUSER then the next step is to create  a new User Profile OBIUSER_PROFILE as below

CREATE PROFILE OBIUSER_PROFILE LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME 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;

If you check the DDL statement above you can see that we have set properties like PASSWORD_LIFE_TIME, PASSWORD_LOCK_TIME, FAILED_LOGIN_ATTEMPTS to unlimited. Hence all the users having this user profile will never get expired. 

The next step is to assign this new User Profile characteristic to the new user OBIUSER,

ALTER USER OBIUSER PROFILE OBIUSER_PROFILE;

Now this user/schema password will never expire. 

References: Oracle Docs