9/09/2017

Oracle How to find user object grants

Here  with this query you can find the objects grants for a specific user.

SELECT tpm.NAME PRIVILEGE,

       DECODE (MOD (oa.OPTION$, 2), 1, 'YES', 'NO') grantable,
       ue.NAME Grantee, ur.NAME grantor, u.NAME owner,
       DECODE (o.TYPE#,
               0, 'NEXT OBJECT',
               1, 'INDEX',
               2, 'TABLE',
               3, 'CLUSTER',
               4, 'VIEW',
               5, 'SYNONYM',
               6, 'SEQUENCE',
               7, 'PROCEDURE',
               8, 'FUNCTION',
               9, 'PACKAGE',
               11, 'PACKAGE BODY',
               12, 'TRIGGER',
               13, 'TYPE',
               14, 'TYPE BODY',
               19, 'TABLE PARTITION',
               20, 'INDEX PARTITION',
               21, 'LOB',
               22, 'LIBRARY',
               23, 'DIRECTORY',
               24, 'QUEUE',
               28, 'JAVA SOURCE',
               29, 'JAVA CLASS',
               30, 'JAVA RESOURCE',
               32, 'INDEXTYPE',
               33, 'OPERATOR',
               34, 'TABLE SUBPARTITION',
               35, 'INDEX SUBPARTITION',
               40, 'LOB PARTITION',
               41, 'LOB SUBPARTITION',
               42, 'MATERIALIZED VIEW',
               43, 'DIMENSION',
               44, 'CONTEXT',
               46, 'RULE SET',
               47, 'RESOURCE PLAN',
               48, 'CONSUMER GROUP',
               51, 'SUBSCRIPTION',
               52, 'LOCATION',
               55, 'XML SCHEMA',
               56, 'JAVA DATA',
               57, 'SECURITY PROFILE',
               59, 'RULE',
               62, 'EVALUATION CONTEXT',
               'UNDEFINED'
              ) object_type,
       o.NAME object_name, '' column_name
  FROM SYS.objauth$ oa, SYS.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 oa.PRIVILEGE# = tpm.PRIVILEGE
   AND u.USER# = o.owner#
   AND o.TYPE# IN (2, 4, 6, 9, 7, 8, 42, 23, 22, 13, 33, 32)
   AND ue.NAME = 'NISA_DL'
UNION ALL                                               -- column level grants
SELECT tpm.NAME PRIVILEGE,
       DECODE (MOD (oa.OPTION$, 2), 1, 'YES', 'NO') grantable,
       ue.NAME Grantee, ur.NAME grantor, u.NAME owner,
       DECODE (o.TYPE#,
               0, 'NEXT OBJECT',
               1, 'INDEX',
               2, 'TABLE',
               3, 'CLUSTER',
               4, 'VIEW',
               5, 'SYNONYM',
               6, 'SEQUENCE',
               7, 'PROCEDURE',
               8, 'FUNCTION',
               9, 'PACKAGE',
               11, 'PACKAGE BODY',
               12, 'TRIGGER',
               13, 'TYPE',
               14, 'TYPE BODY',
               19, 'TABLE PARTITION',
               20, 'INDEX PARTITION',
               21, 'LOB',
               22, 'LIBRARY',
               23, 'DIRECTORY',
               24, 'QUEUE',
               28, 'JAVA SOURCE',
               29, 'JAVA CLASS',
               30, 'JAVA RESOURCE',
               32, 'INDEXTYPE',
               33, 'OPERATOR',
               34, 'TABLE SUBPARTITION',
               35, 'INDEX SUBPARTITION',
               40, 'LOB PARTITION',
               41, 'LOB SUBPARTITION',
               42, 'MATERIALIZED VIEW',
               43, 'DIMENSION',
               44, 'CONTEXT',
               46, 'RULE SET',
               47, 'RESOURCE PLAN',
               48, 'CONSUMER GROUP',
               51, 'SUBSCRIPTION',
               52, 'LOCATION',
               55, 'XML SCHEMA',
               56, 'JAVA DATA',
               57, 'SECURITY PROFILE',
               59, 'RULE',
               62, 'EVALUATION CONTEXT',
               'UNDEFINED'
              ) object_type,
       o.NAME object_name, c.NAME column_name
  FROM SYS.objauth$ oa, SYS.obj$ o, SYS.USER$ u, SYS.USER$ ur, SYS.USER$ ue, SYS.col$ c,
 table_privilege_map
 tpm
 WHERE oa.obj# = o.obj#
   AND oa.grantor# = ur.USER#
   AND oa.Grantee# = ue.USER#
   AND oa.obj# = c.obj#
   AND oa.col# = c.col#
   AND BITAND (c.property, 32) = 0                     /* not hidden column */
   AND oa.col# IS NOT NULL
   AND oa.PRIVILEGE# = tpm.PRIVILEGE
   AND u.USER# = o.owner#
   AND o.TYPE# IN (2, 4)
   AND ue.NAME = 'NISA_DL';

No comments:

Post a Comment

Popular Posts