如何理解Oracle中"通过角色授权"需要用户重新登陆

我们经常听到在Oracle中,通过角色授予用户权限的时候需要用户重新登陆才能获得授予的权限,这句话到底怎么理解呢?通过下面的步骤我们来理解这句话的含义.

1,DBA做如下的操作:

create user u1 identified by u1

create role r1;

grant create session to r1;

grant r1 to u1;

通过查询 select * from dba_sys_privs where grantee='R1',发现权限已经付给了角色.

通过查询 select * from dba_role_privs where grantee='U1',发现角色r1已经付给了用户u1.

2,通过u1登录,能够正常登录,查看相应的权限和角色也已经有了.

SQL> show user;
USER is "U1"
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> select * from session_roles;

ROLE
------------------------------

R1

3,u1尝试建表:没有建表的权限.

SQL> create table t(i int);
create table t(i int)
*
ERROR at line 1:
ORA-01031: insufficient privileges

4,DBA建立另外一个角色r2,授予r2建表的权限,建r2授予u1.

create role r2;
grant create table to r2;
grant r2 to u1;

通过查询 select * from dba_role_privs where grantee='U1',发现角色r2已经付给了用户u1.

5,在u1的session中,发现还没有获得角色权限和系统权限.

SQL> select * from session_roles;

ROLE
------------------------------
R1
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

6,重新登陆,获得了角色权限

SQL> select * from session_roles;

ROLE
------------------------------
R1
R2
SQL> select * from session_privs;

PRIVILEGE
--------------------------------------
CREATE SESSION
CREATE TABLE

7,再次建表,表空间没有quota

SQL>  create table t(i int);
 create table t(i int)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

8,DBA 把resource权限授予u1

grant resource to u1;

9,u1没有获得resource角色,但是有了UNLIMITED TABLESPACE权限从而可以建表了.这可能是resource角色特殊性.

SQL> select * from session_roles;
ROLE
------------------------------
R1
R2
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE

SQL> create table t(i int);
Table created.

10重新登陆u1,可以看到resource角色和其所有的权限也被u1用户获得了.

SQL> select * from session_roles;
ROLE
------------------------------
RESOURCE
R1
R2

SQL> select * from session_privs;
PRIVILEGE
----------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR

11,u1在另外一个schema下建表

SQL> create table yorker.ut(i int);
create table yorker.ut(i int)
*
ERROR at line 1:
ORA-01031: insufficient privileges

12,DBA给角色r2授予在别的schema建表的权限

grant create any table to r2;

13,u1不用重新登陆就或得了该权限.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE ANY TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14,用户查询没有权限查询的表

SQL> select * from yorker.a;
select * from yorker.a                     *
ERROR at line 1:
ORA-01031: insufficient privileges

15,DBA授予查询权限给角色r2,u1不用重新登陆就可以访问表.

grant all on yorker.a  to r2;

总结:在用户登录后,把一个角色授予一个用户,用户重新登陆才能获得这个角色的权限.resource有点特别,它授予用户后,用户马上得到unlimited tablespace权限.这是Oracle的特殊设置,其实resource本身并没有包含unlimited tablespace权限,通过select * from dba_sys_privs where grantee='RESOURCE'可以验证.

在用户登录中,对用户已经获得的角色添加权限,用户无需重新登陆立即获得添加的权限.

当从dba_role_privs查询看到 default_role这一列的值为NO的时候,表示这个角色没有其作用.

alter user u1 default role none/all/role_name;改变的是用户.

set role all/none/role_name;改变的是session.(登陆之后被授予的角色也起作用)