Oracle PL/SQL/System Tables Views/dba tab privs
Query dba_tab_privs table to get table privilege
SQL>
SQL>
SQL> set verify off
SQL> set linesize 78
SQL> set pagesize 9999
SQL> set feedback on
SQL>
SQL>
SQL> column tab format a24 heading "Schema.Object"
SQL> column privilege format a24 heading "Privilege"
SQL> column grantor format a20 heading "Granted By"
SQL> column grantable format a6 heading "Admin?"
SQL>
SQL> select owner || "." || table_name tab,
2 privilege,
3 grantor,
4 grantable
5 from dba_tab_privs
6 where rownum < 50
7 order by owner, table_name, privilege
8 /
Schema.Object Privilege Granted By Admin?
------------------------ ------------------------ -------------------- ------
SYS.ARGUMENT$ SELECT SYS YES
SYS.CCOL$ SELECT SYS YES
SYS.CDEF$ SELECT SYS YES
SYS.COL$ SELECT SYS YES
SYS.CON$ SELECT SYS YES
SYS.ICOL$ SELECT SYS YES
SYS.INCEXP ALTER SYS NO
SYS.INCEXP DEBUG SYS NO
SYS.INCEXP DELETE SYS NO
SYS.INCEXP DELETE SYS NO
SYS.INCEXP FLASHBACK SYS NO
SYS.INCEXP INDEX SYS NO
SYS.INCEXP INSERT SYS NO
SYS.INCEXP INSERT SYS NO
SYS.INCEXP ON COMMIT REFRESH SYS NO
SYS.INCEXP QUERY REWRITE SYS NO
SYS.INCEXP REFERENCES SYS NO
SYS.INCEXP SELECT SYS NO
SYS.INCEXP UPDATE SYS NO
SYS.INCEXP UPDATE SYS NO
SYS.INCFIL DELETE SYS NO
SYS.INCFIL FLASHBACK SYS NO
SYS.INCFIL INSERT SYS NO
SYS.INCFIL UPDATE SYS NO
SYS.INCVID ALTER SYS NO
SYS.INCVID DEBUG SYS NO
SYS.INCVID DELETE SYS NO
SYS.INCVID DELETE SYS NO
SYS.INCVID FLASHBACK SYS NO
SYS.INCVID INDEX SYS NO
SYS.INCVID INSERT SYS NO
SYS.INCVID INSERT SYS NO
SYS.INCVID ON COMMIT REFRESH SYS NO
SYS.INCVID QUERY REWRITE SYS NO
SYS.INCVID REFERENCES SYS NO
SYS.INCVID SELECT SYS NO
SYS.INCVID UPDATE SYS NO
SYS.INCVID UPDATE SYS NO
SYS.IND$ SELECT SYS YES
SYS.OBJ$ SELECT SYS NO
SYS.OBJ$ SELECT SYS YES
SYS.SYN$ SELECT SYS YES
SYS.SYSAUTH$ SELECT SYS YES
SYS.TAB$ SELECT SYS YES
SYS.TS$ SELECT SYS YES
SYS.USER$ SELECT SYS YES
SYS.USER$ SELECT SYS NO
SYS.USER$ SELECT SYS YES
SYS.VIEW$ SELECT SYS YES
49 rows selected.
SQL>
SQL>
SQL> --
Searches for grants made by users other than the table owners
SQL> break on Grantor skip 1 on Owner on Table_Name
SQL> select
2 Grantor, /*Account that made the grant*/
3 Owner, /*Account that owns the table*/
4 Table_Name, /*Name of the table*/
5 Grantee, /*Account granted access*/
6 Privilege, /*Privilege granted*/
7 Grantable /*Granted with admin option?*/
8 from DBA_TAB_PRIVS
9 where rownum < 10
10 order by Grantor, Owner, Table_Name, Grantee, Privilege;
GRANTOR OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------
GRANTEE PRIVILEGE GRA
------------------------------ ---------------------------------------- ---
SYS SYS CCOL$
CTXSYS SELECT YES
COL$
CTXSYS SELECT YES
CON$
CTXSYS SELECT YES
ICOL$
CTXSYS SELECT YES
IND$
CTXSYS SELECT YES
TS$
CTXSYS SELECT YES
USER$
CTXSYS SELECT YES
FLOWS_020100 SELECT YES
XDB SELECT NO
SQL>
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>