溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
