Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Tables Data Dictionary

 

Join dba_users and dba_tab_privs to find out user privileges

SQL> SQL> COLUMN     object FORMAT a25 SQL> COLUMN     grantee FORMAT a15 SQL> SELECT     b.owner || '.' || b.table_name object,   2             b.privilege what_granted, b.grantable, a.username   3  FROM       dba_users a, dba_tab_privs b   4  WHERE      a.username = b.grantee   5  AND        privilege = 'EXECUTE'   6  and        rownum < 50   7  ORDER BY   1,2,3; OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ CTXSYS.CTX_DDL            EXECUTE                                  NO XDB CTXSYS.CTX_DDL            EXECUTE                                  YES FLOWS_020100 CTXSYS.CTX_DOC            EXECUTE                                  YES FLOWS_020100 OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ CTXSYS.CTX_OUTPUT         EXECUTE                                  NO XDB FLOWS_020100.WWV_FLOW     EXECUTE                                  NO FLOWS_FILES FLOWS_020100.WWV_FLOW_EPG EXECUTE                                  NO _INCLUDE_MODULES ANONYMOUS OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ FLOWS_020100.WWV_FLOW_FIL EXECUTE                                  NO E_API FLOWS_FILES FLOWS_020100.WWV_FLOW_FIL EXECUTE                                  NO E_OBJECT_ID FLOWS_FILES OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ FLOWS_020100.WWV_FLOW_ID  EXECUTE                                  NO FLOWS_FILES FLOWS_020100.WWV_FLOW_SEC EXECUTE                                  NO URITY FLOWS_FILES SYS.CHECK_UPGRADE         EXECUTE                                  NO SYSTEM OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.CHECK_UPGRADE         EXECUTE                                  NO XDB SYS.DBMS_ALERT            EXECUTE                                  NO SYSTEM SYS.DBMS_AQ               EXECUTE                                  YES SYSTEM OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.DBMS_AQADM            EXECUTE                                  YES SYSTEM SYS.DBMS_AQELM            EXECUTE                                  YES SYSTEM SYS.DBMS_AQ_BQVIEW        EXECUTE                                  NO RNTSOFT OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.DBMS_AQ_IMPORT_INTERN EXECUTE                                  YES AL SYSTEM SYS.DBMS_CRYPTO           EXECUTE                                  NO FLOWS_020100 SYS.DBMS_DEFER_IMPORT_INT EXECUTE                                  NO OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ ERNAL SYSTEM SYS.DBMS_FLASHBACK        EXECUTE                                  NO FLOWS_020100 SYS.DBMS_LOCK             EXECUTE                                  NO FLOWS_020100 OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.DBMS_LOCK             EXECUTE                                  NO CTXSYS SYS.DBMS_LOCK             EXECUTE                                  NO MDSYS SYS.DBMS_PIPE             EXECUTE                                  NO CTXSYS OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.DBMS_REGISTRY         EXECUTE                                  NO CTXSYS SYS.DBMS_REGISTRY         EXECUTE                                  NO MDSYS SYS.DBMS_REGISTRY         EXECUTE                                  NO XDB OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.DBMS_REPCAT           EXECUTE                                  NO SYSTEM SYS.DBMS_RLS              EXECUTE                                  NO XDB SYS.DBMS_RLS              EXECUTE                                  YES FLOWS_020100 OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.DBMS_RULE_EXIMP       EXECUTE                                  YES SYSTEM SYS.DBMS_SERVER_ALERT     EXECUTE                                  NO DBSNMP SYS.DBMS_STATS            EXECUTE                                  NO HR OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.DBMS_SYSTEM           EXECUTE                                  NO MDSYS SYS.DBMS_SYS_ERROR        EXECUTE                                  NO SYSTEM SYS.DBMS_SYS_SQL          EXECUTE                                  NO FLOWS_020100 OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.DBMS_SYS_SQL          EXECUTE                                  NO XDB SYS.DBMS_TRANSFORM_EXIMP  EXECUTE                                  YES SYSTEM SYS.OUTLN_PKG             EXECUTE                                  NO OUTLN OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.SET_TABLESPACE        EXECUTE                                  NO XDB SYS.SET_TABLESPACE        EXECUTE                                  NO SYSTEM SYS.SYS_GROUP             EXECUTE                                  NO SYSTEM OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.UTL_FILE              EXECUTE                                  NO XDB SYS.UTL_FILE              EXECUTE                                  NO FLOWS_020100 SYS.UTL_HTTP              EXECUTE                                  NO FLOWS_020100 OBJECT                    WHAT_GRANTED                             GRA ------------------------- ---------------------------------------- --- USERNAME ------------------------------ SYS.UTL_SMTP              EXECUTE                                  NO FLOWS_020100 SYS.VALIDATE_CONTEXT      EXECUTE                                  NO CTXSYS SYS.WWV_FLOW_VAL          EXECUTE                                  NO FLOWS_020100 49 rows selected. SQL>