oracle编译存储过程提示表或视图不存在的问题分析

       今天有同事跟我反映有个存储过程编译报错，提示表或视图不存在。存储过程中调用的那个表t是另一个用户b的表。但是当前用户a已经拥有dba角色了，为什么访问不到用户b的表t呢？另一同事说，只要给用户a分配操作用户b的表t的权限就可以了。他的解释是用户即便拥有dba的角色也不一定能操作别的用户的对象。很明显这个解释是错误的。dba角色是数据库管理员的角色，肯定能操作数据库中所有用户的对象。我开始怀疑问题是不是出在存储过程上了。

       经过查看资料，发现存储过程可以定义为定义者权限（DEFINER）或者调用者权限（CURRENT_USER）。默认情况下是定义者权限。定义者权限将会禁止调用者的所有role权限。这也就解释了上面案例中为什么用户a拥有了dba的角色，却不能在存储过程中调用用户b的表t。调用者权限不会禁止调用者所获得的所有role权限。但是，不管是定义者权限还是调用者权限，存储过程在编译的时候，都不会判断role角色的权限，所以如果没有给用户a分配操作用户b的表t的权限的话，使用静态sql是无法编译通过的。只能使用动态sql（编译的时候不判断，执行的时候判断）。

      下面我将写两个测试的存储过程分别定义为定义者权限和调用者权限：

      1.定义者权限

       CREATE OR REPLACE PROCEDURE test1 AUTHID DEFINER AS
       v_sql varchar2(2000) := '';
       BEGIN
             v_sql := 'update b.t set pydm =''LM1'' where yhid =''162''';
             EXECUTE IMMEDIATE v_sql;
       END test1;

      2.调用者权限
        CREATE OR REPLACE PROCEDURE test2 AUTHID CURRENT_USER AS 
         v_sql varchar2(2000) := '';
        BEGIN
             v_sql := 'update b.t set pydm =''LM1'' where yhid =''162''';
             EXECUTE IMMEDIATE v_sql;
         END test2;

       经过测试发现，在不给用户a分配操作用户b的表t的权限的情况下，如果用户a拥有dba角色，exec test1 会提示表或视图不存在的错误，而exec test2 会执行成功。