Re: Question about revoking select from PUBLIC

On Tue, 11 Sep 2007 12:26:55 -0700, bontemps <jontan_mlr_at_yahoo.com> 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?

ALL_TAB_PRIVS SELECT
        Grantor
FROM
        All_Tab_Privs
WHERE

	Grantee		= 'PUBLIC'
     AND	PRIVILEGE	= 'SELECT'
     AND	TABLE_SCHEMA = ''
     AND	TABLE_NAME = '' 

All these views are in the Database Reference "2 Static Data Dictionary Views".

B. Received on Tue Sep 11 2007 - 14:37:38 CDT