oracle 10g 和 11g 关于角色口令的区别

静娴4321

　　角色是一组相关权限的命名集合，使用角色最主要的目的是简化权限管理
　　而一旦这个集合的权限超过了用户的最低需求，就可能带来数据库的安全风险
　　

　　角色口令测试
　　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 生效后，其他角色所具有的权限失效， 只在当前会话有效