Re: Limit public table access

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;

> 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