Using a trigger to turn on tracing 2004-02-15 - By Daniel W. Fink
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 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1
> ORA-01031 (See ORA-01031.ora-code.com): insufficient privileges
> ORA-06512 (See ORA-06512.ora-code.com): 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@(protected)
> 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
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
