VIEW: SYS.USER_SYS_PRIVS

Source

View Text - Preformatted

select decode(sa.grantee#,1,'PUBLIC',su.name),spm.name,
       decode(min(mod(option$, 2)),1,'YES','NO'), 'NO'
from  sys.system_privilege_map spm, sys.sysauth$ sa, sys.user$ su
where ((sa.grantee#=userenv('SCHEMAID') and su.user#=sa.grantee#)
       or sa.grantee#=1)
  and sa.privilege#=spm.privilege
  and bitand(nvl(option$, 0), 4) = 0 /* Local, and maybe Common, Privs */
group by decode(sa.grantee#,1,'PUBLIC',su.name),spm.name
union all
/* Common Privileges */
select decode(sa.grantee#,1,'PUBLIC',su.name),spm.name,
       decode(min(bitand(option$,16)),16,'YES','NO'), 'YES'
from  sys.system_privilege_map spm, sys.sysauth$ sa, sys.user$ su
where ((sa.grantee#=userenv('SCHEMAID') and su.user#=sa.grantee#)
       or sa.grantee#=1)
  and sa.privilege#=spm.privilege
  and bitand(option$,8) = 8 /* Common, and maybe Local, priv */
group by decode(sa.grantee#,1,'PUBLIC',su.name),spm.name

View Text - HTML Formatted

SELECT DECODE(SA.GRANTEE#
, 1
, 'PUBLIC'
, SU.NAME)
, SPM.NAME
, DECODE(MIN(MOD(OPTION$
, 2))
, 1
, 'YES'
, 'NO')
, 'NO' FROM SYS.SYSTEM_PRIVILEGE_MAP SPM
, SYS.SYSAUTH$ SA
, SYS.USER$ SU WHERE ((SA.GRANTEE#=USERENV('SCHEMAID')
AND SU.USER#=SA.GRANTEE#) OR SA.GRANTEE#=1)
AND SA.PRIVILEGE#=SPM.PRIVILEGE
AND BITAND(NVL(OPTION$
, 0)
, 4) = 0 /* LOCAL
,
AND MAYBE COMMON
, PRIVS */ GROUP BY DECODE(SA.GRANTEE#
, 1
, 'PUBLIC'
, SU.NAME)
, SPM.NAME UNION ALL /* COMMON PRIVILEGES */ SELECT DECODE(SA.GRANTEE#
, 1
, 'PUBLIC'
, SU.NAME)
, SPM.NAME
, DECODE(MIN(BITAND(OPTION$
, 16))
, 16
, 'YES'
, 'NO')
, 'YES' FROM SYS.SYSTEM_PRIVILEGE_MAP SPM
, SYS.SYSAUTH$ SA
, SYS.USER$ SU WHERE ((SA.GRANTEE#=USERENV('SCHEMAID')
AND SU.USER#=SA.GRANTEE#) OR SA.GRANTEE#=1)
AND SA.PRIVILEGE#=SPM.PRIVILEGE
AND BITAND(OPTION$
, 8) = 8 /* COMMON
,
AND MAYBE LOCAL
, PRIV */ GROUP BY DECODE(SA.GRANTEE#
, 1
, 'PUBLIC'
, SU.NAME)
, SPM.NAME