How to configure DB user accounts or passwords to never expires?

Most of the time we will encounter our database passwords used to expire after 180 days.

If we don’t have any security constraint then by using the below approach we can make them never expire.

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.  Using the below query we can know ,which profile is used by each user account:

SQL> SELECT USERNAME, PROFILE FROM DBA_USERS;

sqlnoexpire.JPG

You can see all my SOA related user accounts are using “DEFAULT” profile.

To know all the details imposed on DEFAULT profile we have to use the below 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

As shown above all our passwords will expire in 180 days. So for setting them to never expire we have use the below query.

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
Now we can verify the seeing by running the below query.
SQL> select resource_name, limit from dba_profiles where profile=’DEFAULT’ and resource_type=’PASSWORD’;
sqlnoexpire1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s