DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Password change time

  1. #1
    Join Date
    Sep 2002
    Posts
    376

    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

  2. #2
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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

  3. #3
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote 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"

  4. #4
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    Do not use "default" profile. I created a test profile activating/using "password_life_time".
    Able was I ere I saw Elba

  5. #5
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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

  6. #6
    Join Date
    Nov 2005
    Location
    Indianapolis
    Posts
    24

    Look in sys.user$

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

  7. #7
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote 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?

  9. #9
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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
  •  


Click Here to Expand Forum to Full Width