Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: alter session set sql_trace inside a procedure

Re: alter session set sql_trace inside a procedure

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 8 Mar 2004 10:43:44 -0000
Message-ID: <404c4e60$0$19234$ed9e5944@reading.news.pipex.net>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US