Re: Using EXCUTE IMMEDIATE in PL/SQL

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/20
Message-ID: <8io3ut$d94$1@nnrp1.deja.com>#1/1

In article <8io30i$cd2$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> In article <8im0gh$253$1_at_nnrp1.deja.com>,
> ddf_dba_at_my-deja.com wrote:
> > In article <8ijs1g$d32$1_at_perki.connect.com.au>,
> > "Tom Zamani" <tomz_at_redflex.com.au> wrote:
> > > Do you have grant to create table?
> > > Tom
> > > Alexandros Kotsiras, NYC <alexandros_k_at_prodigy.net> wrote in
 message
> > > news:394D6F73.C4005C54_at_prodigy.net...
> > > > CREATE OR REPLACE PROCEDURE TEST_PROC AS
> > > > begin
> > > > execute immediate 'create table TEST_1 as select * from TEST ' ;
> > > > end;
> > > >
> > > > I receive :
> > > > ORA-01031 Insufficient privileges
> > > >
> > > > Why that ? ?
> > > > I do not have problems with procedures that do not have "execute
> > > > immediate" inside.
> > > > I am the owner of the procedure and the TEST table.
> > > > If i execute the create table statement directly from SQL*Plus
 it
 works
> > > > fine ....
> > > >
> > > > The description of the error message in the documentation
 doesn't
 really
> > > > help.
> > > >
> > >
> > >
> >
> > I have tried this and the only user account that can create this
> > procedure and allow it to execute without failure is SYS.
 Apparently
> > there are inherent permissions to the sysdba user that are missing
 and
> > not otherwise available or grantable to any other user account, DBA
 or
> > not. If you absolutely must have 'execute immediate' in your
 procedures
> > you will likely need to create them with the SYS account (connect
> > internal in Server Manager) then create public synonyms for them and
> > grant execute to public on them.
> >
> > --
> > David Fitzjarrell
> > Oracle Certified DBA
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >

>

> No, that is not true at all.

>

> See
> http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

>

> all the user in the above needs is to either:

>

> o grant create table to PROCEDURE_OWNER;

>

> or

>

> o create the procedure with "AUTHID current_user" so the procedure
> executes with invokers rights (new in 8i) and roles are enabled

>

> don't use SYS.

>

> --
> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> Howtos and such: http://osi.oracle.com/~tkyte/index.html
> Oracle Magazine: http://www.oracle.com/oramag
> Opinions are mine and do not necessarily reflect those of Oracle Corp

>

> Sent via Deja.com http://www.deja.com/
> Before you buy.

>

Learn something new every day. :)

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Jun 20 2000 - 00:00:00 CDT