DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: on logon trigger

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    on logon trigger

    hi,

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

    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,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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?

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by vnktummala View Post
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Aug 2009
    Posts
    21
    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

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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