What privilege needs to be granted?

[Ping]

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

[Hans Forbrich]

Re: What privilege needs to be granted?

Ping wrote:[color=blue]
>
> 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[/color]

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

[Ping]

Re: What privilege needs to be granted?

Hans Forbrich <forbrich@yahoo .net> wrote in message news:<3FF46FDC. 87E771DF@yahoo. net>...[color=blue]
> Ping wrote:[color=green]
> >
> > 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[/color]
>
> 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[/color]


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

[Hans Forbrich]

Re: What privilege needs to be granted?

Ping wrote:[color=blue]
>
> 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.[/color]

I'm still wondering whether U2 can do a plain and simple 'SELECT
COUNT(*) FROM U1.T1' and 'SELECT COUNT(*) FROM S1' in SQL*Plus.

I wouldn't even worry about the PL/SQL part of it until the basic access
for the SELECT is set up properly.

/Hans

[Frank van Bortel]

Re: What privilege needs to be granted?

Ping wrote:
[color=blue]
> 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[/color]

Sigh... Roles are *not* active during PL/SQL execution, as mentioned
many times in this ng.
For you to search the archives (or the documentation) for the solution
--
A prosperous 2004,
Regards,
Frank van Bortel

[Ping]

Re: What privilege needs to be granted?

In schema U2, the select count(*) from U1.T1 gives following error message:

ERROR at line 1:
ORA-01031: insufficient privileges

[Hans Forbrich]

Re: What privilege needs to be granted?

Ping wrote:[color=blue]
>
> In schema U2, the select count(*) from U1.T1 gives following error message:
>
> ERROR at line 1:
> ORA-01031: insufficient privileges[/color]

Have you done a 'grant select' from U1 on T1 to U2?

What ROLES do U1 and U2 have?

[Hans Forbrich]

Re: What privilege needs to be granted?

Ping wrote:[color=blue]
>
> In schema U2, the select count(*) from U1.T1 gives following error message:
>
> ERROR at line 1:
> ORA-01031: insufficient privileges[/color]

Your problem is therefore NOT in the PL/SQL routine. It is a basic
accessibility problem.

Now might be a good time to reread the following documentation:

Oracle9i Database Concepts: Chapter 23
Oracle9i Database Administrator's Guide: Chapters 25, 23 & 24

both being available at http://docs.oracle.com
(http://otn.oracle.com/pls/db92/db92....emark=homepage)

[Frank van Bortel]

Re: What privilege needs to be granted?

Hans Forbrich wrote:
[color=blue]
> Ping wrote:
>[color=green]
>>In schema U2, the select count(*) from U1.T1 gives following error message:
>>
>>ERROR at line 1:
>>ORA-01031: insufficient privileges[/color]
>
>
> Your problem is therefore NOT in the PL/SQL routine. It is a basic
> accessibility problem.
>
> Now might be a good time to reread the following documentation:
>
> Oracle9i Database Concepts: Chapter 23
> Oracle9i Database Administrator's Guide: Chapters 25, 23 & 24
>
> both being available at http://docs.oracle.com
> (http://otn.oracle.com/pls/db92/db92....emark=homepage)[/color]

Even worse, then - wonder what the "set of privileges which worked on
oracle 8.1.6" were?
--
A prosperous 2004,
Regards,
Frank van Bortel

[Ping]

Re: What privilege needs to be granted?

> Even worse, then - wonder what the "set of privileges which worked on[color=blue]
> oracle 8.1.6" were?[/color]

Frank, I have the following set for the schema U2 as mentioned in the
above conversation:

ALTER ANY PROCEDURE
ALTER ANY TRIGGER
ALTER SYSTEM
CREATE ANY PROCEDURE
CREATE ANY TRIGGER
CREATE PUBLIC SYNONYM
DROP ANY PROCEDURE
DROP ANY TRIGGER
DROP PUBLIC SYNONYM
EXECUTE ANY PROCEDURE
GRANT ANY PRIVILEGE
SELECT ANY TABLE
UNLIMITED TABLESPACE


Just got oracle 9.2 on XP working after granting the basetables of T1
with grant option, as stated in the Oracle 9i concept:

Additionally, in order to grant other users access to your view, you
must have received object privileges to the base objects with the
GRANT OPTION clause or appropriate system privileges with the ADMIN
OPTION clause. If you have not, grantees cannot access your view.

Thanks, Hans, for pointing out the excact chapters in the documents.

Ping