Solved

Query To Find Out Password Expiration?

Posted on 2006-10-25
9
7,364 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?
0
Comment
Question by:AptDev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 18

Accepted Solution

by:
rbrooker earned 500 total points
ID: 17807817
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
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 17807995
SELECT username, expiry_date
FROM dba_users;
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17809734
expiry date in dba_users is the date the account expired, not the date the account is going to expire..
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 17809868
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17810093
SELECT expiry_date , username
FROM dba_users; --> this query will tell you the expiry date for each user.

Thanks
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 17810111
nav_kum_v, please read the previous posts before commenting... The last one has no added value.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17810160
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
 
LVL 18

Expert Comment

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

Author Comment

by:AptDev
ID: 17812100
It worked great!  Thanks!

select name, ptime from sys.user$
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question