Oracle PL/SQL/System Tables Views/dba users
Содержание
- 1 Check user name and password for users
- 2 Displaying the Account Status of All Users
- 3 Join dba_users, dba_sys_privs
- 4 Join dba_users, dba_tab_privs
- 5 Query dba_users table
- 6 Query dba_users table for default_tablespace and temporary_tablespace
- 7 Verifying Resource Consumer Group Membership of Users
Check user name and password for users
SQL> select
2 Username, /*Username*/
3 Password /*Encrypted password*/
4 from DBA_USERS
5 where Username in ("MCGREGOR","THUMPER","OPS$FARMER");
SQL>
SQL>
SQL>
Displaying the Account Status of All Users
SQL>
SQL> SELECT username, account_status
2 FROM dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
sqle OPEN
SYS OPEN
SYSTEM OPEN
ANONYMOUS OPEN
MDSYS EXPIRED & LOCKED
OUTLN EXPIRED & LOCKED
DIP EXPIRED & LOCKED
TSMSYS EXPIRED & LOCKED
FLOWS_FILES EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED
DBSNMP EXPIRED & LOCKED
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
FLOWS_020100 EXPIRED & LOCKED
XDB EXPIRED & LOCKED
HR EXPIRED & LOCKED
14 rows selected.
Join dba_users, dba_sys_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> TTITLE LEFT "User System Privileges" RIGHT "Page " FORMAT 999 SQL.PNO SKIP 2
SQL>
SQL> SELECT username, privilege
2 FROM dba_users, dba_sys_privs
3 WHERE username = grantee
4 ORDER BY username, privilege;
User System Privileges Page 1
User Privilege
------------ --------------------
ANONYMOUS CREATE SESSION
CTXSYS ALTER SESSION
CTXSYS CREATE PUBLIC SYNONY
M
CTXSYS CREATE SESSION
CTXSYS CREATE SYNONYM
CTXSYS CREATE VIEW
CTXSYS DROP PUBLIC SYNONYM
CTXSYS UNLIMITED TABLESPACE
DBSNMP CREATE PROCEDURE
DBSNMP CREATE TABLE
DBSNMP SELECT ANY DICTIONAR
Y
User System Privileges Page 2
User Privilege
------------ --------------------
DBSNMP UNLIMITED TABLESPACE
DEFINER UNLIMITED TABLESPACE
DIP CREATE SESSION
FLOWS_020100 ALTER DATABASE
FLOWS_020100 ALTER SESSION
FLOWS_020100 ALTER SYSTEM
FLOWS_020100 ALTER USER
FLOWS_020100 CREATE ANY CONTEXT
FLOWS_020100 CREATE ANY DIRECTORY
FLOWS_020100 CREATE CLUSTER
FLOWS_020100 CREATE DATABASE LINK
FLOWS_020100 CREATE DIMENSION
FLOWS_020100 CREATE INDEXTYPE
FLOWS_020100 CREATE JOB
FLOWS_020100 CREATE LIBRARY
User System Privileges Page 3
User Privilege
------------ --------------------
FLOWS_020100 CREATE MATERIALIZED
VIEW
FLOWS_020100 CREATE OPERATOR
FLOWS_020100 CREATE PROCEDURE
FLOWS_020100 CREATE PUBLIC SYNONY
M
FLOWS_020100 CREATE ROLE
FLOWS_020100 CREATE SEQUENCE
SQL> SET FEEDBACK ON
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>
Query dba_users table
SQL>
SQL> set define off
SQL> set echo off
SQL> set linesize 78
SQL>
SQL> prompt User Information
User Information
SQL>
SQL> column username format a20 heading "Username"
SQL> column default_tablespace format a14 heading "Default Tblspc"
SQL> column temporary_tablespace format a10 heading "Temp Tblspc"
SQL> column locked format a1 heading "L"
SQL>
SQL> break on tblspc skip 1
SQL>
SQL> select username, default_tablespace, temporary_tablespace,
2 decode( account_status, "EXPIRED & LOCKED", "*",
3 "OPEN", "" ) locked
4 from dba_users
5 where rownum < 50
6 /
Username Default Tblspc Temp Tblsp L
-------------------- -------------- ---------- -
sqle SYSTEM TEMP
SYS SYSTEM TEMP
SYSTEM SYSTEM TEMP
ANONYMOUS SYSAUX TEMP
MDSYS SYSTEM TEMP *
OUTLN SYSTEM TEMP *
DIP SYSTEM TEMP *
TSMSYS SYSTEM TEMP *
FLOWS_FILES SYSAUX TEMP *
CTXSYS SYSAUX TEMP *
DBSNMP SYSAUX TEMP *
FLOWS_020100 SYSAUX TEMP *
XDB SYSAUX TEMP *
HR USERS TEMP *
14 rows selected.
SQL> set define on
SQL>
SQL>
SQL> --
Query dba_users table for default_tablespace and temporary_tablespace
SQL>
SQL> select default_tablespace, temporary_tablespace
2 from dba_users where username = "SCOTT";
no rows selected
SQL>
SQL>
SQL> drop table foo;
Table dropped.
SQL>
SQL>
Verifying Resource Consumer Group Membership of Users
SQL>
SQL> SELECT username, initial_rsrc_consumer_group
2 FROM dba_users;
USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
sqle DEFAULT_CONSUMER_GROUP
SYS SYS_GROUP
SYSTEM SYS_GROUP
ANONYMOUS DEFAULT_CONSUMER_GROUP
MDSYS DEFAULT_CONSUMER_GROUP
OUTLN DEFAULT_CONSUMER_GROUP
DIP DEFAULT_CONSUMER_GROUP
TSMSYS DEFAULT_CONSUMER_GROUP
FLOWS_FILES DEFAULT_CONSUMER_GROUP
CTXSYS DEFAULT_CONSUMER_GROUP
DBSNMP DEFAULT_CONSUMER_GROUP
USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
FLOWS_020100 DEFAULT_CONSUMER_GROUP
XDB DEFAULT_CONSUMER_GROUP
HR DEFAULT_CONSUMER_GROUP
14 rows selected.
SQL>