展会信息港展会大全

Oracle数据库存储过程与权限
来源:互联网   发布日期:2016-01-28 13:19:56   浏览:2046次  

导读:在执行存储过程时,我们可能会遇到权限问题● 定义者权限存储过程● 调用者权限存储过程在数据库中创建存储过程时,定义者权限是缺省模式当指定AUTHID CURRENT_USER关键字后,便是...

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

● 定义者权限存储过程

● 调用者权限存储过程

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

当指定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

2as

3begin

4delete from u1.t;

5commit;

6end;

7/

Warning: Procedure created with compilation errors.

u2@EMREP> show error;

Errors for PROCEDURE P_TEST:

LINE/COL ERROR

-------- -----------------------------------------------------------------

4/3PL/SQL: SQL Statement ignored

4/18PL/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

2as

3begin

4delete from u1.t;

5commit;

6end;

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

2authid current_user

3as

4begin

5delete from u1.t;

6commit;

7end;

8/

Warning: Procedure created with compilation errors.

u2@EMREP> show error;

Errors for PROCEDURE P_TEST:

LINE/COL ERROR

-------- -----------------------------------------------------------------

5/3PL/SQL: SQL Statement ignored

5/18PL/SQL: ORA-00942: table or view does not exist

u2@EMREP> create or replace procedure p_test

2authid current_user

3as

4begin

5execute immediate

6'delete from u1.t';

7commit;

8end;

9/

Procedure created.

u2@EMREP> exec p_test;

PL/SQL procedure successfully completed.

u2@EMREP> select count(*) from u1.t;

COUNT(*)

----------

0

赞助本站

人工智能实验室

相关热词: 开发 编程 android

AiLab云推荐
展开

热门栏目HotCates

Copyright © 2010-2024 AiLab Team. 人工智能实验室 版权所有    关于我们 | 联系我们 | 广告服务 | 公司动态 | 免责声明 | 隐私条款 | 工作机会 | 展会港