Synonyms and Oracle permissions

Greetings,

First up, I'll genuflect in front of all you gurus here and humbly beg forgiveness: I'm trying to debug somebody else's code, and despite doing the rounds on Google I don't really understand how Oracle's permissions apply in this case, so any help would be much appreciated.

So. I have two users, let's call them OWNER and USER. Running as USER, the installation
script has created the following synonym

old 1: create synonym str_array for &SCHEMA_OWNER..str_array new 1: create synonym str_array for owner.str_array Synonym created.

If I look at this in sqlplus, I can see that the synonym exists:

$ sqlplus user_at_mydb

SQL> select * from all_synonyms where owner='USER';

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
USER                         STR_ARRAY
OWNER                        STR_ARRAY

But USER doesn't seem to have access into OWNER's space:

SQL> describe str_array;
ERROR: ORA-04043: object "OWNER"."STR_ARRAY" does not exist

Of course, if I log in as 'owner', I can see the object just fine:

$ sqlplus owner_at_mydb

SQL> describe str_array
str_array TABLE OF VARCHAR2(20)
SQL> describe "OWNER"."STR_ARRAY";
"OWNER"."STR_ARRAY" TABLE OF VARCHAR2(20) So how do I grant USER access to all of OWNER's objects? In case it matters,
this is Oracle 10.2.0.2.

Cheers,
-jani Received on Fri Nov 03 2006 - 05:46:38 CST