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

jpatokal (AT) iki (DOT) fi schreef:


grant all on <table_name> to user;

which is documented, of course, at http://tahiti.oracle.com

Greetings,

Frank van Bortel wrote:
Thanks for the fast reply! That was so obvious that I stupidly assumed
it wouldn't work
on synonyms, but evidently it does (d'oh). Is there any easy way to
grant USER access
to everything owned by OWNER, or do I need to do it one by one?

Cheers,
-jani

jpatokal (AT) iki (DOT) fi wrote:
You are not granting access to the synonym. Rather, you are granting
access on the object. The synonym just points to that object. When USER
references the synonym, Oracle translates this to the real object and
then checks to see if USER has permission's on that object.

There is not easy way to grant everything owned by OWNER to another user
or role. You'll have to do this one by one. But you can generate a quick
script as follows:

SELECT 'GRANT all ON '||table_name||' TO user;'
FROM dba_objects WHERE owner='OWNER';

Spool the results to a file and then run that file.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

On Nov 3, 1:56 pm, jpato... (AT) iki (DOT) fi wrote:
One by one, for the obvious security reasons.
Do not grant anything you do not need to grant (in that light,
better replace "all" by "select" in my previous post)

<jpatokal (AT) iki (DOT) fi> schreef in bericht
news:1162558606.256614.257940 (AT) b28g2000cwb (DOT) googlegroups.com...
There is: tell USER the password of OWNER ;-)