Oracle許可權分為系統許可權和物件許可權。在文章《【Privilege】Oracle系統許可權非級聯收回現象測試》（http://space.itpub.net/519536/viewspace-709663）給大家演示了系統許可權非級聯收回現象。
這裡探討關於Oracle物件許可權級聯回收效果，與系統許可權效果正好相反：Oracle會級聯回收物件許可權
本實驗中共涉及到三個使用者：secooler1使用者、secooler2使用者和secooler3使用者。

1.清理並初始化使用者
1）清理測試使用者secooler1、secooler2和secooler3
sys@secdb> conn / as sysdba
Connected.
sys@secdb> drop user secooler1 cascade;

User dropped.

sys@secdb> drop user secooler2;

User dropped.

sys@secdb> drop user secooler3;

User dropped.

2）建立secooler1、secooler2和secooler3使用者並授予最基本的系統許可權
（1）建立secooler1使用者，並授予建立會話、建立表以及表空間使用許可權，用於後續在secooler1使用者中建立測試表。
sys@secdb> create user secooler1 identified by secooler1;

User created.

sys@secdb> grant create session,create table,unlimited tablespace to secooler1;

Grant succeeded.

（2）建立secooler2使用者並授予最基本的建立會話的許可權
sys@secdb> create user secooler2 identified by secooler2;

User created.

sys@secdb> grant create session to secooler2;

Grant succeeded.

（3）同樣，建立secooler3使用者並授予最基本的建立會話的許可權
sys@secdb> create user secooler3 identified by secooler3;

User created.

sys@secdb> grant create session to secooler3;

Grant succeeded.

2.建立資料庫物件並完成物件許可權授權
1）在secooler1使用者下建立表T並初始化資料。
這裡，在secooler1使用者下建立表為例
sys@secdb> conn secooler1/secooler1
Connected.
secooler1@secdb> create table t (x int);

Table created.

secooler1@secdb> insert into t values (1);

1 row created.

2）在secooler1使用者下完成將查詢T表的物件許可權以with grant option選項授予給secooler2
secooler1@secdb> grant select on t to secooler2 with grant option;

Grant succeeded.

3）在secooler2使用者下將物件許可權授予給secooler3
secooler1@secdb> conn secooler2/secooler2
Connected.
secooler2@secdb> grant select on secooler1.t to secooler3;

Grant succeeded.

3.驗證物件許可權授予結果
1）透過查詢驗證物件許可權授予結果
secooler2@secdb> select * from secooler1.t;

         X
----------
         1

secooler2@secdb> conn secooler3/secooler3
secooler3@secdb> select * from secooler1.t;

         X
----------
         1

物件許可權使用正常。

2）透過檢視物件許可權確認
secooler2@secdb> conn / as sysdba
Connected.
sys@secdb> col GRANTEE for a9
sys@secdb> col OWNER for a9
sys@secdb> col TABLE_NAME for a5
sys@secdb> col GRANTOR for a9
sys@secdb> col PRIVILEGE for a9
sys@secdb> select * from dba_tab_privs where grantee='SECOOLER2';

GRANTEE   OWNER     TABLE GRANTOR   PRIVILEGE GRA HIE
--------- --------- ----- --------- --------- --- ---
SECOOLER2 SECOOLER1 T     SECOOLER1 SELECT    YES NO

sys@secdb> select * from dba_tab_privs where grantee='SECOOLER3';

GRANTEE   OWNER     TABLE GRANTOR   PRIVILEGE GRA HIE
--------- --------- ----- --------- --------- --- ---
SECOOLER3 SECOOLER1 T     SECOOLER2 SELECT    NO NO

檢視結果，物件許可權授予資訊顯示正常。

4.在secooler1使用者中回收secooler2使用者的物件許可權
sys@secdb> conn secooler1/secooler1
Connected.
secooler1@secdb> revoke select on t from secooler2;

Revoke succeeded.

5.檢視級聯刪除效果
1）透過查詢許可權確認
secooler3@secdb> conn / as sysdba
Connected.
sys@secdb> select * from dba_tab_privs where grantee='SECOOLER2';

no rows selected

sys@secdb> select * from dba_tab_privs where grantee='SECOOLER3';

no rows selected

可見，曾經secooler2授予給secooler3使用者的物件許可權也被級聯收回。兩個使用者都已不具有物件許可權。

2）透過物件查詢測試確認
secooler1@secdb> conn secooler2/secooler2
Connected.
secooler2@secdb> select * from secooler1.t;
select * from secooler1.t
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

secooler2@secdb> conn secooler3/secooler3
Connected.
secooler3@secdb> select * from secooler1.t;
select * from secooler1.t
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

可見secooler2和secooler3的物件許可權都被收回。

6.小結
secooler1回收secooler2的物件許可權的同時也會收回secooler3的物件許可權，這便是Oracle關於物件許可權級聯收回的策略。

Good luck.

secooler
11.10.26

-- The End --

【Privilege】Oracle物件許可權級聯收回現象測試

相關文章