Oracle赋权的回收权限是使用grant和revoke语句，但是赋权和回收权限语句执行完成后就会立即生效么？另外Oracle的权限又分为系统权限、角色权限和对象权限，这三种权限的grant和revoke生效时间又是怎样的呢。我们来看官方文档是如何说的：
Depending on what is granted or revoked, a grant or revoke takes effect at different times:
All grants and revokes of system and object privileges to anything (users, roles, and PUBLIC) take immediate effect.
All grants and revokes of roles to anything (users, other roles, PUBLIC) take effect only when a current user session issues a SET ROLE statement to reenable the role after the grant and revoke, or when a new user session is created after the grant or revoke.
You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.
从上面的描述中我们可以知道，grant和revoke系统权限和对象权限时会立即生效，而grant或revoke角色时对当前会话不会立即生效，除非使用set role语句启用角色或重新连接会话后设置才会生效。
下面以11.2.0.4为例做一个测试，是否与官方文档描述的一致。
一、首先创建一个测试用户，赋予connect角色
sys@ORCL>create user linuxidc identified by linuxidc;
User created.
sys@ORCL>grant connect to linuxidc;
Grant succeeded.
sys@ORCL>select * from dba_role_privs where grantee='linuxidc';
GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R
------------------------------ ------------------------------ --------- ---------
linuxidc CONNECT NO YES
sys@ORCL>select * from dba_sys_privs where grantee='linuxidc';
no rows selected
sys@ORCL>select * from dba_tab_privs where grantee='linuxidc';
no rows selected
sys@ORCL>conn linuxidc/zhaoxu
Connected.
linuxidc@ORCL>select * from session_roles;
ROLE
------------------------------------------------------------
CONNECT
linuxidc@ORCL>select * from session_privs;
PRIVILEGE
------------------------------------------------------------
CREATE SESSION
linuxidc@ORCL>create table t (id number) segment creation immediate;
create table t (id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
现在的linuxidc用户只有CONNECT角色，只能连接到数据库，其他基本什么都做不了。
二、测试系统权限和对象权限的grant和revoke
现在打开另一个会话赋予system privilege给linuxidc用户
1234567891011121314151617181920212223 --session 2
sys@ORCL>grant create table,unlimited tablespace to linuxidc;
Grant succeeded.
--session 1
linuxidc@ORCL>select * from session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
linuxidc@ORCL>select * from session_roles;
ROLE
------------------------------------------------------------------------------------------
CONNECT
linuxidc@ORCL>create table t (id number) segment creation immediate;
Table created.
--使用segment creation immediate是因为要避免11g的新特性段延迟创建造成影响
在赋予linuxidc用户create table和unlimited tablespace系统权限全会话1没有做任何操作，权限就会立即生效。
再测试revoke权限的情况
1234567891011121314151617 --session 2
sys@ORCL>revoke unlimited tablespace from linuxidc;
Revoke succeeded.
--session 1
linuxidc@ORCL>create table t1 (id number) segment creation immediate;
create table t1 (id number) segment creation immediate
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
linuxidc@ORCL>select * from session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE SESSION
CREATE TABLE
同样可以看到回收操作可以立即生效，现有session无需做任何操作。
测试对象权限的grant和revoke
--grant测试
--session 1
linuxidc@ORCL>select count(*) from zx.t;
select count(*) from zx.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
--session 2
sys@ORCL>grant select on zx.t to linuxidc;
Grant succeeded.
sys@ORCL>select * from dba_tab_privs where grantee='linuxidc';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
------------------------------ ------------------------------ ---------- ---------- ---------- --------- ---------
linuxidc ZX T ZX SELECT NO NO
--session 1
linuxidc@ORCL>select count(*) from zx.t;
COUNT(*)
----------
99999
linuxidc@ORCL>select * from session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE SESSION
CREATE TABLE
--revoke测试
--session 2
sys@ORCL>revoke select on zx.t from linuxidc;
Revoke succeeded.
sys@ORCL>select * from dba_tab_privs where grantee='linuxidc';
no rows selected
--session 1
linuxidc@ORCL>select count(*) from zx.t;
select count(*) from zx.t
*
ERROR at line 1:
ORA-00942: table or view does not exist