unable to collect username during fail logon to the database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: unable to collect username during fail logon to the database

  1. #1
    Join Date
    Nov 2000
    Posts
    164

    unable to collect username during fail logon to the database

    Dear all,

    I have a trigger to notify failed user logon; however, I am unable to collect the username. My trigger looks something like this:

    CREATE OR REPLACE TRIGGER failed_logon_notifications
    AFTER SERVERERROR ON DATABASE
    DECLARE
    v_username varchar2(30);
    ...
    BEGIN
    IF ora_is_servererror( 01017 ) THEN
    v_username := SYS_CONTEXT( 'USERENV', 'SESSION_USER');
    ...
    BEGIN
    utl_mail.send
    EXCEPTION
    WHEN others THEN
    RAISE;
    END IF;
    END;
    /

    I notice that both “SYS_CONTEXT( 'USERENV', 'SESSION_USER')” and “select user from dual” return null for failed logon; however, the userid in aud$ is able to collect the username even for failed logon. Does any know how Oracle does that? Or is there a way to collect username for failed logon?


    Thanks,
    Unna

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,156
    If someone fails to log in to the database, then they don't have a username, since they did not successfully authenticate.
    However, auditing is recording what they were trying to log in as. Hence you should rely on auditing for the information
    that you want, not a database level trigger. It seems to me that this behavior is what we should expect.
    this space intentionally left blank

  3. #3
    Join Date
    Nov 2000
    Posts
    164
    Thanks for your reply. Initially I would agree with you; however, looking at aud$ table the userid column records failed logon user name as well. I believe there must be a way to collect that information.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width