Re: execute privileges error

L. Tseng writes:

> I created the following procedure under SYSTEM
> but keep getting the following error
>
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
> ORA-06512: at "SYS.DBMS_SQL", line 25
> ORA-06512: at "SYSTEM.TEST_DBMSSQL", line 7
> ORA-06512: at line 1
>
> SYSTEM has been granted execute any procedure privilege but
> it does not help.
>
> The procedure would only work when created under SYS schema
> and logging in as SYS.
>
> What is wrong???
>
>
> create or replace procedure test_dbmssql
> as
> cursor_name NUMBER;
> ignore NUMBER;
> BEGIN
> cursor_name := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_name,'drop user Austin', DBMS_SQL.V7);
> ignore := DBMS_SQL.EXECUTE(cursor_name);
> END;
>

You have to grant the execute privilege to user SYSTEM explicitly. Something like a design decision of Oracle to improve security.

BTW, please close the cursor after execute, even better, add an exception handler to close the cursor in case of errors and reraise the exception if appropriate.

Stephan Witt

<stephan.witt_at_beusen.de>  | "beusen" Software+Systeme GmbH
fon: +49 30 549932-62     | Landsberger Allee 392
fax: +49 30 549932-21     | 12861 Berlin
---------------------------------------------------------------