Oracle PL/SQL/System Tables Views/dba users

Материал из SQL эксперт
Перейти к: навигация, поиск

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>


Displaying the Account Status of All Users

   <source lang="sql">

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. 


 </source>


Join dba_users, dba_sys_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> 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> 


 </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>


Query dba_users table

   <source lang="sql">

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> -- 


 </source>


Query dba_users table for default_tablespace and temporary_tablespace

   <source lang="sql">

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> 


 </source>


Verifying Resource Consumer Group Membership of Users

   <source lang="sql">

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> 


 </source>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/System_Tables_Views/dba_users&oldid=2317»