Oracle之使用者、特權和角色

本文主要包括：

   · 介紹建立使用者

   · 瞭解如何使用特權來確保使用者能夠在資料庫中執行任務

   · 介紹兩種特權型別：系統特權和物件特權

   · 介紹系統特權怎樣允許執行操作，例如執行 DDL 語句

   · 介紹物件特權怎樣允許執行操作，例如執行 DML 語句

   · 介紹如何將特權組合在一起形成角色

一、 使用者

資料庫使用表空間來儲存物件，其中可以包含表、型別和 PL/SQL 程式碼等

表空間儲存在資料檔案 (datafile) 中 

1. 建立使用者

語法：

CREATE USER user_name IDENTIFIED BY password

[DEFAULT TABLESPACE default_tablespace]

[TEMPORARY TABLESPACE temporary_tablespace];

可以從 user_users 查詢預設表空間和預設臨時表空間

2. 修改使用者密碼

ALTER USER user_name IDENTIFIED BY password;

PASSWORD 可以用來修改當前登入使用者的密碼

3. 刪除使用者

DROP USER user_name;

注：如果要刪除的使用者模式中包含任何表或其他項，就必須在 DROP USER

語句中，在要刪除的使用者名稱後面加上關鍵字 CASCADE

二、 系統特權

系統特權（system privilege）允許使用者在資料庫中執行特定的操作，比如執行 DDL 語句

常用的系統特權如下表

特權組合在一起形成角色（role），授予使用者的兩種有用的角色是 CONNECT 和 RESOURCE

CONNECT 允許使用者連線到資料庫，RESOURCE 允許使用者建立各種資料庫物件，如表、序列等

1. 向使用者授予系統特權

GRANT CREATE SESSION,CREATE USER,CREATE TABLE TO user_name;

注：使用 WITH ADMIN OPTION 選項，這樣被授予的使用者就可以把這種特權再授予其它使用者

GRANT EXECUTE ANY PROCEDURE TO user_nameWITH ADMIN OPTION;

注：通過將一種特權授予 PUBLIC，就可以將該特權授予所有使用者

CONNECT / AS SYSDBA

GRANT EXECUTE ANY PROCEDURE TO PUBLIC;

2. 檢查授予使用者的系統特權

通過查詢 user_sys_privilege 可以檢查某個使用者具有哪些系統特權

3. 使用系統特權

使用者被授予系統特權之後，就可以使用這種特權來執行特定的任務，如建立使用者等

但執行沒有被授予的系統特權，操作會失敗

4. 撤銷使用者的系統特權

可以使用 REVOKE 語句撤銷某個使用者的系統特權

CONN / AS SYSDBA

REVOKE CREATE TABLE FROM user_name;

三、 物件特權

物件特權（object privilege）允許使用者對資料庫物件執行特定的操作，例如對錶執行 DML 語句

1. 向使用者授予物件特權

可以使用 GRANT 語句向使用者授予物件特權

範例：以 store 使用者的身份連線到資料庫，並向 steve 使用者授予以下特權：

對 products 表的 SELECT、INSERT、UPDATE物件特權以及對錶 employees 的 SELECT 特權

CONN store/store_password

GRANT SELECT,INSERT,UPDATE ON store_products TO steve;

GRANT SELECT ON store.employees TO steve;

注：可以使用 GRANT 選項，這樣被授予的使用者就可以把這種特權再授予其他使用者

GRANT SELECT ON store.customers TO steve WITH GRANT OPTION;

2. 檢查已授予的物件特權

通過查詢 user_tab_privs_made 可以檢查某個使用者對哪些表向其他使用者開放了哪些物件特權

範例：檢索 table_name 為 PRODUCTS 的行

SELECT grantee,table_name,grantor,privilege,grantable,hierarchy

FROM user_tab_privs_made

WHERE table_name='PRODUCTS';

通過查詢 user_col_privs_made 可以檢查某個使用者對哪些列物件開放了哪些特權

範例：對 user_col_privs_made 進行查詢

SELECT grantee,table_name,column_name,grantor,privilege,grantable

FROM user_col_privs_made

ORDER BY column_name;

注：

   · 系統特權的傳遞使用 WITH ADMIN OPTION

   · 物件特權的傳遞使用 WITH GRANT OPTION

3. 檢查已接受的物件特權

通過查詢 user_tab_privs_recd表可以檢查某個使用者被授予了哪些表上的哪些物件特權

範例：以 steve 使用者的身份連線到資料庫，並對 user_tab_privs_recd 進行查詢

SELECT owner,table_name,grantor,privilege,grantable,hierarchy

FROM user_tab_privs_recd

ORDER BY table_name,privilege;

通過查詢 user_col_privs_recd可以檢查某個使用者被授予了哪些列的物件特權

範例：對 user_col_privs_recd 進行查詢

SELECT owner,table_name,column_name,grantor,privilege,grantable

FROM user_col_privs_recd;

4. 使用物件特權

使用者被授予物件特權之後，就可以使用這種特權來執行特定的任務，

例如， steve 使用者對 store.customers 具有 SELECT 特權

CONNECT steve/button

SELECT * FROM store.customers;

注：如果 steve 試圖對 purchases 表 (steve 對該表沒有任何許可權) 進行檢索，資料庫就會報錯

SELECT * FROM store.purchases;

5. 建立同義詞

範例：以 system 使用者的身份連線到資料庫，並將 CREATE SYNONYM 系統特權授予 steve 使用者

CONN system/oracle

GRANT CREATE SYNONYM TO steve;

範例：以 steve 身份連線到資料庫，並執行一條 CREATE SYNONYM 語句，為 store.customers 表建立同義詞

CONN steve/button

CREATE SYNONYM customers FOR store.customers;

6. 建立公共同義詞

可以為表建立公共同義詞 (public synonym) ，在建立公共同義詞之後，所有的使用者都可以

看到這個同義詞，下面這4條語句執行以下任務：

   · 以 system 使用者的身份連線到資料庫

   · 將 CREATE PUBLIC SYNONYM 系統特權授予 store 使用者

   · 以 store 使用者的身份連線到資料庫

   · 為 store.products 建立公共同義詞

CONNECT system/oracle

GRANT CREATE PUBLIC SYNONYM TO store;

CONN store/store_password

CREATE PUBLIC SYNNONYM products FOR store.products;

7. 撤銷使用者的物件特權

可以使用 REVOKE 語句撤銷某個使用者的物件特權

範例：以 store 使用者的身份連線到資料庫，並撤銷 steve 使用者對 products 表的 INSERT 特權

CONN store/store_password

REVOKE INSER ON products FROM steve;

注：

   · 系統特權在撤銷時，該使用者授予其它使用者的特權不會消失   

   · 物件特權再撤銷時，該使用者授予其它使用者的特權也會消失     

四、 角色

角色就是一組特權，可以分配給使用者或其它角色，角色的優點可以總結為如下幾點：

   · 並不是一次一個地將特權直接授予使用者，而是先建立角色，向角色授予一些特權，

      然後再將角色授予多個使用者和角色

   · 在增加或刪除角色的某種特權時，被授予該角色的所有使用者和角色都會自動獲得

      新增加的特權或自動失去這種特權

   · 可以將多個角色授予使用者或角色

   · 可以為角色設定密碼

1. 建立角色

要建立角色，必須具有 CREATE ROLE 系統特權

2. 為角色授權 

3. 將角色授予使用者

4. 檢查授予使用者的角色

通過查詢 user_role_privs 可以檢查已經授予使用者哪些角色

注：

   · 密碼保護的角色是禁用的，必須輸入密碼才能使用該角色

   · 建立角色的那個使用者也會被授予該角色

5. 檢查授予角色的系統特權

通過查詢 role_sys_privs 可以檢查已經授予角色哪些系統特權

6. 檢查授予角色的物件特權

通過查詢 role_tab_privs 可以檢查已經授予角色哪些物件特權

7. 使用已授予角色的特權

對於無密碼保護的角色，通過角色將特權授予使用者之後，使用者連線到資料庫後就

可以立即使用這種特權了；

對於密碼保護的角色，使用者必須輸入角色密碼才能使用這個角色

SET ROLE role_name IDENTIFIED BY role_password;

8. 啟用和禁用角色

可以禁用角色，ALTER ROLE 語句可以用來對角色進行修改，使其變為非預設角色

範例：以 system 使用者身份連線到資料庫，並對 john 使用者進行修改，使 hr_manager 不再是預設角色

CONN system/oracle

ALTER USER john DEFAULT ROLE ALL EXCEPT hr_manager;

範例：使用 SET ROLE 命令啟用 hr_manager 角色

SET ROLE hr_manager;

範例：將 hr_manager 角色設定為預設角色，它在退出登入後仍然保留

CONN system/oracle

ALTER USER john DEFAULT ROLE hr_manager;

範例：可以將角色設定為 NONE ，這表示沒有角色

CONN john/brown

SET ROLE NONE;

範例：將角色設定為除 hr_manager 之外的任何角色

SET ROLE ALL EXCEPT hr_manager;

9. 撤銷角色

REVOKE 語句可以用來撤銷角色

REVOKE role_name FROM user_name;

10. 從角色中撤銷特權

REVOKE 語句可以用來從角色中撤銷某種特權

REVOKE ALL ON table_name FROM role_name;

11. 刪除角色

DROP ROLE 語句可以用來刪除角色

DROP ROLE role_name;