Table Privilege / Role Matrix


Just a relatively simply SQL Plus script that gives a privilege matrix of between tables and roles that each table may or may not be granted to. The output appears as below where the matrix entries are truncated to "S", "I", "U", "D" for SELECT, INSERT, UPDATE and DELETE respectively. You'll need to change (or remove) the '...' entries in the script below dependent on your requirements.

ROLE1 ROLE2
etc
TABLE1 IU SIUD
TABLE2 S SI
etc

col sortkey nopri
set lines 300
set trimspool on
col x format a300

prompt Generating SQL for worksheet
set termout off
set feedback off
set pages 0
SELECT DISTINCT 0 sortkey,
'col '||grantee||' format a'||to_char(length(grantee)+1) x
FROM dba_tab_privs
WHERE grantee LIKE '...'
AND owner = '...'
AND privilege IN ('SELECT','INSERT','UPDATE','DELETE')
union all
SELECT 1 sortkey, 'SELECT table_name, ' x FROM dual
UNION ALL
SELECT DISTINCT 2,
' MAX(DECODE(grantee,'''||grantee||''',sel||ins||upd||del,NULL)) 
'||grantee||','
FROM dba_tab_privs
WHERE grantee LIKE '...'
AND owner = '...'
AND privilege IN ('SELECT','INSERT','UPDATE','DELETE')
AND grantee < ( SELECT MAX(grantee) FROM dba_tab_privs
                     WHERE grantee LIKE '...'
                     AND owner = '...'
                     AND privilege IN ('SELECT','INSERT','UPDATE','DELETE') )
UNION ALL
SELECT DISTINCT 3,
' MAX(DECODE(grantee,'''||grantee||''',sel||ins||upd||del,NULL)) 
'||grantee
FROM dba_tab_privs
WHERE grantee LIKE '...'
AND owner = '...'
AND privilege IN ('SELECT','INSERT','UPDATE','DELETE')
AND grantee = ( SELECT MAX(grantee)  FROM dba_tab_privs
                     WHERE grantee LIKE '...'
                     AND owner = '...'
                     AND privilege IN ('SELECT','INSERT','UPDATE','DELETE') )
UNION ALL
SELECT 99999,
'FROM '||CHR(10)||
'  ( SELECT grantee, table_name, '||CHR(10)||
'       MAX(DECODE(privilege,''SELECT'',''S'',NULL)) sel,'||CHR(10)||
'       MAX(DECODE(privilege,''INSERT'',''I'',NULL)) ins,'||CHR(10)||
'       MAX(DECODE(privilege,''UPDATE'',''U'',NULL)) upd,'||CHR(10)||
'       MAX(DECODE(privilege,''DELETE'',''D'',NULL)) del'||CHR(10)||
'    FROM dba_tab_privs    '||CHR(10)||
'    WHERE grantee LIKE ''...'''||CHR(10)||
'    AND owner = ''...'''||CHR(10)||
'    AND privilege IN 
(''SELECT'',''INSERT'',''UPDATE'',''DELETE'')'||CHR(10)||
'    GROUP BY grantee, table_name'||CHR(10)||
'  ) '||CHR(10)||
'GROUP BY table_name;'
FROM dual
ORDER BY 1

spool /tmp/role_sheet.sql
/
spool off
set termout on
PROMPT Generating worksheet
set lines 10000
set trimspool on
set heading on
set pages 9999
spool /tmp/role_sheet.lst
@/tmp/role_sheet.sql
spool off