很多時候，我們都會使用存儲過程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 |
很多時候，我們都會使用存儲過程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 |
English » | | | | | | | | |
Text-to-speech function is limited to 100 characters