Oracle Role detail report

Oracle role detail report


Author : Daniel W. Fink


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





Role RESOURCE CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE