This discussion is archived
7 Replies Latest reply: May 21, 2013 6:39 AM by sybrand_b RSS

getting more info on failed login attempts

codrguy Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points