Re: Question about revoking select from PUBLIC

On Sep 11, 4:26 pm, bontemps <jontan_..._at_yahoo.com> wrote:
> On Sep 11, 3:37 pm, Brian Tkatch <N/A> wrote:
>
>
>
>
>
> > On Tue, 11 Sep 2007 12:26:55 -0700, bontemps <jontan_..._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.- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks again Brian. The query you sent brought back unexpected
> results. Turns out there were only 3 out of the 100 or so tables
> which had the SELECT privilege granted to PUBLIC. So I was mytified
> how these users can see all these tables. After looking more closely,
> I saw a role that had been granted and that role had SELECT ANY table
> privilege. Problem solved. Thanks alot for the help especially in
> pointing out the dictionary. I dont use Oracle often enough to
> remember all these views- Hide quoted text -
>
> - Show quoted text -

If you do not use Oracle that often here are two short FAQ articles on the most useful rdbsm dictionary views:

How do I find information about a database object: table, index, constraint, view, etc... in Oracle ? http://www.jlcomp.demon.co.uk/faq/object_info.html

How do I find out which users have the rights, or privileges, to access a given object ? http://www.jlcomp.demon.co.uk/faq/privileges.html

HTH -- Mark D Powell -- Received on Wed Sep 12 2007 - 12:26:12 CDT