今天一个开发同事私信我，说是在创建存储过程中提示😱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 孙七
🎯现在创建的存储过程（默认定义者模式）可以正常执行
相关文章
