all tables users can access and vice versa?

Help. It would seem that I am trying to do the impossible, but I am close enough to think it can be done. I am looking for a way to create a view that will show me all the tables a user has access to and conversely a view which would show me all the users with access to each table. These don't need to be the same view. In it's most simple form, I would like a view with the structure Username, Table_Owner, Table_Name. I would like this view to show me all users and all tables.

Using the SQL below, I can show all the tables a specific userid has access to. I would like a view which would shows this for every userid and show the userid in the data itself. This SQL also happens to show the role that was responsible for the grant to the user, but that is not necessary, if it complicates things.

select distinct 'TEST_USER' userid, table_name, owner, grantee via from dba_tab_privs tab
where tab.grantee in (
SELECT distinct substr(dbautil.dba_sysobj.user_name(privilege#),1,30) role
FROM sys.sysauth$ auth
WHERE auth.privilege# > 0 START WITH auth.grantee# = (select user# from sys.user$ where name = 'TEST_USER')
CONNECT BY auth.grantee# = prior auth.privilege#) order by table_name;

Any help would be very appreciative.

--
Mike Glasser

DBA - OIT, Operations & Enterprise Applications University of Maryland
mglasser_at_accmail.umd.edu Received on Mon Nov 15 1999 - 14:27:58 CST