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

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » System Tables Views » dba_users 
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
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.