Expiring Oracle 11g Passwords

On an ongoing basis I've been maintaining Oracle 11g databases for performance testing, development and certification.  Because I switch back and forth between Oracle and SQL Server, I've been caught up a few times where the passwords for a given instance of Oracle have expired.  It always seems to hit at the most inopportune time.  And of course I never remember exactly what the problem is, so I end up spending hours pour through logs and configuration files only to find out that the Oracle user no longer has access the database.

So what do you do when the database passwords expire?  How can you tell when a pass word is going to expire and how do you prevent the passwords from expiring all together?

Well after searching around on internet I found the following SQL commands to unlock expired passwords and figure out when the passwords will expire.  Let's start by looking at user accounts and when the passwords will expire.  You will need to log into the database as sysdba and then execute the following query.

select username, account_status, expiry_date, lock_date from dba_users;

The query will return a list of the following information for all of the users.

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA LOCK_DATE

------------------------------ -------------------------------- --------- ---------SCOTT                          EXPIRED & LOCKED                 04-NOV-08 04-NOV-08

 

So as you can our user SCOTT has lost access to the database because the account has expired and is now locked.

To now unlock the account use the following SQL Statement.

alter user scott account unlock;

Executing the first query again we see the following output and the account is no longer locked:

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA LOCK_DATE

------------------------------ -------------------------------- --------- ---------

SCOTT                          EXPIRED                          04-NOV-08

 

Now when our user SCOTT goes to log into the database he will be prompted to change his password before being given access to the database.  In some cases, you as the administrator will be changing a user account where you know what the password is and what you want it to be.  In this case you can change the password while you are logged in as the sysdba.  This can be done with the following SQL statement.

alter user scott identified by tiger;

As a side note, the password can be changed back to the original password.  I found this helpful with ProjectWise in that I didn't need to recreate the ODBC data source or reconfigure my ProjectWise server.

By default the passwords are set to expire 180 days after the account is created.  There is also a 7 day grace period in which the password can be changed.  This all works great when you are logging directly into the database using a tool such as SQL Plus.  In the case of ProjectWise where the database connection is through ODBC, you don't readily get the error messages that the user account is about to or has expired.  For this reason you may choose to disable the expiring password feature in Oracle.  Here are the SQL Statements to view the user's profile and disable the expiring password feature.

select profile from dba_users where username = 'SCOTT';

PROFILE

------------------------------

DEFAULT

 

Next using the profile that was returned by the above query set the default limits to be unlimited.

alter profile default limit

FAILED_LOGIN_ATTEMPTS UNLIMITED

PASSWORD_LIFE_TIME UNLIMITED;

Once we have executed this command we can verify the change using the following query.

select LIMIT from dba_profiles where RESOURCE_NAME ='FAILED_LOGIN_ATTEMPTS'

     and PROFILE = 'DEFAULT';

In ProjectWise the expired Oracle password surfaces as all of a sudden none of the ProjectWise users are able to log into the ProjectWise data source.  When diagnosing the problem checking to see if the Oracle password expired should be one of first places to investigate.

References:

http://www.dba-oracle.com/t_passwords_locking_changing_expiring.htm

http://iadvise.blogspot.com/2008/02/sysman-account-is-locked.html