使用呼叫者許可權實現Schema導向操作

 

很多時候，我們都會使用儲存過程Procedure來實現一些指令碼工具。透過Procedure來實現一些資料庫相關的維護、開發工作，可以大大提高我們日常工作效率。一個朋友最近諮詢了關於Procedure呼叫的問題，覺得比較有意思，記錄下來供需要的朋友不時之需。

 

1、問題描述

 

問題背景是這樣，朋友在運維一個開發專案，同一個資料庫中多個Schema內容相同，用於不同的測試目的。一些開發同步任務促使編寫一個程式來實現Schema內部或者之間物件操作。從軟體版本角度看，維護一份工具指令碼是最好的方法，可以避免由於修改造成的版本錯亂現象。如何實現一份儲存過程指令碼，在不同Schema下執行效果不同就成為問題。

將問題簡化為如下描述：在Schema A裡面包括一個儲存過程Proc，A中還有一個資料表T1。在Proc程式碼中，包括了對T1的操作內容。而Schema B中也存在一個資料表T1，並且B擁有一個名為Proc的私有同義詞synonym指向A.Proc。問題是如何讓Proc根據執行的Schema主體不同，訪問不同Schema的資料表。

也就是說，如果是A呼叫Proc程式包，操作的就是A Schema裡面的資料表T1。如果B呼叫Proc程式包，就操作B Schema裡面的資料表T1。

 

2、測試實驗一

 

為了驗證測試，我們模擬了實驗環境，來觀察現象。選擇11gR2進行測試。

 

 

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE      11.2.0.4.0     Production

TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

 

建立對應Schema和資料表。

 

 

SQL> create user a identified by a;

User created

 

SQL> create user b identified by b;

User created

 

SQL> grant connect, resource to a,b;

Grant succeeded

 

SQL> grant create procedure to a,b;

Grant succeeded

 

SQL> grant create synonym to a,b;

Grant succeeded

 

 

在Schema A下面建立資料表和對應操作儲存過程。

 

 

SQL> conn a/a@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as a

 

SQL> create table a(col varchar2(10));

Table created

 

SQL> create or replace procedure Proc(i_vc_name varchar2) is

  2  begin

  3    insert into a values (i_vc_name);

  4    commit;

  5  end Proc;

  6  /

 

Procedure created

 

 

從Schema A進行呼叫動作：

 

 

SQL> exec proc('iii');

PL/SQL procedure successfully completed

 

SQL> select * from a;

COL

----------------------------------------

Iii

 

SQL> grant execute on proc to b;

Grant succeeded

 

 

另外建立Schema B資料表物件，並且包括同義詞物件。

 

 

SQL> conn b/b@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as b

 

SQL> create table a(col varchar2(10));

Table created

 

SQL> create synonym proc for a.proc;

Synonym created

 

 

進行預設情況測試，在Schema B中呼叫儲存過程proc，看運算元據表是哪張：

 

 

 

SQL> conn b/b@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as b

 

SQL> exec proc('JJJ');

PL/SQL procedure successfully completed

 

SQL> select * from a;

COL

----------------------------------------

 

 

Schema B中資料表a沒有資料，檢視Schema A中資料表情況：

 

 

SQL> select * from a.a;

 

COL

--------------------

JJJ

Iii

 

 

實驗說明：在預設情況下，不同Schema物件呼叫相同儲存過程，其中涉及到的物件都是相同的。也就是Oracle儲存過程中的“所有者許可權”。一旦使用者擁有執行儲存過程的許可權，就意味著在執行體中，使用的是執行體所有者的許可權體系。

那麼這個問題似乎是沒有辦法。執行體指向的是Schema A的資料表a。

 

3、測試實驗二

 

與所有者許可權對應的另一種模式是“呼叫者許可權”。也就說，對使用者是否可以執行該程式體中的物件，完全取決於執行呼叫使用者系統許可權和物件許可權（注意：非角色許可權）。

筆者一種猜想，如果應用呼叫者許可權，從執行使用者許可權角度看，是不是可以直接訪問自己Schema中的物件了。下面透過實驗進行證明。

 

 

SQL> conn a/a@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as a

 

SQL>

SQL> create or replace procedure Proc(i_vc_name varchar2) AUTHID CURRENT_USER is

  2  begin

  3    insert into a values (i_vc_name);

  4    commit;

  5  end Proc;

  6  /

 

Procedure created

 

 

在使用者a中進行實驗，結果：

 

 

SQL> exec proc('mmm');

PL/SQL procedure successfully completed

 

SQL> select * from a;

 

COL

----------------------------------------

JJJ

mmm

iii

 

 

轉換到使用者b中，進行測試：

 

 

SQL> conn b/b@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as b

 

SQL> exec proc('NNN');

PL/SQL procedure successfully completed

 

SQL> select * from a;

COL

----------------------------------------

NNN

 

 

SQL> select * from a.a

  2  ;

COL

--------------------

JJJ

mmm

iii

 

 

測試成功！在呼叫者許可權模式下，可以實現呼叫Schema下資料表優先的效果。如果此時Schema B中沒有資料表a，效果如何？

 

 

SQL> drop table a;

Table dropped

 

SQL> exec proc('mt');

begin proc('mt'); end;

 

ORA-00942: 表或檢視不存在

ORA-06512: 在 "A.PROC", line 3

ORA-06512: 在 line 1

 

 

如果b使用者可以有a使用者下的資料表a許可權呢？問題依然。

 

 

SQL> conn a/a@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as a

 

SQL> grant all on a to b;

 

Grant succeeded

 

SQL> conn b/b@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as b

 

SQL> exec proc('mt');

 

begin proc('mt'); end;

 

ORA-00942: 表或檢視不存在

ORA-06512: 在 "A.PROC", line 3

ORA-06512: 在 line 1

 

 

4、結論

 

所有者許可權和呼叫者許可權，是Oracle儲存過程中兩個重要的概念物件，一些麻煩場景下應用往往有不錯的效果。