DBA by Day

databases, technology, and random thoughts

Oracle Password Life Expectancy

on April 13, 2011

The PASSWORD_LIFE_TIME parameter specifies the length of time the same password can be used to authenticate to a database account. After the alloted time has passed, the user must change their password or they will be unable to access the database. As much of a pain in the ass this is… unfortunately it is safety precaution that most gov’t agencies and private companies want to take.

Run this SQL script in your database to determine what your default password life time is:

SELECT profile, limit
FROM   dba_profiles,
(SELECT limit AS def_pwd_life_tm
FROM   dba_profiles
WHERE  profile = ‘DEFAULT’
AND    resource_name = ‘PASSWORD_LIFE_TIME’)
WHERE  resource_name = ‘PASSWORD_LIFE_TIME’
AND    ((replace(limit,’DEFAULT’,def_pwd_life_tm) IN (‘UNLIMITED’,NULL))
OR    (lpad(replace(limit,’DEFAULT’,def_pwd_life_tm),40,’0′) >
lpad(’90’,40,’0′)));

If the result returns “Unlimited” then your passwords never expire. If the result returns a number, this is the amount of days the user has before being required to change their password. To change this parameter:

alter profile default limit password_life_time 60;

In the example we’re changing the default password profile to 60 days, however you can change any of your password profile’s by swapping “default” for a profile name.  Requiring users to change their passwords more often then 60 days might end up more of a headache and could result in a less secure system. Often times when users are required to change their passwords too frequently they’re more likely to write them down on their desk or forget them and need to be reset. Currently the DoD standard is 60 days.

About these ads

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

Follow

Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: