ORA-00942: 表或视图不存在_eason_hyj_表或视图不存在

大大的周 02-07 3252

??今天一个开发同事私信我，说是在创建存储过程中提示😱ORA-00942: 表或视图不存在！！但是将存储过程中的sql拿出来单独执行时，sql语句能正常执行。其实到这里就能想到，单独的sql可以执行表明语句本身是正确，而存储过程中无法执行，应该是权限出了问题。通过查看该用户角色与权限，发现该用户拥有dba角色，但是在对象权限中没有访问该表的权限，在执行grant select on tablename to username后，创建存储过程正常。 ??很多人会疑惑为什么出现这种情况呢？用户都拥有了DBA角色了，单独的sql也能执行，存储过程为什么不可以？下面我将通过实验过程来解释这其中的原因👇

1. 创建测试表

[oracle@jwdb:/home/oracle]$ sqlplus / as sysdba create tablespace eason datafile '/oradata/orcl/eason.dbf' size 10m autoextend on; create user hyj identified by hyj default tablespace eason ; grant connect,resource to hyj; create user fym identified by fym default tablespace eason ; grant dba to fym; [oracle@jwdb:/home/oracle]$ sqlplus hyj/hyj create table students( id int, name varchar(20) ); hyj@ORCL> INSERT ALL INTO students values(3,'张三') INTO students values (4,'李四') INTO students values (5,'王五') INTO students values (6,'赵六') INTO students values (7,'孙七') select 1 from dual; 5 rows created. Elapsed: 00:00:00.09 hyj@ORCL> select * from students; ID NAME ----- ----------- 3 张三 4 李四 5 王五 6 赵六 7 孙七

2.创建存储过程

确认用户的角色和权限 system@ORCL> select grantee,granted_role,default_role,admin_option from dba_role_privs where grantee ='FYM'; GRANTEE GRANTED_ROLE DEF ADM ------------------------------ ------------------------------ --- --- FYM DBA YES NO #fym用户拥有的对象权限 sysem@ORCL> select privilege,table_name,grantee from dba_tab_privs where grantee='FYM'; no rows selected Elapsed: 00:00:00.07 创建PROCEDURE fym@ORCL> CREATE OR REPLACE PROCEDURE TEST_update01 as v_sql varchar2(2000) := ''; BEGIN v_sql := 'update hyj.students set id=id+1'; EXECUTE IMMEDIATE v_sql; END ; / 2 3 4 5 6 7 Procedure created. 执行存储过程 fym@ORCL> BEGIN TEST_UPDATE01; END; /fym@ORCL> 2 3 4 BEGIN * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "FYM.TEST_UPDATE01", line 5 ORA-06512: at line 2 单独执行存储过程中的sql fym@ORCL> update hyj.students set id=id+1; 5 rows updated. Elapsed: 00:00:00.00 fym@ORCL> select * from hyj.students; ID NAME ---------- ------------------------------------------ 4 张三 5 李四 6 王五 7 赵六 8 孙七

💥可以看到这里将procedure中的sql部分单独取出执行，可以再fym用户下正常执行，在procedure中缺报错?ORA-00942: table or view does not exist? ??下面我们在创建一个存储过程，观察一下有什么不同之处

3.创建存储过程(调用者权限)

fym@ORCL> CREATE OR REPLACE PROCEDURE TEST_update02 AUTHID CURRENT_USER as v_sql varchar2(2000) := ''; BEGIN v_sql := 'update hyj.students set id=id+1'; EXECUTE IMMEDIATE v_sql; END ; / 2 3 4 5 6 7 Procedure created. Elapsed: 00:00:00.12 fym@ORCL> BEGIN TEST_UPDATE02; END; / 2 3 4 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 fym@ORCL> select * from hyj.students; ID NAME ---------- -------------------------------------------------------------------------------- 5 张三 6 李四 7 王五 8 赵六 9 孙七

??为什么同样内容的存储过程，FYM用户现在就能执行成功了呢？ 🚩这里我们创建存储过程时，使用了AUTHID CURRENT_USER模式

Oracle 从8i开始引入了调用者权限体系结构，之前一直使用定义者权限体系结构（默认情况下是定义者权限）。下面描述了调用者权限与定义者权限之间的差异

1 执行的schema不同，操作的对象也不同在定义者(definer)权限下，执行的用户操作的schema为定义者，所操作的对象是定义者在编译时指定的对象。在调用者(invoker)权限下，执行的用户操作的schema为当前用户，所操作的对象是当前模式下的对象。

2、执行的权限不同在定义者(definer)权限下，当前用户的权限为角色无效情况下所拥有的权限。在调用者(invoker)权限下，当前用户的权限为当前所拥有的权限(含角色)。 3、执行的效率不同在定义者(definer)权限下，过程被静态编译静态执行(相对而言)，所执行sql语句在共享区池中是可被共享使用的在调用者(invoker)权限下，过程静态编译，但动态执行，虽然执行的语句相同，但不同用户执行，其sql语句在共享池中并不能共享

? 知道了原理，我们就知道如何解决问题啦

sys@ORCL> grant update on hyj.students to fym; Grant succeeded. Elapsed: 00:00:00.02 fym@ORCL> BEGIN TEST_UPDATE01; END; / 2 3 4 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 fym@ORCL 01:13:08> select * from hyj.students; ID NAME ---------- -------------------------------------------------------------------------------- 6 张三 7 李四 8 王五 9 赵六 10 孙七

🎯现在创建的存储过程（默认定义者模式）可以正常执行