But, every 180 days, you need to repeat the same action. If you are NOT concerned with strict security rules for your database, you can take the following approach to set user accounts to never expire.
What Profile Used by a User Account?
A profile[3] is a database object - a named set of resource limits. Using profile, you can enforce a limit on resource utilization using resource limit parameters Also you can maintain database security by using password management feature. Here is the SQL command you can use to query which profile is used by each user account:
SQL> SELECT USERNAME, PROFILE FROM DBA_USERS;
USERNAME PROFILE
------------------------------ ------------------------------
USERNAME PROFILE
------------------------------ ------------------------------
OAM_OAM DEFAULT
OAM_IAU_APPEND DEFAULT
OAM_IAU_VIEWER DEFAULT
OAM_IAU DEFAULT
OIM_SOAINFRA DEFAULT
OIM_ORASDPM DEFAULT
OIM_MDS DEFAULT
OIM_OIM DEFAULT
OAM_IAU_APPEND DEFAULT
OAM_IAU_VIEWER DEFAULT
OAM_IAU DEFAULT
OIM_SOAINFRA DEFAULT
OIM_ORASDPM DEFAULT
OIM_MDS DEFAULT
OIM_OIM DEFAULT
As shown above, both OAM and OIM user accounts use "DEFAULT" profile.
What Limits Set with a Profile?
We are only interested in "DEFAULT" profile and resource of PASSWORD type. To query all sorts of limits imposed with "DEFAULT" profile, you do the following query:
SQL> select resource_name, limit from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
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
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
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
As shown above, all our OAM and OIM user accounts will expire in 180 days. However, we would like to set it to never expire.
How to Set User Password to Never Expire?
Here is the alter statement that you can use:
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
The above command has set password life time associated with "DEFAULT" profile to be unlimited. You can verify the setting by:
SQL> select resource_name, limit from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
7 rows selected.
