--================================
--Oracle 角色、配置檔案
一、角色
1.角色
權限的集合，可以配置設定給一個使用者或其他角色，但角色不能授予自己，也不能循環授予
角色的優點
可以先建立角色，向該角色賦予一系列權限，然後再将該角色授予多個使用者或角色
增加或删除角色中的某一權限，被授予該角色的所有使用者或角色自動地獲得新增權限或删除舊的權限
可以為角色設定密碼
2.建立修改角色
CREATE ROLE role_name
[NOT IDENTIFIED(預設) | IDENTIFIED BY password | EXTERNALLY |GLOBALLY];
注：同一個資料庫中角色名稱必須唯一，且不能使用已存在的使用者名稱
不支援with grant option 為角色授予對象權限
支援with admin option 為角色授予系統權限或另一個角色
使用Enterprise Manager建立某個使用者時，該使用者被自動授予了CONNECT角色，
即同時具有了該角色的所有權限
IDENTIFIED BY EXTERNALLY
意味着了啟用一個角色，使用者必須是某個作業系統組的一個成員，該作業系統組的名稱應當與角色相對應。
當希望通過作業系統對角色進行身份認證，則需要設定OS_ROLE參數為TRUE
且當設定了使用IDENTIFIED BY EXTERNALLY身份驗證，必須在資料庫駐留的伺服器上按以下格式建立組
ora_<SID>_<ROLE>[_[d][a]]
d:訓示<ROLE>部分指定的角色為用于使用者的預設角色
a:訓示可以使用with admin option為使用者授予<ROLE>部分所指定的角色
常用的角色
角色 被授予的權限
DBA 幾乎所有系統權限
SELECT_CATALOG_ROLE 資料字典上的對象權限，未被授予任何系統權限
EXECUTE_CATALOG_ROLE 資料字典上的程式包、過程、函數的對象權限
DELETE_CATALOG_ROLE DELETE ON SYS.AUD$
DELETE ON SYS.FGA_LOG$
EXP_FULL_DATABASE 從資料庫中導出資料時查詢任何表或序列、執行任何過程或類型以及修改
資料字典對象的權限
IMP_FULL_DATABASE 執行導入時，在資料庫内除了sys模式之外的任何模式中建立對象的權限
CONNECT ALTER SESSION
CREATE CLUSTER
CREATE DATABASE LINK
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE VIEW
RESOURCE CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE (when granted)
AQ_ADMINISTRATOR_ROLE Advanced Queuing 對象上的對象權限
CREATE EVALUATION CONTEXT
CREATE RULE
CREATE RULE SET
DEQUEUE ANY QUEUE
ENQUEUE ANY QUEUE
MANAGE ANY QUEUE
AQ_USER_ROLE EXECUTE ON SYS.DBMS_AQ
EXECUTE ON SYS.DBMS_AQIN
EXECUTE ON SYS.DBMS_AQJMS_INTERNAL
EXECUTE ON SYS.DBMS_TRANSFORM
SCHEDULER_ADMIN CREATE ANY JOB
CREATE JOB
EXECUTE ANY CLASS
EXECUTE ANY PROGRAM
MANAGE SCHEDULE
(使用WITH ADMIN OPTION授予上述所有權限)
PUBLIC 不具有特殊的權限，不過為public角色授予權限時，所有使用者都會繼承該權限
--建立不要密碼的角色clerk
SQL> CREATE ROLE clerk;
--建立要密碼的角色sales
SQL> CREATE ROLE sales IDENTIFIED BY money;
--建立一個需要使用外部辨別（如作業系統）的角色manager
SQL> CREATE ROLE manager IDENTIFIED EXTERNALLY;
--建立後檢視角色：
SQL> SELECT role,password_required FROM dba_roles;
ROLE PASSWORD
------------------------------ --------
CLERK NO
SALES YES
MANAGER EXTERNAL
角色修改：
ALTER ROLE rolename
[NOT IDENTIFIED | IDENTIFIED
BY password | EXTERNALLY | GLOBALLY];
一個角色在建立後可以修改，但隻能修改它的驗證方法。
但隻有角色是使用帶有with ADMIN option 選項的GRANT 語句授予的或者具
有ALTER ANY ROLE 系統權限的使用者時，才可以修改這個角色
--将角色clerk 的驗證方法改為使用外部（如作業系統）辨別
SQL> ALTER ROLE clerk IDENTIFIED EXTERNALLY;
--将角色sales 的驗證方法改為不使用任何辨別方法
SQL> ALTER ROLE sales NOT IDENTIFIED;
--将角色manager 的驗證方法改為使用密碼辨別，密碼為vampire
SQL> ALTER ROLE manager IDENTIFIED BY vampires;
--再查詢後即可看到變化
SELECT role,password_required FROM dba_roles
3.為角色授予和取消權限
a.角色授權
為角色授予系統權限文法
GRANT system_priv [, system_priv, ...]
TO role | PUBLIC [, role | PUBLIC, ...]
[WITH ADMIN OPTION];
為角色授予對象權限文法
GRANT ALL [PRIVILEGES] | object_priv [(column, column, ...)]
[, object_priv [(column, column, ...(] , ...]
ON [schema_name.]object_name
TO role | PUBLIC [, role | PUBLIC, ...];
--為角色賦予權限（GRANT）：
SQL> show user;
USER is "SYSTEM"
SQL> CREATE ROLE manager;
Role created.
--賦予系統權限
SQL> GRANT CREATE TABLE,CREATE VIEW,CREATE SESSION TO manager WITH ADMIN OPTION;
Grant succeeded.
--賦予對象權限
SQL> GRANT SELECT ,INSERT ,UPDATE ON scott.emp TO manager;
--檢視角色的系統權限（role_sys_privs）
SQL> SELECT * FROM role_sys_privs WHERE role = 'MANAGER';
ROLE PRIVILEGE ADM
------------------------------ -------------------- ---
MANAGER CREATE SESSION YES
MANAGER CREATE TABLE YES
MANAGER CREATE VIEW YES
--檢視角色的對象權限(role_tab_privs)
SQL> SELECT * FROM role_tab_privs WHERE role = 'MANAGER';
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA
------------------------- -------------------- -------------------- ----------- -------------------- ---
MANAGER SCOTT EMP UPDATE NO
MANAGER SCOTT EMP INSERT NO
MANAGER SCOTT EMP
b.取消角色所擁有的權限
取消角色擁有的系統權限文法
REVOKE system_priv | role_name [, system_priv | role_name, ...]
FROM role | PUBLIC [,role | PUBLIC, ...];
取消角色使用者的對象權限文法
REVOKE ALL [PRIVILEGES] | object_priv [, object_priv, ...]
FROM role | PUBLIC [,role | PUBLIC, ...]
[CASCADE CONSTRAINTS]
--取消角色的系統權限
SQL> REVOKE CREATE VIEW FROM manager;
Revoked succeeded.
--取消角色的對象權限
SQL> REVOKE INSERT ,UPDATE ON scott.emp FROM manager;
--檢視被取消權限後所剩餘的權限的集合
SQL> SELECT role,'System_privs' owner,privilege
2 FROM role_sys_privs
3 WHERE role = 'MANAGER'
4 UNION
5 SELECT role,owner,privilege
6 FROM role_tab_privs
7 WHERE role = 'MANAGER';
ROLE OWNER PRIVILEGE
------------------------------ -------------------- --------------------
MANAGER SCOTT SELECT
MANAGER System_privs CREATE SESSION
MANAGER System_privs CREATE TABLE
4.角色賦予與角色取消
a.将角色賦予使用者（grant）：
文法：
GRANT role_name [, role_name, ...]
TO user_name | role | PUBLIC [, user_name | role | PUBLIC, ...]
--将角色賦予robinson且使用了WITH ADMIN OPTION
SQL> GRANT manager TO robinson WITH ADMIN OPTION;
--robinson有權将角色授予john，如下
SQL> CONN robinson/lion;
Connected.
SQL> GRANT manager TO john;
--檢視角色授予了哪些使用者(dba_role_privs)
SQL> SELECT * FROM dba_role_privs WHERE granted_role = 'MANAGER';
GRANTEE GRANTED_ROLE ADM DEF
-------------------- ------------------------------ --- ---
SYSTEM MANAGER YES YES
JOHN MANAGER NO YES
ROBINSON MANAGER YES YES
--檢視使用者擁有哪些角色
SQL> CONN scott/tiger;
SQL> SELECT * FROM user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT CONNECT NO YES NO
SCOTT RESOURCE NO YES NO
--檢視使用者擁有哪些角色(使用session_roles)
SQL> SELECT * FROM session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
b.取消使用者擁有的角色
REVOKE role_name [, role_name, ...]
FROM user_name | role | PUBLIC [, user_name | role | PUBLIC, ...];
--取消使用者角色
SQL> REVOKE resource FROM scott;
Revoke succeeded.
--檢視resource 角色已被取消
SQL> CONN scott/tiger;
--對于使用WITH ADMIN OPTION參數，收回robinson角色，并不影響john的級聯角色，如下：
SQL> select * from dba_role_privs where grantee='JOHN';
GRANTEE GRANTED_ROLE ADM DEF
JOHN MANAGER NO YES
SQL> revoke manager from robinson;
SQL> select * from dba_role_privs where grantee='ROBINSON';
no rows selected
5.設定預設角色
可以将多個角色授予一個使用者。預設角色是這些角色的一個子集，預設角色在使用者登入系統時
自動激活（開啟）。在預設情況下，所有賦予使用者的角色在使用者登入時不需要密碼就被激活。
可以使用ALTER USER語句來限制使用者有的預設角色。
文法：
ALTER USER username DEFAULT ROLE
role [, role, ...] | ALL [EXCEPT role [, role, ...]] | NONE;
ALTER USER語句中的DEFAULT ROLE子句隻适用于那些使用GRANT語句直接授予使用者的角色。
DEFAULT ROLE子句在下列情況下是不能使用的：
通過其它角色授予的角色
沒有直接授予該使用者的角色
通過外部服務（如作業系統）管理的角色
--建立使用者martin并授予resource,connect角色
SQL> CREATE USER martin IDENTIFIED BY abc;
User created.
SQL> GRANT RESOURCE,CONNECT TO martin;
Grant succeeded.
SQL> CONN martin/abc;
Connected.
SQL> CONN system/redhat
--設定預設的角色為NONE後，無法登陸
SQL> ALTER USER martin DEFAULT ROLE NONE;
User altered.
ERROR:
ORA-01045: user MARTIN lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> CONN system/redhat;
--重置角色後可以正常登陸
SQL> ALTER USER martin DEFAULT ROLE ALL;
--預設角色為除resource之外的所有角色
SQL> ALTER USER martin DEFAULT ROLE ALL EXCEPT RESOURCE;
6.激活和禁止角色（SET ROLE）
SET ROLE ALL [EXCEPT role_name [,role_name]] | NONE |
role_name [IDENTIFIED BY password] [, role_name [IDENTIFIED BY password, ...];
SQL> CONN robinson/lion
Connected.
--檢視使用者擁有的所有角色
SQL> SELECT * FROM user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ROBINSON MANAGER NO YES NO
ROBINSON RESOURCE NO YES NO
--檢視使用者擁有的所有權限
SQL> SELECT * FROM session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
9 rows selected.
--禁用所有的角色後，使用者的權限無顯示記錄
SQL> SET ROLE NONE;
Role set.
no rows selected
--使用者重新啟用所有角色
SQL> SET ROLE ALL
--啟用一個manager角色
SQL> SET ROLE manager;
SQL> SELECT * FROM session_roles;
ROLE
------------------------------
MANAGER
在這裡不象一般的ALTER 指令，沒有用到ENABLE 和DISABLE 之類的選項。
7.角色删除：
DROP ROLE role_name
--角色的删除需要适當的權限，如下提示沒有權限删除角色
SQL> SHOW USER;
USER is "ROBINSON"
SQL> DROP ROLE manager;
DROP ROLE manager
*
ERROR at line 1:
ORA-01031: insufficient privileges
--使用system帳戶删除角色
--帳戶robisnon唯一的角色被删除後，再次使用該帳戶登陸已提示沒有權限
--即角色的删除，原來所關聯的使用者會自動分離該角色及所有相關權限
SQL> CONN robinson/lion;
ORA-01045: user ROBINSON lacks CREATE SESSION privilege; logon denied
Role dropped.
關于connect和resource預定義角色：
connect, resoure 是role,包含很多的權限的
其中resource具有建立表，索引，視圖和其他的Oracle對象的能力，
同時預設帶有unlimited tablespace權限
一般将connect授予所有的普通使用者
connect和resource授予開發人員
oracle聲稱connect和resource角色是為了與它早期的版相容而保留的，
勸告使用者盡可能不要使用這兩個角色，以避免産生安全漏洞。
二、使用者配置檔案
配置檔案實作中幾類安全控制措施：
帳戶上鎖
資源限制
直接權限
角色權限
1.資源限制：
利用配置檔案來實作，可用以下兩種方法之一來開啟資源限制：
a.在初始化參數檔案中将RESOURCE_LIMIT 設為TRUE
b.使用ALTER SYSTEM 指令将RESOURCE_LIMIT 設為TRUE
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
配置檔案的資源限制既可加在會話一級，也可加在調用一級，會話級設定的資源限制是強加在每一個
連接配接上的。當超過了會話級的資源限制時，ORACLE系統将傳回出錯資訊。伺服器與使用者的連接配接斷開
在會話級可以設定的資源限制如下：
SESSION_PER_USER: 每個使用者所允許的并行會話數
CPU_PER_SESSION: 總共的CPU時間，其機關是%s
IDLE_TIME: 沒有活動的時間，機關是分鐘
CONNECT_TIME: 連接配接時間
LOGICAL_READS_PER_SESSION: 實體和邏輯讀的資料塊數。
建立資源限制檔案：
CREATE PROFILE profile_name LIMIT
[SESSION_PER_USER max_value]
[CPU_PER_SESSION max_value]
[CPU_PER_CALL max_value]
[CONNECT_TIME max_value]
[IDLE_TIME max_value]
[LOGICAL_READS_PER_SESSION max_value]
[LOGICAL_READS_PER_CALL max_value]
--示範建立資源限制配置檔案
SQL> CREATE PROFILE luckly_prof LIMIT
2 SESSIONS_PER_USER 8 --同一使用者可打開個會話（連接配接）
3 CPU_PER_SESSION 168000 --每個會話最多可以使用的CPU時間為個%s
4 LOGICAL_READS_PER_SESSION 21888 --每個會話最多可以讀個資料塊
5 CONNECT_TIME 180 --每個會話的連接配接時間最多為分鐘
6 IDLE_TIME 10 ; --每個會話的沒有活動時間不能超分鐘
Profile created.
--檢視剛剛建立的配置檔案
SQL> SELECT * FROM dba_profiles WHERE profile = 'LUCKLY_PROF';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- -----------------------
LUCKLY_PROF COMPOSITE_LIMIT KERNEL DEFAULT
LUCKLY_PROF SESSIONS_PER_USER KERNEL 8
LUCKLY_PROF CPU_PER_SESSION KERNEL 168000
LUCKLY_PROF CPU_PER_CALL KERNEL DEFAULT
LUCKLY_PROF LOGICAL_READS_PER_SESSION KERNEL 21888
LUCKLY_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT
LUCKLY_PROF IDLE_TIME KERNEL 10
LUCKLY_PROF CONNECT_TIME KERNEL 180
LUCKLY_PROF PRIVATE_SGA KERNEL DEFAULT
LUCKLY_PROF FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_LIFE_TIME PASSWORD DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ------------------------
LUCKLY_PROF PASSWORD_REUSE_TIME PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_LOCK_TIME PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_GRACE_TIME PASSWORD DEFAULT
2.密碼限制
FAILED_LOGIN_ATTEMPTS 帳戶被鎖之前可以嘗試登入失敗的次數
PASSWORD_LOCK_TIME 嘗試指定次數失敗後，賬戶被鎖的天數
PASSWORD_LIFE_TIME 密碼的生命周期（可使用的天數）
PASSWORD_GRACE_TIME 密碼過期之後第一次成功地使用原密碼登入後要改變密碼的寬限天數
PASSWORD_REUSE_TIME 在一個密碼可以重用之前的天數
PASSWORD_REUSE_MAX 在一個密碼可以重用之前的最大變化數
PASSWORD_VERIFY_FUNCTION 在一個新的密碼賦予一個使用者之前，要驗證密碼的複雜性是否滿足
安全要求的一個PL/SQL函數（該函數要sys使用者運作utlpwdmg.sql
腳本來生成）
在執行utlpwdmg.sql腳本檔案期間，ORACLE伺服器将建立VERIFY_FUNCTION函數，并且使用如下
的ALTER PROFILE 指令來修改luckly_prof概要檔案：
--為luckly_prof配置檔案增加密碼限制
SQL> ALTER PROFILE luckly_prof LIMIT
2 PASSWORD_LIFE_TIME 60
3 PASSWORD_GRACE_TIME 10
4 PASSWORD_REUSE_TIME 1800
5 PASSWORD_REUSE_MAX UNLIMITED
6 FAILED_LOGIN_ATTEMPTS 3
7 PASSWORD_LOCK_TIME 1/1440;
Profile altered.
之後，ORACLE伺服器就要對所有使用者提供的密碼進行如下檢查：
密碼的最小長度為個字元
密碼不應該與使用者名相同
密碼應該包含至少一個字元、一個數字和一個特殊字元
密碼應該至少有個字母與以前的密碼不同。除了ORACLE提供的預設複雜性檢驗函數外，
資料庫管理者也可以自己寫一個PL/SQL函數進行密碼的複雜性檢驗。
使用者提供的密碼函數一定要在SYS模式下建立并且必須使用以下函數說明（即函數接口）
function_name (userid_parameter IN VARCHAR2(30),
password_parmeter IN VARCHAR2(30),
old_password_parmeter IN VARCHAR2(30)
RETURN BOOLEAN
建立密碼限制的概要檔案：
同建立資源限制檔案一樣，隻要把密碼限制方面的内容加到profile_name中即可。
如：
CREATE PROFILE unluck_prof LIMIT
FAILED_LOGIN_ATTEMPTS 7
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 44
PASSWORD_REUSE_TIME 24
PASSWORD_GRACE_TIME 4;
--檢視資源限制(dba_profiles)：
------------------------------ -------------------------------- -------- ----------------------
LUCKLY_PROF LOGICAL_READS_PER_SESSION KERNEL 21888
LUCKLY_PROF IDLE_TIME KERNEL 10
LUCKLY_PROF FAILED_LOGIN_ATTEMPTS PASSWORD 3
LUCKLY_PROF PASSWORD_LIFE_TIME PASSWORD 60
LUCKLY_PROF PASSWORD_REUSE_TIME PASSWORD 1800
LUCKLY_PROF PASSWORD_REUSE_MAX PASSWORD UNLIMITED
LUCKLY_PROF PASSWORD_LOCK_TIME PASSWORD .0006
LUCKLY_PROF PASSWORD_GRACE_TIME PASSWORD 10
16 rows selected.
RESOURCE_TYPE為KERNEL表示這是一個資源限制，為PASSWORD表示為密碼限制。
3.修改配置檔案：
ALTER PROFILE profile_name LIMIT
......
例：ALTER PROFILE luck_prof LIMIT
IDLE_TIME 40
4.将PROFILE賦予使用者：
CREATE USER user_name [PROFILE { profile_name | DEFAULT }]
ALTER USER user_name [PROFILE { profile_name | DEFAULT }]
SQL> CREATE USER andy IDENTIFIED BY oracle PROFILE luckly_prof;
User created.
SQL> ALTER USER robinson PROFILE luckly_prof;
User altered.
5.删除配置檔案：
DROP PROFILE profile_name [CASCADE]
如果一個概要檔案已經賦予了使用者，那麼在DROP PROFILE時要用CASCADE
無法删除DEFAULT配置檔案，也無法删除MONITORING_PROFILE配置檔案
--已被賦予給用的profile删除時需要使用cascade，如下：
SQL> DROP PROFILE luckly_prof;
DROP PROFILE luckly_prof
ORA-02382: profile LUCKLY_PROF has users assigned, cannot drop without CASCADE
SQL> DROP PROFILE luckly_prof CASCADE;
Profile dropped.
三、更多
<a href="http://blog.csdn.net/robinson_0612/archive/2010/06/23/5688875.aspx">Oracle 使用者、對象權限、系統權限</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx">SQL 基礎--> ROLLUP與CUBE運算符實作資料彙總</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx">SQL基礎-->階層化查詢(START BY ... CONNECT BY PRIOR)</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/05/28/5630426.aspx">SQL 基礎--> 視圖(CREATE VIEW)</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/05/29/5633093.aspx">Oracle 常用目錄結構(10g)</a>
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)