-
Password change time
Where can i find the date and time when the last time password of oracle schema/user was changed
------------------------------------------------------------------------
The most enjoyable things in the world are either Immoral or too Expensive or otherwise Inaccessible anyway
-
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.
Able was I ere I saw Elba
-
Originally Posted by 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 column in dba_users will show the date for expired users, then what abt current open account users?????
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
"What is past is PROLOGUE"
-
Do not use "default" profile. I created a test profile activating/using "password_life_time".
Able was I ere I saw Elba
-
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
Able was I ere I saw Elba
-
Look in sys.user$
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
Able was I ere I saw Elba
-
Originally Posted by 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
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:
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
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$.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
Able was I ere I saw Elba
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|