Re: What privilege needs to be granted?

Hans Forbrich <forbrich_at_yahoo.net> wrote in message news:<3FF46FDC.87E771DF_at_yahoo.net>...
> Ping wrote:
> >
> > Hello,
> >
> > Have a wonderful new year!
> >
> > I have oracle 9.2 on XP.
> >
> > There is a table T1 created by user U1 in U1's schema,
> > a synonym S1 created also by U1 to point to T1 in U1's schema,
> > U1 granted S1 to public for select.
> >
> > Procedure P2 created by user U2 in U2's schema,
> > in P2, the S1 is referenced for select in a cursor.
> >
> > Problem and question:
> > when P2 is compiled, got an error message saying that not sufficient
> > privilege. I tried a set of privileges which worked on oracle 8.1.6,
> > but not
> > on oracle 9.2 I memtioned above.
> >
> > What is the privilege needed to be granted or something else needs to
> > be done other than just granting privileges?
> >
> > Thanks in advance.
> >
> > Ping
>
> What other tests have you performed? Can U2 select data from S1? How
> about from U1.T1?
>
> Please post the exact message, especially the error number.
>
> First thought is that perhaps U2 does not have capability to compile.
> However, insufficient info provided to verify.
>
>
> /Pong

Thanks Hans.

Sorry, some modification to the problem description: the T1 in fact is a view and T1's base tables are all owned by sys. The T1 is successfully compiled in U1's schema.

U2 has 'create procedure' privilege granted in addition to some other privileges.

I also have U1 granted select specifically to U2, still got same error message as below when compiled:

PL/SQL: Declaration ignored
PLS-00904: insufficient privilege to access object U1.T1   

Any thoughts and help would be appreciated.

Ping Received on Fri Jan 02 2004 - 06:04:58 CET