很多時(shí)候，我們都會(huì)使用存儲(chǔ)過(guò)程Procedure來(lái)實(shí)現(xiàn)一些腳本工具。通過(guò)Procedure來(lái)實(shí)現(xiàn)一些
數(shù)據(jù)庫(kù)相關(guān)的維護(hù)、開(kāi)發(fā)
工作，可以大大提高我們?nèi)粘９ぷ餍省Ｒ粋€(gè)朋友最近咨詢了關(guān)于Procedure調(diào)用的問(wèn)題，覺(jué)得比較有意思，記錄下來(lái)供需要的朋友不時(shí)之需。
1、問(wèn)題描述
問(wèn)題背景是這樣，朋友在運(yùn)維一個(gè)開(kāi)發(fā)項(xiàng)目，同一個(gè)數(shù)據(jù)庫(kù)中多個(gè)Schema內(nèi)容相同，用于不同的
測(cè)試目的。一些開(kāi)發(fā)同步任務(wù)促使編寫(xiě)一個(gè)程序來(lái)實(shí)現(xiàn)Schema內(nèi)部或者之間對(duì)象操作。從軟件版本角度看，維護(hù)一份工具腳本是最好的方法，可以避免由于修改造成的版本錯(cuò)亂現(xiàn)象。如何實(shí)現(xiàn)一份存儲(chǔ)過(guò)程腳本，在不同Schema下執(zhí)行效果不同就成為問(wèn)題。
將問(wèn)題簡(jiǎn)化為如下描述：在Schema A里面包括一個(gè)存儲(chǔ)過(guò)程Proc，A中還有一個(gè)數(shù)據(jù)表T1。在Proc代碼中，包括了對(duì)T1的操作內(nèi)容。而Schema B中也存在一個(gè)數(shù)據(jù)表T1，并且B擁有一個(gè)名為Proc的私有同義詞synonym指向A.Proc。問(wèn)題是如何讓Proc根據(jù)執(zhí)行的Schema主體不同，訪問(wèn)不同Schema的數(shù)據(jù)表。
也就是說(shuō)，如果是A調(diào)用Proc程序包，操作的就是A Schema里面的數(shù)據(jù)表T1。如果B調(diào)用Proc程序包，就操作B Schema里面的數(shù)據(jù)表T1。
2、測(cè)試實(shí)驗(yàn)一
為了驗(yàn)證測(cè)試，我們模擬了實(shí)驗(yàn)環(huán)境，來(lái)觀察現(xiàn)象。選擇11gR2進(jìn)行測(cè)試。
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 創(chuàng)建對(duì)應(yīng)Schema和數(shù)據(jù)表。 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下面創(chuàng)建數(shù)據(jù)表和對(duì)應(yīng)操作存儲(chǔ)過(guò)程。
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進(jìn)行調(diào)用動(dòng)作：
SQL> exec proc('iii'); PL/SQL procedure successfully completed SQL> select * from a; COL ---------------------------------------- Iii SQL> grant execute on proc to b; Grant succeeded |
另外創(chuàng)建Schema B數(shù)據(jù)表對(duì)象，并且包括同義詞對(duì)象。
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
進(jìn)行默認(rèn)情況測(cè)試，在Schema B中調(diào)用存儲(chǔ)過(guò)程proc，看操作數(shù)據(jù)表是哪張：
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中數(shù)據(jù)表a沒(méi)有數(shù)據(jù)，查看Schema A中數(shù)據(jù)表情況：
SQL> select * from a.a;
COL
--------------------
JJJ
Iii
實(shí)驗(yàn)說(shuō)明：在默認(rèn)情況下，不同Schema對(duì)象調(diào)用相同存儲(chǔ)過(guò)程，其中涉及到的對(duì)象都是相同的。也就是Oracle存儲(chǔ)過(guò)程中的“所有者權(quán)限”。一旦用戶擁有執(zhí)行存儲(chǔ)過(guò)程的權(quán)限，就意味著在執(zhí)行體中，使用的是執(zhí)行體所有者的權(quán)限體系。
那么這個(gè)問(wèn)題似乎是沒(méi)有辦法。執(zhí)行體指向的是Schema A的數(shù)據(jù)表a。
3、測(cè)試實(shí)驗(yàn)二
與所有者權(quán)限對(duì)應(yīng)的另一種模式是“調(diào)用者權(quán)限”。也就說(shuō)，對(duì)用戶是否可以執(zhí)行該程序體中的對(duì)象，完全取決于執(zhí)行調(diào)用用戶系統(tǒng)權(quán)限和對(duì)象權(quán)限（注意：非角色權(quán)限）。
筆者一種猜想，如果應(yīng)用調(diào)用者權(quán)限，從執(zhí)行用戶權(quán)限角度看，是不是可以直接訪問(wèn)自己Schema中的對(duì)象了。下面通過(guò)實(shí)驗(yàn)進(jìn)行證明。
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 |
很多時(shí)候，我們都會(huì)使用存儲(chǔ)過(guò)程Procedure來(lái)實(shí)現(xiàn)一些腳本工具。通過(guò)Procedure來(lái)實(shí)現(xiàn)一些
數(shù)據(jù)庫(kù)相關(guān)的維護(hù)、開(kāi)發(fā)
工作，可以大大提高我們?nèi)粘９ぷ餍省Ｒ粋€(gè)朋友最近咨詢了關(guān)于Procedure調(diào)用的問(wèn)題，覺(jué)得比較有意思，記錄下來(lái)供需要的朋友不時(shí)之需。
1、問(wèn)題描述
問(wèn)題背景是這樣，朋友在運(yùn)維一個(gè)開(kāi)發(fā)項(xiàng)目，同一個(gè)數(shù)據(jù)庫(kù)中多個(gè)Schema內(nèi)容相同，用于不同的
測(cè)試目的。一些開(kāi)發(fā)同步任務(wù)促使編寫(xiě)一個(gè)程序來(lái)實(shí)現(xiàn)Schema內(nèi)部或者之間對(duì)象操作。從軟件版本角度看，維護(hù)一份工具腳本是最好的方法，可以避免由于修改造成的版本錯(cuò)亂現(xiàn)象。如何實(shí)現(xiàn)一份存儲(chǔ)過(guò)程腳本，在不同Schema下執(zhí)行效果不同就成為問(wèn)題。
將問(wèn)題簡(jiǎn)化為如下描述：在Schema A里面包括一個(gè)存儲(chǔ)過(guò)程Proc，A中還有一個(gè)數(shù)據(jù)表T1。在Proc代碼中，包括了對(duì)T1的操作內(nèi)容。而Schema B中也存在一個(gè)數(shù)據(jù)表T1，并且B擁有一個(gè)名為Proc的私有同義詞synonym指向A.Proc。問(wèn)題是如何讓Proc根據(jù)執(zhí)行的Schema主體不同，訪問(wèn)不同Schema的數(shù)據(jù)表。
也就是說(shuō)，如果是A調(diào)用Proc程序包，操作的就是A Schema里面的數(shù)據(jù)表T1。如果B調(diào)用Proc程序包，就操作B Schema里面的數(shù)據(jù)表T1。
2、測(cè)試實(shí)驗(yàn)一
為了驗(yàn)證測(cè)試，我們模擬了實(shí)驗(yàn)環(huán)境，來(lái)觀察現(xiàn)象。選擇11gR2進(jìn)行測(cè)試。
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 創(chuàng)建對(duì)應(yīng)Schema和數(shù)據(jù)表。 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下面創(chuàng)建數(shù)據(jù)表和對(duì)應(yīng)操作存儲(chǔ)過(guò)程。
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