In case you do not want to grant a user access to data dictionary tables like DBA_TABLES, but will let user B see the list of all tables belonging to user A, you can work around it with a pipelined function in schema A:
create type str_set as table of varchar2(30); / create or replace function a_tables return str_set pipelined is l_str varchar2(30); begin for l_str in (select table_name from user_tables) loop pipe row(l_str.table_name); end loop; return; end; / grant execute on a_tables to B;
Then user B can see the list of A’s table with:
select * from table(a.a_tables);
Trying to solve this with a view in schema A that selects on USER_TABLES does not work, but prove me wrong, please.