Re: alter session set sql_trace inside a procedure

"VC" <boston103_at_hotmail.com> wrote in message news:eR72c.184716$jk2.668490_at_attbi_s53...
> Pls. see below:
>
> "FC" <flavio_at_tin.it> wrote in message
> news:pW42c.3330$z23.119630_at_news3.tin.it...
> >
> > "VC" <boston103_at_hotmail.com> wrote in message
> > news:Gv42c.183522$jk2.667605_at_attbi_s53...
> > You're right, now it works, but why is that?
>
> During a procedure execution with the default definer rights, all the
roles
> are disabled.(for more information pls. see the PL/SQL manual or
> http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html ).
>
> If roles were enabled for the definer right procedures, it would mean
that
> the number of cases when a procedure is invalidated would be greatly
> increased. Virtually any time any role is modified, many procedures
would
> need to be recompiled. Clearly, a situation like this is undesirable.
> >
> > Thanks,
> > Flavio
> >
> >
>

I see...
I tried wrapping the relevant dynamic sql in a procedure compiled as user SYSTEM and then granting execute to the specific user and it works (after granting ALTER SESSION to SYSTEM).
I cannot say if this workaround is better than assigning the privilege to the individual user.

Thanks and bye,
Flavio Received on Fri Mar 05 2004 - 17:25:57 CST