Oracle資料庫預存程序與許可權

在執行預存程序時，我們可能會遇到許可權問題

● 定義者許可權預存程序
● 調用者許可權預存程序

在資料庫中建立預存程序時，定義者許可權是預設模式
當指定AUTHID CURRENT_USER關鍵字後，便是調用者許可權預存程序
他倆之間最根本的差異在於role能否在預存程序中生效

㈠ 定義者許可權預存程序問題
定義者許可權預存程序role無效，必須要有顯式授權
即便是擁有dba role，還是不能訪問不同使用者的表

sys@EMREP> grant connect,resource to u1 identified by u1;Grant succeeded.sys@EMREP> grant dba to u2 identified by u2;Grant succeeded.sys@EMREP> conn u1/u1Connected.u1@EMREP> create table t as select * from user_objects;Table created.sys@EMREP> conn u2/u2Connected.u2@EMREP> create or replace procedure p_test  2  as  3  begin  4    delete from u1.t;  5    commit;  6  end;  7  /Warning: Procedure created with compilation errors.u2@EMREP> show error;Errors for PROCEDURE P_TEST:LINE/COL ERROR-------- -----------------------------------------------------------------4/3      PL/SQL: SQL Statement ignored4/18     PL/SQL: ORA-00942: table or view does not existu2@EMREP> conn u1/u1Connected.u1@EMREP> grant all on t to u2;Grant succeeded.u1@EMREP> conn u2/u2Connected.u2@EMREP> create or replace procedure p_test  2  as  3  begin  4    delete from u1.t;  5    commit;  6  end;  7  /Procedure created.

㈡ 調用者許可權預存程序問題
調用者許可權預存程序role編譯不可見，但運行時可見
用動態SQL避免直接授權，而將許可權的檢查延後至運行時

u2@EMREP> conn u1/u1           Connected.u1@EMREP> revoke all on t from u2;Revoke succeeded.u1@EMREP> conn u2/u2Connected.u2@EMREP> create or replace procedure p_test  2  authid current_user  3  as  4  begin  5    delete from u1.t;  6    commit;  7  end;  8  /Warning: Procedure created with compilation errors.u2@EMREP> show error;Errors for PROCEDURE P_TEST:LINE/COL ERROR-------- -----------------------------------------------------------------5/3      PL/SQL: SQL Statement ignored5/18     PL/SQL: ORA-00942: table or view does not existu2@EMREP> create or replace procedure p_test  2  authid current_user  3  as  4  begin  5    execute immediate  6   'delete from u1.t';  7    commit;  8  end;  9  /Procedure created.u2@EMREP> exec p_test;PL/SQL procedure successfully completed.u2@EMREP> select count(*) from u1.t;  COUNT(*)----------         0