Home » SQL & PL/SQL » SQL & PL/SQL » Privileges for using execute immediate or dbms_sql for DDL in Pl/SQL block
Privileges for using execute immediate or dbms_sql for DDL in Pl/SQL block [message #229540] Mon, 09 April 2007 02:00 Go to next message
ataufique
Messages: 79
Registered: November 2006
Member
Is there anyone who knows what privileges are required to
execute DDL in a Pl/Sql block using either execute immediate
or dbms_sql.
..Same code get created & executed when connected as SYS.

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "EDCH.TTEST", line 11
ORA-06512: at line 1

The sample script is below

create or replace procedure ttest as
sqlstr VARCHAR2(200);
tCursor PLS_INTEGER;
RetVal NUMBER;
begin
sqlstr := 'create table ttest1 (ename varchar2(10))';
tCursor := dbms_sql.open_cursor;
dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
RetVal := dbms_sql.execute(tCursor);
dbms_sql.close_cursor(tCursor);
end;
/
Re: Privileges for using execute immediate or dbms_sql for DDL in Pl/SQL block [message #229554 is a reply to message #229540] Mon, 09 April 2007 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The privilege is the privilege to execute the statement but this privilege must have been granted directly not via a role.

(Notice that you should have a very good reason to make DDL inside a procedure.)

Regards
Michel
Re: Privileges for using execute immediate or dbms_sql for DDL in Pl/SQL block [message #229558 is a reply to message #229554] Mon, 09 April 2007 02:17 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
yes i am granting the privi to the user directly as i know that
it doesnot work for a role..
pls suggest..
Re: Privileges for using execute immediate or dbms_sql for DDL in Pl/SQL block [message #229559 is a reply to message #229558] Mon, 09 April 2007 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Suggest what?
It does not work with a role.
It works if it is granted directly.
This is how it works.

But the real question is: do you really need to create a table through a procedure?

Regards
Michel
Re: Privileges for using execute immediate or dbms_sql for DDL in Pl/SQL block [message #229579 is a reply to message #229559] Mon, 09 April 2007 04:30 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
Yes i need to use it in a plsql..
now what will be the grant statement..
Re: Privileges for using execute immediate or dbms_sql for DDL in Pl/SQL block [message #229583 is a reply to message #229579] Mon, 09 April 2007 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
grant create table! what else?

You affirm you need it.
I affirm that 99% people don't, they just have a bad design and try to program Oracle as they program Sybase or MS/SQL Server.

Regards
Michel
Re: Privileges for using execute immediate or dbms_sql for DDL in Pl/SQL block [message #229772 is a reply to message #229583] Tue, 10 April 2007 00:29 Go to previous message
ataufique
Messages: 79
Registered: November 2006
Member
Thanks...
I go by ur word, i wont be using the same...
Previous Topic: calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL
Next Topic: generate and xml file
Goto Forum:
  


Current Time: Fri Dec 09 11:59:06 CST 2016

Total time taken to generate the page: 0.05362 seconds