| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Using a trigger to turn on tracing
Peter,
The problem is not ALTER SESSION (which is granted to the user via CREATE role).
The problem is that the creator of the trigger needs to have CREATE ANY TRIGGER granted to it directly. PL/SQL requires direct granting and triggers are pl/sql units. This privilege is part of the DBA role, so the creation of the tigger succeeds, but the execution fails. Grant CREATE ANY TRIGGER directly and there won't be a problem. If you create this trigger as SYS, you don't run into the problem, but as any other user (including SYSTEM), you get dinged.
It's irritating in that the trigger creates without error, but the execution fails. It would be nice if Oracle would report the error on creation.
Daniel Fink
"Schauss, Peter" wrote:
> I am trying to use a trigger to turn on tracing for a specified user.
> I copied the example from Cary Millsap's _Optimizing Oracle Performance_.
>
> The text of the trigger is:
>
> create or replace trigger trace_user after logon on database
> begin
> if user = 'TEST' then
> execute immediate 'alter session set timed_statistics =
> true';
> execute immediate 'alter session set max_dump_file_size =
> unlimited';
> execute immediate
> 'alter session set events ''10046 trace name
> context forever, level 8''';
> end if;
> end;
> /
>
> When I try to log on as user TEST, I get:
>
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01031: insufficient privileges
> ORA-06512: at line 5
>
> What privilege does TEST need here?
>
> Thanks,
> Peter Schauss
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sun Feb 15 2004 - 22:30:50 CST
 |
 |