使用呼叫者許可權實現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儲存過程中兩個重要的概念物件,一些麻煩場景下應用往往有不錯的效果。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1351099/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PL/SQL呼叫時使用呼叫者的許可權SQL
- 對定義者許可權和呼叫者許可權的理解
- Oracle中定義者許可權和呼叫者許可權案例分析Oracle
- 呼叫者許可權與定義者許可權的pl/sql子程式SQL
- Oracle 定義者許可權與呼叫者許可權(AUTHID CURRENT_USER)Oracle
- PHP 物件導向 (一)許可權修飾符PHP物件
- PostgreSQL_通過schema控制使用者許可權SQL
- 如何用 Vue 實現前端許可權控制(路由許可權 + 檢視許可權 + 請求許可權)Vue前端路由
- 使用動態路由實現許可權管理路由
- Laravel實現許可權控制Laravel
- 你應該知道的數倉安全——預設許可權實現共享schema
- 儲存過程,角色相關的呼叫者許可權和定義者許可權問題儲存過程
- 呼叫者許可權過程對OWNER物件的依賴物件
- 呼叫者儲存過程訪問許可權問題儲存過程訪問許可權
- MYSQL學習筆記13: DCL許可權控制(使用者許可權操作)MySql筆記
- 使用Java API操作zookeeper的acl許可權JavaAPI
- mysql 8.0.21使用者及許可權操作MySql
- Hyperf 使用 hyperf-permission 元件實現許可權管理元件
- ASP.Net實現使用者許可權管理ASP.NET
- SpringMVC使用攔截器實現許可權控制SpringMVC
- 提問:使用spring aop實現許可權管理Spring
- 檔案目錄許可權操作
- Vue許可權路由實現總結Vue路由
- 基於RBAC實現許可權管理
- 從0實現RBAC許可權模型模型
- spring aop實現許可權管理Spring
- Django許可權機制的實現Django
- Oracle 使用者、物件許可權、系統許可權Oracle物件
- 使用者有connect,resource,dba角色許可權後回收dba許可權導致無UNLIMITED TABLESPACE許可權造成業務中斷MIT
- C#不提升自己程式的許可權實現操作登錄檔C#
- 檢視使用者的目錄操作許可權
- 【許可權管理】Oracle中檢視、回收使用者許可權Oracle
- vue許可權路由實現方式總結Vue路由
- Oracle的物件許可權、角色許可權、系統許可權Oracle物件
- 使用Servlet Filter實現系統登入許可權校驗ServletFilter
- Pb中多使用者許可權管理實現方案 (轉)
- 許可權之選單許可權
- 使用者許可權繼承另一使用者的許可權繼承