how to unlock a oracle user account when it expires and set to unlimited

I have written an article on how to unlock a user account when it expires:

• ORA-28002: the password will expire within 1 days

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
—————————— ——————————

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

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';

OR

SQL> select resource_name, limit from dba_profiles;

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.