"FC" <flavio_at_tin.it> wrote in message
news:9082c.4015$z23.160285_at_news3.tin.it...
>
> "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.
It does rather reinvent the wheel though since the supplied package
DBMS_SUPPORT allows you to start and stop trace from PL/SQL already :(
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Mon Mar 08 2004 - 04:43:44 CST
