Re: DBMS_SYS_SQL Privileges..??

A copy of this was sent to "Chittesh Mehta" <chittesh_at_swi.com.sg> (if that email address didn't require changing) On Fri, 20 Aug 1999 15:28:57 +0800, you wrote:

>Hi,
>
>I have a stored procedure for creation of sequence
>and it is having following statement for creation of sequence.
>
>create sequence TEST_0
>increment by 1 start with 2
>NOMAXVALUE
>NOMINVALUE
>NOCYCLE
>NOCACHE
>
>This is executed using Dynamic SQL package DBMS_SQL
>
>When we execute statement
>DBMS_SQL.PARSE() with the above statement I get follwing error.
>
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
>ORA-06512: at "SYS.DBMS_SQL", line 32
>ORA-06512: at "MAXCARE.MX_CREATE_SEQUENCE", line 51
>ORA-06512: at line 1
>

roles are never enabled during the execution of a procedure except in the special case of Invokers Rights which is a new feature in Oracle8i, release 8.1.

This fact is documented application developers guide:

<quote>
Privileges Required to Create Procedures and Functions

To create a stand-alone procedure or function, or package specification or body, you must meet the following prerequisites:

• You must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user’s schema.

Attention: To create without errors, that is, to compile the procedure or package successfully, requires the following additional privileges: The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.

If the privileges of a procedure’s or package’s owner change, the procedure must be reauthenticated before it is executed. If a necessary privilege to a referenced object is revoked from the owner of the procedure (or package), the procedure cannot be executed.
</quote>

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

>Where MaxCare is my user name (Owner of the procedure) and
>MX_CREATE_SEQUENCE is the procedure name.
>
>I have chaked all the privileges required and looks ok.
>
>Even I have recreated PACKAGE and PACKAGE BODY for
>DBMS_SQL and DBMS_SYS_SQL.
>
>This is really a very much frustrating problem as I am not able to get any
>more information on this.
>
>Any help on this will be highly appriciated.
>
>Thanks
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 20 1999 - 07:26:32 CDT