Use this view as an example to create your own to see who has access to what:
create or replace view table_privs as
SELECT r.GRANTEE, r.owner, r.table_name, rle, granted_role, direct , r.grantor as role_grantor, d.grantor as direct_grantor, r.privilege, r.grantable
FROM
(
SELECT rp.GRANTEE, 'Y' as RLE, rp.GRANTED_ROLE, tp.OWNER, table_name, grantor, privilege, grantable
FROM DBA_TAB_PRIVS tp, dba_role_privs rp
WHERE
tp.GRANTEE = rp.GRANTED_ROLE
and tp.OWNER IN ('EDW','EDW_P','SECURE')
AND tp.GRANTEE NOT IN (SELECT USERname FROM DBA_USERS)) r
,
(SELECT GRANTEE, 'Y' as DIRECT, owner, table_name, grantor, privilege, grantable
FROM DBA_TAB_PRIVS WHERE OWNER IN ('EDW','EDW_P','SECURE')
AND GRANTEE IN (SELECT USERname FROM DBA_USERS)) d
WHERE
r.grantee = d.grantee (+)
and
r.table_name = d.table_name (+)
and
r.privilege = d.privilege (+)
UNION
SELECT d.GRANTEE, d.owner, d.table_name, rle, granted_role, direct , d.grantor as rg, d.grantor as dg, d.privilege, d.grantable
FROM
(
SELECT rp.GRANTEE, 'Y' as RLE, rp.GRANTED_ROLE, tp.OWNER, table_name, grantor, privilege, grantable
FROM DBA_TAB_PRIVS tp, dba_role_privs rp
WHERE
tp.GRANTEE = rp.GRANTED_ROLE
and tp.OWNER IN ('EDW','EDW_P','SECURE')
AND tp.GRANTEE NOT IN (SELECT USERname FROM DBA_USERS)) r
,
(SELECT GRANTEE, 'Y' as DIRECT, owner, table_name, grantor, privilege, grantable
FROM DBA_TAB_PRIVS WHERE OWNER IN ('EDW','EDW_P','SECURE')
AND GRANTEE IN (SELECT USERname FROM DBA_USERS)) d
WHERE
d.grantee = r.grantee (+)
and
d.table_name = r.table_name (+)
and
d.privilege = r.privilege (+)
ORDER BY GRANTEE, TABLE_NAME, PRIVILEGE;
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5