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@xxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l