Re: Using EXCUTE IMMEDIATE in PL/SQL

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.