/* COLGRANT.SQL */

/* Lists all direct grants made against all columns of a specific table or  */
/* view, segregated by the nature of the grant recipient.  This report is   */
/* a complete listing of all methods by which the columns may be accessed.  */
/* Used to research the potential impact of revokes against columns that    */
/* have multiple access paths that lead to it.                              */

/* Input:  A user name and the name of a table or view owned by that user */

/* Output Column Legend: */
/*   LEVEL indicates whether the permission on the column was issued to */
/*         PUBLIC, a role, or directly to a user */
/*   PERMISSION contains the name of the privilege granted against the column */

set pagesize 66
set verify off
set feedback off
column column_name format A30
column lvl format A8 Heading "Level"
column grantee format A27
column privilege format A27
column grantable format A4 heading "Gtbl"
ttitle left 'COLGRANT - Direct Grants made on cols to '&3' for '&1 skip 2
spool colgrant.lst
set termout off
SELECT c.column_name, 'Role' lvl, c.grantee, c.privilege, c.grantable
FROM dba_col_privs c
WHERE c.owner = UPPER('&2') AND c.table_name = UPPER('&3') AND
      c.grantee IN (SELECT role FROM dba_roles WHERE role = c.grantee)
UNION
SELECT c.column_name, 'User' lvl, c.grantee, c.privilege, c.grantable
FROM dba_col_privs c
WHERE c.owner = UPPER('&2') AND c.table_name = UPPER('&3') AND
      c.grantee IN (SELECT username FROM dba_users WHERE username = c.grantee)
UNION
SELECT c.column_name, 'Public' lvl, c.grantee, c.privilege, c.grantable
FROM dba_col_privs c
WHERE c.owner = UPPER('&2') AND c.table_name = UPPER('&3') AND
      c.grantee = 'PUBLIC'
ORDER BY 1, 2, 3;
spool off
set termout on
/* prompt Created colgrant.lst for your viewing pleasure... */
exit