User Table Privileges report : dba_tab_privs « System Tables Views « Oracle PL / SQL






User Table Privileges report

    

SQL>
SQL>
SQL> SET FEEDBACK OFF
SQL> SET PAGESIZE 20
SQL> SET LINESIZE 77
SQL> SET HEADING ON
SQL>
SQL> COLUMN username FORMAT A12 HEADING 'User'
SQL> COLUMN privilege FORMAT A20 HEADING 'Privilege'
SQL> COLUMN owner FORMAT A12 HEADING 'Table Owner'
SQL> COLUMN table_name FORMAT A30 HEADING 'Table Name'
SQL> TTITLE LEFT 'User Table Privileges' RIGHT 'Page ' FORMAT 999 SQL.PNO SKIP 2
SQL>
SQL> SELECT username, privilege, owner, table_name
  2  FROM dba_users, dba_tab_privs
  3  WHERE username = grantee
  4  ORDER BY username, owner, table_name, privilege;

User Table Privileges                                               Page    1

User         Privilege            Table Owner  Table Name
------------ -------------------- ------------ ------------------------------
ANONYMOUS    EXECUTE              FLOWS_020100 WWV_FLOW_EPG_INCLUDE_MODULES
ANONYMOUS    ALTER                FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    DEBUG                FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    DELETE               FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    FLASHBACK            FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    INDEX                FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    INSERT               FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    ON COMMIT REFRESH    FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    QUERY REWRITE        FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    REFERENCES           FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    SELECT               FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
ANONYMOUS    UPDATE               FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
CTXSYS       SELECT               SYS          ARGUMENT$
CTXSYS       SELECT               SYS          CCOL$
CTXSYS       SELECT               SYS          CDEF$

User Table Privileges                                               Page    2

User         Privilege            Table Owner  Table Name
------------ -------------------- ------------ ------------------------------
CTXSYS       SELECT               SYS          COL$
CTXSYS       SELECT               SYS          COLTYPE$
CTXSYS       SELECT               SYS          CON$
CTXSYS       SELECT               SYS          DBA_COLL_TYPES
CTXSYS       SELECT               SYS          DBA_CONSTRAINTS
CTXSYS       SELECT               SYS          DBA_CONS_COLUMNS
CTXSYS       SELECT               SYS          DBA_DB_LINKS
CTXSYS       SELECT               SYS          DBA_INDEXTYPES
CTXSYS       SELECT               SYS          DBA_JOBS
CTXSYS       SELECT               SYS          DBA_JOBS_RUNNING
CTXSYS       SELECT               SYS          DBA_OBJECTS
CTXSYS       SELECT               SYS          DBA_ROLES
CTXSYS       SELECT               SYS          DBA_ROLE_PRIVS
CTXSYS       SELECT               SYS          DBA_SYNONYMS
CTXSYS       SELECT               SYS          DBA_SYS_PRIVS

User Table Privileges                                               Page    3

User         Privilege            Table Owner  Table Name
------------ -------------------- ------------ ------------------------------
CTXSYS       SELECT               SYS          DBA_TABLES
CTXSYS       SELECT               SYS          DBA_TAB_COLS
CTXSYS       SELECT               SYS          DBA_TAB_COLUMNS
CTXSYS       SELECT               SYS          DBA_TAB_PARTITIONS
CTXSYS       SELECT               SYS          DBA_TAB_PRIVS
CTXSYS       SELECT               SYS          DBA_TYPES
CTXSYS       SELECT               SYS          DBA_TYPE_ATTRS
CTXSYS       SELECT               SYS          DBA_USERS
CTXSYS       EXECUTE              SYS          DBMS_LOCK
CTXSYS       EXECUTE              SYS          DBMS_PIPE
CTXSYS       EXECUTE              SYS          DBMS_REGISTRY
CTXSYS       SELECT               SYS          GV_$PARAMETER
CTXSYS       SELECT               SYS          HIST_HEAD$
CTXSYS       SELECT               SYS          ICOL$
CTXSYS       SELECT               SYS          IND$

User Table Privileges                                               Page    4

User         Privilege            Table Owner  Table Name
------------ -------------------- ------------ ------------------------------
CTXSYS       SELECT               SYS          INDPART$
CTXSYS       SELECT               SYS          LOB$
CTXSYS       SELECT               SYS          LOBFRAG$
CTXSYS       SELECT               SYS          OBJ$
CTXSYS       SELECT               SYS          PARTOBJ$
CTXSYS       SELECT               SYS          SYN$
CTXSYS       SELECT               SYS          SYSAUTH$
CTXSYS       SELECT               SYS          TAB$
CTXSYS       SELECT               SYS          TABPART$
CTXSYS       SELECT               SYS          TS$
CTXSYS       SELECT               SYS          USER$
CTXSYS       EXECUTE              SYS          VALIDATE_CONTEXT
CTXSYS       SELECT               SYS          VIEW$
CTXSYS       SELECT               SYS          V_$PARAMETER
CTXSYS       SELECT               SYS          V_$RESOURCE

User Table Privileges                                               Page    5

User         Privilege            Table Owner  Table Name
------------ -------------------- ------------ ------------------------------
CTXSYS       SELECT               SYS          V_$SESSION
CTXSYS       SELECT               SYS          V_$THREAD
DBSNMP       EXECUTE              SYS          DBMS_SERVER_ALERT
FLOWS_020100 EXECUTE              CTXSYS       CTX_DDL
FLOWS_020100 EXECUTE              CTXSYS       CTX_DOC
FLOWS_020100 ALTER                FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 DEBUG                FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 DELETE               FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 FLASHBACK            FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 INDEX                FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 INSERT               FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 ON COMMIT REFRESH    FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 QUERY REWRITE        FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 REFERENCES           FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 SELECT               FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$

User Table Privileges                                               Page    6

User         Privilege            Table Owner  Table Name
------------ -------------------- ------------ ------------------------------
FLOWS_020100 UPDATE               FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$
FLOWS_020100 SELECT               SYS          DBA_DATA_FILES
FLOWS_020100 SELECT               SYS          DBA_FREE_SPACE
FLOWS_020100 SELECT               SYS          DBA_IND_COLUMNS
FLOWS_020100 SELECT               SYS          DBA_OBJECTS
FLOWS_020100 SELECT               SYS          DBA_ROLE_PRIVS
FLOWS_020100 SELECT               SYS          DBA_ROLLBACK_SEGS
FLOWS_020100 SELECT               SYS          DBA_SEGMENTS
FLOWS_020100 SELECT               SYS          DBA_SEQUENCES
FLOWS_020100 SELECT               SYS          DBA_SYS_PRIVS
FLOWS_020100 SELECT               SYS          DBA_TABLES
FLOWS_020100 SELECT               SYS          DBA_TABLESPACES
FLOWS_020100 SELECT               SYS          DBA_TAB_COLUMNS
FLOWS_020100 SELECT               SYS          DBA_TAB_PRIVS
SQL>
SQL>
SQL> SET FEEDBACK ON
SQL>

   
    
    
    
  








Related examples in the same category

1.Query dba_tab_privs table to get table privilege
2.Searches for grants made by users other than the table owners