Trigger not getting user name after SERVERERROR
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Trigger not getting user name after SERVERERROR

  1. #1
    Join Date
    Nov 2000
    Posts
    162

    Trigger not getting user name after SERVERERROR

    Dear all,

    I have a trigger for failed login to catch who is the user trying to login, but fails so far. Here is the trigger:


    CREATE OR REPLACE TRIGGER test
    after servererror on database
    BEGIN
    If (ORA_IS_SERVERERROR(1017)) Then
    insert into test_tb values (user, ora_login_user, SYS_CONTEXT ('USERENV', 'OS_USER') , '-1017', userenv('terminal'), sysdate);
    End If;
    End;
    /


    Connect sdfadf/dafsaf
    ERROR:
    ORA-01017: invalid username/password; logon denied

    Connect / as sysdba
    select * from system.test_tb;

    A_USER USERNAME S_USER MESSAGE TERMINAL A_DATE
    ---------- ---------------- ----------- ---------- ------------------------------ ---------
    abc -1017 FRANKLINIA 11-MAY-12
    abc -1017 FRANKLINIA 11-MAY-12

    I am just not able to get the user id who is trying to login, in this case the user “sdfadf”. Does any anyone has any idea how to grep this user info?


    Thanks,
    Unna

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Try an

    pragma autonomous_transaction;
    insert ...
    commit;

    this space intentionally left blank

  3. #3
    Join Date
    Nov 2000
    Posts
    162
    Thanks for your advice. I changed the code to:

    CREATE OR REPLACE TRIGGER test
    after servererror on database
    declare
    pragma autonomous_transaction;
    BEGIN
    If (ORA_IS_SERVERERROR(1017)) Then
    insert into test_tb values (user, ora_login_user, SYS_CONTEXT ('USERENV', 'OS_USER') , '-1017', userenv('terminal'), sysdate);
    commit;
    End If;
    End;
    /

    But the user and ora_login_user fields are still not capturing anything or I miss something? Any advice?


    Thanks,
    Unna

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    If there is an error with the insert, then you probably won't see it.
    Did you test the insert statement? You should have an exception
    when others then capture the error in a log table.
    this space intentionally left blank

  5. #5
    Join Date
    Nov 2000
    Posts
    162
    Thanks gandolf989. Yes, I did have exception, but not helping. I think the problem is when there is failed login and when the trigger fires at the time of servererror, the username is not collected. I am able to get os user, sid and the rest into a log table but just not the username.

    However, if I have another trigger after the log table is being upated, I was able to get the username from dba_audit_session. The user info does not seem to exist until the trigger of servererror.

    I can workaround with 2 triggers, but hoping to have one trigger instead of 2. Any idea on how to get the username?


    Thanks,
    unna

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    you might also look at the Oracle Audit features.
    You can audit for failed connections for example.
    It would probably work better than a system level trigger.
    You can even keep the log in the database.
    this space intentionally left blank

  7. #7
    Join Date
    Nov 2000
    Posts
    162
    The failed login audit has been turned on, but we required to have trigger to alert us when there is a failed login.

    I was able to work around that, but unfortunately I need two triggers to complish this task.


    Thanks for all your help!

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