We help IT Professionals succeed at work.

Query To Find Out Password Expiration?

AptDev
AptDev asked
on
Medium Priority
7,431 Views
Last Modified: 2008-01-09
In my Oracle 8i database, I have set up a profile that requires users to change their password every 90 days (policy requires it, and sarbanes/oxley enforces it.)  My users change their password using a 3rd party tool.  If the don't change it, it simply locks them out without warning.

Is there a query I can run, that will tell me when a user's password is about to expire, so I can send them an automatic email that it's nearly time to change their password again?
Comment
Watch Question

Top Expert 2006
Commented:
Hi,

you can query the last time a person set their password from sys.user$ and add the expiration duration from what profile the user has...

good luck ... :)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Pierrick LOUBIERIS Operational Excellence Manager

Commented:
SELECT username, expiry_date
FROM dba_users;
Top Expert 2006

Commented:
expiry date in dba_users is the date the account expired, not the date the account is going to expire..
Pierrick LOUBIERIS Operational Excellence Manager

Commented:
I'm sorry rbrooker but...

SQL> SELECT username, expiry_date
  2  FROM dba_users
  3  WHERE username='SCOTT';

USERNAME                       EXPIRY_DA
------------------------------ ---------
SCOTT

SQL> CREATE PROFILE proftest
  2     LIMIT PASSWORD_LIFE_TIME 10;

Profile created.

SQL> ALTER USER scott
  2     PROFILE proftest;

User altered.

SQL> SELECT username, expiry_date
  2  FROM dba_users
  3  WHERE username='SCOTT';

USERNAME                       EXPIRY_DA
------------------------------ ---------
SCOTT                          05-NOV-06

SQL>
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
SELECT expiry_date , username
FROM dba_users; --> this query will tell you the expiry date for each user.

Thanks
Pierrick LOUBIERIS Operational Excellence Manager

Commented:
nav_kum_v, please read the previous posts before commenting... The last one has no added value.
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
ploubier,  
   
i just updated that this might help the author who posted this question. I cannot go and validate each users comments which are posted for each and every question on which i am going to write something. Kindly understand that i have not posted it for points.
   
AptDev,  
     
my comments are duplicate comments. Just ignore them.  
 
Thanks
Top Expert 2006

Commented:
well, always learning...  in this case, there are a bunch of users in my db whose password never expires... ( null expiry dates )

Author

Commented:
It worked great!  Thanks!

select name, ptime from sys.user$
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.