SMC Enterprise Experts
SMC ENTERPRISE Is a consulting firm that implements and develops Information Technology and ERP solutions. This is what our experts has to say.

When you get nervous about access rights

May 31, 2010 12:46 by Herman

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

Related posts

Comments

Add comment


(Will show your Gravatar icon)  

  Country flag





Live preview

September 5. 2010 16:33