7 Replies Latest reply: May 21, 2013 8:39 AM by sybrand_b RSS

    getting more info on failed login attempts

    codrguy
      Hi All,

      I have audit enabled on my DB and i have set it like below :

      NAME TYPE VALUE
      -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
      audit_file_dest string /lapps/ag2/oappsr12/db/tech_st/11.1.0/rdbms/audit
      audit_sys_operations boolean FALSE
      audit_syslog_level string
      audit_trail string DB, EXTENDED

      I run the following query to get more info on who tries to login with incorrect username/password and who locks the user.


      select USERID,
      userhost,
      decode(returncode,01017,'Login Error','Acount Locked') "ISSUE",
      spare1,
      TO_CHAR ( CAST(
      ( FROM_TZ(
      CAST(
      TO_DATE(
      TO_CHAR( ntimestamp# , 'DD/MM/YYYY HH:MI PM'),
      'DD/MM/YYYY HH:MI PM'
      )
      AS TIMESTAMP
      ) ,
      'GMT'
      ) AT LOCAL
      )
      AS TIMESTAMP)
      , 'DD/MM/YYYY HH:MI PM') "Time",
      sqltext,
      comment$text from SYS.aud$
      where ( returncode=1017 OR returncode=28000 )
      order by ntimestamp# desc ;


      As you see, even though i have "DB,EXTENDED" enabled, i still get all nulls in the sqltext column. I would like to see the exact text of the login attempt being made ( ie the actual incorrect username and password used).
      So
      1) How do i get sqltext to show and not be null?
      2) Is there anyway to see the actual invalid username and password values attempted?

      Thanks
        • 1. Re: getting more info on failed login attempts
          L-MachineGun
          codrguy wrote:... Etc ...
          So
          1) How do i get sqltext to show and not be null?
          2) Is there anyway to see the actual invalid username and password values attempted?
          1) What sql text do you expect to get when a user is attempting to login?
          2) It's in the SYS.USER$ table, but I don't remember the query to get it, except for an invalid (non-existing) username.
          :p
          • 2. Re: getting more info on failed login attempts
            codrguy
            Thank you. You are right about my #1 question. It makes sense. So my only questions becomes "Is there any way to see the incorrect password someone tried to login with?"
            • 3. Re: getting more info on failed login attempts
              EdStevens
              codrguy wrote:
              Thank you. You are right about my #1 question. It makes sense. So my only questions becomes "Is there any way to see the incorrect password someone tried to login with?"
              If there were, wouldn't that be a huge security hole?
              • 4. Re: getting more info on failed login attempts
                mBk77
                To enable auditing of failed sign-on attempts:

                1 - Add initialization parameters & bounce instance:

                alter system set audit_trail=DB scope=spfile ;

                2 - Enable auditing of failed logion attempts as SYSDBA:

                SQL> audit create session whenever not successful;

                3 - You can now view failed login attempts in dba_audit_trail:

                select
                os_username,
                username,
                terminal,
                to_char(timestamp,'MM-DD-YYYY HH24:MI:SS')
                from
                dba_audit_trail;

                OS_USERNAME USERNAME TERMINAL TO_CHAR(TIMESTAMP,'
                --------------- --------------- --------------- -------------------
                alex SCOTT xyz93 05-05-2012 16:21:13
                • 5. Re: getting more info on failed login attempts
                  UweHesse
                  I am not aware about a way to audit the (wrong) password that was specified during connect - and as Ed already indicated: I hope there is none :-)

                  Kind regards
                  Uwe Hesse

                  "Don't believe it, test it!"
                  http://uhesse.com
                  • 6. Re: getting more info on failed login attempts
                    aorosar
                    Hi,

                    I'm sorry to open this thread again but I cannot find information about my problem anywhere. I read that enabling tracking of failed logon attemps can be very resource-consuming so I would like to disable it now that I know the information I needed, is there a way to do that without disabling the whole audit process?

                    Thanks for your time.

                    Best Regards.
                    • 7. Re: getting more info on failed login attempts
                      sybrand_b
                      I'm sorry to see you are hijacking a thread of more than a year old, and don't care to specify four digit database info and platform info, nor where you read this.
                      The observation doesn't make sense to me, because the only thing which is tracked is the error code of the login attemp, where 0 means successful.
                      Tracking invalid logins shouldn't need more resources.

                      ------------
                      Sybrand Bakker
                      Senior Oracle DBA