Sunday, September 27, 2015

Function to let user B see all tables of user A



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.