Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Synonyms and Oracle permissions

Re: Synonyms and Oracle permissions

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Fri, 3 Nov 2006 13:35:18 GMT
Message-ID: <J85pqw.M6L@igsrsparc2.er.usgs.gov>


jpatokal_at_iki.fi wrote:
> Greetings,
>
> Frank van Bortel wrote:

>> jpatokal_at_iki.fi schreef:
>>> So how do I grant USER access to all of OWNER's objects?  In case it
>>> matters, this is Oracle 10.2.0.2.
>> grant all on <table_name> to user;

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

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.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
Received on Fri Nov 03 2006 - 07:35:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US