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

From: Frank <fvanbortel_at_netscape.net>
Date: Wed, 21 May 2003 22:37:05 +0200
Message-ID: <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.

-- 
Regards, Frank van Bortel
Received on Wed May 21 2003 - 22:37:05 CEST

Original text of this message