VIEW: SYS.ROLE_SYS_PRIVS

Source

View Text - Preformatted

select u.name,spm.name,decode(min(mod(option$, 2)),1,'YES','NO'), 'NO'
from  sys.user$ u, sys.system_privilege_map spm, sys.sysauth$ sa
where grantee# in
   (select distinct(privilege#)
    from sys.sysauth$ sa
    where privilege# > 0
    connect by prior sa.privilege# = sa.grantee#
    start with grantee#=userenv('SCHEMAID') or grantee#=1 or grantee# in
      (select kzdosrol from x$kzdos))
  and u.user#=sa.grantee# and sa.privilege#=spm.privilege
  and bitand(nvl(option$, 0), 4) = 0 /* Local, and maybe Common, Privs */
group by u.name, spm.name
union all
/* Common Privileges */
select u.name,spm.name,decode(min(bitand(option$,16)),16,'YES','NO'), 'YES'
from  sys.user$ u, sys.system_privilege_map spm, sys.sysauth$ sa
where grantee# in
   (select distinct(privilege#)
    from sys.sysauth$ sa
    where privilege# > 0
    connect by prior sa.privilege# = sa.grantee#
    start with grantee#=userenv('SCHEMAID') or grantee#=1 or grantee# in
      (select kzdosrol from x$kzdos))
  and u.user#=sa.grantee# and sa.privilege#=spm.privilege
  and bitand(option$,8) = 8 /* Common, and maybe Local, priv */
group by u.name, spm.name

View Text - HTML Formatted

SELECT U.NAME
, SPM.NAME
, DECODE(MIN(MOD(OPTION$
, 2))
, 1
, 'YES'
, 'NO')
, 'NO' FROM SYS.USER$ U
, SYS.SYSTEM_PRIVILEGE_MAP SPM
, SYS.SYSAUTH$ SA WHERE GRANTEE# IN (SELECT DISTINCT(PRIVILEGE#)
FROM SYS.SYSAUTH$ SA
WHERE PRIVILEGE# > 0 CONNECT BY PRIOR SA.PRIVILEGE# = SA.GRANTEE# START WITH GRANTEE#=USERENV('SCHEMAID') OR GRANTEE#=1 OR GRANTEE# IN (SELECT KZDOSROL
FROM X$KZDOS))
AND U.USER#=SA.GRANTEE#
AND SA.PRIVILEGE#=SPM.PRIVILEGE
AND BITAND(NVL(OPTION$
, 0)
, 4) = 0 /* LOCAL
,
AND MAYBE COMMON
, PRIVS */ GROUP BY U.NAME
, SPM.NAME UNION ALL /* COMMON PRIVILEGES */ SELECT U.NAME
, SPM.NAME
, DECODE(MIN(BITAND(OPTION$
, 16))
, 16
, 'YES'
, 'NO')
, 'YES' FROM SYS.USER$ U
, SYS.SYSTEM_PRIVILEGE_MAP SPM
, SYS.SYSAUTH$ SA WHERE GRANTEE# IN (SELECT DISTINCT(PRIVILEGE#)
FROM SYS.SYSAUTH$ SA
WHERE PRIVILEGE# > 0 CONNECT BY PRIOR SA.PRIVILEGE# = SA.GRANTEE# START WITH GRANTEE#=USERENV('SCHEMAID') OR GRANTEE#=1 OR GRANTEE# IN (SELECT KZDOSROL
FROM X$KZDOS))
AND U.USER#=SA.GRANTEE#
AND SA.PRIVILEGE#=SPM.PRIVILEGE
AND BITAND(OPTION$
, 8) = 8 /* COMMON
,
AND MAYBE LOCAL
, PRIV */ GROUP BY U.NAME
, SPM.NAME