Oracle数据库存储过程与权限

　2012-12-04 12:38:31　来源：WEB开发网　闂傚倷绶氬ḿ褍螞閹绢喖绠柨鐕傛嫹

闂傚倷绀侀幉锟犲垂閻㈠灚宕查柟鎵閸庡秵銇勯幒鎴濃偓鐢稿磻閹炬枼妲堟繛鍡楃С濞岊亞绱撻崒姘扁枌闁瑰嚖鎷�

婵犵數濮幏鍐川椤撴繄鎹曢梻渚€娼уú銈吤洪妸鈺佺劦妞ゆ帊鑳堕埊鏇㈡煏閸モ晛浠х紒杈╁仱閺佹捇鏁撻敓锟�闂傚倷绶氬ḿ褍螞閹绢喖绠柨鐕傛嫹　　闂傚倷鑳舵灙缂佺粯顨呴埢宥夊即閵忕姵鐎梺缁樺姇閻忔氨鈧凹鍓熷娲垂椤曞懎鍓伴梺閫炲苯澧紒澶婄秺瀵濡歌閸嬫捇妫冨☉娆忔殘闂佷紮缍€娴滎剟鍩€椤掑倹鏆柛瀣躬瀹曚即寮借閺嗭箓鏌ㄩ悤鍌涘

核心提示： 在执行存储过程时，我们可能会遇到权限问题● 定义者权限存储过程● 调用者权限存储过程在数据库中创建存储过程时，Oracle数据库存储过程与权限，定义者权限是缺省模式当指定AUTHID CURRENT_USER关键字后，便是调用者权限存储过程他俩之间最根本的差异在于role能否在存储过程中生效㈠定义者权限存储过程问题

在执行存储过程时，我们可能会遇到权限问题

● 定义者权限存储过程
● 调用者权限存储过程

在数据库中创建存储过程时，定义者权限是缺省模式
当指定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