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.