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:

>
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

[Ping]

Re: What privilege needs to be granted?

Hans Forbrich <forbrich@yahoo .netwrote in message news:<3FF46FDC. 87E771DF@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

[Hans Forbrich]

Re: What privilege needs to be granted?

Ping wrote:

>
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.

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:

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

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:

>
In schema U2, the select count(*) from U1.T1 gives following error message:
>
ERROR at line 1:
ORA-01031: insufficient privileges

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:

>
In schema U2, the select count(*) from U1.T1 gives following error message:
>
ERROR at line 1:
ORA-01031: insufficient privileges

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:

Ping wrote:
>

>>In schema U2, the select count(*) from U1.T1 gives following error message:
>>
>>ERROR at line 1:
>>ORA-01031: insufficient privileges

>
>
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)

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