如何進行Privilege Oracle對象權限級聯收回現象測試

本篇文章給大家分享的是有關如何進行Privilege Oracle對象權限級聯收回現象測試，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

  Oracle權限分為系統權限和對象權限。

  這里探討關于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關于對象權限級聯收回的策略。

以上就是如何進行Privilege Oracle對象權限級聯收回現象測試，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。