/* allgrant.sql */

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

/* Input:  A user name and the name of a table, view, or program in the */
/*         schema */

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

prompt Warning - This script takes a while to run and creates a very large file!
set pagesize 50000
set verify off
set feedback off
column lvl format A4 Heading "Lvl"
column owner format A14
column table_name format A29
column grantee format A18
column privilege format A10
ttitle left 'allgrant - Direct Grants made to all objects' skip 2
spool allgrant.lst
set termout off
SELECT 'Role' lvl, t.owner, t.table_name, t.grantee, t.privilege
FROM dba_tab_privs t
WHERE t.grantee IN (SELECT role FROM dba_roles WHERE role = t.grantee)
UNION
SELECT 'User' lvl, t.owner, t.table_name, t.grantee, t.privilege
FROM dba_tab_privs t
WHERE t.grantee IN (SELECT username FROM dba_users WHERE username = t.grantee)
UNION
SELECT 'Pub' lvl, t.owner, t.table_name, t.grantee, t.privilege
FROM dba_tab_privs t
WHERE t.grantee = 'PUBLIC'
ORDER BY 1, 2, 3;
spool off
set termout on
/* prompt Created allgrant.lst for your viewing pleasure... */
exit