Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: Tracing what sql a user runs using sql_trace...

    Hello,

    I have a problem im sure many of you recognise. I have a third party application that does stupid things and i need to see what sql it runs so I can deal with it.

    How to do this? Well, im not a real dba so i have close to no knowledge. After searching on the net i found out that my best bet is to create a login trigger that alters the users session.

    The trigger is the simplest possible:

    CREATE OR REPLACE TRIGGER fjutt_log_sql
    AFTER LOGON ON DATABASE
    BEGIN
    execute immediate 'alter session set sql_trace=true';
    END;
    /

    Problem is that after this is created when trying to log in i get the following error:

    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01031: insufficient privileges
    ORA-06512: at line 2

    Insufficient priviliges... So what priviliges are needed to do a alter session? I thought only the alter session priviligie.

    Doing a grant alter session to user; changes nothing though.

    Any help would be highly appreciated. Alternative solutions to my problem is also very welcome.

    Thanks a lot in advance.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    That whould not be a good idea, all users including database processes would trace. Bad, Bad idea. CHeck out the folowing link

    http://asktom.oracle.com/pls/ask/f?p...D:330817260752
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2003
    Posts
    9
    Mm.. I realize this. The idea was to create the trigger, click the button in the third party application and then remove the trigger (the db isnt used very heavily). I agree that its a really ugly way though.

    Reading the comments in the link you gave me made me find another user with the same problem:

    Tom says:

    the OWNER of the trigger needs to have ALTER SESSION granted directly to them.

    Will test this.

    Thanks a lot for your help!

    Best regards,

    Fredrik

  4. #4
    Join Date
    Sep 2003
    Posts
    9
    Tested it and it worked great.

    Changed the trigger to only alter the session if the right user logged on. Granted alter session to the user that created the trigger. Worked like a charm.

    Thanks a lot for your answer again.

    (trigger now looks like:

    CREATE OR REPLACE TRIGGER fjutt_log_sql
    AFTER LOGON ON DATABASE
    BEGIN
    if ( user='USERNAME') then
    execute immediate 'alter session set sql_trace=true';
    End if;
    END;
    /

    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •