Oracle之數據字典

數據字典

數據字典是oracle數據庫中最重要的組成部分，它提供了數據庫的一些系統信息

數據字典記錄了數據庫的系統信息，它是只讀表和視圖的集合，數據字典的所有者是sys用戶

用戶只能在數據字典上執行查詢操作，而其維護和修改是由系統自動完成的

數據字典包括數據字典基表和數據字典視圖，其中基表存儲數據庫的基本信息，普通用戶不能直接訪問數據字典的基表，數據字典視圖是基于數據字典基表所建立的視圖，普通用戶可以通過查詢數據字典視圖取得系統信息

數據字典視圖主要包括：user_xxx，all_xxx，dba_xxx三種類型

動態性能視圖擠在了例程啟動后的相關信息（經常變化的信息）

數據字典由數據基表和動態視圖組成

數據字典基表存放的是靜態數據

數據字典動態視圖存放的是動態信息

數據字典記錄有oracle數據庫的所有系統信息，通過查詢數據字典可以取得

一下系統信息，比如：

1、對象定義情況

2、對象占用空間大小

3、列信息

4、約束信息

動態性能視圖

  動態性能視圖用于記錄當前例程的活動信息，當啟動oracle server時，系統

會建立動態性能視圖；當停止oracle server時，系統會刪除動態性能視圖。oracle

的所有動態性能視圖都是以v_$開始的，并且oracle為每個動態性能視圖都提供了相應的同義詞

并且同義詞是以V$開始的，例如v_$datafile的同義詞為v$datafile；

動態性能視圖的所有者為sys，一般情況下，由dba或是特權用戶來查詢動態性能視圖

user_tables：用于顯示當前用戶所擁有的所有表，它只返回用戶所對應方案的所有表

SQL> desc user_tables;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 TABLESPACE_NAME                                    VARCHAR2(30)

 CLUSTER_NAME                                       VARCHAR2(30)

 IOT_NAME                                           VARCHAR2(30)

 STATUS                                             VARCHAR2(8)

用于顯示當前方案的所有表信息

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

DEPT

EMP

BONUS

SALGRADE

STUDENT

all_tables:用于顯示當前用戶可以訪問的所有表，它不僅會返回當前用戶方案的所有表

還會返回當前用戶可以訪問的其他方案的表

例：某個用戶授權某張表給指定用戶可以查詢等操作

SQL> select table_name from all_all_tables;

dba_tables

它會顯示所有方案擁有的數據庫表，但是查詢這種數據庫字典視圖，要求用戶必須是dba角色

或是有select any table系統權限

例如：當用system用戶查詢數據庫字典視圖dba_tables時，會返回system、sys，scott。。。

方案所對應的數據庫表

使用system用戶

select table_name from dba_tables;

MYEMP

TEST

WRI$_ADV_OBJSPACE_TREND_DATA

MYTABLE

2729 rows selected

用戶名，權限，角色

  在建立用戶時，oracle會把用戶的信息存放到數據字典中，當給用戶授予權限或是角色時

oracle會將權限和角色的信息存放到數據字典中

通過查詢dba_users可以顯示所有數據庫用戶的詳細信息   --使用的system查詢

SQL> desc dba_users; 

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 USERNAME                                  NOT NULL VARCHAR2(30)

 USER_ID                                   NOT NULL NUMBER

 PASSWORD                                           VARCHAR2(30)

 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)

 LOCK_DATE                                          DATE

 EXPIRY_DATE                                        DATE

 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)

 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)

 CREATED                                   NOT NULL DATE

 PROFILE                                   NOT NULL VARCHAR2(30)

 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)

 EXTERNAL_NAME                                      VARCHAR2(4000)

 PASSWORD_VERSIONS                                  VARCHAR2(8)

 EDITIONS_ENABLED                                   VARCHAR2(1)

 AUTHENTICATION_TYPE                                VARCHAR2(8)

有字段是username，即數據庫用戶名，于是可以查出數據庫有多少用戶

SQL> select username from dba_users;

USERNAME

------------------------------

SYS

SYSTEM

XIAOBAI

XIAOMING

SCOTT

OUTLN

MGMT_VIEW

通過查詢數據字典視圖dba_sys_privs，可以顯示用戶所具有的系統權限   

SQL> desc dba_sys_privs;    系統權限表

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 GRANTEE                                   NOT NULL VARCHAR2(30)

 PRIVILEGE                                 NOT NULL VARCHAR2(40)

 ADMIN_OPTION                                       VARCHAR2(3)

SQL> select * from dba_sys_privs where grantee='SCOTT';

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

SCOTT                          UNLIMITED TABLESPACE                     NO

可以查詢到每個用戶所擁有的系統權限，以及他是否配置了with admin option

SQL> select * from dba_sys_privs where grantee='SYSTEM';

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

SYSTEM                         GLOBAL QUERY REWRITE                     NO

SYSTEM                         CREATE MATERIALIZED VIEW                 NO

SYSTEM                         CREATE TABLE                             NO

SYSTEM                         UNLIMITED TABLESPACE                     YES

SYSTEM                         SELECT ANY TABLE                         NO

通過查詢數據字典視圖dba_tab_privs，可以顯示用戶具有的對象權限

SQL> desc dba_tab_privs;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 GRANTEE                                   NOT NULL VARCHAR2(30)

 OWNER                                     NOT NULL VARCHAR2(30)

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 GRANTOR                                   NOT NULL VARCHAR2(30)

 PRIVILEGE                                 NOT NULL VARCHAR2(40)

 GRANTABLE                                          VARCHAR2(3)

 HIERARCHY                                          VARCHAR2(3)

通過查詢數據字典dba_col_privs，可以顯示用戶具有的列權限

SQL> desc dba_col_privs;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 GRANTEE                                   NOT NULL VARCHAR2(30)

 OWNER                                     NOT NULL VARCHAR2(30)

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 COLUMN_NAME                               NOT NULL VARCHAR2(30)

 GRANTOR                                   NOT NULL VARCHAR2(30)

 PRIVILEGE                                 NOT NULL VARCHAR2(40)

 GRANTABLE                                          VARCHAR2(3)

通過查詢數據庫字典視圖dba_role_privs，可以顯示用戶所具有的角色        ------》如何查看某個用戶，具有什么樣的角色？

SQL> desc  dba_role_privs;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 GRANTEE                                            VARCHAR2(30)

 GRANTED_ROLE                              NOT NULL VARCHAR2(30)

 ADMIN_OPTION                                       VARCHAR2(3)

 DEFAULT_ROLE                                       VARCHAR2(3)

查詢Scott用戶具有的角色？

SQL> select granted_role from dba_role_privs where grantee='SCOTT';

GRANTED_ROLE

------------------------------

RESOURCE

CONNECT      唯獨的兩個角色

用戶system具有的角色

SQL> select granted_role from dba_role_privs where grantee='SYSTEM';

GRANTED_ROLE

------------------------------

AQ_ADMINISTRATOR_ROLE

MGMT_USER

DBA

1、如何查詢一個角色所包含的權限？

一個角色包含的系統權限   系統權限表為dba_sys_privs

SQL> desc dba_sys_privs;

Name         Type         Nullable Default Comments                                       

------------ ------------ -------- ------- ---------------------------------------------- 

GRANTEE      VARCHAR2(30)                  Grantee Name, User or Role receiving the grant 

grantee：值為user或者role

查詢角色connect的系統權限？

SQL> select * from dba_sys_privs where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

CONNECT                        CREATE SESSION                           NO

于是系統權限create session就是能夠登陸數據庫的權限，也就是connect角色

還有一張表：role_sys_privs角色系統權限表

SQL> desc role_sys_privs;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ROLE                                      NOT NULL VARCHAR2(30)

 PRIVILEGE                                 NOT NULL VARCHAR2(40)

 ADMIN_OPTION                                       VARCHAR2(3)

一個角色包含的對象權限  對象權限表為：dba_tab_privs

SQL> desc dba_tab_privs;     系統對象權限表

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 GRANTEE                                   NOT NULL VARCHAR2(30)

 OWNER                                     NOT NULL VARCHAR2(30)

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 GRANTOR                                   NOT NULL VARCHAR2(30)

 PRIVILEGE                                 NOT NULL VARCHAR2(40)

 GRANTABLE                                          VARCHAR2(3)

 HIERARCHY                                          VARCHAR2(3)

SQL> select privilege from dba_tab_privs where grantee='CONNECT';

no rows selected

一個角色包含的權限來自于系統權限和對象權限

2、oracle究竟有多少種角色？   查詢表dba_roles

SQL> desc dba_roles;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ROLE                                      NOT NULL VARCHAR2(30)

 PASSWORD_REQUIRED                                  VARCHAR2(8)

 AUTHENTICATION_TYPE                                VARCHAR2(11)

SQL> select count(*) from dba_roles;

  COUNT(*)

----------

        55

SQL> select role from dba_roles;

ROLE

------------------------------

CONNECT

RESOURCE

DBA

SELECT_CATALOG_ROLE

EXECUTE_CATALOG_ROLE

DELETE_CATALOG_ROLE

EXP_FULL_DATABASE

IMP_FULL_DATABASE

LOGSTDBY_ADMINISTRATOR

DBFS_ROLE

AQ_ADMINISTRATOR_ROLE

一共有55種角色

3、查詢oracle中所有的系統權限？

select * from system_privilege_map order by name;

SQL> desc system_privilege_map;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PRIVILEGE                                 NOT NULL NUMBER

 NAME                                      NOT NULL VARCHAR2(40)

 PROPERTY                                  NOT NULL NUMBER

SQL> select count(*) from system_privilege_map;

  COUNT(*)

----------

       208

4、查詢oracle中所有對象權限？

select distinct privilege from dba_tab_privs;

5、查詢數據庫的表空間？  查詢表系統表空間dba_tablespaces

SQL> desc dba_tablespaces;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)

 BLOCK_SIZE                                NOT NULL NUMBER

 INITIAL_EXTENT                                     NUMBER

 NEXT_EXTENT                                        NUMBER

 MIN_EXTENTS                               NOT NULL NUMBER

 MAX_EXTENTS                                        NUMBER

 MAX_SIZE                                           NUMBER

 PCT_INCREASE                                       NUMBER

 MIN_EXTLEN                                         NUMBER

 STATUS                                             VARCHAR2(9)

 CONTENTS                                           VARCHAR2(9)

 LOGGING                                            VARCHAR2(9)

 FORCE_LOGGING                                      VARCHAR2(3)

 EXTENT_MANAGEMENT                                  VARCHAR2(10)

 ALLOCATION_TYPE                                    VARCHAR2(9)

 PLUGGED_IN                                         VARCHAR2(3)

 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)

 DEF_TAB_COMPRESSION                                VARCHAR2(8)

 RETENTION                                          VARCHAR2(11)

 BIGFILE                                            VARCHAR2(3)

 PREDICATE_EVALUATION                               VARCHAR2(7)

 ENCRYPTED                                          VARCHAR2(3)

 COMPRESS_FOR                                       VARCHAR2(12)

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

顯示當前數據庫的全稱

select * from global_name;

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

ORCL11G.US.ORACLE.COM

顯示當前用戶可以訪問的所有數據字典視圖

select * from dict where comments like '%grant%';

SQL> desc dict;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLE_NAME                                         VARCHAR2(30)

 COMMENTS                                           VARCHAR2(4000)

SQL> select * from dict where comments like '%grant%';

TABLE_NAME                     COMMENTS

------------------------------ --------------------------------------------------------------------------------

USER_AUDIT_STATEMENT           Audit trail records concerning  grant, revoke, audit, noaudit and alter system

USER_COL_PRIVS