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