Re: Problem with DBMS SQL in stored procedures

Jairam, Kuppuswamy, nck_at_hotmail.com wrote:
>
> I tried executing a ddl statement using dbms_sql. this works in an anonymous
> plsql block, but while trying to do the same in a stored procedure
> i get the error of insufficient privileges.
>
> What i am trying to do is as follows
>
> SQL> declare
> 1 cid integer
> 2 begin
> 3 cid := dbms_sql.open_cursor;
> 4 dbms_sql.parse(cid,'create table jai(ram char(3))',dbms_sql.native);
> 5 dbms_sql.close_cursor(cid);
> 6 end;
> 7 /
>
> PL/SQL Procedure executed successfully.
>
> SQL> create or replace procedure proc1 as
> 1 cid integer;
> 2 begin
> 3 cid := dbms_sql.open_cursor;
> 4 dbms_sql.parse(cid,'create table jai(ram char(3))',dbms_sql.native);
> 5 dbms_sql.close_cursor(cid);
> 6 end;
> 7 /
>
> Procedure Created.
>
> SQL> execute proc1
> begin proc1; end;
>
> *
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
> ORA-06512: at "SYS.DBMS_SQL" , line 25
> ORA-06512: at "PROC1", line 5
> ORA-06512: at line 1
>
> SQL>
>
> I would appreciate if anyone could solve this problem of mine.
> I tried this on a user with DBA privileges but with the same result.
> It only works for the SYS account.
>
> Thanks in advance
>
> Jairam Kuppuswamy
> nck_at_hotmail.com

For security reasons (I can't remember why) privileges granted through roles are disabled when running through dynamic SQL in stored procedures. You will need the privileges granted directly (eg "grant create any table to user").

Cheers,

Finn

-- 
--------------------------------------------------------------------------------
 Finn Ellebaek Nielsen          Oracle Associated Senior Consultant
 Ellebaek Consulting            E-mail:           ellebuk_at_post3.tele.dk
 Niels Ebbesens Vej 9, 3. th.   Mobile Phone:     +45 20 32 49 25
 DK-1911  Frederiksberg C       Mobile Phone SMS: 20324925_at_sms.tdm.dk (Subject)
 Denmark                        Private Phone:    +45 33 25 34 50
--------------------------------------------------------------------------------
 "Life is a beach and then you dive"                      "Divers do it deeper"