Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Tables Data Dictionary

 

Find out all dba table privileges

SQL> SQL> COLUMN     object FORMAT a25 SQL> COLUMN     grantee FORMAT a15 SQL> SELECT     owner || '.' || table_name object,   2             privilege what_granted, grantable, grantee   3  FROM       dba_tab_privs   4  WHERE      NOT EXISTS   5  (SELECT    'x'   6  FROM       dba_users   7  WHERE      username = grantee)   8  and        rownum < 50   9  AND        privilege =  'EXECUTE'  10  ORDER BY   1,2,3; OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.ANYDATA               EXECUTE                                  YES PUBLIC SYS.ANYDATASET            EXECUTE                                  YES PUBLIC SYS.ANYTYPE               EXECUTE                                  YES PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.CREATE_TABLE_COST_COL EXECUTE                                  NO INFO PUBLIC SYS.CREATE_TABLE_COST_COL EXECUTE                                  NO UMNS PUBLIC SYS.DBMSOUTPUT_LINESARRAY EXECUTE                                  NO OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- PUBLIC SYS.DBMS_ADVISOR          EXECUTE                                  NO PUBLIC SYS.DBMS_ALERT            EXECUTE                                  NO EXECUTE_CATALOG _ROLE OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.DBMS_APPLICATION_INFO EXECUTE                                  NO PUBLIC SYS.DBMS_ASSERT           EXECUTE                                  NO PUBLIC SYS.DBMS_DESCRIBE         EXECUTE                                  NO PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.DBMS_ERRLOG           EXECUTE                                  NO PUBLIC SYS.DBMS_EXPORT_EXTENSION EXECUTE                                  NO PUBLIC SYS.DBMS_JAVA_TEST        EXECUTE                                  NO PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.DBMS_JOB              EXECUTE                                  YES PUBLIC SYS.DBMS_LOB              EXECUTE                                  NO PUBLIC SYS.DBMS_LOCK             EXECUTE                                  NO EXECUTE_CATALOG _ROLE OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.DBMS_OUTPUT           EXECUTE                                  NO PUBLIC SYS.DBMS_PCLXUTIL         EXECUTE                                  NO PUBLIC SYS.DBMS_PICKLER          EXECUTE                                  NO PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.DBMS_PIPE             EXECUTE                                  NO EXECUTE_CATALOG _ROLE SYS.DBMS_ROWID            EXECUTE                                  NO PUBLIC SYS.DBMS_SESSION          EXECUTE                                  NO OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- PUBLIC SYS.DBMS_SPACE            EXECUTE                                  NO PUBLIC SYS.DBMS_SQL              EXECUTE                                  NO PUBLIC SYS.DBMS_STANDARD         EXECUTE                                  NO OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- PUBLIC SYS.DBMS_TRANSACTION      EXECUTE                                  NO PUBLIC SYS.DBMS_TYPES            EXECUTE                                  NO PUBLIC SYS.DBMS_UTILITY          EXECUTE                                  NO OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- PUBLIC SYS.DBMS_WARNING          EXECUTE                                  YES PUBLIC SYS.DIANA                 EXECUTE                                  NO EXECUTE_CATALOG _ROLE OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.DIUTIL                EXECUTE                                  NO PUBLIC SYS.GETTVOID              EXECUTE                                  NO PUBLIC SYS.ODCICOLINFO           EXECUTE                                  YES PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.PIDL                  EXECUTE                                  NO EXECUTE_CATALOG _ROLE SYS.PLITBLM               EXECUTE                                  NO PUBLIC SYS.PSTUB                 EXECUTE                                  NO PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.PSTUBT                EXECUTE                                  NO PUBLIC SYS.RE$NV_LIST            EXECUTE                                  NO PUBLIC SYS.STANDARD              EXECUTE                                  NO PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.SUBPTXT               EXECUTE                                  NO PUBLIC SYS.UTL_COMPRESS          EXECUTE                                  NO PUBLIC SYS.UTL_ENCODE            EXECUTE                                  NO PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.UTL_GDK               EXECUTE                                  NO PUBLIC SYS.UTL_I18N              EXECUTE                                  NO PUBLIC SYS.UTL_INADDR            EXECUTE                                  NO PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- SYS.UTL_LMS               EXECUTE                                  NO PUBLIC SYS.UTL_RAW               EXECUTE                                  NO PUBLIC SYS.UTL_URL               EXECUTE                                  NO PUBLIC OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- GRANTEE --------------- 49 rows selected. SQL>