oracle 调用mysql存储过程_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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！