role info
from web ,,,,,
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
/
============
XYZ Direct UNLIMITED TABLESPACE
ABC.X X
Role CONNECT CREATE SESSION
Role RESOURCE CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
Search This Blog
Total Pageviews
Tuesday, 11 October 2011
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ▼ 2011 (362)
No comments:
Post a Comment