VIEW: SYS.DBA_ROLE_PRIVS

Source

View Text - Preformatted

select /*+ ordered */ decode(sa.grantee#, 1, 'PUBLIC', u1.name), u2.name,
       decode(min(bitand(nvl(option$, 0), 1)), 1, 'YES', 'NO'),
       decode(min(bitand(nvl(option$, 0), 2)), 2, 'YES', 'NO'),
       decode(min(u1.defrole), 0, 'NO',
              1, decode(min(u2.password), NULL, decode(min(u2.spare4), NULL, 'YES', 'NO'),'NO'),
              2, decode(min(ud.role#), NULL, 'NO', decode(min(u2.password), NULL, decode(min(u2.spare4), NULL, 'YES', 'NO'), 'NO')),
              3, decode(min(ud.role#), NULL, decode(min(u2.password), NULL, decode(min(u2.spare4), NULL, 'YES', 'NO'), 'NO'), 'NO'), 'NO'), 'NO'
from sysauth$ sa, user$ u1, user$ u2, defrole$ ud
where sa.grantee#=ud.user#(+)
  and sa.privilege#=ud.role#(+) and u1.user#=sa.grantee#
  and u2.user#=sa.privilege#
  and bitand(nvl(option$, 0), 4) = 0 /* Local, and maybe Common, Privs */
group by decode(sa.grantee#,1,'PUBLIC',u1.name),u2.name
union all
/* Common Privileges */
select /*+ ordered */ decode(sa.grantee#, 1, 'PUBLIC', u1.name), u2.name,
       decode(min(bitand(nvl(option$, 0), 16)), 16, 'YES', 'NO'),
       decode(min(bitand(nvl(option$, 0), 32)), 32, 'YES', 'NO'),
       decode(min(u1.defrole), 0, 'NO',
              1, decode(min(u2.password), NULL, decode(min(u2.spare4), NULL, 'YES', 'NO'),'NO'),
              2, decode(min(ud.role#), NULL, 'NO', decode(min(u2.password), NULL, decode(min(u2.spare4), NULL, 'YES', 'NO'), 'NO')),
              3, decode(min(ud.role#), NULL, decode(min(u2.password), NULL, decode(min(u2.spare4), NULL, 'YES', 'NO'), 'NO'), 'NO'), 'NO'), 'YES'
from sysauth$ sa, user$ u1, user$ u2, defrole$ ud
where sa.grantee#=ud.user#(+)
  and sa.privilege#=ud.role#(+) and u1.user#=sa.grantee#
  and u2.user#=sa.privilege#
  and bitand(nvl(option$, 0), 8) = 8 /* Common, and maybe Local, priv */
group by decode(sa.grantee#,1,'PUBLIC',u1.name),u2.name

View Text - HTML Formatted

SELECT /*+ ORDERED */ DECODE(SA.GRANTEE#
, 1
, 'PUBLIC'
, U1.NAME)
, U2.NAME
, DECODE(MIN(BITAND(NVL(OPTION$
, 0)
, 1))
, 1
, 'YES'
, 'NO')
, DECODE(MIN(BITAND(NVL(OPTION$
, 0)
, 2))
, 2
, 'YES'
, 'NO')
, DECODE(MIN(U1.DEFROLE)
, 0
, 'NO'
, 1
, DECODE(MIN(U2.PASSWORD)
, NULL
, DECODE(MIN(U2.SPARE4)
, NULL
, 'YES'
, 'NO')
, 'NO')
, 2
, DECODE(MIN(UD.ROLE#)
, NULL
, 'NO'
, DECODE(MIN(U2.PASSWORD)
, NULL
, DECODE(MIN(U2.SPARE4)
, NULL
, 'YES'
, 'NO')
, 'NO'))
, 3
, DECODE(MIN(UD.ROLE#)
, NULL
, DECODE(MIN(U2.PASSWORD)
, NULL
, DECODE(MIN(U2.SPARE4)
, NULL
, 'YES'
, 'NO')
, 'NO')
, 'NO')
, 'NO')
, 'NO' FROM SYSAUTH$ SA
, USER$ U1
, USER$ U2
, DEFROLE$ UD WHERE SA.GRANTEE#=UD.USER#(+)
AND SA.PRIVILEGE#=UD.ROLE#(+)
AND U1.USER#=SA.GRANTEE#
AND U2.USER#=SA.PRIVILEGE#
AND BITAND(NVL(OPTION$
, 0)
, 4) = 0 /* LOCAL
,
AND MAYBE COMMON
, PRIVS */ GROUP BY DECODE(SA.GRANTEE#
, 1
, 'PUBLIC'
, U1.NAME)
, U2.NAME UNION ALL /* COMMON PRIVILEGES */ SELECT /*+ ORDERED */ DECODE(SA.GRANTEE#
, 1
, 'PUBLIC'
, U1.NAME)
, U2.NAME
, DECODE(MIN(BITAND(NVL(OPTION$
, 0)
, 16))
, 16
, 'YES'
, 'NO')
, DECODE(MIN(BITAND(NVL(OPTION$
, 0)
, 32))
, 32
, 'YES'
, 'NO')
, DECODE(MIN(U1.DEFROLE)
, 0
, 'NO'
, 1
, DECODE(MIN(U2.PASSWORD)
, NULL
, DECODE(MIN(U2.SPARE4)
, NULL
, 'YES'
, 'NO')
, 'NO')
, 2
, DECODE(MIN(UD.ROLE#)
, NULL
, 'NO'
, DECODE(MIN(U2.PASSWORD)
, NULL
, DECODE(MIN(U2.SPARE4)
, NULL
, 'YES'
, 'NO')
, 'NO'))
, 3
, DECODE(MIN(UD.ROLE#)
, NULL
, DECODE(MIN(U2.PASSWORD)
, NULL
, DECODE(MIN(U2.SPARE4)
, NULL
, 'YES'
, 'NO')
, 'NO')
, 'NO')
, 'NO')
, 'YES' FROM SYSAUTH$ SA
, USER$ U1
, USER$ U2
, DEFROLE$ UD WHERE SA.GRANTEE#=UD.USER#(+)
AND SA.PRIVILEGE#=UD.ROLE#(+)
AND U1.USER#=SA.GRANTEE#
AND U2.USER#=SA.PRIVILEGE#
AND BITAND(NVL(OPTION$
, 0)
, 8) = 8 /* COMMON
,
AND MAYBE LOCAL
, PRIV */ GROUP BY DECODE(SA.GRANTEE#
, 1
, 'PUBLIC'
, U1.NAME)
, U2.NAME