eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS.USER_ROLE_PRIVS

Source

View Text - Preformatted


select groles.username, groles.granted_role, groles.admin_option,
       groles.delegate_option, groles.default_role, groles.os_granted,
       groles.common
from
(
select /*+ ordered */ decode(sa.grantee#, 1, 'PUBLIC', u1.name) username,
    u2.name granted_role,
    decode(min(bitand(nvl(option$, 0), 1)), 1, 'YES', 'NO') admin_option,
    decode(min(bitand(nvl(option$, 0), 2)), 2, 'YES', 'NO') delegate_option,
    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') default_role,
    'NO' os_granted, 'NO' common
from sysauth$ sa, user$ u1, user$ u2, defrole$ ud
where sa.grantee# in (userenv('SCHEMAID'),1) and 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) username,
  u2.name granted_role,
  decode(min(bitand(nvl(option$, 0), 16)), 16, 'YES', 'NO') admin_option,
  decode(min(bitand(nvl(option$, 0), 32)), 32, 'YES', 'NO') delegate_option,
  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') default_role,
  'NO' os_granted, 'YES' common
from sysauth$ sa, user$ u1, user$ u2, defrole$ ud
where sa.grantee# in (userenv('SCHEMAID'),1) and 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
union
select su.name username,u.name granted_role,
       decode(kzdosadm,'A','YES','NO') admin_option, NULL delegate_option,
       decode(kzdosdef,'Y','YES','NO') default_role,
       'YES' os_granted, 'NO' common
from sys.user$ u,x$kzdos, sys.user$ su
where u.user#=x$kzdos.kzdosrol and
      su.user#=userenv('SCHEMAID')
) groles
where sys_context('userenv', 'proxy_user') is null
      or
      (sys_context('userenv', 'proxy_user') is not null and
      (EXISTS (select 1 from
               sys.proxy_info$ p where
               p.client#=userenv('SCHEMAID') and
               p.proxy# = sys_context('userenv', 'proxy_userid')
               and BITAND(p.flags,2) =0 and (BITAND(p.flags,1)>0  or
       EXISTS (select 1 from sys.proxy_role_info$ pr, sys.user$ u where
               p.client# = pr.client# and p.proxy# = pr.proxy# and
               ((BITAND(p.flags,4) > 0 and
              (pr.role# = u.user# and u.name = groles.granted_role)) or
             (BITAND(p.flags,8) > 0 and
              (pr.role# = u.user# and u.name != groles.granted_role)))))) or
              /* it could be a RAS proxy session. Since xs$proxy_role is not yet created,
               * assume RAS proxy session if no row found in proxy_info$.
               */
       NOT EXISTS (select 1 from
                   sys.proxy_info$ p where
                   p.client#=userenv('SCHEMAID') and
                   p.proxy# = sys_context('userenv', 'proxy_userid'))))

View Text - HTML Formatted

SELECT GROLES.USERNAME
, GROLES.GRANTED_ROLE
, GROLES.ADMIN_OPTION
, GROLES.DELEGATE_OPTION
, GROLES.DEFAULT_ROLE
, GROLES.OS_GRANTED
, GROLES.COMMON FROM ( SELECT /*+ ORDERED */ DECODE(SA.GRANTEE#
, 1
, 'PUBLIC'
, U1.NAME) USERNAME
, U2.NAME GRANTED_ROLE
, DECODE(MIN(BITAND(NVL(OPTION$
, 0)
, 1))
, 1
, 'YES'
, 'NO') ADMIN_OPTION
, DECODE(MIN(BITAND(NVL(OPTION$
, 0)
, 2))
, 2
, 'YES'
, 'NO') DELEGATE_OPTION
, 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') DEFAULT_ROLE
, 'NO' OS_GRANTED
, 'NO' COMMON FROM SYSAUTH$ SA
, USER$ U1
, USER$ U2
, DEFROLE$ UD WHERE SA.GRANTEE# IN (USERENV('SCHEMAID')
, 1)
AND 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) USERNAME
, U2.NAME GRANTED_ROLE
, DECODE(MIN(BITAND(NVL(OPTION$
, 0)
, 16))
, 16
, 'YES'
, 'NO') ADMIN_OPTION
, DECODE(MIN(BITAND(NVL(OPTION$
, 0)
, 32))
, 32
, 'YES'
, 'NO') DELEGATE_OPTION
, 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') DEFAULT_ROLE
, 'NO' OS_GRANTED
, 'YES' COMMON FROM SYSAUTH$ SA
, USER$ U1
, USER$ U2
, DEFROLE$ UD WHERE SA.GRANTEE# IN (USERENV('SCHEMAID')
, 1)
AND 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 UNION SELECT SU.NAME USERNAME
, U.NAME GRANTED_ROLE
, DECODE(KZDOSADM
, 'A'
, 'YES'
, 'NO') ADMIN_OPTION
, NULL DELEGATE_OPTION
, DECODE(KZDOSDEF
, 'Y'
, 'YES'
, 'NO') DEFAULT_ROLE
, 'YES' OS_GRANTED
, 'NO' COMMON FROM SYS.USER$ U
, X$KZDOS
, SYS.USER$ SU WHERE U.USER#=X$KZDOS.KZDOSROL AND SU.USER#=USERENV('SCHEMAID') ) GROLES WHERE SYS_CONTEXT('USERENV'
, 'PROXY_USER') IS NULL OR (SYS_CONTEXT('USERENV'
, 'PROXY_USER') IS NOT NULL AND (EXISTS (SELECT 1 FROM SYS.PROXY_INFO$ P WHERE P.CLIENT#=USERENV('SCHEMAID') AND P.PROXY# = SYS_CONTEXT('USERENV'
, 'PROXY_USERID')
AND BITAND(P.FLAGS
, 2) =0
AND (BITAND(P.FLAGS
, 1)>0 OR EXISTS (SELECT 1
FROM SYS.PROXY_ROLE_INFO$ PR
, SYS.USER$ U WHERE P.CLIENT# = PR.CLIENT#
AND P.PROXY# = PR.PROXY# AND ((BITAND(P.FLAGS
, 4) > 0 AND (PR.ROLE# = U.USER#
AND U.NAME = GROLES.GRANTED_ROLE)) OR (BITAND(P.FLAGS
, 8) > 0 AND (PR.ROLE# = U.USER#
AND U.NAME != GROLES.GRANTED_ROLE)))))) OR /* IT COULD BE A RAS PROXY SESSION. SINCE XS$PROXY_ROLE IS NOT YET CREATED
, * ASSUME RAS PROXY SESSION IF NO ROW FOUND IN PROXY_INFO$. */ NOT EXISTS (SELECT 1 FROM SYS.PROXY_INFO$ P WHERE P.CLIENT#=USERENV('SCHEMAID') AND P.PROXY# = SYS_CONTEXT('USERENV'
, 'PROXY_USERID'))))