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