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

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 Received on Tue May 27 2003 - 22:19:12 CEST