2 Replies Latest reply: Dec 4, 2008 6:49 AM by 641067 RSS

    User-Account's expiration-date

    641067
      Hi All,
      I have a lot of schema and generic users to follow up to avoid its password's expiration. I tried to find which of them are near to their expiration date but for some accounts my query didn't work...

      SELECT USERNAME,
      EXPIRY_DATE ,
      PROFILE
      FROM
      (SELECT u.username ,
      U.account_status ,
      U.profile ,
      P.limit AS passwd_exp_limit,
      SYU.ptime ,
      SYU.ptime + P.limit AS expiry_date
      FROM dba_users U,
      sys.user$ SYU ,
      dba_profiles P
      WHERE u.username NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DIP', 'TSMSYS',
      'DBSNMP', 'ORACLE_OCM', 'OPS$ORACLE')
      AND SYU.user# = U.user_id
      AND u.username NOT LIKE 'OPS$%'
      AND p.profile = u.profile
      AND p.resource_name = 'PASSWORD_LIFE_TIME'
      )
      WHERE TRUNC (EXPIRY_DATE) - 30 <= TRUNC(SYSDATE)
      AND PROFILE <> 'END_USER';
      It was the query's result.
      USERNAME EXPIRY_DATE PROFILE
      _________________________________________________
      ...
      ETLDWDW 30-DEC-08 DEFAULT_PASSWORD_PROFILE

      8 rows selected
      But... It didn't expired at logon, even when according to my query it should be expired from a long time ago.
      SQL> conn etldwdw@instance_name
      Enter password: ***********
      Connected.
      SQL>
      I checked the PASSWORD_LIFE_TIME for this profile, and it's equal to 90 days.

      How can I determine which day will expire each account?

      Thanks by your help

      Martin

      P.S: My version is

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
      PL/SQL Release 10.2.0.4.0 - Production
      "CORE     10.2.0.4.0     Production"
      TNS for Linux: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production
        • 1. Re: User-Account's expiration-date
          Jaffy
          hi,

          To check expiry date you have to query dba_users view.

          As per your query its shows correct value and it expires on 30th dec, so u can login with that user till 30th dec.

          regards

          Jafar
          • 2. Re: User-Account's expiration-date
            641067
            Hi Jaffy... I need a new eyeglasses. ;)

            Thanks by your help!

            Martin

            P.S.: I've lost almost 2 hours with this "date-issue".
            Jaffy wrote:
            hi,

            To check expiry date you have to query dba_users view.

            As per your query its shows correct value and it expires on 30th dec, so u can login with that user till 30th dec.

            regards

            Jafar
            Edited by: martinmorono on Dec 4, 2008 10:47 AM