Is there a way to get a list of tables and views that a specific user can see, without logging in as that user? Initially, I tried something like
select distinct p.table_name as value from dba_tab_privs p join dba_synonyms s on p.owner = s.table_owner and p.table_name = s.table_name and s.owner = 'PUBLIC' join all_objects o on p.owner = o.owner and p.table_name = o.object_name and o.object_type in ('TABLE', 'VIEW') where p.privilege = 'SELECT' and p.grantee = :user order by p.table_name
However, while this would show tables and views that a specific user had been explicitly granted select on, it didn't seem to accurately reflect the full range of visible tables and views a user could see upon connecting to the database. For example, one user I tested had a little over 2000 tables and views visible when selecting on
all_views, but only reflected 300 records using the above query.