oracle10g角色,oracle10g和11g关于角色口令的区别

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

角色口令测试

oracle 10g中，无论角色是否有口令，只要你将角色grant给某个用户，那么，默认的情况下，这些角色中的权限，用户都拥有。

oracle 11g中，角色的口令略有修正，当某个角色是拥有口令的话，当你将带有口令的角色grant给某个用户的话，那么默认的情况下，这个带口令的角色下的所有权限，用户是无法拥有的，只有当set那个拥有口令的角色后，那么

，带口令的角色下的权限只有在当前会话下才可以使用，不过，其他的角色都暂时失效，修改只在当前会话有效。

语法：------只在当前会话有效

SET ROLE

{

role [ IDENTIFIED BY password ]

[, role [ IDENTIFIED BY password ] ]...

|

ALL [ EXCEPT role [, role ]... ]

|

NONE

}

;

1.1.oracle

10g 中测试带口令的角色

SYS@ORCL>select * from v$version;

BANNER

----------------------------------------------------------------

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 : Version 10.2.0.1.0

- Production

NLSRTL Version 10.2.0.1.0 - Production

1.1.1创建两个角色 role_01 没有密码 role_02 有密码

SYS@ORCL>create role role_01;

Role created.

SYS@ORCL>create role role_02 identified

by oracle;

Role created.

1.1.2赋予角色 role_01 连接、建表权限

SYS@ORCL>grant connect,create table to

role_01;

Grant succeeded.

1.1.3赋予角色 role_02 连接、创建视图权限

SYS@ORCL>grant connect,create view to

role_02;

Grant succeeded.

1.1.4创建测试用户 tyger

SYS@ORCL>create user tyger identified by

tyger quota unlimited on users;

User created.

1.1.5将两个角色赋予tyger

SYS@ORCL>grant role_01,role_02 to tyger;

Grant succeeded.

1.1.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

1.1.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

1.1.8查看当前会话具有的权限

TYGER@ORCL>select * from session_privs;

PRIVILEGE

----------------------------------------

CREATE SESSION

CREATE TABLE

CREATE VIEW

1.1.9 set的应用 ----set 在当前会话中设置角色状态

TYGER@ORCL>set role ROLE_01;

Role set.

1.1.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

1.1.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

1.1.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.

1.1.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

1.1.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

1.1.15重新登录会话，连接用户 所有权限都恢复原样

TYGER@ORCL>conn tyger/tyger;

Connected.

TYGER@ORCL>select * from session_privs;

PRIVILEGE

----------------------------------------

CREATE SESSION

CREATE TABLE

CREATE VIEW

1.1.16总结

在oracle 10g无论角色是否有口令，将角色赋予给用户后，用户具有角色的全部权限。

1.2.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.2.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.

1.2.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

1.2.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

1.2.4再查看当前会话的权限，果然没有 create view 权限

TYGER@ORA11G>select * from

session_privs;

PRIVILEGE

----------------------------------------

CREATE SESSION

CREATE TABLE

1.2.5设置 tyger_ro2 权限生效

TYGER@ORA11G>set role tyger_ro2

identified by oracle;

Role set.

1.2.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

1.2.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

1.2.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

1.2.9总结

在oracle 11g中，带有口令的角色赋予用户，默认情况下是失效的，当set role生效后，其他角色所具有的权限失效，

只在当前会话有效

reference :