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

● 定义者权限存储过程

● 调用者权限存储过程

在数据库中创建存储过程时，定义者权限是缺省模式

当指定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技术专栏