Trigger not getting user name after SERVERERROR

[Unna]

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

[gandolf989]

Try an

pragma autonomous_transaction;
insert ...
commit;

[Unna]

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

[gandolf989]

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.

[Unna]

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

[gandolf989]

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.

[Unna]

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!