Join dba_users, dba_tab_privs : dba_users « System Tables Views « Oracle PL / SQL






Join dba_users, dba_tab_privs

  
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$

SQL> SET FEEDBACK ON
SQL>

   
    
  








Related examples in the same category

1.Query dba_users table
2.Displaying the Account Status of All Users
3.Verifying Resource Consumer Group Membership of Users
4.Query dba_users table for default_tablespace and temporary_tablespace
5.Join dba_users, dba_sys_privs
6.Check user name and password for users