您好,登录后才能下订单哦!
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 zhaoxu identified by zhaoxu; User created. sys@ORCL>grant connect to zhaoxu; Grant succeeded. sys@ORCL>select * from dba_role_privs where grantee='ZHAOXU'; GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R ------------------------------ ------------------------------ --------- --------- ZHAOXU CONNECT NO YES sys@ORCL>select * from dba_sys_privs where grantee='ZHAOXU'; no rows selected sys@ORCL>select * from dba_tab_privs where grantee='ZHAOXU'; no rows selected sys@ORCL>conn zhaoxu/zhaoxu Connected. zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------------------------ CONNECT zhaoxu@ORCL>select * from session_privs; PRIVILEGE ------------------------------------------------------------ CREATE SESSION zhaoxu@ORCL>create table t (id number) segment creation immediate; create table t (id number) * ERROR at line 1: ORA-01031: insufficient privileges
现在的zhaoxu用户只有CONNECT角色,只能连接到数据库,其他基本什么都做不了。
二、测试系统权限和对象权限的grant和revoke
现在打开另一个会话赋予system privilege给zhaoxu用户
--session 2 sys@ORCL>grant create table,unlimited tablespace to zhaoxu; Grant succeeded. --session 1 zhaoxu@ORCL>select * from session_privs; PRIVILEGE ------------------------------------------------------------------------------------------------------------------------ CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------------------------------------------------------ CONNECT zhaoxu@ORCL>create table t (id number) segment creation immediate; Table created. --使用segment creation immediate是因为要避免11g的新特性段延迟创建造成影响
在赋予zhaoxu用户create table和unlimited tablespace系统权限全会话1没有做任何操作,权限就会立即生效。
再测试revoke权限的情况
--session 2 sys@ORCL>revoke unlimited tablespace from zhaoxu; Revoke succeeded. --session 1 zhaoxu@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' zhaoxu@ORCL>select * from session_privs; PRIVILEGE ------------------------------------------------------------------------------------------------------------------------ CREATE SESSION CREATE TABLE
同样可以看到回收操作可以立即生效,现有session无需做任何操作。
测试对象权限的grant和revoke
--grant测试 --session 1 zhaoxu@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 zhaoxu; Grant succeeded. sys@ORCL>select * from dba_tab_privs where grantee='ZHAOXU'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --------- ZHAOXU ZX T ZX SELECT NO NO --session 1 zhaoxu@ORCL>select count(*) from zx.t; COUNT(*) ---------- 99999 zhaoxu@ORCL>select * from session_privs; PRIVILEGE ------------------------------------------------------------------------------------------------------------------------ CREATE SESSION CREATE TABLE --revoke测试 --session 2 sys@ORCL>revoke select on zx.t from zhaoxu; Revoke succeeded. sys@ORCL>select * from dba_tab_privs where grantee='ZHAOXU'; no rows selected --session 1 zhaoxu@ORCL>select count(*) from zx.t; select count(*) from zx.t * ERROR at line 1: ORA-00942: table or view does not exist
对对象权限的grant和revoke操作与系统权限的一致,所有的命令都是立即生效,包括对已经连接的会话。
三、测试角色的grant和revoke
现在的zhaoxu用户仍然只有connect角色,并且已经打开一个会话
--session 2 sys@ORCL>select * from dba_role_privs where grantee='ZHAOXU'; GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R ------------------------------ ------------------------------ --------- --------- ZHAOXU CONNECT NO YES --session 1 zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------ CONNECT
测试grant DBA权限
--session 1查看会话中的角色 zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------------------------------------------------------ CONNECT --session 2赋予zhaoxu用户dba角色 sys@ORCL>grant dba to zhaoxu; Grant succeeded. sys@ORCL>select * from dba_role_privs where grantee='ZHAOXU'; GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R ------------------------------ ------------------------------ --------- --------- ZHAOXU DBA NO YES ZHAOXU CONNECT NO YES --session 1再次查看会话中的角色,没有dba角色,也没有查看v$session的权限 zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------------------------------------------------------ CONNECT zhaoxu@ORCL>select count(*) from v$session; select count(*) from v$session * ERROR at line 1: ORA-00942: table or view does not exist --session 1执行set role命令,可以看到DBA及相关的角色已经加载到session1中了,也可以查询v$session zhaoxu@ORCL>set role dba; Role set. zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------------------------------------------------------ DBA SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE ...... 19 rows selected. zhaoxu@ORCL>select count(*) from v$session; COUNT(*) ---------- 29 --使用zhaoxu用户打开session 3,可以看到新会话中默认会加载DBA及相关角色 [oracle@rhel6 ~]$ sqlplus zhaoxu/zhaoxu SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 21 16:22:01 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------------------------------------------------------ CONNECT DBA SELECT_CATALOG_ROLE ...... 20 rows selected.
测试revoke DBA角色
--session 2回收DBA角色 sys@ORCL>revoke dba from zhaoxu; Revoke succeeded. sys@ORCL>select * from dba_role_privs where grantee='ZHAOXU'; GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R ------------------------------ ------------------------------ --------- --------- ZHAOXU CONNECT NO YES --session 3查看会话的角色,仍然有DBA及相关角色 zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------------------------------------------------------ CONNECT DBA SELECT_CATALOG_ROLE ...... 20 rows selected. --使用zhaoxu用户打开session 4,查看只有CONNECT角色 [oracle@rhel6 ~]$ sqlplus zhaoxu/zhaoxu SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 21 16:30:19 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------------------------------------------------------ CONNECT --session 3执行set role命令 zhaoxu@ORCL>set role dba; set role dba * ERROR at line 1: ORA-01924: role 'DBA' not granted or does not exist zhaoxu@ORCL>set role all; Role set. zhaoxu@ORCL>select * from session_roles; ROLE ------------------------------------------------------------------------------------------ CONNECT
从上面的测试中可以总结出,grant和revoke系统权限和对象权限时会立即生效,而grant或revoke角色时对当前会话不会立即生效,除非使用set role语句启用角色或重新连接会话后设置才会生效。与官方文档的描述一致。
但是有一个问题是如果查看已经连接的其他会话所拥有的role呢?
官方文档:http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG99974
system privilege:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#BABEFFEE
object privilege:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#BGBCIIEG
set role:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10004.htm#SQLRF01704
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。