Oracle 赋权和回收权限的生效时间

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 <code>PUBLIC</code>) take immediate effect.

All grants and revokes of roles to anything (users, other roles, <code>PUBLIC</code>) take effect only when a current user session issues a <code>SET ROLE</code> 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 <code>SESSION_ROLES</code> data dictionary view.

从上面的描述中我们可以知道，grant和revoke系统权限和对象权限时会立即生效，而grant或revoke角色时对当前会话不会立即生效，除非使用set role语句启用角色或重新连接会话后设置才会生效。

下面以11.2.0.4为例做一个测试，是否与官方文档描述的一致。

一、首先创建一个测试用户，赋予connect角色

<code>sys@ORCL></code><code>create</code> <code>user</code> <code>zhaoxu identified </code><code>by</code> <code>zhaoxu;</code>

<code>User</code> <code>created.</code>

<code>sys@ORCL></code><code>grant</code> <code>connect</code> <code>to</code> <code>zhaoxu;</code>

<code>Grant</code> <code>succeeded.</code>

<code>sys@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>dba_role_privs </code><code>where</code> <code>grantee=</code><code>'ZHAOXU'</code><code>;</code>

<code>GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R</code>

<code>ZHAOXU </code><code>CONNECT</code> <code>NO</code> <code>YES</code>

<code>sys@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>dba_sys_privs </code><code>where</code> <code>grantee=</code><code>'ZHAOXU'</code><code>;</code>

<code>no</code> <code>rows</code> <code>selected</code>

<code>sys@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>dba_tab_privs </code><code>where</code> <code>grantee=</code><code>'ZHAOXU'</code><code>;</code>

<code>sys@ORCL>conn zhaoxu/zhaoxu</code>

<code>Connected.</code>

<code>zhaoxu@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>session_roles;</code>

<code>CONNECT</code>

<code>zhaoxu@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>session_privs;</code>

<code>PRIVILEGE</code>

<code>CREATE</code> <code>SESSION</code>

<code>zhaoxu@ORCL></code><code>create</code> <code>table</code> <code>t (id number) segment creation immediate;</code>

<code>create</code> <code>table</code> <code>t (id number)</code>

<code>ERROR </code><code>at</code> <code>line 1:</code>

<code>ORA-01031: insufficient </code><code>privileges</code>

现在的zhaoxu用户只有CONNECT角色，只能连接到数据库，其他基本什么都做不了。

二、测试系统权限和对象权限的grant和revoke

现在打开另一个会话赋予system privilege给zhaoxu用户

<code>--session 2</code>

<code>sys@ORCL></code><code>grant</code> <code>create</code> <code>table</code><code>,unlimited tablespace </code><code>to</code> <code>zhaoxu;</code>

<code>--session 1</code>

<code>UNLIMITED TABLESPACE</code>

<code>CREATE</code> <code>TABLE</code>

<code>Table</code> <code>created.</code>

<code>--使用segment creation immediate是因为要避免11g的新特性段延迟创建造成影响</code>

在赋予zhaoxu用户create table和unlimited tablespace系统权限全会话1没有做任何操作，权限就会立即生效。

再测试revoke权限的情况

<code>sys@ORCL></code><code>revoke</code> <code>unlimited tablespace </code><code>from</code> <code>zhaoxu;</code>

<code>Revoke</code> <code>succeeded.</code>

<code>zhaoxu@ORCL></code><code>create</code> <code>table</code> <code>t1 (id number) segment creation immediate;</code>

<code>create</code> <code>table</code> <code>t1 (id number) segment creation immediate</code>

<code>ORA-01950: </code><code>no</code> <code>privileges</code> <code>on</code> <code>tablespace </code><code>'USERS'</code>

同样可以看到回收操作可以立即生效，现有session无需做任何操作。

测试对象权限的grant和revoke

<code>--grant测试</code>

<code>zhaoxu@ORCL></code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>zx.t;</code>

<code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>zx.t</code>

<code>ORA-00942: </code><code>table</code> <code>or</code> <code>view</code> <code>does </code><code>not</code> <code>exist</code>

<code>sys@ORCL></code><code>grant</code> <code>select</code> <code>on</code> <code>zx.t </code><code>to</code> <code>zhaoxu;</code>

<code>GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY</code>

<code>ZHAOXU ZX T ZX </code><code>SELECT</code> <code>NO</code> <code>NO</code>

<code> </code><code>COUNT</code><code>(*)</code>

<code>--revoke测试</code>

<code>sys@ORCL></code><code>revoke</code> <code>select</code> <code>on</code> <code>zx.t </code><code>from</code> <code>zhaoxu;</code>

对对象权限的grant和revoke操作与系统权限的一致，所有的命令都是立即生效，包括对已经连接的会话。

三、测试角色的grant和revoke

现在的zhaoxu用户仍然只有connect角色，并且已经打开一个会话

测试grant DBA权限

<code>--session 1查看会话中的角色</code>

<code>--session 2赋予zhaoxu用户dba角色</code>

<code>sys@ORCL></code><code>grant</code> <code>dba </code><code>to</code> <code>zhaoxu;</code>

<code>ZHAOXU DBA </code><code>NO</code> <code>YES</code>

<code>--session 1再次查看会话中的角色，没有dba角色，也没有查看v$session的权限</code>

<code>zhaoxu@ORCL></code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>v$session;</code>

<code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>v$session</code>

<code>--session 1执行set role命令，可以看到DBA及相关的角色已经加载到session1中了，也可以查询v$session</code>

<code>zhaoxu@ORCL></code><code>set</code> <code>role dba;</code>

<code>SELECT_CATALOG_ROLE</code>

<code>HS_ADMIN_SELECT_ROLE</code>

<code>19 </code><code>rows</code> <code>selected.</code>

<code>--使用zhaoxu用户打开session 3，可以看到新会话中默认会加载DBA及相关角色</code>

<code>[oracle@rhel6 ~]$ sqlplus zhaoxu/zhaoxu</code>

<code>SQL*Plus: Release 11.2.0.4.0 Production </code><code>on</code> <code>Sat Jan 21 16:22:01 2017</code>

<code>Connected </code><code>to</code><code>:</code>

<code>Oracle </code><code>Database</code> <code>11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production</code>

<code>With</code> <code>the Partitioning, OLAP, Data Mining </code><code>and</code> <code>Real</code> <code>Application Testing options</code>

<code>20 </code><code>rows</code> <code>selected.</code>

测试revoke DBA角色

<code>--session 2回收DBA角色</code>

<code>sys@ORCL></code><code>revoke</code> <code>dba </code><code>from</code> <code>zhaoxu;</code>

<code>--session 3查看会话的角色，仍然有DBA及相关角色</code>

<code>--使用zhaoxu用户打开session 4，查看只有CONNECT角色</code>

<code>SQL*Plus: Release 11.2.0.4.0 Production </code><code>on</code> <code>Sat Jan 21 16:30:19 2017</code>

<code>--session 3执行set role命令</code>

<code>ORA-01924: role </code><code>'DBA'</code> <code>not</code> <code>granted </code><code>or</code> <code>does </code><code>not</code> <code>exist</code>

<code>zhaoxu@ORCL></code><code>set</code> <code>role </code><code>all</code><code>;</code>

从上面的测试中可以总结出，grant和revoke系统权限和对象权限时会立即生效，而grant或revoke角色时对当前会话不会立即生效，除非使用set role语句启用角色或重新连接会话后设置才会生效。与官方文档的描述一致。

但是有一个问题是如果查看已经连接的其他会话所拥有的role呢？

本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1893674，如需转载请自行联系原作者

Oracle 赋权和回收权限的生效时间

继续阅读

oracle海量数据表删除重复记录

[引]在Oracle中如何利用Rowid查找和删除表中的重复记录

oralce与mysql中，如何删除重复记录

查询表中重复记录

ORACLE 表压缩

Java Web项目开发需要掌握的技术

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程序

LoadRunner 的多种Vuser类型

性能测试（并发负载压力）测试分析－简要篇

赋予用户dba权限--解释以上：

Oracle的基本操作

为什么要选择UniDAC

windows不能在本地计算机上运行oracleDbConsoleorcl

SQL语言基础：常用的数据查询语句

Oracle 批量查询传入List 返回List