在執(zhí)行存儲(chǔ)過(guò)程時(shí)，我們可能會(huì)遇到權(quán)限問(wèn)題

● 定義者權(quán)限存儲(chǔ)過(guò)程

● 調(diào)用者權(quán)限存儲(chǔ)過(guò)程

在數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程時(shí)，定義者權(quán)限是缺省模式

當(dāng)指定AUTHID CURRENT_USER關(guān)鍵字后，便是調(diào)用者權(quán)限存儲(chǔ)過(guò)程

他倆之間最根本的差異在于role能否在存儲(chǔ)過(guò)程中生效

㈠ 定義者權(quán)限存儲(chǔ)過(guò)程問(wèn)題

定義者權(quán)限存儲(chǔ)過(guò)程role無(wú)效，必須要有顯式授權(quán)

即便是擁有dba role，還是不能訪問(wèn)不同用戶(hù)的表

> 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)限存儲(chǔ)過(guò)程問(wèn)題

調(diào)用者權(quán)限存儲(chǔ)過(guò)程role編譯不可見(jiàn)，但運(yùn)行時(shí)可見(jiàn)

用動(dòng)態(tài)SQL避免直接授權(quán)，而將權(quán)限的檢查延后至運(yùn)行時(shí)

> 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

更多信息請(qǐng)查看IT技術(shù)專(zhuān)欄