Re: Question about revoking select from PUBLIC

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 11 Sep 2007 15:02:54 -0500
Message-ID: <46e6e875$0$16299$88260bb3@free.teranews.com>

bontemps wrote:

> On Sep 11, 3:08 pm, Brian Peasland <d..._at_nospam.peasland.net> wrote:

>> bontemps wrote:
>>> All of our tables have PUBLIC synonyms and of course PUBLIC has SELECT
>>> privileges on all of them. How can I make some of them visible ONLY to
>>> certain users?
>> You'll have to revoke SELECT access from PUBLIC and then grant SELECT
>> access only to those that need to see the table.
>>
>> > If I drop the PUBLIC synonyms, nobody except the owner
>>
>>> can see them.
>> Actually, if you drop the public synonym, they will still be able to
>> access the table. That is because PUBLIC has been granted SELECT access
>> on the table. They will just need to use schema-dot notation to access
>> the table, i.e. they need to prepend the table name with the owner and a
>> period as in SCOTT.EMP.
>>
>> > If I keep the PUBLIC synonyms everyone sees them. We
>>
>>> know that we can drop the synonym and include the schema owner when
>>> referencing the table name but we dont want to rewrite the application
>>> to insert them.
>> The PUBLIC syn does not let them query the table. The SELECT priv
>> (object privilege) does.
>>
>> HTH,
>> Brian
>>
>> --
>> ===================================================================
>>
>> Brian Peasland
>> d...@nospam.peasland.nethttp://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
>>
>> --
>> Posted via a free Usenet account fromhttp://www.teranews.com

> 
> Thanks you for your response, Brian.  You suggest that I revoke SELECT
> on these tables from PUBLIC and grant select on these tables only to
> chosen users.  Then prepend the schema owner.  I did try that first
> and got error message ORA-01927: cannot REVOKE privileges you did not
> grant.  I am sure that either I or the owner did the grant and I dont
> know where in the data dictioanry to find grants to PUBLIC.  Any
> ideas?
> 

If you did not grant these privileges, then you won't be able to revoke them. Try revoking them as the SYSTEM user. Or query DBA_OBJ_PRIVS to see who did grant them and then revoke with that user.

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

-- 
Posted via a free Usenet account from http://www.teranews.com

Received on Tue Sep 11 2007 - 15:02:54 CDT