Oracle 赋权和回收权限的生效时间
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 赋权和回收权限的生效时间,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9398字,纯文字阅读大概需要14分钟。
内容图文
![Oracle 赋权和回收权限的生效时间](/upload/InfoBanner/zyjiaocheng/475/a742d51ce70f46c88acfff0b18543ab4.jpg)
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 aSET 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
本文出自 “DBA Fighting!” 博客,请务必保留此出处http://hbxztc.blog.51cto.com/1587495/1893674
Oracle 赋权和回收权限的生效时间
标签:oracle grant revoke
本文系统来源:http://hbxztc.blog.51cto.com/1587495/1893674
内容总结
以上是互联网集市为您收集整理的Oracle 赋权和回收权限的生效时间全部内容,希望文章能够帮你解决Oracle 赋权和回收权限的生效时间所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。