what are the privileges to be granted to execute a procedure?

Hi Friends,

I created a procedure as follows

SQL> create procedure proctab (table_name in varchar2,column1 in varchar2,column2 in varchar2)
2 as
3 begin
4 execute immediate 'create table ' || table_name || ' (' || column1 || ' varchar2(10),'
5 || column2 || ' varchar2(10))';
6 end;
7 /

Procedure created.

when i am trying to execute the above procedure its giving the following error


SQL> execute proctab('test10','name','city');
BEGIN proctab('test10','name','city'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.PROCTAB", line 4
ORA-06512: at line 1


i connected as system/manager
i think "system" has all(create procedure , execute any procedure) the privileges. but the above statement giving the error that " insufficient privileges .

is there any special privileges to be granted when we are using the dynamic sql ? Because i have used dynamic sql in the procedure.

please tell me what are the additional privileges that i have to grant to system? and how to grant the privileges to "system" . because to grant the privileges first we have to connect to the database know, so by what user shall i connect to the datbase to grant the privileges to "system"(system has the dba role) .



please help me


regards

SrinivasM



[Edited by srinivasm on 01-09-2001 at 10:25 PM]

EXECUTE ANY PROCEDURE system privilege can execute any procedure in the database

It is not the problem with the privileges of the user who is executing the procedure. As long as he has permision to execute this procedure, either through role or directly, he is OK.

The problem is with the privileges of the owner of the procedure, in this case user SYSTEM. Although he has almost unlimited privileges on the database, thos privileges has been granted to him through role (DBA role). Hovewer roles are disabled during execution of the procedures, hence insuficient privileges to create the table.

Simply GRANT CREATE TABLE TO SYSTEM (connected as any user with DBA role, it can even be SYSTEM himself), recompile the procedure and you'll be able to execute this procedure.

Bevare, no matter which user will be executing this procedure, table will always be created under schema SYSTEM, that is undret the schema who owns the procedure.

HTH,