Function to let user B see all tables of user A

September 27, 2015

oracle, pipelined, SQL

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.