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
Received on Tue Sep 11 2007 - 15:26:38 CDT
