| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: all tables users can access and vice versa?
That's not an easy one.
You have to take into account all the tables owned by a user,
all tables that access is granted to the user or to one of his
roles or to public or to one of the roles granted to public,
and all tables if one of the system privileges "DELETE ANY TABLE",
"INSERT ANY TABLE", "UPDATE ANY TABLE", "SELECT ANY TABLE" or
"LOCK ANY TABLE" is granted to the user or to one of his roles or
to public or to one of the roles granted to public.
If you are not in a hurry to get your result, you can use
the following view that gives you for all users (column username),
the owner and the name of the accessible tables (columns
owner and table_name) and the origin of the access privilege
(column why) with:
<Owner> if the user is the owner of the table
Object Priv. if the access is via an object privilege
System Priv. if the access is via a system privilege
<Public> if the privilege or the role is granted to public
<Private> if the privilege or the role is granted to the user.
Create or replace view my_all_tables as
Select ou.name username, ou.name owner, o.name table_name, '<Owner>' why
from sys.obj$ o, sys.tab$ t, sys.user$ ou
where t.obj# = o.obj#
and o.owner# = ou.user#
Union /* object privilege on a table */
Select op.username, ou.name owner, o.name table_name, op.why why
from sys.obj$ o, sys.tab$ t, sys.user$ ou,
( select uu.name username, o.obj# obj#, 'Object Priv. / '||r.name why
from sys.obj$ o, sys.sysauth$ sa, sys.user$ r, sys.user$ uu
where ( o.obj# in ( select oa.obj#
from sys.objauth$ oa
where oa.grantee# in
( select privilege#
from sys.sysauth$
where privilege# > 0
connect by grantee# = prior privilege#
start with grantee# = r.user#) )
or o.obj# in ( select obj#
from sys.objauth$
where grantee# = r.user#) )
and r.type = 0
and r.user# = sa.privilege#
and sa.grantee# = uu.user#
union
select uu.name username, o.obj#, 'Object Priv. / <Public> / '||r.name
from sys.obj$ o, sys.sysauth$ sa, sys.user$ r, sys.user$ uu
where ( o.obj# in ( select oa.obj#
from sys.objauth$ oa
where oa.grantee# in
( select privilege#
from sys.sysauth$
where privilege# > 0
connect by grantee# = prior privilege#
start with grantee# = r.user#) )
or o.obj# in ( select obj#
from sys.objauth$
where grantee# = r.user#) )
and r.type = 0
and r.user# = sa.privilege#
and sa.grantee# = 1
union
select uu.name username, o.obj#, 'Object Priv. / <Private>'
from sys.obj$ o, sys.user$ uu
where o.obj# in ( select obj#
from sys.objauth$
where grantee# = uu.user#)
union
select uu.name username, o.obj#, 'Object Priv. / <Public>'
from sys.obj$ o, sys.user$ uu
where o.obj# in ( select obj#
from sys.objauth$
where grantee# = 1) ) op
where t.obj# = o.obj#
( select uu.name username, 'System Priv. / '||r.name why
from sys.sysauth$ sa, sys.sysauth$ sar, sys.user$ r, sys.user$ uu
where ( sar.privilege# in ( select privilege#
from sys.sysauth$
where privilege# > 0
connect by prior grantee# = privilege#
start with privilege# in
(-45, -47, -48, -49, -50) )
or sar.privilege# in (-45, -47, -48, -49, -50) )
and r.type = 0
and r.user# = sar.grantee#
and r.user# = sa.privilege#
and sa.grantee# = uu.user#
union
select uu.name username, 'System Priv. / <Public> / '||r.name
from sys.sysauth$ sa, sys.sysauth$ sar, sys.user$ r, sys.user$ uu
where ( sar.privilege# in ( select privilege#
from sys.sysauth$
where privilege# > 0
connect by prior grantee# = privilege#
start with privilege# in
(-45, -47, -48, -49, -50) )
or sar.privilege# in (-45, -47, -48, -49, -50) )
and r.type = 0
and r.user# = sar.grantee#
and r.user# = sa.privilege#
and sa.grantee# = 1
union
select uu.name username, 'System Priv. / <Private>'
from sys.sysauth$ sa, sys.user$ uu
where sa.privilege# in (-45, -47, -48, -49, -50)
and sa.grantee# = uu.user#
union
select uu.name username, 'System Priv. / <Public>'
from sys.sysauth$ sa, sys.user$ uu
where sa.privilege# in (-45, -47, -48, -49, -50)
and sa.grantee# = 1 ) sp
--
Have a nice day
Michel
Mike Glasser <mglasser_at_accmail.umd.edu> a écrit dans le message : 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 Tue Nov 16 1999 - 07:11:39 CST
 |
 |