Oracle賦權(quán)的回收權(quán)限是使用grant和revoke語句，但是賦權(quán)和回收權(quán)限語句執(zhí)行完成后就會(huì)立即生效么？另外Oracle的權(quán)限又分為系統(tǒng)權(quán)限、角色權(quán)限和對(duì)象權(quán)限，這三種權(quán)限的grant和revoke生效時(shí)間又是怎樣的呢。我們來看官方文檔是如何說的：
創(chuàng)新互聯(lián)建站是一家集網(wǎng)站建設(shè),阿合奇企業(yè)網(wǎng)站建設(shè),阿合奇品牌網(wǎng)站建設(shè),網(wǎng)站定制,阿合奇網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,阿合奇網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。
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系統(tǒng)權(quán)限和對(duì)象權(quán)限時(shí)會(huì)立即生效，而grant或revoke角色時(shí)對(duì)當(dāng)前會(huì)話不會(huì)立即生效，除非使用set role語句啟用角色或重新連接會(huì)話后設(shè)置才會(huì)生效。
下面以11.2.0.4為例做一個(gè)測(cè)試，是否與官方文檔描述的一致。
一、首先創(chuàng)建一個(gè)測(cè)試用戶，賦予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
現(xiàn)在的zhaoxu用戶只有CONNECT角色，只能連接到數(shù)據(jù)庫，其他基本什么都做不了。
二、測(cè)試系統(tǒng)權(quán)限和對(duì)象權(quán)限的grant和revoke
現(xiàn)在打開另一個(gè)會(huì)話賦予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是因?yàn)橐苊?1g的新特性段延遲創(chuàng)建造成影響
在賦予zhaoxu用戶create table和unlimited tablespace系統(tǒng)權(quán)限全會(huì)話1沒有做任何操作，權(quán)限就會(huì)立即生效。
再測(cè)試revoke權(quán)限的情況
--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
同樣可以看到回收操作可以立即生效，現(xiàn)有session無需做任何操作。
測(cè)試對(duì)象權(quán)限的grant和revoke
--grant測(cè)試 --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測(cè)試 --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
對(duì)對(duì)象權(quán)限的grant和revoke操作與系統(tǒng)權(quán)限的一致，所有的命令都是立即生效，包括對(duì)已經(jīng)連接的會(huì)話。
三、測(cè)試角色的grant和revoke
現(xiàn)在的zhaoxu用戶仍然只有connect角色，并且已經(jīng)打開一個(gè)會(huì)話
--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
測(cè)試grant DBA權(quán)限
--session 1查看會(huì)話中的角色 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再次查看會(huì)話中的角色，沒有dba角色，也沒有查看v$session的權(quán)限 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執(zhí)行set role命令，可以看到DBA及相關(guān)的角色已經(jīng)加載到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，可以看到新會(huì)話中默認(rèn)會(huì)加載DBA及相關(guān)角色 [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.
測(cè)試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查看會(huì)話的角色，仍然有DBA及相關(guān)角色 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執(zhí)行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
從上面的測(cè)試中可以總結(jié)出，grant和revoke系統(tǒng)權(quán)限和對(duì)象權(quán)限時(shí)會(huì)立即生效，而grant或revoke角色時(shí)對(duì)當(dāng)前會(huì)話不會(huì)立即生效，除非使用set role語句啟用角色或重新連接會(huì)話后設(shè)置才會(huì)生效。與官方文檔的描述一致。
但是有一個(gè)問題是如果查看已經(jīng)連接的其他會(huì)話所擁有的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
重慶分公司
重慶分公司
