在执行存储过程时，我们可能会遇到权限问题
● 定义者权限存储过程
● 调用者权限存储过程
在数据库中创建存储过程时，定义者权限是缺省模式
当指定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技术专栏