在執(zhí)行存儲過程時，我們可能會遇到權(quán)限問題
● 定義者權(quán)限存儲過程
● 調(diào)用者權(quán)限存儲過程
在數(shù)據(jù)庫中創(chuàng)建存儲過程時，定義者權(quán)限是缺省模式
當(dāng)指定AUTHID CURRENT_USER關(guān)鍵字后，便是調(diào)用者權(quán)限存儲過程
他倆之間最根本的差異在于role能否在存儲過程中生效
㈠ 定義者權(quán)限存儲過程問題
定義者權(quán)限存儲過程role無效，必須要有顯式授權(quán)
即便是擁有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.
㈡ 調(diào)用者權(quán)限存儲過程問題
調(diào)用者權(quán)限存儲過程role編譯不可見，但運行時可見
用動態(tài)SQL避免直接授權(quán)，而將權(quán)限的檢查延后至運行時
> 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