搞我，DBA角色用户调用存储过程失败？

日常工作中业务用户在调用存储过程中有时会出现`ORA-00942: 表或视图不存在`的问题，但是将存储过程中的sql拿出来单独执行时，sql语句能正常执行。单独的sql可以执行表明语句本身是正确，而存储过程中无法执行，应该是权限出了问题。通过查看执行用户的角色与权限，发现执行用户拥有dba角色，但是在对象权限中没有存储过程中相关表的权限。很多人会疑惑为什么出现这种情况呢？执行用户都拥有了DBA角色了，单独的sql也能执行，存储过程为什么不可以？

下面通过实验过程来解释这其中的原因👇

## 创建测试表

```sql

[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 孙七

```

## 创建存储过程

- 确认用户的角色和权限

```sql

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

```sql

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.

```

- 执行存储过程

```sql

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

```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❌

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

✏️下面我们在创建一个存储过程，观察一下有什么不同之处

```sql

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.create tablespace eason datafile '/oradata/orcl/eason.dbf' size 10m autoextend on;

Elapsed: 00:00:00.01

fym@ORCL> select * from hyj.students;

ID NAME

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

5 张三

6 李四

7 王五

8 赵六

9 孙七

```

为什么同样内容的存储过程，FYM用户现在就能执行成功了呢？

这里我们创建存储过程时，使用了`AUTHID CURRENT_USER`模式

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

>- **执行的schema不同，操作的对象也不同**

>在定义者(definer)权限下，执行的用户操作的schema为定义者，所操作的对象是定义者在编译时指定的对象。

在调用者(invoker)权限下，执行的用户操作的schema为当前用户，所操作的对象是当前模式下的对象。

>- **执行的权限不同**

>在定义者(definer)权限下，当前用户的权限为角色无效情况下所拥有的权限。

在调用者(invoker)权限下，当前用户的权限为当前所拥有的权限(含角色)。

>- **执行的效率不同**

>在定义者(definer)权限下，过程被静态编译静态执行(相对而言)，所执行sql语句在共享区池中是可被共享使用的

在调用者(invoker)权限下，过程静态编译，但动态执行，虽然执行的语句相同，但不同用户执行，其sql语句在共享池中并不能共享

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

```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 孙七

```

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