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?
Received on Tue Sep 11 2007 - 14:26:55 CDT
