on logon trigger

hi,

I have created a logon trigger to trace one particular user when he connects do DB. Here is the script.

Quote:

---

CREATE OR REPLACE TRIGGER ON_LOGON_USER
AFTER LOGON ON DATABASE
WHEN ( USER = 'USER' )
BEGIN
execute immediate 'alter session set tracefile_identifier="trace"';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

---

This trigger is working if I am login with USER from the DB Server. But It is not working when the USER is connecting from application.

I am trying to understand what is going wrong. Any help is appreciated.

by the way, here is the environment.

Windowz 2003 SP2
Oracle 10G R2
Application: Remedy

Thanks,

well you have an exception putting everything to null, why would you do that?

and you sure you have got the right user for the schema?

Quote:

---

Originally Posted by vnktummala

This trigger is working if I am login with USER from the DB Server. But It is not working when the USER is connecting from application.

---

Many applications tend to connect to the database using a generic account instead of using the individual user account. Check which Oracle account is used by the application.

Invoke DBMS_SESSION.SET_IDENTIFIER procedure when session logs(logon trigger) to set client_identifer ,you could distinguish the session with either IP,OSUSER,HOSTNAME and make use of dbms_contxt for calling the user environment.

Hope this helps you..

http://www.databasejournal.com/featu...ing-Part-I.htm

Hi Dave -

I will remove NULL and put some thing to print and see if it is printing any exception.

Yes, I have got the right user for the schema. As I said, If i login from DB server the trigger is firing.

Hi PAVB -

Yes, application is using the same account to connect to DB.

Hi Rayyah -

The one you advised is some thing new to me. I will read the link provided by you and see it that is useful for me.

Thank you all for your inputs.

Thanks,