VIEW: SYS.USER_TAB_PRIVS

Source

View Text - Preformatted

select ue.name, 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,
     sys.user$ ue, table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and oa.col# is null
  and u.user# = o.owner#
  and oa.privilege# = tpm.privilege
  and userenv('SCHEMAID') in (oa.grantor#, oa.grantee#, o.owner#)
  and bitand(nvl(oa.option$, 0), 4) = 0 /* Local, and maybe Common, Privs */
union all
/* Common Privileges */
select ue.name, 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,
     sys.user$ ue, table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and oa.col# is null
  and u.user# = o.owner#
  and oa.privilege# = tpm.privilege
  and userenv('SCHEMAID') in (oa.grantor#, oa.grantee#, o.owner#)
  and bitand(oa.option$,8) = 8 /* Common, and maybe Local, priv */
union all
/* Local User privileges */
select ue.name, '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$ ue, sys.user_privilege_map upm
where ua.user# = u.user#
  and ua.grantor# = ur.user#
  and ua.grantee# = ue.user#
  and ua.privilege# = upm.privilege
  and userenv('SCHEMAID') in (ua.grantor#, ua.grantee#, ua.user#)
  and bitand(nvl(ua.option$, 0), 4) = 0 /* Local, and maybe Common, Privs */
union all
/* Common User Privileges */
select ue.name, '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$ ue, sys.user_privilege_map upm
where ua.user# = u.user#
  and ua.grantor# = ur.user#
  and ua.grantee# = ue.user#
  and ua.privilege# = upm.privilege
  and userenv('SCHEMAID') in (ua.grantor#, ua.grantee#, ua.user#)
  and bitand(ua.option$,8) = 8 /* Common, and maybe Local, priv */

View Text - HTML Formatted

SELECT UE.NAME
, 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
, SYS.USER$ UE
, TABLE_PRIVILEGE_MAP TPM WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTOR# = UR.USER#
AND OA.GRANTEE# = UE.USER#
AND OA.COL# IS NULL
AND U.USER# = O.OWNER#
AND OA.PRIVILEGE# = TPM.PRIVILEGE
AND USERENV('SCHEMAID') IN (OA.GRANTOR#
, OA.GRANTEE#
, O.OWNER#)
AND BITAND(NVL(OA.OPTION$
, 0)
, 4) = 0 /* LOCAL
,
AND MAYBE COMMON
, PRIVS */ UNION ALL /* COMMON PRIVILEGES */ SELECT UE.NAME
, 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
, SYS.USER$ UE
, TABLE_PRIVILEGE_MAP TPM WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTOR# = UR.USER#
AND OA.GRANTEE# = UE.USER#
AND OA.COL# IS NULL
AND U.USER# = O.OWNER#
AND OA.PRIVILEGE# = TPM.PRIVILEGE
AND USERENV('SCHEMAID') IN (OA.GRANTOR#
, OA.GRANTEE#
, O.OWNER#)
AND BITAND(OA.OPTION$
, 8) = 8 /* COMMON
,
AND MAYBE LOCAL
, PRIV */ UNION ALL /* LOCAL USER PRIVILEGES */ SELECT UE.NAME
, '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$ UE
, SYS.USER_PRIVILEGE_MAP UPM WHERE UA.USER# = U.USER#
AND UA.GRANTOR# = UR.USER#
AND UA.GRANTEE# = UE.USER#
AND UA.PRIVILEGE# = UPM.PRIVILEGE
AND USERENV('SCHEMAID') IN (UA.GRANTOR#
, UA.GRANTEE#
, UA.USER#)
AND BITAND(NVL(UA.OPTION$
, 0)
, 4) = 0 /* LOCAL
,
AND MAYBE COMMON
, PRIVS */ UNION ALL /* COMMON USER PRIVILEGES */ SELECT UE.NAME
, '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$ UE
, SYS.USER_PRIVILEGE_MAP UPM WHERE UA.USER# = U.USER#
AND UA.GRANTOR# = UR.USER#
AND UA.GRANTEE# = UE.USER#
AND UA.PRIVILEGE# = UPM.PRIVILEGE
AND USERENV('SCHEMAID') IN (UA.GRANTOR#
, UA.GRANTEE#
, UA.USER#)
AND BITAND(UA.OPTION$
, 8) = 8 /* COMMON
,
AND MAYBE LOCAL
, PRIV */