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