Re: alter session set sql_trace inside a procedure

"VC" <boston103_at_hotmail.com> wrote in message news:Gv42c.183522$jk2.667605_at_attbi_s53...
> Hello,
>
>
> "FC" <flavio_at_tin.it> wrote in message
> news:y522c.3026$O31.82972_at_news4.tin.it...
> > Hi all,
> > I know normally one issues alter session statements from sqlplus, but
> > anyway, why is the following statement failing with Ora-01031 when run
> > inside a pl/sql procedure?
> >
> > ...
> > Execute immediate 'ALTER SESSION SET SQL_TRACE = TRUE';
> > ...
> >
> > ORA-01031: insufficient privileges
> > ORA-06512: at "TEST.TEST_ALTER", line 6
> > ORA-06512: at line 1
> >
> >
> > Some addtional info:
> >
> > Oracle 8.1.7 (both on Solaris and Windows machines).
> > The procedure resides in a schema whose user is granted ALTER SESSION
> > privilege.
> > The procedure runs with definer rights and is run by the owner.
> > The same command succeeds when run "interactively" from sqlplus prompt.
> > If I take just the "body" of the procedure and run it as anonymous
pl/sql
> > block, it works fine.
> >
> > Here is the test procedure:
> >
> > Procedure Test_Alter
> > is
> > a number;
> > Begin
> > Execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ' ||
> chr(39)
> > || 'test' || chr(39);
> > Execute immediate 'ALTER SESSION SET SQL_TRACE = TRUE';
> > -- dbms_utility.exec_ddl_statement('ALTER SESSION SET SQL_TRACE =
> > TRUE');
> > Select count(*)
> > into a
> > from dual;
> > -- dbms_utility.exec_ddl_statement('ALTER SESSION SET SQL_TRACE =
> FALSE');
> > Execute immediate 'ALTER SESSION SET SQL_TRACE = FALSE';
> > End;
> >
> > I also tried using DBMS_UTILITY and funnily enough, it doesn't return
any
> > errors,
> > but after checking the trace file (enabled manually) it seems it doesn't
> > execute the command, just parses it!
> >
> > Bye,
> > Flavio
> >
>
>
> You have to grant the alter session privilege directly, granting via a
role
> is not sufficient:
>
> grant alter session to <user>
>
> ... or use AUTHID CURRENT_USER which is not advisable.
>
>
> VC
>
> >
>

You're right, now it works, but why is that?

Thanks,
Flavio Received on Fri Mar 05 2004 - 13:55:01 CST