在執行存儲過程時，我們可能會遇到權限問題

● 定義者權限存儲過程

● 調用者權限存儲過程

在數據庫中創建存儲過程時，定義者權限是缺省模式

當指定AUTHID CURRENT_USER關鍵字后，便是調用者權限存儲過程

他倆之間最根本的差異在于role能否在存儲過程中生效

㈠ 定義者權限存儲過程問題

定義者權限存儲過程role無效，必須要有顯式授權

即便是擁有dba role，還是不能訪問不同用戶的表

> grant connect,resource to u1 identified by u1;

Grant succeeded.

> grant dba to u2 identified by u2;

Grant succeeded.

> conn u1/u1

Connected.

> create table t as select * from user_objects;

Table created.

> conn u2/u2

Connected.

> 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.

> 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

> conn u1/u1

Connected.

> grant all on t to u2;

Grant succeeded.

> conn u2/u2

Connected.

> create or replace procedure p_test

2 as

3 begin

4 delete from u1.t;

5 commit;

6 end;

7 /

Procedure created.

㈡ 調用者權限存儲過程問題

調用者權限存儲過程role編譯不可見，但運行時可見

用動態SQL避免直接授權，而將權限的檢查延后至運行時

> conn u1/u1

Connected.

> revoke all on t from u2;

Revoke succeeded.

> conn u2/u2

Connected.

> 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.

> 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

> 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.

> exec p_test;

PL/SQL procedure successfully completed.

> select count(*) from u1.t;

COUNT(*)

----------

0

更多信息請查看IT技術專欄