Oracle PL\SQL操作（六）使用者和角色

1.使用者管理

（1）建立使用者（資料庫驗證）          CREATE  USER   smith     IDENTIFIED  BY   smith_pwd     DEFAULT TABLESPACE  users     TEMPORARY   TABLESPACE  temp     QUOTA  5m  ON  users;

（2）修改使用者

ALTER USER smith QUOTA 0 ON SYSTEM;

（3）刪除使用者

DROP USER smith; DROP USER smith CASCADE; 

（4）顯示使用者資訊

DBA_USERS

DBA_TS_QUOTAS

 

2.系統許可權

系統許可權	作用
CREATE SESSION	串連到資料庫
CREATE TABLE	建表
CREATE TABLESPACE	建立資料表空間
CREATE VIEW	建立視圖
CREATE SEQUENCE	建立序列
CREATE USER	建立使用者

系統許可權是指執行特定類型SQL命令的權利，用於控制使用者可以執行的一個或一類資料庫操作。（建立使用者沒有任何許可權）

（1）授予系統許可權

GRANT CREATE SESSION，CREATE TABLE TO smith; GRANT CREATE SESSION TO smith WITH ADMIN OPTION; 

選項：ADMIN OPTION 使該使用者具有轉授系統許可權的許可權。

（2）顯示系統許可權

查看所有系統許可權：

system_privilege_map

顯示使用者所具有的系統許可權：

dba_sys_privis

顯示目前使用者所具有的系統許可權：

user_sys_privis

顯示當前會話所具有的系統許可權：

session_privis

（3）收回系統許可權

REVOKE CREATE TABLE FROM smith;REVOKE CREATE SESSION FROM smith;

 

3.角色：是一組相關許可權的命名集合，使用角色最主要的目的是簡化許可權管理。

•預定義角色。

ØCONNECT 自動建立，包含以下許可權：ALTER SESSION、CREATE CLUSTER、CREATE DATABASE LINK、CREATE SEQUENCE、CREATE SESSION、CREATE SYNONYM、CREATE TABLE、CREATE VIEW 。

RESOURCE 自動建立，包含以下許可權：CREATE CLUSTER、CREATE PROCEDURE、CREATE SEQUENCE、CREATE TABLE、CREATE TRIGGR 。

Ø顯示角色資訊，

§ROLE_SYS_PRIVS

§ROLE_TAB_PRIVS

§ROLE_ROLE_PRIVS

§SESSION_ROLES

§USER_ROLE_PRIVS

§DBA_ROLES

 

4.Oracle使用者角色

每個Oracle使用者都有一個名字和口令,並擁有一些由其建立的表、視圖和其他資源。Oracle角色（role）就是一組許可權（privilege）(或者是每個使用者根據其狀態和條件所需的訪問類型)。使用者可以給角色授予或賦予指定的許可權，然後將角色賦給相應的使用者。一個使用者也可以直接給其他使用者授權。

資料庫系統許可權（Database System Privilege）允許使用者執行特定的命令集。例如，CREATE TABLE許可權允許使用者建立表，GRANTANY PRIVILEGE許可權允許使用者授予任何系統許可權。

資料庫物件許可權（Database Object Privilege）使得使用者能夠對各個對象進行某些操作。例如DELETE許可權允許使用者刪除表或視圖的行，SELECT許可權允許使用者通過select從表、視圖、序列（sequences）或快照 （snapshots）中查詢資訊。

一、建立使用者

Oracle內部有兩個建好的使用者：SYSTEM和SYS。使用者可直接登入到SYSTEM使用者以建立其他使用者，因為SYSTEM具有建立別的使用者的許可權。在安裝Oracle時，使用者或系統管理員首先可以為自己建立一個使用者。例如：

create user user01 identified by u01;

    該命令還可以用來設定其他許可權，詳細情況參見自學資料。要改變一個口令，可以使用alter user命令：

alter user user01 identified by usr01;

    現在user01的口令已由“u01”改為“usr01”。

    除了alter user命令以外，使用者還可以使用password命令。如果使用password命令，使用者輸入的新口令將不在螢幕上顯示。有dba特權的使用者可以通過password命令改變任何其他使用者的口令；其他使用者只能改變自己的口令。

    當使用者輸入password命令時，系統將提示使用者輸入舊口令和新口令，如下所示：

password

    Changing password for user01

    Old password:

    New password:

    Retype new password:

    當成功地修改了口令時，使用者會得到如下的反饋：

    Password changed

二、刪除使用者

    刪除使用者，可以使用drop user命令，如下所示：

drop user user01;

如果使用者擁有對象，則不能直接刪除，否則將返回一個錯誤值。指定關鍵字CASCADE，可刪除使用者所有的對象，然後再刪除使用者。下面的例子用來刪除使用者與其對象：

drop user user01 CASCADE;

三、3種標準角色

    Qracle為了相容以前的版本，提供了三種標準的角色（role）：CONNECT、RESOURCE和DBA。

1. CONNECT Role(串連角色)

    臨時使用者，特別是那些不需要建表的使用者，通常只賦予他們CONNECTrole。CONNECT是使用Oracle的簡單許可權，這種許可權只有在對其他使用者的表有訪問權時，包括select、insert、update和delete等，才會變得有意義。擁有CONNECT role的使用者還能夠建立表、視圖、序列（sequence）、簇（cluster）、同義字（synonym ）、會話（session）和與其他資料庫的鏈（link）。

2. RESOURCE Role(資源角色)

    更可靠和正式的資料庫使用者可以授予RESOURCE role。RESOURCE提供給使用者另外的許可權以建立他們自己的表、序列、過程（procedure）、觸發器（trigger）、索引（index）和簇（cluster）。

3. DBA Role(資料庫管理員角色)

    DBA role擁有所有的系統許可權----包括無限制的空間限額和給其他使用者授予各種許可權的能力。SYSTEM由DBA使用者擁有。下面介紹一些DBA經常使用的典型許可權。

   （1）grant（授權）命令

    下面對剛才建立的使用者user01授權，命令如下：

grant connect, resource to user01;

   （2）revoke（撤消）許可權

    已授與權限可以撤消。例如撤消（1）中的授權，命令如下：

    revoke connect, resource from user01;

一個具有DBA角色的使用者可以撤消任何別的使用者甚至別的DBA的CONNECT、RESOURCE 和DBA的其他許可權。當然，這樣是很危險的，因此，除非真正需要，DBA許可權不應隨便授予那些不是很重要的一般使用者。

    撤消一個使用者的所有許可權，並不意味著從Oracle中刪除了這個使用者，也不會破壞使用者建立的任何錶；只是簡單禁止其對這些表的訪問。其他要訪問這些表的使用者可以象以前那樣地訪問這些表。

四、建立角色

   除了前面講到的三種系統角色----CONNECT、RESOURCE和DBA，使用者還可以在Oracle建立自己的role。使用者建立的role可以由表或系統許可權或兩者的組合構成。為了建立role，使用者必須具有CREATE ROLE系統許可權。下面給出一個create role命令的執行個體：

create role STUDENT;

    這條命令建立了一個名為STUDENT的role。

    一旦建立了一個role，使用者就可以給他授權。給role授權的grant命令的文法與對對使用者的文法相同。在給role授權時，在grant命令的to子句中要使用role的名稱，如下所示：

grant select on CLASS to STUDENT;

現在，擁有STUDENT角色的所有使用者都具有對CLASS表的select許可權。

五、刪除角色

   要刪除角色，可以使用drop role命令，如下所示：

drop role STUDENT;

指定的role連同與之相關的許可權將從資料庫中全部刪除。

 

5.oracle sys system 使用者的區別

      sys是Oracle資料庫中許可權最高的帳號，具有create database的許可權，
而system沒有這個許可權，sys的角色是sysdba，system的角色是sysoper。其餘就是他們兩個使用者共有的許可權了：
startup/shutdown/dba兩個使用者都是可以管理的。平時用system來管理資料庫就可以了。這個使用者的許可權對於普通的資料庫管理來說已經足夠許可權了。

 

6.查看許可權和角色

Oracle角色、許可權的一些常用視圖http://space.itpub.net/12778571/viewspace-504123

ORACLE中資料字典視圖分為3大類, 用首碼區別，分別為：USER，ALL 和 DBA，許多資料字典視圖包含相似的資訊。

USER_*:有關使用者所擁有的對象資訊，即使用者自己建立的對象資訊

ALL_*：有關使用者可以訪問的對象的資訊，即使用者自己建立的對象的資訊加上其他使用者建立的對象但該使用者有權訪問的資訊

DBA_*：有關整個資料庫中對象的資訊

（這裡的*可以為TABLES， INDEXES， OBJECTS， USERS等）。

比如：只知道scott使用者的密碼，需要查看一下scott的一些資訊
1、查scott使用者的建立時間、使用者狀態、使用的預設資料表空間、暫存資料表空間等資訊

SQL> conn scott/mzl

已串連。

SQL>select * from user_users;

另：select * from all_users;(scott使用者可以訪問其他資料庫使用者對資訊的使用者名稱)
另：select * from all_users;(所有資料庫的使用者資訊，各使用者的密碼、狀態、預設資料表空間、暫存資料表空間等)

2、查看scott使用者自己擁有什麼角色

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_

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

SCOTT       CONNECT      NO     YES      NO

SCOTT        RESOURCE   NO     YES     NO

註：“ADM”表示這個使用者是否可以把該具有的角色賦予給其他的使用者

另：沒有all_role_privs這個視圖
另：select * from dba_role_privs（所有資料庫使用者具有哪些角色，這個視圖只有dba角色的許可權才可以查詢）

3、查看scott使用者自己具有什麼的許可權

SQL> select * from session_privs;

4、查看scott使用者具有什麼的系統許可權呢

SQL>select * from user_sys_privs;

另：沒有all_sys_privs視圖
另：select * from dba_sys_privs;(所有資料庫使用者、角色所用於的系統許可權)

5、查看scott使用者中，都哪些使用者把對象授予給scott使用者呢（讀取其他使用者物件的許可權）

SQL>select * from user_tab_privs;

另：select * from all_tab_privs;   select * from dba_tab_privs;  有空再琢磨琢磨

6、查看scott使用者中擁有的resource角色都具有什麼許可權呢

SQL> select * from role_sys_privs where role='RESOURCE';

ROLE                               PRIVILEGE                                    ADM

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

RESOURCE CREATE SEQUENCE NO

RESOURCE CREATE TRIGGER NO

RESOURCE CREATE CLUSTER NO

RESOURCE CREATE PROCEDURE NO

RESOURCE CREATE TYPE NO

RESOURCE CREATE OPERATOR NO

RESOURCE CREATE TABLE NO

RESOURCE CREATE INDEXTYPE NO

已選擇8行。

7、scott使用者自己擁有多少的表

SQL>select * from user_tables;

另：select * from all_tables; 其他使用者所擁有的表
另：select * from dba_tables；資料庫中所有使用者的表

8、查看scott使用者已經使用多大的空間，允許使用的最大空間是多少

SQL> select tablespace_name,bytes,max_bytes from user_ts_quotas;

另：select * from dba_ts_quotas;(所有的資料庫使用者在每個資料表空間已使用的空間，最大空間)

9、把自己的表賦予給其他使用者

SQL>grant select on emp to mzl;

查看都把哪些表什麼許可權賦予了其他使用者

SQL>select * from user_tab_privs_made

10、把表的某一列操作許可權賦予給其他使用者

SQL>grant update(job) on emp to mzl;

註：查看資料庫中所有的角色select * form. dba_roles;

1、sys授予scott使用者dba角色

SQL> conn /as sysdba

已串連。

SQL> grant dba to scott;

授權成功。

另：如果這樣

SQL> grant dba to scott with admin option;

授權成功。

scott使用者就可以把dba的許可權授予給其他的使用者了。

2、sys回收scott使用者的dba角色

SQL> revoke dba from scott;

撤銷成功。