Re: all tables users can access and vice versa?

Did you ever look at the standard dictionary view ALL_TABLES? That already does what you want to accomplish in your first view.

Hth,

--
Sybrand Bakker, Oracle DBA
Mike Glasser <mglasser_at_accmail.umd.edu> wrote in message news:38306CCE.DC9B70C7_at_accmail.umd.edu...
> 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 - 15:32:00 CST