Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba users
Содержание
Check user name and password for users
<source lang="sql">
SQL> select
2 Username, /*Username*/
3 Password /*Encrypted password*/
4 from DBA_USERS
5 where Username in ("MCGREGOR","THUMPER","OPS$FARMER");
SQL> SQL> SQL></source>
Join dba_users and dba_tab_privs to find out user privileges
<source lang="sql">
SQL> SQL> SQL> COLUMN object FORMAT a25 SQL> COLUMN grantee FORMAT a15 SQL> SELECT b.owner || "." || b.table_name object,
2 b.privilege what_granted, b.grantable, a.username 3 FROM dba_users a, dba_tab_privs b 4 WHERE a.username = b.grantee 5 AND privilege = "EXECUTE" 6 and rownum < 50 7 ORDER BY 1,2,3;
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
CTXSYS.CTX_DDL EXECUTE NO XDB CTXSYS.CTX_DDL EXECUTE YES FLOWS_020100 CTXSYS.CTX_DOC EXECUTE YES FLOWS_020100
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
CTXSYS.CTX_OUTPUT EXECUTE NO XDB FLOWS_020100.WWV_FLOW EXECUTE NO FLOWS_FILES FLOWS_020100.WWV_FLOW_EPG EXECUTE NO _INCLUDE_MODULES ANONYMOUS OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
FLOWS_020100.WWV_FLOW_FIL EXECUTE NO E_API FLOWS_FILES FLOWS_020100.WWV_FLOW_FIL EXECUTE NO E_OBJECT_ID FLOWS_FILES
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
FLOWS_020100.WWV_FLOW_ID EXECUTE NO FLOWS_FILES FLOWS_020100.WWV_FLOW_SEC EXECUTE NO URITY FLOWS_FILES SYS.CHECK_UPGRADE EXECUTE NO SYSTEM OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.CHECK_UPGRADE EXECUTE NO XDB SYS.DBMS_ALERT EXECUTE NO SYSTEM SYS.DBMS_AQ EXECUTE YES SYSTEM OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.DBMS_AQADM EXECUTE YES SYSTEM SYS.DBMS_AQELM EXECUTE YES SYSTEM SYS.DBMS_AQ_BQVIEW EXECUTE NO sqle OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.DBMS_AQ_IMPORT_INTERN EXECUTE YES AL SYSTEM SYS.DBMS_CRYPTO EXECUTE NO FLOWS_020100 SYS.DBMS_DEFER_IMPORT_INT EXECUTE NO OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
ERNAL SYSTEM SYS.DBMS_FLASHBACK EXECUTE NO FLOWS_020100 SYS.DBMS_LOCK EXECUTE NO FLOWS_020100
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.DBMS_LOCK EXECUTE NO CTXSYS SYS.DBMS_LOCK EXECUTE NO MDSYS SYS.DBMS_PIPE EXECUTE NO CTXSYS
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.DBMS_REGISTRY EXECUTE NO CTXSYS SYS.DBMS_REGISTRY EXECUTE NO MDSYS SYS.DBMS_REGISTRY EXECUTE NO XDB
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.DBMS_REPCAT EXECUTE NO SYSTEM SYS.DBMS_RLS EXECUTE NO XDB SYS.DBMS_RLS EXECUTE YES FLOWS_020100
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.DBMS_RULE_EXIMP EXECUTE YES SYSTEM SYS.DBMS_SERVER_ALERT EXECUTE NO DBSNMP SYS.DBMS_STATS EXECUTE NO HR
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.DBMS_SYSTEM EXECUTE NO MDSYS SYS.DBMS_SYS_ERROR EXECUTE NO SYSTEM SYS.DBMS_SYS_SQL EXECUTE NO FLOWS_020100
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.DBMS_SYS_SQL EXECUTE NO XDB SYS.DBMS_TRANSFORM_EXIMP EXECUTE YES SYSTEM SYS.OUTLN_PKG EXECUTE NO OUTLN
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.SET_TABLESPACE EXECUTE NO XDB SYS.SET_TABLESPACE EXECUTE NO SYSTEM SYS.SYS_GROUP EXECUTE NO SYSTEM
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.UTL_FILE EXECUTE NO XDB SYS.UTL_FILE EXECUTE NO FLOWS_020100 SYS.UTL_HTTP EXECUTE NO FLOWS_020100
OBJECT WHAT_GRANTED GRA
---------------------------------------- ---
USERNAME
SYS.UTL_SMTP EXECUTE NO FLOWS_020100 SYS.VALIDATE_CONTEXT EXECUTE NO CTXSYS SYS.WWV_FLOW_VAL EXECUTE NO FLOWS_020100
49 rows selected. SQL></source>
Join dba_users, dba_tab_privs
<source lang="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$ SQL> SET FEEDBACK ON SQL></source>
Union user name from sys.dba_users table
<source lang="sql">
SQL> SQL> SELECT a.username,
2 b.granted_role || DECODE(admin_option,"YES", 3 " (With Admin Option)",NULL) what_granted 4 FROM sys.dba_users a, sys.dba_role_privs b 5 WHERE a.username = b.grantee and rownum < 50 6 UNION 7 SELECT a.username, 8 b.privilege || DECODE(admin_option,"YES", 9 " (With Admin Option)", NULL) what_granted 10 FROM sys.dba_users a, sys.dba_sys_privs b 11 WHERE a.username = b.grantee and rownum < 50 12 UNION 13 SELECT a.username, 14 b.table_name || " - " || b.privilege 15 || DECODE(grantable,"YES", 16 " (With Grant Option)",NULL) what_granted 17 FROM sys.dba_users a, sys.dba_tab_privs b 18 WHERE a.username = b.grantee and rownum < 50 19 ORDER BY 1;
USERNAME
WHAT_GRANTED
CTXSYS ARGUMENT$ - SELECT (With Grant Option) CTXSYS CCOL$ - SELECT (With Grant Option) CTXSYS CDEF$ - SELECT (With Grant Option)
USERNAME
WHAT_GRANTED
CTXSYS COL$ - SELECT (With Grant Option) CTXSYS CON$ - SELECT (With Grant Option) CTXSYS CREATE SESSION
USERNAME
WHAT_GRANTED
CTXSYS CTXAPP (With Admin Option) CTXSYS ICOL$ - SELECT (With Grant Option) CTXSYS IND$ - SELECT (With Grant Option)
USERNAME
WHAT_GRANTED
CTXSYS OBJ$ - SELECT (With Grant Option) CTXSYS RESOURCE CTXSYS SYN$ - SELECT (With Grant Option)
USERNAME
WHAT_GRANTED
CTXSYS SYSAUTH$ - SELECT (With Grant Option) CTXSYS TAB$ - SELECT (With Grant Option) CTXSYS TS$ - SELECT (With Grant Option)
USERNAME
WHAT_GRANTED
CTXSYS USER$ - SELECT (With Grant Option) CTXSYS VIEW$ - SELECT (With Grant Option) DBSNMP CREATE PROCEDURE
USERNAME
WHAT_GRANTED
DBSNMP OEM_MONITOR FLOWS_020100 ALTER USER FLOWS_020100 CONNECT (With Admin Option)
USERNAME
WHAT_GRANTED
FLOWS_020100 CREATE ANY CONTEXT (With Admin Option) FLOWS_020100 CREATE ANY DIRECTORY FLOWS_020100 CREATE DATABASE LINK (With Admin Option)
USERNAME
WHAT_GRANTED
FLOWS_020100 CREATE DIMENSION (With Admin Option) FLOWS_020100 CREATE LIBRARY (With Admin Option) FLOWS_020100 CREATE PUBLIC SYNONYM
USERNAME
WHAT_GRANTED
FLOWS_020100 CREATE SYNONYM (With Admin Option) FLOWS_020100 CREATE USER FLOWS_020100 DBA (With Admin Option)
USERNAME
WHAT_GRANTED
FLOWS_020100 DROP ANY DIRECTORY FLOWS_020100 OBJ$ - SELECT FLOWS_020100 RESOURCE (With Admin Option)
USERNAME
WHAT_GRANTED
FLOWS_020100 SELECT_CATALOG_ROLE FLOWS_020100 USER$ - SELECT (With Grant Option) FLOWS_FILES CONNECT
USERNAME
WHAT_GRANTED
FLOWS_FILES CREATE ANY SYNONYM FLOWS_FILES CREATE PUBLIC SYNONYM FLOWS_FILES RESOURCE
USERNAME
WHAT_GRANTED
HR CONNECT HR CREATE DATABASE LINK HR CREATE VIEW
USERNAME
WHAT_GRANTED
HR RESOURCE HR UNLIMITED TABLESPACE sqle DBA
USERNAME
WHAT_GRANTED
MDSYS CONNECT MDSYS CREATE OPERATOR MDSYS CREATE SEQUENCE
USERNAME
WHAT_GRANTED
MDSYS CREATE TABLE MDSYS CREATE TYPE MDSYS CREATE VIEW
USERNAME
WHAT_GRANTED
MDSYS DELETE ANY TABLE MDSYS RESOURCE MDSYS UNLIMITED TABLESPACE
USERNAME
WHAT_GRANTED
OUTLN RESOURCE OUTLN UNLIMITED TABLESPACE PLSQL CONNECT
USERNAME
WHAT_GRANTED
PLSQL CTXAPP PLSQL RESOURCE PLSQL UNLIMITED TABLESPACE
USERNAME
WHAT_GRANTED
SYS ALTER ANY RULE (With Admin Option) SYS AQ_ADMINISTRATOR_ROLE (With Admin Option) SYS AQ_USER_ROLE (With Admin Option)
USERNAME
WHAT_GRANTED
SYS AUTHENTICATEDUSER (With Admin Option) SYS CONNECT (With Admin Option) SYS CREATE ANY EVALUATION CONTEXT (With Admin Option)
USERNAME
WHAT_GRANTED
SYS CREATE ANY RULE (With Admin Option) SYS CREATE RULE SET (With Admin Option) SYS CTXAPP (With Admin Option)
USERNAME
WHAT_GRANTED
SYS DBA (With Admin Option) SYS DELETE_CATALOG_ROLE (With Admin Option) SYS ENQUEUE ANY QUEUE (With Admin Option)
USERNAME
WHAT_GRANTED
SYS EXECUTE ANY EVALUATION CONTEXT (With Admin Option) SYS EXECUTE ANY PROCEDURE SYS EXECUTE ANY TYPE
USERNAME
WHAT_GRANTED
SYS EXECUTE_CATALOG_ROLE (With Admin Option) SYS EXP_FULL_DATABASE (With Admin Option) SYS GATHER_SYSTEM_STATISTICS (With Admin Option)
USERNAME
WHAT_GRANTED
SYS HS_ADMIN_ROLE (With Admin Option) SYS IMP_FULL_DATABASE (With Admin Option) SYS INSERT ANY TABLE
USERNAME
WHAT_GRANTED
SYS LOCK ANY TABLE SYS LOGSTDBY_ADMINISTRATOR (With Admin Option) SYS MANAGE ANY QUEUE (With Admin Option)
USERNAME
WHAT_GRANTED
SYS OEM_ADVISOR (With Admin Option) SYS OEM_MONITOR (With Admin Option) SYS PLUSTRACE (With Admin Option)
USERNAME
WHAT_GRANTED
SYS RECOVERY_CATALOG_OWNER (With Admin Option) SYS RESOURCE (With Admin Option) SYS SCHEDULER_ADMIN (With Admin Option)
USERNAME
WHAT_GRANTED
SYS SELECT ANY TABLE (With Admin Option) SYS SELECT_CATALOG_ROLE (With Admin Option) SYS UPDATE ANY TABLE
USERNAME
WHAT_GRANTED
SYS XDBADMIN (With Admin Option) SYS XDBWEBSERVICES (With Admin Option) SYSTEM AQ_ADMINISTRATOR_ROLE (With Admin Option)
USERNAME
WHAT_GRANTED
SYSTEM CREATE MATERIALIZED VIEW SYSTEM CREATE TABLE SYSTEM DBA (With Admin Option)
USERNAME
WHAT_GRANTED
SYSTEM GLOBAL QUERY REWRITE SYSTEM INCEXP - ALTER SYSTEM INCEXP - DEBUG
USERNAME
WHAT_GRANTED
SYSTEM INCEXP - DELETE SYSTEM INCEXP - FLASHBACK SYSTEM INCEXP - INDEX
USERNAME
WHAT_GRANTED
SYSTEM INCEXP - INSERT SYSTEM INCEXP - ON COMMIT REFRESH SYSTEM INCEXP - QUERY REWRITE
USERNAME
WHAT_GRANTED
SYSTEM INCEXP - REFERENCES SYSTEM INCEXP - SELECT SYSTEM INCEXP - UPDATE
USERNAME
WHAT_GRANTED
SYSTEM INCFIL - DEBUG SYSTEM INCFIL - DELETE SYSTEM INCFIL - FLASHBACK
USERNAME
WHAT_GRANTED
SYSTEM INCFIL - INDEX SYSTEM INCFIL - INSERT SYSTEM INCFIL - ON COMMIT REFRESH
USERNAME
WHAT_GRANTED
SYSTEM INCFIL - QUERY REWRITE SYSTEM INCFIL - REFERENCES SYSTEM INCFIL - SELECT
USERNAME
WHAT_GRANTED
SYSTEM INCFIL - UPDATE SYSTEM INCVID - ALTER SYSTEM INCVID - DEBUG
USERNAME
WHAT_GRANTED
SYSTEM INCVID - DELETE SYSTEM INCVID - FLASHBACK SYSTEM INCVID - INDEX
USERNAME
WHAT_GRANTED
SYSTEM INCVID - INSERT SYSTEM INCVID - ON COMMIT REFRESH SYSTEM INCVID - QUERY REWRITE
USERNAME
WHAT_GRANTED
SYSTEM INCVID - REFERENCES SYSTEM INCVID - SELECT SYSTEM INCVID - UPDATE
USERNAME
WHAT_GRANTED
TSMSYS RESOURCE XDB CREATE INDEXTYPE XDB CREATE LIBRARY
USERNAME
WHAT_GRANTED
XDB CREATE OPERATOR XDB CREATE PUBLIC SYNONYM XDB CTXAPP
USERNAME
WHAT_GRANTED
XDB DROP PUBLIC SYNONYM XDB QUERY REWRITE XDB RESOURCE
USERNAME
WHAT_GRANTED
XDB UNLIMITED TABLESPACE XDB USER$ - SELECT
143 rows selected. SQL> SQL></source>
