5 Replies Latest reply on Jan 31, 2010 1:28 PM by Bipul

    How to track Account Lock

    Bipul
      We have mechanism to lock the ID after 10 consecutive wrong attempts.
      I want to implement a script which will find out which user did this.
      I want to find out the record for returncode = 1017 rows right before the id locked (Returncode=28000)
      how can I get that ... can anyone help ?

      Data dictionary view DBA_AUDIT_SESSION keeps track of the Account Lock event.
      Returncode : 
      ORA-01017: invalid username/password; logon denied
      and
      ORA-28000: the account is locked
      I was trying something like this ...
      select OS_USERNAME, USERNAME , USERHOST, RETURNCODE, TIMESTAMP
      from dba_audit_session
      where to_date(TIMESTAMP, 'DD-Mon-YY') in (select to_date(TIMESTAMP, 'DD-Mon-YY')
      from dba_audit_session
      where to_date(TIMESTAMP,'DD-Mon-YY') = to_date(sysdate, 'DD-Mon-YY'))
      and RETURNCODE = 28000;
        • 1. Re: How to track Account Lock
          558383
          What is your Oracle version ?
          What is the output of:
          show parameter audit
          Did you run;
          audit session;
          • 2. Re: How to track Account Lock
            Bipul
            Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
            
            sys@CSMIPIT> show parameter audit
            
            NAME                                 TYPE    VALUE
            ------------------------------------ ------- ------------------------------
            audit_file_dest                      string  $ORACLE_HOME/rdbms/audit
            audit_trail                          string  TRUE
            transaction_auditing                 boolean TRUE
            • 3. Re: How to track Account Lock
              ajallen
              If you turn on audit session as previously stated, you will then see all logins in the view DBA_AUDIT_SESSION. You can also set audit session whenever not successful to trap only failed logins. The column returncode in the view corresponds with the ORA- exception raised on the failed login. Thus you will be able to find the invalid username/password attempts easily. The view will also tell you the os username, terminal, hostname, and timestamp of the event.

              Do not forget to clean out old data in this table from time to time.
              • 4. Re: How to track Account Lock
                MichaelS
                I want to find out the record for returncode = 1017 rows right before the id locked (Returncode=28000)
                Maybe this:
                SELECT t2.*, t1.*
                  FROM dba_audit_session t2, dba_audit_session t1
                 WHERE     t2.returncode = 28000
                       AND t1.timestamp < t2.timestamp
                       AND t1.returncode = 1017
                       AND t1.username = t2.username
                ?
                • 5. Re: How to track Account Lock
                  Bipul
                  Thanks for tips ... it working