角色是一組相關權限的命名集合，使用角色最主要的目的是簡化權限管理
而一旦這個集合的權限超過了用戶的最低需求，就可能帶來數據庫的安全風險
角色口令測試
oracle 10g中，無論角色是否有口令，只要你將角色grant給某個用戶，那麼，默認的情況下，這些角色中的權限，用戶都擁有。
oracle 11g中，角色的口令略有修正，當某個角色是擁有口令的話，當你將帶有口令的角色 grant 給某個用戶的話，那麼默認的情況
下，這個帶口令的角色下的所有權限，用戶是無法擁有的，只有當 set 那個擁有口令的角色後，那麼 ，帶口令的
角色下的權限才在當前會話下才可以使用，不過，其他的角色都暫時失效，修改只在當前會話有效。
語法： ------只在當前會話有效
SET ROLE
{ role [ IDENTIFIED BY password ]
[, role [ IDENTIFIED BY password ] ]...
| ALL [ EXCEPT role [, role ]... ]
| NONE
} ;
oracle 10g 中測試帶口令的角色
SYS@ORCL>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
1. 創建兩個角色 role_01 沒有密碼 role_02 有密碼
SYS@ORCL>create role role_01;
Role created.
SYS@ORCL>create role role_02 identified by oracle;
Role created.
2. 賦予角色 role_01 連接、建表權限
SYS@ORCL>grant connect,create table to role_01;
Grant succeeded.
3. 賦予角色 role_02 連接、創建視圖權限
SYS@ORCL>grant connect,create view to role_02;
Grant succeeded.
4. 創建測試用戶 tyger
SYS@ORCL>create user tyger identified by tyger quota unlimited on users;
User created.
5. 將兩個角色賦予tyger
SYS@ORCL>grant role_01,role_02 to tyger;
Grant succeeded.
6. 連接到用戶測試
SYS@ORCL>conn tyger/tyger
Connected.
TYGER@ORCL>create table t(x int);
Table created.
TYGER@ORCL>insert into t values(1);
1 row created.
TYGER@ORCL>commit;
Commit complete.
TYGER@ORCL>select * from t;
X
----------
1
TYGER@ORCL>create view view_t as select * from t;
View created.
TYGER@ORCL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
VIEW_T VIEW
T TABLE
7. 查看當前用戶的角色，兩個角色的 DEFAULT_ROLE 都為 YES 說明，這兩個角色都生效
TYGER@ORCL>desc user_role_privs;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
GRANTED_ROLE VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
OS_GRANTED VARCHAR2(3)
TYGER@ORCL>col username for a10
TYGER@ORCL>col granted_role for a20
TYGER@ORCL>col default_role for a20
TYGER@ORCL>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- -------------------- --------------------
TYGER ROLE_01 YES
TYGER ROLE_02 YES
8. 查看當前會話具有的權限
TYGER@ORCL>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
9. set 的應用 ----set 在當前會話中設置角色狀態
TYGER@ORCL>set role ROLE_01;
Role set.
10. 查看當前用戶的角色，沒變化
TYGER@ORCL>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- -------------------- --------------------
TYGER ROLE_01 YES
TYGER ROLE_02 YES
11. 查看當前會話權限，已經沒有 create view 權限， 原因：set role role_01 只有role_01 在當前會話生效
TYGER@ORCL>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
TYGER@ORCL>create view view_2 as select * from t;
create view view_2 as select * from t
*
ERROR at line 1:
ORA-01031: insufficient privileges
12. 同樣使 role_02 生效，同時 role_01 失效，不過設置的時候需要我們提供密碼，因為我們創建角色時使用了密碼
TYGER@ORCL>set role role_02;
set role role_02
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'ROLE_02'
TYGER@ORCL>set role role_02 identified by oracle;
Role set.
13. 查看當前用戶所擁有的權限，還是沒變化
TYGER@ORCL>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- -------------------- --------------------
TYGER ROLE_01 YES
TYGER ROLE_02 YES
14. 查看當前會話的權限，已經沒有 create table 權限
TYGER@ORCL>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE VIEW
TYGER@ORCL>create table t1(x int);
create table t1(x int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
15. 重新登錄會話，連接用戶 所有權限都恢復原樣
TYGER@ORCL>conn tyger/tyger;
Connected.
TYGER@ORCL>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
總結：在oracle 10g 無論角色是否有口令，將角色賦予給用戶後，用戶具有角色的全部權限。
oracle 11g 中測試 擁有口令的角色
[oracle@ora11gr2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 19 15:28:13 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ORA11G>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
1. 創建角色 tyger_ro1 無密碼 tyger_ro2 有密碼
SYS@ORA11G>create role tyger_ro1;
Role created.
SYS@ORA11G>create role tyger_ro2 identified by oracle;
Role created.
SYS@ORA11G>grant connect,create table to tyger_ro1;
Grant succeeded.
SYS@ORA11G>grant connect,create view to tyger_ro2;
Grant succeeded.
SYS@ORA11G>create user tyger identified by tyger quota unlimited on users;
User created.
SYS@ORA11G>grant tyger_ro1,tyger_ro2 to tyger;
Grant succeeded.
SYS@ORA11G>conn tyger/tyger
Connected.
TYGER@ORA11G>create table t(x int);
Table created.
TYGER@ORA11G>insert into t values(1);
1 row created.
TYGER@ORA11G>commit;
Commit complete.
2. 此時就出現問題了，role_02 明明有 create view 而且賦予給了 tyger 為什麼這裡就沒有呢？
TYGER@ORA11G>create view view_t as select * from t;
create view view_t as select * from t
*
ERROR at line 1:
ORA-01031: insufficient privileges
3. 查看 tyger_ro2 的default_role 為NO 難道 role_02 角色失效？？？
TYGER@ORA11G>col username for a10
TYGER@ORA11G>col granted_role for a20
TYGER@ORA11G>col default_role for a20
TYGER@ORA11G>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- -------------------- --------------------
TYGER TYGER_RO1 YES
TYGER TYGER_RO2 NO
4. 再查看當前會話的權限，果然沒有 create view 權限
TYGER@ORA11G>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
5. 設置 tyger_ro2 權限生效
TYGER@ORA11G>set role tyger_ro2 identified by oracle;
Role set.
6. 當前用戶具有的權限不變
TYGER@ORA11G>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- -------------------- --------------------
TYGER TYGER_RO1 YES
TYGER TYGER_RO2 NO
7.當前會話用了 create view 權限 卻沒有了create table 權限
TYGER@ORA11G>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE VIEW
TYGER@ORA11G>create view view_t as select * from t;
View created.
TYGER@ORA11G>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE
VIEW_T VIEW
TYGER@ORA11G>create table t1(x int);
create table t1(x int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
8. 重新登錄會話，權限恢復原樣
TYGER@ORA11G>conn tyger/tyger
Connected.
TYGER@ORA11G>create table t1(x int);
Table created.
TYGER@ORA11G>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- -------------------- --------------------
TYGER TYGER_RO1 YES
TYGER TYGER_RO2 NO
總結：
在oracle 11g 中，帶有口令的角色賦予用戶，默認情況下是失效的，當 set role 生效後，其他角色所具有的權限失效， 只在當前會話有效
