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/u1
Connected.
u1@EMREP> create table t as select * from user_objects;

Table created.
sys@EMREP> conn u2/u2
Connected.
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 ignored
4/18     PL/SQL: ORA-00942: table or view does not exist

u2@EMREP> conn u1/u1
Connected.
u1@EMREP> grant all on t to u2;

Grant succeeded.

u1@EMREP> conn u2/u2
Connected.
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/u2
Connected.
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 ignored
5/18     PL/SQL: ORA-00942: table or view does not exist
u2@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