Where can i find the date and time when the last time password of oracle schema/user was changed
Printable View
Where can i find the date and time when the last time password of oracle schema/user was changed
The view dba_users has a column "expiry_date" which you can use to determane the date the password was changed, by using the profile information on "password_life_time" .
"expiry_date"(date) - password_life_time(days) should give you the result.
Expiry_date column in dba_users will show the date for expired users, then what abt current open account users?????Quote:
Originally Posted by robertbalmer
Code:USERNAME ACCOUNT_STATUS EXPIRY_DA LOCK_DATE
------------------------------ -------------------------------- --------- ---------
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
PS OPEN
BRUNTM OPEN
LEWISD OPEN
CRYSTAL OPEN
PEOPLE OPEN
OUTLN EXPIRED & LOCKED 22-SEP-05 22-SEP-05
WMSYS EXPIRED & LOCKED 22-SEP-05 22-SEP-05
ORDSYS EXPIRED & LOCKED 22-SEP-05 22-SEP-05
ORDPLUGINS EXPIRED & LOCKED 22-SEP-05 22-SEP-05
MDSYS EXPIRED & LOCKED 22-SEP-05 22-SEP-05
Do not use "default" profile. I created a test profile activating/using "password_life_time".
but there should be a better way,because if you alter the profile of a current user the result doesnt match.
rem:I think your open account users do not have "password_life_time" expire
The PTIME column in sys.user$ contains the time the password was last changed.
thanks floyd didnt know about that one.
I found something bothersome.Look at the results from queries. The dba_ view is correct, but the sys.user$ expdate is wrong....
select t.username,t.expiry_date,
TO_date(t.expiry_date,'DD/MM/YYYY') - to_date(sysdate,'DD/MM/YYYY') "DAYS BEFORE EXPIRE"
from dba_users t
where t.username in('TEST_PROFILE','TEST_PROFILE_2')
USERNAME EXPIRY_DATE DAYS BEFORE EXPIRE
TEST_PROFILE_2 13/Nov/05 0224:33:23 PM 3
TEST_PROFILE 24/Nov/05 1024:45:20 AM 14
select name,ptime,exptime
from sys.user$
where name in ('TEST_PROFILE','TEST_PROFILE_2')
NAME PTIME EXPTIME
TEST_PROFILE_2 10/Nov/05 0224:33:23 PM 10/Nov/05 0224:31:16 PM
TEST_PROFILE 10/Nov/05 1024:45:20 AM
1. You are using TO_DATE() where you shouldn't (on DATE columns, like t.expiry_date and on sysdate) and on the other hand you don't use TO_CHAR() with appropriate format mask where you realy should (when displaying DATE columns).Quote:
Originally Posted by robertbalmer
2. You have some serious issues with your default date format mask - what kind of date is something like "10/Nov/05 0224:33:23 PM"
3. You should start using [ code ] tags to make results readable. This probably makes much more sense to readers:
4. You are comparing apples and owen toasters with the above two queries, so it is not realy clear what you thing is wrong with your results. Look at the source of the DBA_USERS view and you'll find out how DBA_USERS.EXPIRY_DATE is defined and that it is not based only on USER$. If you'll still find your results to be strange, then you'll have to post some more information about your two users - at least their relevant values from PROFILE$.Code:NAME PTIME EXPTIME
TEST_PROFILE_2 10/Nov/05 0224:33:23 PM 10/Nov/05 0224:31:16 PM
TEST_PROFILE 10/Nov/05 1024:45:20 AM
jmodic
1@2@3 besides the point. But, my apologies for the format,I did it quick on a test box.
4.1 The EXPTIME(in user$) for user=TEST_PROFILE_2 is
10/Nov/05 0224:31:16 PM;
4.2 The EXPIRY_DATE(in dba_users) for user=TEST_PROFILE_2 is
13/Nov/05 0224:33:23 PM;
4.3 The EXPTIME(in user$) for user=TEST_PROFILE is null;
4.4 The EXPIRY_DATE(in dba_users) for user=TEST_PROFILE is
24/Nov/05 1024:45:20 AM;
Thats my concern.
Have a good day :D