7 Replies Latest reply on May 21, 2013 1:39 PM by sybrand_b

    getting more info on failed login attempts

    Codrguy-Oracle
      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