Re: Limit public table access

Thanks Andy, Yep came to this solution earlier as well. Working on implementing it. This should work fine for what I am doing.

On Tue, Mar 26, 2019 at 11:16 AM Andy Wattenhofer <watt0012_at_umn.edu> wrote:

> Maybe order of precedence can solve this. A private synonym takes
> precedence over a public one such as public.all_tables. Will this work?
>
> 1. Create a view against sys.all_tables in another schema:
> create or replace view another_schema.my_all_tables
> bequeath current_user
> as
> select * from sys.all_tables
> where owner=user;
> grant select on another_schema.my_all_tables to public; -- must be sysdba
> to grant this
>
> 2. Create a synonym for it in the user's schema:
> create synonym all_tables for another_schema.my_all_tables;
>
> Now when the user tries to select from all_tables it will hit the private
> synonym before the public synonym and it will go to
> another_schema.my_all_tables.
>
> The drawback of course is that now you have to include synonym definitions
> in your user creation template and update every user account whenever the
> synonyms change.
>
> On Tue, Mar 26, 2019 at 10:55 AM Jeff Chirco <backseatdba_at_gmail.com>
> wrote:
>
>> Well maybe the big thing is ALL_USERS and ALL_TABLES. I suppose I could
>> try to remove the privilege and see what breaks later. Nothing we have
>> personally will break, I am thinking more of when I go to patch the
>> database is when I had issues when I first tried removing things from
>> public.
>>
>> On Mon, Mar 25, 2019 at 7:23 PM Mladen Gogala <gogala.mladen_at_gmail.com>
>> wrote:
>>
>>> What exactly is granted to public? What would you like to limit the
>>> access to? And no, there is no way to prevent users from seeing things
>>> granted to public. Things granted to public are visible to every DB
>>> user. That's SQL standard. You may revoke things from PUBLIC, but
>>> whatever is granted to public is visible to everybody.
>>>
>>> On 3/12/19 1:56 PM, Jeff Chirco wrote:
>>> > Ok so we have a reporting tool for users that uses their Oracle
>>> > account. When the first connect to it and get a table list they see
>>> > all tables they have access to through PUBLIC. This obvisouly confuses
>>> > them and I would like to remove this. I've tested removing objects
>>> > from public but that didn't go well when it came time to patch the
>>> > database. A bunch of things failed. Oracle must want things granted to
>>> > public for a reason. But is there another way around to prevent some
>>> > users from seeing public?
>>> >
>>> > Thanks,
>>> > Jeff
>>>
>>> --
>>> Mladen Gogala
>>> Database Consultant
>>> Tel: (347) 321-1217
>>>
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l