/* allprivs.sql */
/* Lists all direct grants made to all users and roles, segregated by */
/* the nature of the grant.  */

/* Output Column Legend: */
/*   LEVEL indicates the type of grant issued. */
/*   PERMISSION contains the privilege granted for all levels except for */
/*              Role.  The permission listed in a role level grant is the */
/*              name of the role that was granted. */

prompt Warning - This script takes a while to run and creates a very large file!
set pagesize 66
set verify off
set pagesize 66
set feedback off
column lvl format A8 Heading "Level"
column privilege format A24
column grantable format A4 heading "Gtbl"
column owner format A9
column table_name format A30 heading "Table Name"
ttitle left 'allprivs - All Direct Grants' skip 2
spool allprivs.lst
set termout off
SELECT 'Column' lvl, c.privilege, c.grantable, c.owner, c.table_name
FROM dba_col_privs c
UNION
SELECT 'Role' Gr_Type, r.granted_role obj, r.admin_option a, NULL, NULL
FROM dba_role_privs r
UNION
SELECT 'Sys Priv', s.privilege, s.admin_option, NULL, NULL
FROM dba_sys_privs s
UNION
SELECT 'Table', t.privilege, t.grantable, t.owner, t.table_name
FROM dba_tab_privs t
ORDER BY 1, 2, 3, 4, 5;
spool off
set termout on
/* prompt Created allprivs.lst for your viewing pleasure... */
exit