如何理解
Oracle中"通过角色授权"需要用户重新登陆的问题
我们经常听到在Oracle中,通过角色授予用户权限的时候需要用户重新登陆才能获得授予的权限,
这句话到底怎么理解呢 通过下面的步骤我们来理解这句话的含义.
1,DBA做如下的操作:
create user u1 identified by u1
www.2cto.com
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)
* www.2cto.com
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 www.2cto.com
------------------------------
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: insufficie
