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
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
------------------------------ -------------------------------- --------- ---------
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
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;
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';
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
thank you very much really
Thanks much Allen. very helpful tip indeed.
Thanks so much for this article - you are a lifesaver!
its really help full to me
It was really helpful.