REM Program     : privilege_report.sql
REM Purpose     : This script will report all users (excluding sys, system, etc.) and their privileges, whether granted
REM               through a role or directly.
REM Author      : Daniel W. Fink OptimalDBA.com
REM Created     : January 20, 2007 
REM Update      : 
REM Parameters  : Not Used
REM Exit Codes  : Not Used
REM Comments    : This will work in 10g only. If any username includes a '.', then the '.' used to separate the privilege
REM               hierarchy must be changed to another character.
REM Disclaimer  : No warranty is provided for any use of the script, statements or logic included. 
REM               This script, statements and logic are for personal use only and may not be included as part of a commercial product. 
REM               This comment block and all lines above must be included.

SET LINESIZE 300 PAGESIZE 50000 FEEDBACK OFF ECHO OFF VERIFY OFF SHOWMODE OFF TRIMSPOOL ON
COLUMN grantee_name FORMAT A30 HEADING 'User Name'
COLUMN role_path FORMAT A50 HEADING 'Role Hierarchy'
COLUMN priv_obj_name FORMAT A60 HEADING 'Privilege/Object Name'
COLUMN sort_order1 NOPRINT
COLUMN priv_path_names NOPRINT
COLUMN insert_priv FORMAT A2 HEADING 'IN'
COLUMN update_priv FORMAT A2 HEADING 'UP'
COLUMN delete_priv FORMAT A2 HEADING 'DE'
COLUMN select_priv FORMAT A2 HEADING 'SE'
COLUMN alter_priv  FORMAT A2 HEADING 'AL'
COLUMN execute_priv FORMAT A2 HEADING 'EX'
COLUMN other_priv  FORMAT A2 HEADING 'OT'


BREAK ON grantee_name NODUPLICATE SKIP 1 ON role_path NODUPLICATE

SELECT 1 sort_order1,
       urm.priv_path_names priv_path_names,
       CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
            ELSE urm.priv_path_names
       END  grantee_name,
       CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
            ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
       END  role_path,
       spm.name priv_obj_name,
       NULL insert_priv,
       NULL update_priv,
       NULL delete_priv,
       NULL select_priv,
       NULL alter_priv,
       NULL execute_priv,
       NULL other_priv
FROM   system_privilege_map spm,
       sysauth$  sa,
       ( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
                     ELSE urm2.priv_path
                END  root_user_id,
                CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
                     ELSE urm2.priv_path
                END  role_id,
                urm2.priv_path,
                urm2.priv_path_names
         FROM ( SELECT TO_CHAR(u.user#) priv_path,
                       u.name           priv_path_names
                FROM   user$ u
                UNION
                SELECT sam.priv_path priv_path,
                       sam.priv_path_names priv_path_names
                FROM   (SELECT sa.grantee#,
                               sa.privilege#,
                               LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
                               LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
                        FROM   sysauth$ sa,
                               user$ u1,
                               user$ u2
                        WHERE  sa.privilege# > 0
                          AND  u1.user# = sa.grantee#
                          AND  u2.user# = sa.privilege#
                        CONNECT BY sa.grantee# = PRIOR sa.privilege#
                        ORDER SIBLINGS BY sa.privilege#
                       ) sam
              ) urm2
       ) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
                            FROM   user$ usq
                            WHERE  usq.type# = 1
                              AND  usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
                              )
  AND sa.grantee# = urm.role_id
  AND sa.privilege# < 0
  AND sa.privilege# = spm.privilege
UNION
SELECT 2 sort_order1,
       urm.priv_path_names priv_path_names,
       CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
            ELSE urm.priv_path_names
       END  grantee_name,
       CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
            ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
       END  role_path,
       u.name||'.'||o.name priv_obj_name,
       MAX(DECODE(tpm.name, 'INSERT',              'X', NULL)) insert_priv,
       MAX(DECODE(tpm.name, 'UPDATE',              'X', NULL)) update_priv,
       MAX(DECODE(tpm.name, 'DELETE',              'X', NULL)) delete_priv,
       MAX(DECODE(tpm.name, 'SELECT',              'X', NULL)) select_priv,
       MAX(DECODE(tpm.name, 'ALTER',               'X', NULL)) alter_priv,
       MAX(DECODE(tpm.name, 'EXECUTE',             'X', NULL)) execute_priv,
       MAX(DECODE(tpm.name, 'INSERT', NULL, 'UPDATE', NULL, 'DELETE', NULL,
                            'SELECT', NULL, 'ALTER',  NULL, 'EXECUTE', NULL,
                            'X' )) other_priv
FROM   objauth$ oa,
       obj$ o,
       user$ u,
       table_privilege_map tpm,
       ( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
                     ELSE urm2.priv_path
                END  root_user_id,
                CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
                     ELSE urm2.priv_path
                END  role_id,
                urm2.priv_path,
                urm2.priv_path_names
         FROM ( SELECT TO_CHAR(u.user#) priv_path,
                       u.name           priv_path_names
                FROM   user$ u
                UNION
                SELECT sam.priv_path priv_path,
                       sam.priv_path_names priv_path_names
                FROM   (SELECT sa.grantee#,
                               sa.privilege#,
                               LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
                               LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
                        FROM   sysauth$ sa,
                               user$ u1,
                               user$ u2
                        WHERE  sa.privilege# > 0
                          AND  u1.user# = sa.grantee#
                          AND  u2.user# = sa.privilege#
                        CONNECT BY sa.grantee# = PRIOR sa.privilege#
                        ORDER SIBLINGS BY sa.privilege#
                       ) sam
              ) urm2
       ) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
                            FROM   user$ usq
                            WHERE  usq.type# = 1
                              AND  usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
                              )
  AND   oa.grantee# = urm.role_id
  AND   oa.obj# = o.obj#
  AND   o.owner# != 59
  AND   o.owner# = u.user#
  AND   oa.privilege# = tpm.privilege
GROUP BY 2, urm.root_user_id, urm.priv_path, urm.priv_path_names, u.name, o.name
ORDER BY priv_path_names, sort_order1, priv_obj_name


SPOOL allprivs.lst

/

SPOOL OFF