VIEW: SYS.TABLE_PRIVILEGES

Source

View Text - Preformatted

select ue.name, u.name, o.name, ur.name,
    decode(substr(lpad(sum(power(10, privilege#*2) +
      decode(mod(option$,2), 1, power(10, privilege#*2 + 1), 0)), 26, '0'), 7, 2),
      '00', 'N', '01', 'Y', '11', 'G', 'N'),
     decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) +
       decode(mod(option$,2), 1, power(10, privilege#*2 + 1), 0), 0)), 26, '0'),
              13, 2), '01', 'A', '11', 'G',
          decode(sum(decode(col#,
                            null, 0,
                            decode(privilege#, 6, 1, 0))), 0, 'N', 'S')),
    decode(substr(lpad(sum(power(10, privilege#*2) +
      decode(mod(option$,2), 1, power(10, privilege#*2 + 1), 0)), 26, '0'), 19, 2),
      '00', 'N', '01', 'Y', '11', 'G', 'N'),
    decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) +
      decode(mod(option$,2), 1, power(10, privilege#*2 + 1), 0), 0)), 26, '0'),
             5, 2),'01', 'A', '11', 'G',
          decode(sum(decode(col#,
                            null, 0,
                            decode(privilege#, 10, 1, 0))), 0, 'N', 'S')),
    decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) +
      decode(mod(option$,2), 1, power(10, privilege#*2 + 1), 0), 0)), 26, '0'),
             3, 2), '01', 'A', '11', 'G',
          decode(sum(decode(col#,
                            null, 0,
                            decode(privilege#, 11, 1, 0))), 0, 'N', 'S')),
   decode(substr(lpad(sum(power(10, privilege#*2) +
      decode(mod(option$,2), 1, power(10, privilege#*2 + 1), 0)), 26, '0'), 25, 2),
      '00', 'N', '01', 'Y', '11', 'G', 'N'),
    decode(substr(lpad(sum(power(10, privilege#*2) +
      decode(mod(option$,2), 1, power(10, privilege#*2 + 1), 0)), 26, '0'), 15, 2),
      '00', 'N', '01', 'Y', '11', 'G', 'N'), min(null)
from sys.objauth$ oa, sys."_CURRENT_EDITION_OBJ" o, sys.user$ ue, sys.user$ ur, sys.user$ u
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and u.user# = o.owner#
  and (oa.grantor# = userenv('SCHEMAID') or
       oa.grantee# in (select kzsrorol from x$kzsro) or
       o.owner# = userenv('SCHEMAID'))
  group by u.name, o.name, ur.name, ue.name

View Text - HTML Formatted

SELECT UE.NAME
, U.NAME
, O.NAME
, UR.NAME
, DECODE(SUBSTR(LPAD(SUM(POWER(10
, PRIVILEGE#*2) + DECODE(MOD(OPTION$
, 2)
, 1
, POWER(10
, PRIVILEGE#*2 + 1)
, 0))
, 26
, '0')
, 7
, 2)
, '00'
, 'N'
, '01'
, 'Y'
, '11'
, 'G'
, 'N')
, DECODE(SUBSTR(LPAD(SUM(DECODE(COL#
, NULL
, POWER(10
, PRIVILEGE#*2) + DECODE(MOD(OPTION$
, 2)
, 1
, POWER(10
, PRIVILEGE#*2 + 1)
, 0)
, 0))
, 26
, '0')
, 13
, 2)
, '01'
, 'A'
, '11'
, 'G'
, DECODE(SUM(DECODE(COL#
, NULL
, 0
, DECODE(PRIVILEGE#
, 6
, 1
, 0)))
, 0
, 'N'
, 'S'))
, DECODE(SUBSTR(LPAD(SUM(POWER(10
, PRIVILEGE#*2) + DECODE(MOD(OPTION$
, 2)
, 1
, POWER(10
, PRIVILEGE#*2 + 1)
, 0))
, 26
, '0')
, 19
, 2)
, '00'
, 'N'
, '01'
, 'Y'
, '11'
, 'G'
, 'N')
, DECODE(SUBSTR(LPAD(SUM(DECODE(COL#
, NULL
, POWER(10
, PRIVILEGE#*2) + DECODE(MOD(OPTION$
, 2)
, 1
, POWER(10
, PRIVILEGE#*2 + 1)
, 0)
, 0))
, 26
, '0')
, 5
, 2)
, '01'
, 'A'
, '11'
, 'G'
, DECODE(SUM(DECODE(COL#
, NULL
, 0
, DECODE(PRIVILEGE#
, 10
, 1
, 0)))
, 0
, 'N'
, 'S'))
, DECODE(SUBSTR(LPAD(SUM(DECODE(COL#
, NULL
, POWER(10
, PRIVILEGE#*2) + DECODE(MOD(OPTION$
, 2)
, 1
, POWER(10
, PRIVILEGE#*2 + 1)
, 0)
, 0))
, 26
, '0')
, 3
, 2)
, '01'
, 'A'
, '11'
, 'G'
, DECODE(SUM(DECODE(COL#
, NULL
, 0
, DECODE(PRIVILEGE#
, 11
, 1
, 0)))
, 0
, 'N'
, 'S'))
, DECODE(SUBSTR(LPAD(SUM(POWER(10
, PRIVILEGE#*2) + DECODE(MOD(OPTION$
, 2)
, 1
, POWER(10
, PRIVILEGE#*2 + 1)
, 0))
, 26
, '0')
, 25
, 2)
, '00'
, 'N'
, '01'
, 'Y'
, '11'
, 'G'
, 'N')
, DECODE(SUBSTR(LPAD(SUM(POWER(10
, PRIVILEGE#*2) + DECODE(MOD(OPTION$
, 2)
, 1
, POWER(10
, PRIVILEGE#*2 + 1)
, 0))
, 26
, '0')
, 15
, 2)
, '00'
, 'N'
, '01'
, 'Y'
, '11'
, 'G'
, 'N')
, MIN(NULL) FROM SYS.OBJAUTH$ OA
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ UE
, SYS.USER$ UR
, SYS.USER$ U WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTOR# = UR.USER#
AND OA.GRANTEE# = UE.USER#
AND U.USER# = O.OWNER#
AND (OA.GRANTOR# = USERENV('SCHEMAID') OR OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO) OR O.OWNER# = USERENV('SCHEMAID')) GROUP BY U.NAME
, O.NAME
, UR.NAME
, UE.NAME