Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba tab privs
Содержание
Find out all dba table privileges
<source lang="sql">
SQL> SQL> COLUMN object FORMAT a25 SQL> COLUMN grantee FORMAT a15 SQL> SELECT owner || "." || table_name object,
2 privilege what_granted, grantable, grantee 3 FROM dba_tab_privs 4 WHERE NOT EXISTS 5 (SELECT "x" 6 FROM dba_users 7 WHERE username = grantee) 8 and rownum < 50 9 AND privilege = "EXECUTE" 10 ORDER BY 1,2,3;
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.ANYDATA EXECUTE YES PUBLIC SYS.ANYDATASET EXECUTE YES PUBLIC SYS.ANYTYPE EXECUTE YES PUBLIC
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.CREATE_TABLE_COST_COL EXECUTE NO INFO PUBLIC SYS.CREATE_TABLE_COST_COL EXECUTE NO UMNS PUBLIC SYS.DBMSOUTPUT_LINESARRAY EXECUTE NO OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
PUBLIC SYS.DBMS_ADVISOR EXECUTE NO PUBLIC SYS.DBMS_ALERT EXECUTE NO EXECUTE_CATALOG _ROLE
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.DBMS_APPLICATION_INFO EXECUTE NO PUBLIC SYS.DBMS_ASSERT EXECUTE NO PUBLIC SYS.DBMS_DESCRIBE EXECUTE NO PUBLIC
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.DBMS_ERRLOG EXECUTE NO PUBLIC SYS.DBMS_EXPORT_EXTENSION EXECUTE NO PUBLIC SYS.DBMS_JAVA_TEST EXECUTE NO PUBLIC
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.DBMS_JOB EXECUTE YES PUBLIC SYS.DBMS_LOB EXECUTE NO PUBLIC SYS.DBMS_LOCK EXECUTE NO EXECUTE_CATALOG _ROLE OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.DBMS_OUTPUT EXECUTE NO PUBLIC SYS.DBMS_PCLXUTIL EXECUTE NO PUBLIC SYS.DBMS_PICKLER EXECUTE NO PUBLIC OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.DBMS_PIPE EXECUTE NO EXECUTE_CATALOG _ROLE SYS.DBMS_ROWID EXECUTE NO PUBLIC SYS.DBMS_SESSION EXECUTE NO OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
PUBLIC SYS.DBMS_SPACE EXECUTE NO PUBLIC SYS.DBMS_SQL EXECUTE NO PUBLIC SYS.DBMS_STANDARD EXECUTE NO OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
PUBLIC SYS.DBMS_TRANSACTION EXECUTE NO PUBLIC SYS.DBMS_TYPES EXECUTE NO PUBLIC SYS.DBMS_UTILITY EXECUTE NO OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
PUBLIC SYS.DBMS_WARNING EXECUTE YES PUBLIC SYS.DIANA EXECUTE NO EXECUTE_CATALOG _ROLE
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.DIUTIL EXECUTE NO PUBLIC SYS.GETTVOID EXECUTE NO PUBLIC SYS.ODCICOLINFO EXECUTE YES PUBLIC
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.PIDL EXECUTE NO EXECUTE_CATALOG _ROLE SYS.PLITBLM EXECUTE NO PUBLIC SYS.PSTUB EXECUTE NO PUBLIC OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.PSTUBT EXECUTE NO PUBLIC SYS.RE$NV_LIST EXECUTE NO PUBLIC SYS.STANDARD EXECUTE NO PUBLIC OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.SUBPTXT EXECUTE NO PUBLIC SYS.UTL_COMPRESS EXECUTE NO PUBLIC SYS.UTL_ENCODE EXECUTE NO PUBLIC OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.UTL_GDK EXECUTE NO PUBLIC SYS.UTL_I18N EXECUTE NO PUBLIC SYS.UTL_INADDR EXECUTE NO PUBLIC OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
SYS.UTL_LMS EXECUTE NO PUBLIC SYS.UTL_RAW EXECUTE NO PUBLIC SYS.UTL_URL EXECUTE NO PUBLIC OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
GRANTEE
49 rows selected. SQL> SQL></source>
Object Grants to Roles
<source lang="sql">
SQL> SQL> SQL> SELECT owner || "." || table_name obj,
2 privilege what_granted, grantable, grantee 3 FROM sys.dba_tab_privs 4 WHERE NOT EXISTS 5 (SELECT "x" 6 FROM sys.dba_users 7 WHERE username = grantee) 8 and rownum < 50 9 ORDER BY 1,2,3;
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
OUTLN.OL$ SELECT NO SELECT_CATALOG_ROLE OUTLN.OL$HINTS SELECT NO SELECT_CATALOG_ROLE OUTLN.OL$NODES SELECT NO SELECT_CATALOG_ROLE
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.AUD$ DELETE NO DELETE_CATALOG_ROLE SYS.AUX_STATS$ DELETE NO GATHER_SYSTEM_STATISTICS SYS.AUX_STATS$ INSERT NO GATHER_SYSTEM_STATISTICS
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.AUX_STATS$ SELECT NO GATHER_SYSTEM_STATISTICS SYS.AUX_STATS$ UPDATE NO GATHER_SYSTEM_STATISTICS SYS.AW$ DEBUG NO DBA
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.AW$ SELECT NO DBA SYS.AWSEQ$ ALTER NO DBA SYS.AWSEQ$ SELECT NO DBA
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.AW_OBJ$ DEBUG NO DBA SYS.AW_OBJ$ SELECT NO DBA SYS.AW_PROP$ DEBUG NO DBA
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.AW_PROP$ SELECT NO DBA SYS.DBMS_STANDARD EXECUTE NO PUBLIC SYS.DUAL SELECT YES PUBLIC
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.EXPIMP_TTS_CT$ DELETE NO IMP_FULL_DATABASE SYS.EXPIMP_TTS_CT$ INSERT NO IMP_FULL_DATABASE SYS.EXPIMP_TTS_CT$ SELECT NO IMP_FULL_DATABASE
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.EXPIMP_TTS_CT$ UPDATE NO IMP_FULL_DATABASE SYS.FGA_LOG$ DELETE NO DELETE_CATALOG_ROLE SYS.INCEXP DELETE NO EXP_FULL_DATABASE
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.INCEXP INSERT NO EXP_FULL_DATABASE SYS.INCEXP UPDATE NO EXP_FULL_DATABASE SYS.INCFIL DELETE NO EXP_FULL_DATABASE
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.INCFIL INSERT NO EXP_FULL_DATABASE SYS.INCFIL UPDATE NO EXP_FULL_DATABASE SYS.INCVID DELETE NO EXP_FULL_DATABASE
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.INCVID INSERT NO EXP_FULL_DATABASE SYS.INCVID UPDATE NO EXP_FULL_DATABASE SYS.MAP_OBJECT ALTER NO DBA
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.MAP_OBJECT DEBUG NO DBA SYS.MAP_OBJECT DELETE NO DBA SYS.MAP_OBJECT FLASHBACK NO DBA
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.MAP_OBJECT INSERT NO DBA SYS.MAP_OBJECT ON COMMIT REFRESH NO DBA SYS.MAP_OBJECT QUERY REWRITE NO DBA
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.MAP_OBJECT SELECT NO DBA SYS.MAP_OBJECT SELECT NO SELECT_CATALOG_ROLE SYS.MAP_OBJECT UPDATE NO DBA
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.PS$ DEBUG NO DBA SYS.PS$ SELECT NO DBA SYS.RE$NV_LIST EXECUTE NO PUBLIC
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.STANDARD EXECUTE NO PUBLIC SYS.STMT_AUDIT_OPTION_MAP SELECT NO PUBLIC SYS.SYSTEM_PRIVILEGE_MAP SELECT YES PUBLIC
OBJ
WHAT_GRANTED GRA GRANTEE
--- ------------------------------
SYS.TABLE_PRIVILEGE_MAP SELECT YES PUBLIC
49 rows selected. SQL></source>
Object Grants to Users
<source lang="sql">
SQL> SQL> SQL> SELECT b.owner || "." || b.table_name obj,
2 b.privilege what_granted, b.grantable, 3 a.username 4 FROM sys.dba_users a, sys.dba_tab_privs b 5 WHERE a.username = b.grantee 6 and rownum < 50 7 ORDER BY 1,2,3;
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.ARGUMENT$ SELECT YES CTXSYS SYS.CCOL$ SELECT YES CTXSYS SYS.CDEF$ SELECT YES CTXSYS
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.COL$ SELECT YES CTXSYS SYS.CON$ SELECT YES CTXSYS SYS.ICOL$ SELECT YES CTXSYS
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCEXP ALTER NO SYSTEM SYS.INCEXP DEBUG NO SYSTEM SYS.INCEXP DELETE NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCEXP FLASHBACK NO SYSTEM SYS.INCEXP INDEX NO SYSTEM SYS.INCEXP INSERT NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCEXP ON COMMIT REFRESH NO SYSTEM SYS.INCEXP QUERY REWRITE NO SYSTEM SYS.INCEXP REFERENCES NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCEXP SELECT NO SYSTEM SYS.INCEXP UPDATE NO SYSTEM SYS.INCFIL DEBUG NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCFIL DELETE NO SYSTEM SYS.INCFIL FLASHBACK NO SYSTEM SYS.INCFIL INDEX NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCFIL INSERT NO SYSTEM SYS.INCFIL ON COMMIT REFRESH NO SYSTEM SYS.INCFIL QUERY REWRITE NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCFIL REFERENCES NO SYSTEM SYS.INCFIL SELECT NO SYSTEM SYS.INCFIL UPDATE NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCVID ALTER NO SYSTEM SYS.INCVID DEBUG NO SYSTEM SYS.INCVID DELETE NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCVID FLASHBACK NO SYSTEM SYS.INCVID INDEX NO SYSTEM SYS.INCVID INSERT NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCVID ON COMMIT REFRESH NO SYSTEM SYS.INCVID QUERY REWRITE NO SYSTEM SYS.INCVID REFERENCES NO SYSTEM
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.INCVID SELECT NO SYSTEM SYS.INCVID UPDATE NO SYSTEM SYS.IND$ SELECT YES CTXSYS
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.OBJ$ SELECT NO FLOWS_020100 SYS.OBJ$ SELECT YES CTXSYS SYS.SYN$ SELECT YES CTXSYS
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.SYSAUTH$ SELECT YES CTXSYS SYS.TAB$ SELECT YES CTXSYS SYS.TS$ SELECT YES CTXSYS
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.USER$ SELECT NO XDB SYS.USER$ SELECT YES CTXSYS SYS.USER$ SELECT YES FLOWS_020100
OBJ
WHAT_GRANTED GRA USERNAME
--- ------------------------------
SYS.VIEW$ SELECT YES CTXSYS
49 rows selected. SQL></source>
User Table Privileges report
<source lang="sql">
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></source>
