Find user object grants
                                                 Last update (2011-02-15 13:16:49)
                                                                                                                   Date added (2011-02-15 13:14:27)

Summary
How you find the objects grants for a specific user with the following query:
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';

PRIVILEGE                                GRA GRANTEE                        GRANTOR                        OWNER                          OBJECT_TYPE        OBJECT_NAME        
---------------------------------------- --- ------------------------------ ------------------------
SELECT                                   NO  NISA_DL                        BORAIG                         BORAIG                         TABLE              PCM
SELECT                                   NO  NISA_DL                        PLDM                           PLDM                           TABLE              FER
SELECT                                   NO  NISA_DL                        PLDM                           PLDM                           TABLE              PCM
SELECT                                   NO  NISA_DL                        BATH                           BATH                           TABLE              FER
SELECT                                   NO  NISA_DL                        BATH                           BATH                           TABLE              PCM
SELECT                                   NO  NISA_DL                        GIAN                           GIAN                           TABLE              FER
SELECT                                   NO  NISA_DL                        GIAN                           GIAN                           TABLE              PCM
SELECT                                   NO  NISA_DL                        DYAT                           DYAT                           TABLE              FER
SELECT                                   NO  NISA_DL                        DYAT                           DYAT                           TABLE              PCM
SELECT                                   NO  NISA_DL                        PRS                            PRS                            TABLE              FER
SELECT                                   NO  NISA_DL                        ANMAK                          ANMAK                          TABLE              PCM
SELECT                                   NO  NISA_DL                        LRS                            LRS                            TABLE              FER
SELECT                                   NO  NISA_DL                        TRP                            TRP                            TABLE              FER
SELECT                                   NO  NISA_DL                        TRP                            TRP                            TABLE              PCM
SELECT                                   NO  NISA_DL                        NISIA                          NISIA                          TABLE              PCM
SELECT                                   NO  NISA_DL                        BORAIG                         BORAIG                         TABLE              FER
SELECT                                   NO  NISA_DL                        BER                            BER                            TABLE              FER
SELECT                                   NO  NISA_DL                        BER                            BER                            TABLE              PCM
SELECT                                   NO  NISA_DL                        GLOB                           GLOB                           TABLE              TEK_KENTRO
SELECT                                   NO  NISA_DL                        ATHINA                         ATHINA                         TABLE              FER
SELECT                                   NO  NISA_DL                        ATHINA                         ATHINA                         TABLE              PCM
SELECT                                   NO  NISA_DL                        ANAT                           ANAT                           TABLE              FER
SELECT                                   NO  NISA_DL                        ANAT                           ANAT                           TABLE              PCM
SELECT                                   NO  NISA_DL                        PATRA                          PATRA                          TABLE              FER
SELECT                                   NO  NISA_DL                        PATRA                          PATRA                          TABLE              PCM
SELECT                                   NO  NISA_DL                        PRS                            PRS                            TABLE              PCM
SELECT                                   NO  NISA_DL                        ANMAK                          ANMAK                          TABLE              FER
SELECT                                   NO  NISA_DL                        GATH                           GATH                           TABLE              FER
SELECT                                   NO  NISA_DL                        GATH                           GATH                           TABLE              PCM
SELECT                                   NO  NISA_DL                        LAM                            LAM                            TABLE              FER
SELECT                                   NO  NISA_DL                        LAM                            LAM                            TABLE              PCM
SELECT                                   NO  NISA_DL                        HR                             HR                             TABLE              FER
SELECT                                   NO  NISA_DL                        HR                             HR                             TABLE              PCM
SELECT                                   NO  NISA_DL                        NISIA                          NISIA                          TABLE              FER
SELECT                                   NO  NISA_DL                        ANTHES                         ANTHES                         TABLE              FER
SELECT                                   NO  NISA_DL                        ANTHES                         ANTHES                         TABLE              PCM
SELECT                                   NO  NISA_DL                        LRS                            LRS                            TABLE              PCM
SELECT                                   NO  NISA_DL                        KZN                            KZN                            TABLE              FER
SELECT                                   NO  NISA_DL                        KZN                            KZN                            TABLE              PCM
WRITE                                    NO  NISA_DL                        SYS                            SYS                            DIRECTORY          DMPDIR
READ                                     NO  NISA_DL                        SYS                            SYS                            DIRECTORY          DMPDIR
SELECT                                   NO  NISA_DL                        GLOB                           GLOB                           VIEW               ROOTALL
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search