Query To Find Out Password Expiration?

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?
AptDevAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rbrookerConnect With a Mentor 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 ... :)
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
SELECT username, expiry_date
FROM dba_users;
0
 
rbrookerCommented:
expiry date in dba_users is the date the account expired, not the date the account is going to expire..
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
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>
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
SELECT expiry_date , username
FROM dba_users; --> this query will tell you the expiry date for each user.

Thanks
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
nav_kum_v, please read the previous posts before commenting... The last one has no added value.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
rbrookerCommented:
well, always learning...  in this case, there are a bunch of users in my db whose password never expires... ( null expiry dates )
0
 
AptDevAuthor Commented:
It worked great!  Thanks!

select name, ptime from sys.user$
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.