使用调用者权限实现Schema导向操作

很多时候，我们都会使用存储过程Procedure来实现一些脚本工具。通过Procedure来实现一些数据库相关的维护、开发工作，可以大大提高我们日常工作效率。一个朋友最近咨询了关于Procedure调用的问题，觉得比较有意思，记录下来供需要的朋友不时之需。

 

1、问题描述

 

问题背景是这样，朋友在运维一个开发项目，同一个数据库中多个Schema内容相同，用于不同的测试目的。一些开发同步任务促使编写一个程序来实现Schema内部或者之间对象操作。从软件版本角度看，维护一份工具脚本是最好的方法，可以避免由于修改造成的版本错乱现象。如何实现一份存储过程脚本，在不同Schema下执行效果不同就成为问题。

将问题简化为如下描述：在Schema A里面包括一个存储过程Proc，A中还有一个数据表T1。在Proc代码中，包括了对T1的操作内容。而Schema B中也存在一个数据表T1，并且B拥有一个名为Proc的私有同义词synonym指向A.Proc。问题是如何让Proc根据执行的Schema主体不同，访问不同Schema的数据表。

也就是说，如果是A调用Proc程序包，操作的就是A Schema里面的数据表T1。如果B调用Proc程序包，就操作B Schema里面的数据表T1。

 

2、测试实验一

 

为了验证测试，我们模拟了实验环境，来观察现象。选择11gR2进行测试。

 

 

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE      11.2.0.4.0     Production

TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

 

创建对应Schema和数据表。

 

 

SQL> create user a identified by a;

User created

 

SQL> create user b identified by b;

User created

 

SQL> grant connect, resource to a,b;

Grant succeeded

 

SQL> grant create procedure to a,b;

Grant succeeded

 

SQL> grant create synonym to a,b;

Grant succeeded

 

 

在Schema A下面创建数据表和对应操作存储过程。

 

 

SQL> conn a/a@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as a

 

SQL> create table a(col varchar2(10));

Table created

 

SQL> create or replace procedure Proc(i_vc_name varchar2) is

  2  begin

  3    insert into a values (i_vc_name);

  4    commit;

  5  end Proc;

  6  /

 

Procedure created

 

 

从Schema A进行调用动作：

 

 

SQL> exec proc('iii');

PL/SQL procedure successfully completed

 

SQL> select * from a;

COL

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

Iii

 

SQL> grant execute on proc to b;

Grant succeeded

 

 

另外创建Schema B数据表对象，并且包括同义词对象。

 

 

SQL> conn b/b@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as b

 

SQL> create table a(col varchar2(10));

Table created

 

SQL> create synonym proc for a.proc;

Synonym created

 

 

进行默认情况测试，在Schema B中调用存储过程proc，看操作数据表是哪张：

 

 

 

SQL> conn b/b@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as b

 

SQL> exec proc('JJJ');

PL/SQL procedure successfully completed

 

SQL> select * from a;

COL

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

 

 

Schema B中数据表a没有数据，查看Schema A中数据表情况：

 

 

SQL> select * from a.a;

 

COL

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

JJJ

Iii

 

 

实验说明：在默认情况下，不同Schema对象调用相同存储过程，其中涉及到的对象都是相同的。也就是Oracle存储过程中的“所有者权限”。一旦用户拥有执行存储过程的权限，就意味着在执行体中，使用的是执行体所有者的权限体系。

那么这个问题似乎是没有办法。执行体指向的是Schema A的数据表a。

 

3、测试实验二

 

与所有者权限对应的另一种模式是“调用者权限”。也就说，对用户是否可以执行该程序体中的对象，完全取决于执行调用用户系统权限和对象权限（注意：非角色权限）。

笔者一种猜想，如果应用调用者权限，从执行用户权限角度看，是不是可以直接访问自己Schema中的对象了。下面通过实验进行证明。

 

 

SQL> conn a/a@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as a

 

SQL>

SQL> create or replace procedure Proc(i_vc_name varchar2) AUTHID CURRENT_USER is

  2  begin

  3    insert into a values (i_vc_name);

  4    commit;

  5  end Proc;

  6  /

 

Procedure created

 

 

在用户a中进行实验，结果：

 

 

SQL> exec proc('mmm');

PL/SQL procedure successfully completed

 

SQL> select * from a;

 

COL

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

JJJ

mmm

iii

 

 

转换到用户b中，进行测试：

 

 

SQL> conn b/b@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as b

 

SQL> exec proc('NNN');

PL/SQL procedure successfully completed

 

SQL> select * from a;

COL

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

NNN

 

 

SQL> select * from a.a

  2  ;

COL

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

JJJ

mmm

iii

 

 

测试成功！在调用者权限模式下，可以实现调用Schema下数据表优先的效果。如果此时Schema B中没有数据表a，效果如何？

 

 

SQL> drop table a;

Table dropped

 

SQL> exec proc('mt');

begin proc('mt'); end;

 

ORA-00942: 表或视图不存在

ORA-06512: 在 "A.PROC", line 3

ORA-06512: 在 line 1

 

 

如果b用户可以有a用户下的数据表a权限呢？问题依然。

 

 

SQL> conn a/a@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as a

 

SQL> grant all on a to b;

 

Grant succeeded

 

SQL> conn b/b@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as b

 

SQL> exec proc('mt');

 

begin proc('mt'); end;

 

ORA-00942: 表或视图不存在

ORA-06512: 在 "A.PROC", line 3

ORA-06512: 在 line 1

 

 

4、结论

 

所有者权限和调用者权限，是Oracle存储过程中两个重要的概念对象，一些麻烦场景下应用往往有不错的效果。