Daniel, PL/SQL does not require direct granting; stored objects, including views, do. An anonymous block using privileges granted via a role will work: oet@PBD1> select * from user_sys_privs; USERNAME PRIVILEGE ADM ------------------------------ ---------------------------------------- --- OET CREATE SESSION NO OET SELECT ANY DICTIONARY NO oet@PBD1> begin 2 execute immediate 'alter session set sql_trace=true'; 3 end; 4 / PL/SQL procedure successfully completed. oet@PBD1> create procedure foo as 2 begin 3 execute immediate 'alter session set sql_trace=false'; 4 end; 5 / Procedure created. oet@PBD1> exec foo BEGIN foo; END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "OET.FOO", line 3 ORA-06512: at line 1 --- "Daniel W. Fink" <Daniel.Fink@xxxxxxx> wrote: > 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@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- __________________________________ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------