oracle 10g 和 11g 關於角色口令的區別

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

而一旦這個集合的許可權超過了使用者的最低需求，就可能帶來資料庫的安全風險

角色口令測試

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 生效後，其他角色所具有的許可權失效， 只在當前會話有效