Password change time

[bang_dba]

Where can i find the date and time when the last time password of oracle schema/user was changed

[robertbalmer]

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.

[dbasan]

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 column in dba_users will show the date for expired users, then what abt current open account users?????

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

[robertbalmer]

Do not use "default" profile. I created a test profile activating/using "password_life_time".

[robertbalmer]

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

[wdfloyd]

The PTIME column in sys.user$ contains the time the password was last changed.

[robertbalmer]

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

[jmodic]

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).

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:

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

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$.

[robertbalmer]

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