PL/SQL过程与权限

最近有个小伙伴问我：一个用户拥有DBA权限，为什么在执行存储过程的时候还会提示找不到表或试图？

我来简单模拟下他所说的情况

首先SCOTT用户拥有DBA权限

SCOTT用户可以正常查看HR用户下的DEPARTMENTS表数据

但是在SCOTT下创建过程时，却提示表或试图不存在，这是为什么呢？

看下官网的解释：

Using Roles with Invoker's Rights Subprograms

The use of roles in a subprogram depends on whether it executes with definer's rights or invoker's rights. Within a definer's rights subprogram, all roles are disabled. Roles are not used for privilege checking, and you cannot set roles.

Within an invoker's rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer's rights subprogram). Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects because roles apply at run time, not at compile time.

简言之，当过程的使用定义者权限的时候，所有的角色都不生效；当使用调用者权限的时候，角色才能够生效。

所以应当显式的授予对象权限，如 

grant select on hr.departments to scott 或 grant select any table to scott