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.

[Home] [Help]

VIEW: SYS.USER_TAB_PRIVS_RECD

Source

View Text - Preformatted


select u.name, o.name,
       ur.name, tpm.name,
       decode(mod(oa.option$,2), 1, 'YES', 'NO'),
       decode(bitand(oa.option$,2), 2, 'YES', 'NO'), 'NO',
       decode (o.type#, 1, 'INDEX',
                        2, 'TABLE',
                        3, 'CLUSTER',
                        4, 'VIEW',
                        5, 'SYNONYM',
                        6, 'SEQUENCE',
                        7, 'PROCEDURE',
                        8, 'FUNCTION',
                        9, 'PACKAGE',
                       10, 'NON-EXISTENT',
                       11, 'PACKAGE BODY',
                       12, 'TRIGGER',
                       13, 'TYPE',
                       14, 'TYPE BODY',
                       19, 'TABLE PARTITION',
                       20, 'INDEX PARTITION',
                       21, 'LOB',
                       22, 'LIBRARY',
                       23, 'DIRECTORY',
                       24, 'QUEUE',
                       25, 'IOT',
                       26, 'REPLICATION OBJECT GROUP',
                       27, 'REPLICATION PROPAGATOR',
                       28, 'JAVA SOURCE',
                       29, 'JAVA CLASS',
                       30, 'JAVA RESOURCE',
                       31, 'JAVA JAR',
                       32, 'INDEXTYPE',
                       33, 'OPERATOR',
                       34, 'TABLE SUBPARTITION',
                       35, 'INDEX SUBPARTITION',
                       57, 'EDITION',
                       82, '(Data Mining) MODEL',
                       92, 'OLAP CUBE DIMENSION',
                       93, 'OLAP CUBE',
                       94, 'OLAP MEASURE FOLDER',
                       95, 'OLAP CUBE BUILD PROCESS',
                      100, 'FILE WATCHER',
                      101, 'DESTINATION',
                      114, 'SQL TRANSLATION PROFILE', 'UNKNOWN')
from sys.objauth$ oa, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ ur,
     table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and u.user# = o.owner#
  and oa.col# is null
  and oa.privilege# = tpm.privilege
  and oa.grantee# = userenv('SCHEMAID')
  and bitand(nvl(oa.option$, 0), 4) = 0 /* Local, and maybe Common, Privs */
union all
/* Common Privileges */
select u.name, o.name,
       ur.name, tpm.name,
       decode(bitand(oa.option$,16), 16, 'YES', 'NO'),
       decode(bitand(oa.option$,32), 32, 'YES', 'NO'), 'YES',
       decode (o.type#, 1, 'INDEX',
                        2, 'TABLE',
                        3, 'CLUSTER',
                        4, 'VIEW',
                        5, 'SYNONYM',
                        6, 'SEQUENCE',
                        7, 'PROCEDURE',
                        8, 'FUNCTION',
                        9, 'PACKAGE',
                       10, 'NON-EXISTENT',
                       11, 'PACKAGE BODY',
                       12, 'TRIGGER',
                       13, 'TYPE',
                       14, 'TYPE BODY',
                       19, 'TABLE PARTITION',
                       20, 'INDEX PARTITION',
                       21, 'LOB',
                       22, 'LIBRARY',
                       23, 'DIRECTORY',
                       24, 'QUEUE',
                       25, 'IOT',
                       26, 'REPLICATION OBJECT GROUP',
                       27, 'REPLICATION PROPAGATOR',
                       28, 'JAVA SOURCE',
                       29, 'JAVA CLASS',
                       30, 'JAVA RESOURCE',
                       31, 'JAVA JAR',
                       32, 'INDEXTYPE',
                       33, 'OPERATOR',
                       34, 'TABLE SUBPARTITION',
                       35, 'INDEX SUBPARTITION',
                       57, 'EDITION',
                       82, '(Data Mining) MODEL',
                       92, 'OLAP CUBE DIMENSION',
                       93, 'OLAP CUBE',
                       94, 'OLAP MEASURE FOLDER',
                       95, 'OLAP CUBE BUILD PROCESS',
                      100, 'FILE WATCHER',
                      101, 'DESTINATION',
                      114, 'SQL TRANSLATION PROFILE', 'UNKNOWN')
from sys.objauth$ oa, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ ur,
     table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and u.user# = o.owner#
  and oa.col# is null
  and oa.privilege# = tpm.privilege
  and oa.grantee# = userenv('SCHEMAID')
  and bitand(oa.option$,8) = 8 /* Common, and maybe Local, priv */
union all
/* Local User privileges */
select 'SYS', u.name,
       ur.name, upm.name,
       decode(bitand(ua.option$,1), 1, 'YES', 'NO'),
       'NO',
       'NO',
       'USER'
from sys.userauth$ ua, sys.user$ u, sys.user$ ur, sys.user_privilege_map upm
where ua.user# = u.user#
  and ua.grantor# = ur.user#
  and ua.privilege# = upm.privilege
  and ua.grantee# = userenv('SCHEMAID')
  and bitand(nvl(ua.option$, 0), 4) = 0 /* Local, and maybe Common, Privs */
union all
/* Common User privileges */
select 'SYS', u.name,
       ur.name, upm.name,
       decode(bitand(ua.option$,16), 16, 'YES', 'NO'),
       'NO',
       'YES',
       'USER'
from sys.userauth$ ua, sys.user$ u, sys.user$ ur, sys.user_privilege_map upm
where ua.user# = u.user#
  and ua.grantor# = ur.user#
  and ua.privilege# = upm.privilege
  and ua.grantee# = userenv('SCHEMAID')
  and bitand(ua.option$,8) = 8 /* Common, and maybe Local, priv */

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, UR.NAME
, TPM.NAME
, DECODE(MOD(OA.OPTION$
, 2)
, 1
, 'YES'
, 'NO')
, DECODE(BITAND(OA.OPTION$
, 2)
, 2
, 'YES'
, 'NO')
, 'NO'
, DECODE (O.TYPE#
, 1
, 'INDEX'
, 2
, 'TABLE'
, 3
, 'CLUSTER'
, 4
, 'VIEW'
, 5
, 'SYNONYM'
, 6
, 'SEQUENCE'
, 7
, 'PROCEDURE'
, 8
, 'FUNCTION'
, 9
, 'PACKAGE'
, 10
, 'NON-EXISTENT'
, 11
, 'PACKAGE BODY'
, 12
, 'TRIGGER'
, 13
, 'TYPE'
, 14
, 'TYPE BODY'
, 19
, 'TABLE PARTITION'
, 20
, 'INDEX PARTITION'
, 21
, 'LOB'
, 22
, 'LIBRARY'
, 23
, 'DIRECTORY'
, 24
, 'QUEUE'
, 25
, 'IOT'
, 26
, 'REPLICATION OBJECT GROUP'
, 27
, 'REPLICATION PROPAGATOR'
, 28
, 'JAVA SOURCE'
, 29
, 'JAVA CLASS'
, 30
, 'JAVA RESOURCE'
, 31
, 'JAVA JAR'
, 32
, 'INDEXTYPE'
, 33
, 'OPERATOR'
, 34
, 'TABLE SUBPARTITION'
, 35
, 'INDEX SUBPARTITION'
, 57
, 'EDITION'
, 82
, '(DATA MINING) MODEL'
, 92
, 'OLAP CUBE DIMENSION'
, 93
, 'OLAP CUBE'
, 94
, 'OLAP MEASURE FOLDER'
, 95
, 'OLAP CUBE BUILD PROCESS'
, 100
, 'FILE WATCHER'
, 101
, 'DESTINATION'
, 114
, 'SQL TRANSLATION PROFILE'
, 'UNKNOWN') FROM SYS.OBJAUTH$ OA
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
, SYS.USER$ UR
, TABLE_PRIVILEGE_MAP TPM WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTOR# = UR.USER#
AND U.USER# = O.OWNER#
AND OA.COL# IS NULL
AND OA.PRIVILEGE# = TPM.PRIVILEGE
AND OA.GRANTEE# = USERENV('SCHEMAID')
AND BITAND(NVL(OA.OPTION$
, 0)
, 4) = 0 /* LOCAL
,
AND MAYBE COMMON
, PRIVS */ UNION ALL /* COMMON PRIVILEGES */ SELECT U.NAME
, O.NAME
, UR.NAME
, TPM.NAME
, DECODE(BITAND(OA.OPTION$
, 16)
, 16
, 'YES'
, 'NO')
, DECODE(BITAND(OA.OPTION$
, 32)
, 32
, 'YES'
, 'NO')
, 'YES'
, DECODE (O.TYPE#
, 1
, 'INDEX'
, 2
, 'TABLE'
, 3
, 'CLUSTER'
, 4
, 'VIEW'
, 5
, 'SYNONYM'
, 6
, 'SEQUENCE'
, 7
, 'PROCEDURE'
, 8
, 'FUNCTION'
, 9
, 'PACKAGE'
, 10
, 'NON-EXISTENT'
, 11
, 'PACKAGE BODY'
, 12
, 'TRIGGER'
, 13
, 'TYPE'
, 14
, 'TYPE BODY'
, 19
, 'TABLE PARTITION'
, 20
, 'INDEX PARTITION'
, 21
, 'LOB'
, 22
, 'LIBRARY'
, 23
, 'DIRECTORY'
, 24
, 'QUEUE'
, 25
, 'IOT'
, 26
, 'REPLICATION OBJECT GROUP'
, 27
, 'REPLICATION PROPAGATOR'
, 28
, 'JAVA SOURCE'
, 29
, 'JAVA CLASS'
, 30
, 'JAVA RESOURCE'
, 31
, 'JAVA JAR'
, 32
, 'INDEXTYPE'
, 33
, 'OPERATOR'
, 34
, 'TABLE SUBPARTITION'
, 35
, 'INDEX SUBPARTITION'
, 57
, 'EDITION'
, 82
, '(DATA MINING) MODEL'
, 92
, 'OLAP CUBE DIMENSION'
, 93
, 'OLAP CUBE'
, 94
, 'OLAP MEASURE FOLDER'
, 95
, 'OLAP CUBE BUILD PROCESS'
, 100
, 'FILE WATCHER'
, 101
, 'DESTINATION'
, 114
, 'SQL TRANSLATION PROFILE'
, 'UNKNOWN') FROM SYS.OBJAUTH$ OA
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
, SYS.USER$ UR
, TABLE_PRIVILEGE_MAP TPM WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTOR# = UR.USER#
AND U.USER# = O.OWNER#
AND OA.COL# IS NULL
AND OA.PRIVILEGE# = TPM.PRIVILEGE
AND OA.GRANTEE# = USERENV('SCHEMAID')
AND BITAND(OA.OPTION$
, 8) = 8 /* COMMON
,
AND MAYBE LOCAL
, PRIV */ UNION ALL /* LOCAL USER PRIVILEGES */ SELECT 'SYS'
, U.NAME
, UR.NAME
, UPM.NAME
, DECODE(BITAND(UA.OPTION$
, 1)
, 1
, 'YES'
, 'NO')
, 'NO'
, 'NO'
, 'USER' FROM SYS.USERAUTH$ UA
, SYS.USER$ U
, SYS.USER$ UR
, SYS.USER_PRIVILEGE_MAP UPM WHERE UA.USER# = U.USER#
AND UA.GRANTOR# = UR.USER#
AND UA.PRIVILEGE# = UPM.PRIVILEGE
AND UA.GRANTEE# = USERENV('SCHEMAID')
AND BITAND(NVL(UA.OPTION$
, 0)
, 4) = 0 /* LOCAL
,
AND MAYBE COMMON
, PRIVS */ UNION ALL /* COMMON USER PRIVILEGES */ SELECT 'SYS'
, U.NAME
, UR.NAME
, UPM.NAME
, DECODE(BITAND(UA.OPTION$
, 16)
, 16
, 'YES'
, 'NO')
, 'NO'
, 'YES'
, 'USER' FROM SYS.USERAUTH$ UA
, SYS.USER$ U
, SYS.USER$ UR
, SYS.USER_PRIVILEGE_MAP UPM WHERE UA.USER# = U.USER#
AND UA.GRANTOR# = UR.USER#
AND UA.PRIVILEGE# = UPM.PRIVILEGE
AND UA.GRANTEE# = USERENV('SCHEMAID')
AND BITAND(UA.OPTION$
, 8) = 8 /* COMMON
,
AND MAYBE LOCAL
, PRIV */