Oracle中定義者許可權和呼叫者許可權案例分析

迷倪小魏發表於2017-09-30
Oracle


定義者許可權：定義者許可權指使用它所有者的許可權，而不是當前使用者來執行過程。因此，你可以限制使用者執行的資料庫操作，允許他們僅透過執行定義者許可權的過程和函式訪問資料。建立過程、函式和程式包的預設許可權是定義者許可權。

 

呼叫者許可權：在當前的使用者模式下用當前的使用者許可權來執行過程。換句話說，就是呼叫者的許可權過程並不與某個特定的使用者或模式繫結。呼叫者許可權程式可以使應用程式開發人員很容易的將應用邏輯集中起來，即使底層的資料在使用者和模式中被劃分。建立時需要顯式使用AUTHID CURRENT_USER來定義呼叫者過程。

 

（注意：在閱讀下面案例的時候注意sqlplus的使用的不同使用者）

 

具體演示如下所示：

 

1、建立兩個測試使用者wjq1和wjq2，並分別授權

SYS@seiang11g>create user wjq1 identified by wjq1 default tablespace seiang;

 

User created.

 

SYS@seiang11g>create user wjq2 identified by wjq2 default tablespace seiang;

 

User created.

 

SYS@seiang11g>grant connect,resource to wjq1,wjq2;

 

Grant succeeded.

 

2、切換到使用者wjq1，建立兩個過程proc_definer和proc_invoker

 

過程1：定義者許可權，為建立過程的預設許可權

WJQ1@seiang11g>create or replace procedure proc_definer is

  2  begin

  3     dbms_output.put_line('Current User:'|| sys_context('userenv','current_user'));

  4     dbms_output.put_line('Session User:'|| sys_context('userenv','session_user'));

  5     dbms_output.put_line('Current Schema:'|| sys_context('userenv','current_schema'));

  6  end proc_definer;

  7  /

 

Procedure created.

 

過程2：使用使用者許可權

WJQ1@seiang11g>create or replace procedure proc_invoker authid current_user is

  2  begin

  3     dbms_output.put_line('Current User:'|| sys_context('userenv','current_user'));

  4     dbms_output.put_line('Session User:'|| sys_context('userenv','session_user'));

  5     dbms_output.put_line('Current Schema:'|| sys_context('userenv','current_schema'));

  6  end proc_invoker;

  7  /

 

Procedure created.

 

3、檢視兩個過程的許可權

WJQ1@seiang11g>select object_name,procedure_name,authid from user_procedures

  2  where object_name like '%PROC%';

 

OBJECT_NAME                    PROCEDURE_NAME                 AUTHID

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

PROC_INVOKER                                                  CURRENT_USER

PROC_DEFINER                                                  DEFINER

 

4、在使用者wjq1下分別執行定義者許可權和使用者許可權的過程

WJQ1@seiang11g>set serveroutput on

WJQ1@seiang11g>

WJQ1@seiang11g>exec proc_definer;

Current User:WJQ1

Session User:WJQ1

Current Schema:WJQ1

 

PL/SQL procedure successfully completed.

 

WJQ1@seiang11g>exec proc_invoker;

Current User:WJQ1

Session User:WJQ1

Current Schema:WJQ1

 

PL/SQL procedure successfully completed.

 

5、將使用者wjq1建立的兩個過程授權給使用者wjq2

WJQ1@seiang11g>grant execute on proc_definer to wjq2;

 

Grant succeeded.

 

WJQ1@seiang11g>grant execute on proc_invoker to wjq2;

 

Grant succeeded.

 

6、在使用者wjq2下分別呼叫兩個過程，結果顯示在呼叫者許可權下，程式在當前使用者下用當前使用者的許可權執行

WJQ2@seiang11g>set serveroutput on

WJQ2@seiang11g>exec wjq1.proc_definer;

Current User:WJQ1

Session User:WJQ2

Current Schema:WJQ1

 

PL/SQL procedure successfully completed.

 

WJQ2@seiang11g>exec wjq1.proc_invoker;

Current User:WJQ2

Session User:WJQ2

Current Schema:WJQ2

 

PL/SQL procedure successfully completed.

 

透過上面的簡單的演示，已經對定義者許可權和呼叫者許可權有了一定的認識和理解，但是僅僅初步的瞭解在生產環境中，遇到實際的案例還是有點摸不著頭腦，下面透過兩個實際的案例來分析定義者許可權和呼叫者許可權，更加深入的去理解它們的使用方法。

 

在儲存過程中，時常會遇到這樣一種場景：使用者A下有一個儲存過程（或者函式體、包體）Proc，在過程中間引用了物件obj。在編譯儲存過程時，是要求使用者A有物件obj的許可權的，如果沒有，則系統報編譯錯誤。當成功進行編譯之後，使用者A將執行execute儲存過程Proc的許可權賦給了使用者B。但是使用者B不一定擁有物件obj的使用許可權，那麼問題就來了，此時使用者B能否成功執行儲存過程Proc呢？

 

下面我們透過實驗來進行驗證：

接著上面例子，使用者wjq1和wjq2除了擁有connect和resource角色的許可權之外，出於實驗的目的，我們將select any dictionary的系統許可權賦予給使用者wjq1

 

SYS@seiang11g>grant select any dictionary to wjq1;

 

Grant succeeded.

 

select any dictionary的系統許可權意味著使用者可以訪問資料字典檢視層面的檢視中的物件資料。

 

WJQ1@seiang11g>select count(*) from dba_objects;

 

  COUNT(*)

----------

     86993

 

WJQ1@seiang11g>create or replace procedure proc_wjq1 is

  2  i number;

  3  begin

  4     select count(*) into i from dba_objects;

  5     dbms_output.put_line(to_char(i));

  6  end;

  7  /

 

Procedure created.

 

WJQ1@seiang11g>set serveroutput on

WJQ1@seiang11g>

WJQ1@seiang11g>exec proc_wjq1;

86994

 

PL/SQL procedure successfully completed. 

 

由上可見：授予select any dictionary的使用者wjq1可以對dba_objects檢視進行訪問操作。同時，儲存過程proc_wjq1也可以執行編譯操作。

 

 

案例一：定義者許可權

 

緊接著上面的實驗，對於wjq2使用者，只有執行wjq1使用者下proc_wjq1儲存過程許可權，但是沒有訪問dba_objects檢視許可權，看實際效果。

 

使用者wjq2只具有基本的connect和resource許可權。將proc_wjq1過程的執行許可權授權給wjq2

WJQ1@seiang11g>grant execute on proc_wjq1 to wjq2;

 

Grant succeeded. 

 

之後，檢查wjq2使用者下，過程proc_wjq1的執行情況

 

WJQ2@seiang11g>select count(*) from dba_objects;

select count(*) from dba_objects

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

wjq2使用者沒有dba_objects許可權，顯示訪問必然報錯

 

WJQ2@seiang11g>exec wjq1.proc_wjq1;

86994

 

PL/SQL procedure successfully completed.

 

結果顯而易見，wjq2雖然沒有訪問dba_objects許可權，但是因為擁有執行proc_wjq1的許可權，在執行proc_wjq1的時候，也是可以在方法中訪問到dba_objects。顯然，此時wjq2在執行proc_wjq1上應用了wjq1使用者對於dba_objects的許可權，也就是物件定義者的許可權。

 

為了進一步證明結果的準確性，下面將繼續進行實驗變化來演示

 

當定義者許可權失去時，即使呼叫者擁有許可權也是無用的（也就是說wjq1使用者沒有訪問dba_objects的許可權，wjq2使用者有訪問dba_objects的許可權）

 

回收了wjq1使用者上的select any dictionary許可權，此時wjq1對dba_objects物件許可權消失；然後，賦予wjq2使用者select any dictionary許可權，這樣wjq2就能訪問dba_objects了

 

SYS@seiang11g>revoke select any dictionary from wjq1;

 

Revoke succeeded.

 

WJQ1@seiang11g>select count(*) from dba_objects;

select count(*) from dba_objects

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SYS@seiang11g>

SYS@seiang11g>grant select any dictionary to wjq2;

 

Grant succeeded.

 

WJQ2@seiang11g>select count(*) from dba_objects;

 

  COUNT(*)

----------

     86994

 

WJQ2@seiang11g>exec wjq1.proc_wjq1;

BEGIN wjq1.proc_wjq1; END;

 

           *

ERROR at line 1:

ORA-06550: line 1, column 12:

PLS-00905: object WJQ1.PROC_WJQ1 is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

 

透過上面的實驗結果發現：wjq2使用者擁有dba_objects物件訪問許可權，同時也有執行proc_wjq1的許可權，但是執行的時候卻報錯，認為物件無效。這是為什麼呢？唯一的原因就是因為wjq1使用者失去了dba_objects物件的許可權，而wjq2在呼叫proc_wjq1時使用的是dba_objects的許可權。

 

 

以上案例一的實驗介紹了Oracle在儲存過程中使用的許可權配置的“定義者許可權”。簡單的說，當執行一個程式體（儲存過程、函式和包等）的時候，方法體內部使用的許可權體系為當前該程式體定義者的許可權體系，而與呼叫方法的使用者無關。儲存過程proc_wjq1無論是哪一個使用者執行，許可權體系都是該儲存過程的定義者wjq1的許可權。

 

定義者許可權是Oracle使用的預設許可權選擇方式，在使用的時候很方便。呼叫者只要擁有簡單的物件執行許可權就可以了，無需顧及自己是否擁有許可權訪問方法中使用的物件。

 

案例一對定義者許可權透過實驗進行了分析，呼叫者許可權的含義就相對容易理解了。呼叫者許可權體系就是執行方法體的時候，使用的許可權按照呼叫者許可權體系來判斷。一個方法的執行，呼叫者除了要擁有執行該方法的許可權，還要擁有該方法中使用物件的許可權才可以。

 

下面案例二透過實驗對呼叫者許可權作出詳細的分析：

 

案例二：呼叫者許可權

 

接著上面案例一的實驗環境。注意，此時wjq1使用者沒有select any dictionary許可權，而wjq2有。

 

WJQ1@seiang11g>create or replace procedure proc_wjq1_1 is

  2  i number;

  3  begin

  4     select count(*) into i from dba_objects;

  5     dbms_output.put_line(to_char(i));

  6  end;

  7  /

 

Warning: Procedure created with compilation errors.

 

WJQ1@seiang11g>

WJQ1@seiang11g>select name,line,text from user_errors;

 

NAME                  LINE TEXT

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

PROC_WJQ1_1              4 PL/SQL: ORA-00942: table or view does not exist

PROC_WJQ1_1              4 PL/SQL: SQL Statement ignored 

 

檢視報錯資訊，還是因為wjq1沒有dba_objects的許可權，所以建立過程失敗。

 

 

此時，如果在方法定義上加入authid current_user關鍵字，就可以將儲存過程變化為呼叫者許可權。

 

WJQ1@seiang11g>create or replace procedure proc_wjq1_1 authid current_user is

  2  i number;

  3  begin

  4     select count(*) into i from dba_objects;

  5     dbms_output.put_line(to_char(i));

  6  end;

  7  /

 

Warning: Procedure created with compilation errors.

 

WJQ1@seiang11g>

WJQ1@seiang11g>select name,line,text from user_errors;

 

NAME                  LINE TEXT

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

PROC_WJQ1_1              4 PL/SQL: ORA-00942: table or view does not exist

PROC_WJQ1_1              4 PL/SQL: SQL Statement ignored 

 

顯然，還在因為wqj1使用者沒有許可權訪問dba_objects而報錯，畢竟不管是什麼體系，wjq1目前是沒有物件許可權的建立過程是不會成功的。不過，為了實驗成功，還是要讓wjq1能順利建立proc_wjq1_1過程。

 

SYS@seiang11g>grant select any dictionary to wjq1;

 

Grant succeeded.

 

WJQ1@seiang11g>create or replace procedure proc_wjq1_1 authid current_user is

  2  i number;

  3  begin

  4     select count(*) into i from dba_objects;

  5     dbms_output.put_line(to_char(i));

  6  end;

  7  /

 

Procedure created. 

 

WJQ1@seiang11g>

WJQ1@seiang11g>grant execute on proc_wjq1_1 to wjq2;

 

Grant succeeded.

 

 

切換到wjq2使用者，注意此時它是擁有select any dictionary許可權的。

WJQ2@seiang11g>select count(*) from dba_objects;

 

  COUNT(*)

----------

     86995

 

WJQ2@seiang11g>exec wjq1.proc_wjq1_1;

86995

 

PL/SQL procedure successfully completed. 

 

此時，wjq2執行過程成功。因為此時wjq1和wjq2都擁有select any dictionary許可權，所以即使在呼叫者許可權下，也是會成功的。此時，如果收回wjq2上的許可權，結果會如何呢？

SYS@seiang11g>revoke select any dictionary from wjq2;

 

Revoke succeeded.

 

WJQ2@seiang11g>select count(*) from dba_objects;

select count(*) from dba_objects

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

WJQ2@seiang11g>exec wjq1.proc_wjq1;

BEGIN wjq1.proc_wjq1; END;

 

           *

ERROR at line 1:

ORA-06550: line 1, column 12:

PLS-00905: object WJQ1.PROC_WJQ1 is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

 

此時，就看出呼叫者許可權的差異了。wjq1始終有dba_objects的許可權，而wjq2在之後被回收了select any dictionary的許可權。如果是定義者許可權，wjq2呼叫proc_wjq1_1是沒有問題的。但是此時報錯，說明此處使用的是wjq2的呼叫者許可權。

 

       透過上述兩個案例，分別透過實驗對定義者許可權和使用者許可權進行了分析，想必大家已經有了比較深刻的認識和理解了，但是很多時候，我們都會使用儲存過程Procedure來實現一些指令碼功能。透過Procedure來實現一些資料庫相關的維護、開發工作，可以大大提高我們日常工作效率。下面資料庫運維過程中有這樣一種情況需要我們DBA去處理，實際的問題這樣的：


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


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

 

 

為了對上述問題有一個明確的處理方法，下面透過以下示例進行模擬實驗：

 

SYS@seiang11g>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 Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

 

SYS@seiang11g>create user wjq1 identified by wjq1 default tablespace seiang;

 

User created.

 

SYS@seiang11g>create user wjq2 identified by wjq2 default tablespace seiang;

 

User created.

 

SYS@seiang11g>grant connect,resource,create procedure,create synonym to wjq1,wjq2;

 

Grant succeeded.

 

SYS@seiang11g>select * from dba_sys_privs where grantee in ('WJQ1','WJQ2');

 

GRANTEE                        PRIVILEGE                                ADM

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

WJQ1                           CREATE SYNONYM                           NO

WJQ2                           UNLIMITED TABLESPACE                     NO

WJQ2                           CREATE SYNONYM                           NO

WJQ1                           UNLIMITED TABLESPACE                     NO

WJQ2                           CREATE PROCEDURE                         NO

WJQ1                           CREATE PROCEDURE                         NO

 

6 rows selected. 

 

在Schema wjq1下面建立資料表和相應的儲存過程。

 

 WJQ1@seiang11g>create table tab_wjq(name varchar2(20));

 

Table created.

 

WJQ1@seiang11g>create or replace procedure proc_wjq1(v_name varchar2) is

  2  begin

  3     insert into tab_wjq values(v_name);

  4     commit;

  5  end proc_wjq1;

  6  /

 

Procedure created.

 

Schema wjq1進行執行儲存過程

WJQ1@seiang11g>exec proc_wjq1('wjq');

 

PL/SQL procedure successfully completed.

 

WJQ1@seiang11g>select * from tab_wjq;

 

NAME

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

wjq 

 

 

將過程proc_wjq1的許可權賦予Schema wjq2

 WJQ1@seiang11g>grant execute on proc_wjq1 to wjq2;

 

Grant succeeded. 

 

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

 

 WJQ2@seiang11g>create table tab_wjq(name varchar2(20));

 

Table created.

 

WJQ2@seiang11g>create synonym proc_wjq1 for wjq1.proc_wjq1;

 

Synonym created.

 

WJQ2@seiang11g>select * from user_synonyms;

 

SYNONYM_NAME    TABLE_OWNER     TABLE_NAME           DB_LINK

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

PROC_WJQ1       WJQ1            PROC_WJQ1 

 

 

進行預設情況測試，在Schema wjq2中呼叫儲存過程proc_wjq1，檢視操作的是哪一個Schema下的資料表

 

WJQ2@seiang11g>exec proc_wjq1('seiang');

 

PL/SQL procedure successfully completed.

 

WJQ2@seiang11g>select * from tab_wjq;

 

no rows selected

 

Schema wjq2中資料表tab_wjq沒有資料，檢視Schema wjq1中資料表情況：

 

WJQ1@seiang11g>select * from tab_wjq;

 

NAME

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

wjq

seiang 

 

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

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

 

與定義者許可權對應的就是“呼叫者許可權”。也就說，對使用者是否可以執行該程式體中的物件，完全取決於執行呼叫使用者系統許可權和物件許可權（注意：非角色許可權）。大膽的猜想，如果使用呼叫者許可權，從執行使用者許可權角度看，是不是可以直接訪問自己Schema中的物件了？下面透過實驗進行驗證：

 

 WJQ1@seiang11g>create or replace procedure proc_wjq1(v_name varchar2) authid current_user is

  2  begin

  3     insert into tab_wjq values(v_name);

  4     commit;

  5  end proc_wjq1;

  6  /

 

Procedure created.

 

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

 

WJQ1@seiang11g>exec proc_wjq1('wjqbest');

 

PL/SQL procedure successfully completed.

 

WJQ1@seiang11g>select * from tab_wjq;

 

NAME

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

wjq

seiang

wjqbest

 

轉換到使用者wjq2中，進行實驗：

 

 WJQ2@seiang11g>exec proc_wjq1('seiangbest');

 

PL/SQL procedure successfully completed.

 

WJQ2@seiang11g>select * from tab_wjq;

 

NAME

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

seiangbest 

 

從上面的實驗結果可以發現：在呼叫者許可權模式下，可以實現呼叫Schema下資料表優先的效果。如果此時Schema wjq2中沒有資料表tab_wjq，結果又會如何呢？

 

 WJQ2@seiang11g>drop table tab_wjq;

 

Table dropped.

 

WJQ2@seiang11g>exec proc_wjq1('hahaha');

BEGIN proc_wjq1('hahaha'); END;

 

*

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "WJQ1.PROC_WJQ1", line 3

ORA-06512: at line 1

 

如果wjq2使用者擁有wjq1使用者下的資料表tab_wjq的許可權，結果又會如何呢？

 

 WJQ1@seiang11g>grant all on tab_wjq to wjq2;

 

Grant succeeded.

 

WJQ2@seiang11g>select * from wjq1.tab_wjq;

 

NAME

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

wjq

seiang

wjqbest

 

WJQ2@seiang11g>exec proc_wjq1('hahaha');

BEGIN proc_wjq1('hahaha'); END;

 

*

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "WJQ1.PROC_WJQ1", line 3

ORA-06512: at line 1

 

從上面的結果發現，即使wjq2擁有wjq1下資料表tab_wjq的許可權，在使用呼叫者許可權時，也是隻能操作自己Schema下的物件。所以定義者許可權和呼叫者許可權，是Oracle儲存過程中兩個重要的概念物件，一些麻煩場景下應用往往有不錯的效果。

 


作者：SEian.G（苦練七十二變，笑對八十一難）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31015730/viewspace-2145643/，如需轉載，請註明出處，否則將追究法律責任。

相關文章