Re: Cannot compile the stored procedures with two of the three users

From: Frank <fvanbortel_at_netscape.net>
Date: Thu, 29 May 2003 15:01:39 +0200
Message-ID: <3ED604B3.5080702_at_netscape.net>

Naga Challa wrote:

> Frank <fvanbortel_at_netscape.net> wrote in message news:<3ECBE371.1000800_at_netscape.net>...
> 

>>Naga Challa wrote:
>>
>>>All,
>>>We have three different user id's on one of the Oracle Instance.
>>>But I can only compile stored procs using only one user id.
>>>When I looked into the Privileges, they all have the same as follows:
>>>
>>>select * from user_sys_privs;
>>>
>>>
>>>USERNAME PRIVILEGE ADM
>>>------------------------------ ---------------------------------------- ---
>>>PIEPROCUNIT CREATE PUBLIC SYNONYM NO
>>>
>>>select * from user_sys_privs;
>>>
>>>
>>>USERNAME PRIVILEGE ADM
>>>------------------------------ ---------------------------------------- ---
>>>PIEPROC CREATE PUBLIC SYNONYM NO
>>>
>>>select * from user_sys_privs;
>>>
>>>
>>>USERNAME PRIVILEGE ADM
>>>------------------------------ ---------------------------------------- ---
>>>PIEPROCSYS CREATE PUBLIC SYNONYM NO
>>>select * from user_role_privs;
>>>
>>>
>>>USERNAME GRANTED_ROLE ADM DEF OS_
>>>------------------------------ ------------------------------ --- --- ---
>>>PIEPROCUNIT ORA_DEV NO YES NO
>>>PIEPROCUNIT ORA_PROC NO YES NO
>>>PIEPROCUNIT PCE_READ NO YES NO
>>>PIEPROCUNIT PIE_DEV NO YES NO
>>>PIEPROCUNIT PIE_PROC NO YES NO
>>>PIEPROCUNIT PIE_WRITE NO YES NO
>>>PUBLIC PLUSTRACE NO YES NO
>>>
>>>select * from user_role_privs;
>>>
>>>
>>>USERNAME GRANTED_ROLE ADM DEF OS_
>>>------------------------------ ------------------------------ --- --- ---
>>>PIEPROC ORA_DEV NO YES NO
>>>PIEPROC ORA_PROC NO YES NO
>>>PIEPROC PCE_READ NO YES NO
>>>PIEPROC PIE_DEV NO YES NO
>>>PIEPROC PIE_PROC NO YES NO
>>>PIEPROC PIE_WRITE NO YES NO
>>>PUBLIC PLUSTRACE NO YES NO
>>>
>>>select * from user_role_privs;
>>>
>>>
>>>USERNAME GRANTED_ROLE ADM DEF OS_
>>>------------------------------ ------------------------------ --- --- ---
>>>PIEPROCSYS ORA_DEV NO YES NO
>>>PIEPROCSYS ORA_PROC NO YES NO
>>>PIEPROCSYS PCE_READ NO YES NO
>>>PIEPROCSYS PIE_DEV NO YES NO
>>>PIEPROCSYS PIE_PROC NO YES NO
>>>PIEPROCSYS PIE_WRITE NO YES NO
>>>PUBLIC PLUSTRACE NO YES NO
>>>
>>>I can only compile the stored procs use PIEPROC user id.
>>>Any ideas what I am missing for other users.
>>>
>>>Thanks in advance,
>>>
>>>Naga Challa
>>
>>For starters, why would you want to compile someone
>>else's packages?
>>
>>Another faq: roles are not enabled when executing pl/sql code. Hence you
>>need the grants, needed by the package, directly.
>>If PIEPROC owns the tables, and the packages manipulate data, you
>>would need to execute:
>>grant all on [table in question] to PIEPROCSYS;
>>as PIEPROC.
>>Repeat for all system priveleges needed - then you can compile
>>the packages using:
>>alter package pieproc.[package in question] compile;
>>as PIEPROCSYS.
>>If it doesn't work as advertised, come back, and state the error
>>codes.

> 
> 
> Frank,
> Thanks for your help.
> Even when I try to create a package with the following I am still
> having the problem.
> 
> CREATE OR REPLACE PACKAGE StaticTypes AS
>   TYPE static_cursor_type IS REF CURSOR;
> END StaticTypes;
> /
> 
> CREATE OR REPLACE PACKAGE StaticTypes AS
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> 
> Naga

You do not have the create procedure privilege; usually comes with the resource role.
 From earlier messages in this thread, you do not have this role, or it is in some of the ora_xxx roles, or you are logged on as someone not in the list...

Sort out your privileges first with whomever is responsible for them.

-- 
Regards, Frank van Bortel

Received on Thu May 29 2003 - 15:01:39 CEST