创建角色的时候，是允许增加口令的，角色的口令在oracle10g中不是很好理解，感觉也没啥大用，在oracle11g中做了微调，感觉口令有些用处了，并且相对更好理解了。
角色有两种增加口令的方法：
1)创建角色的时候直接指定：CREATE ROLE rol_name IDENTIFIED BY password;
2)角色已经存在，用命令行增加口令：ALTER ROLE role IDENTIFIED BY password;
角色有了口令后，有什么用呢？这就要跟set role配合使用了。
set role:在当前会话设置角色的状态
语法：
SET ROLE
{ role [ IDENTIFIED BY password ]
[, role [ IDENTIFIED BY password ] ]...
| ALL [ EXCEPT role [, role ]... ]
| NONE
} ;
下面我们通过测试验证一下角色的口令及设置角色状态
一：搭建测试环境
1）创建用户、创建角色（其中角色my_r3增加口令）、将三个角色分别赋予不同的权限并将角色赋予新创建的用户
SYS@ORA11GR2>create user xzq identified by xzq quota unlimited on users;
User created.
SYS@ORA11GR2>
SYS@ORA11GR2>create role my_r1;
Role created.
SYS@ORA11GR2>create role my_r2;
Role created.
SYS@ORA11GR2>create role my_r3 identified by oracle;
Role created.
SYS@ORA11GR2>
SYS@ORA11GR2>grant create session,create table to my_r1;
Grant succeeded.
SYS@ORA11GR2>grant create session,create view to my_r2;
Grant succeeded.
SYS@ORA11GR2>grant create session,create sequence to my_r3;
Grant succeeded.
SYS@ORA11GR2>
SYS@ORA11GR2>grant my_r1,my_r2,my_r3 to xzq;
Grant succeeded.
SYS@ORA11GR2>
2）连接xzq用户，查看当前用户的角色及当前会话的所有权限
SYS@ORA11GR2>conn xzq/xzq
Connected.
XZQ@ORA11GR2>
XZQ@ORA11GR2>col username for a10
XZQ@ORA11GR2>col granted_role for a15
XZQ@ORA11GR2>col default_role for a15
XZQ@ORA11GR2>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- --------------- ---------------
XZQ MY_R1 YES
XZQ MY_R2 YES
XZQ MY_R3 NO
XZQ@ORA11GR2>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
XZQ@ORA11GR2>
至此我们发现，带口令的角色和不带口令的角色是不同的，带口令的角色，DEFAULT_ROLE的值为NO，意思就是这个角色是失效的，我们在session_privs视图中也得以验证，my_r3角色的create sequence权限确实不存在
二：set设置角色my_r1生效其他角色失效
SET ROLE role [ IDENTIFIED BY password ][, role [ IDENTIFIED BY password ] ]...
XZQ@ORA11GR2>set role my_r1 identified by oracle;
Role set.
XZQ@ORA11GR2>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- --------------- ---------------
XZQ MY_R1 YES
XZQ MY_R2 YES
XZQ MY_R3 NO
XZQ@ORA11GR2>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
XZQ@ORA11GR2>
三：删除角色口令
XZQ@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>
SYS@ORA11GR2>alter role my_r3 not identified;
Role altered.
SYS@ORA11GR2>conn xzq/xzq
Connected.
XZQ@ORA11GR2>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- --------------- ---------------
XZQ MY_R1 YES
XZQ MY_R2 YES
XZQ MY_R3 YES
XZQ@ORA11GR2>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE
XZQ@ORA11GR2>
四：设置除my_r1角色失效之外，其他角色都生效
SET ROLE role ALL [ EXCEPT role [, role ]... ]
XZQ@ORA11GR2>set role all except my_r1;
Role set.
XZQ@ORA11GR2>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- --------------- ---------------
XZQ MY_R1 YES
XZQ MY_R2 YES
XZQ MY_R3 YES
XZQ@ORA11GR2>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE VIEW
CREATE SEQUENCE
XZQ@ORA11GR2>
五：角色my_r2设置密码
XZQ@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>alter role my_r2 identified by oracle;
Role altered.
SYS@ORA11GR2>
SYS@ORA11GR2>conn xzq/xzq
Connected.
XZQ@ORA11GR2>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- --------------- ---------------
XZQ MY_R1 YES
XZQ MY_R2 NO
XZQ MY_R3 YES
XZQ@ORA11GR2>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
XZQ@ORA11GR2>
六：SET ROLE NONE
XZQ@ORA11GR2>set role none;
Role set.
XZQ@ORA11GR2>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- --------------- ---------------
XZQ MY_R1 YES
XZQ MY_R2 NO
XZQ MY_R3 YES
XZQ@ORA11GR2>select * from session_privs;
no rows selected
XZQ@ORA11GR2>
XZQ@ORA11GR2>create table t(x int);
create table t(x int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
XZQ@ORA11GR2>
小结：
oracle10g的角色口令之所以觉得没什么用，是因为在oracle10g中设置口令后，将带口令的的角色赋予用户后，default_role依然为YES，也就是说，无论是否设置口令，角色都生效，而11g则做了一个小小的改动，如果角色有口令的话，那么当将带口令的的角色赋予用户后，default_role值为NO，此时，如果想使用这个角色，那么就需要set role，也就是用到了口令。
oracle10g的简单测试，如下：
SYS@ORA10G>create role r1;
Role created.
SYS@ORA10G>create role r2 identified by oracle;
Role created.
SYS@ORA10G>
SYS@ORA10G>grant create session,create table to r1;
Grant succeeded.
SYS@ORA10G>grant create session,create view to r2;
Grant succeeded.
SYS@ORA10G>
SYS@ORA10G>create user xxf identified by xxf quota unlimited on users;
User created.
SYS@ORA10G>grant r1,r2 to xxf;
Grant succeeded.
SYS@ORA10G>
SYS@ORA10G>conn xxf/xxf
Connected.
XXF@ORA10G>
XXF@ORA10G>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- --------------- ---------------
XXF R1 YES
XXF R2 YES
XXF@ORA10G>
XXF@ORA10G>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
XXF@ORA10G>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/685769/viewspace-745390/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
【闪回】表
请登录后发表评论
登录
全部评论
