存储过程中出现的Oracle错误："表或视图不存在"

Question

情景如下：

我有一个命名空间XXX，在其中创建了一些表和存储过程...

他们有一个命名空间YYY，在其中创建了一些表...

他们授予XXX访问他们的表的权限，因此当我使用XXX连接打开SQL Developer时，我可以执行以下操作：

SELECT * FROM YYY.TableA

但是如果我试图从存储过程内运行完全相同的语句（无论是简单存储过程还是包），存储过程将无法编译。这种情况经常发生在许多存储过程中。我是否需要请求其他权限？像这样运行存储过程：

CREATE OR REPLACE PROCEDURE PRC_SOMESP(
) AS BEGIN
END PRC_SOMESP;

不访问 YYY 表的存储过程可以成功编译。

提前致谢。

在 Justin Cave 的回复后，我尝试向存储过程中添加“AUTHID CURRENT_USER”语句，但仍然收到“表或视图不存在”的结果。

CREATE OR REPLACE PROCEDURE PRC_PROC1( PARAMETERS... )  
AUTHID CURRENT_USER  
AS  
    MYVAR NUMBER;  
BEGIN  
    STATEMENTS...
END PRC_PROC1;  

CREATE OR REPLACE PACKAGE PKG_PROC2  
AUTHID CURRENT_USER  
AS  
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE PRC_PROC2( PARAMETERS... )  
END PKG_PROC2  

我需要检查其他什么吗？

- user509925

---

首先，你不是像你说的那样运行存储过程，而是创建它们。其次，请确保 PRC_SOMESP() 有权在 YYY.TableA 上进行选择操作。 - Will Marcouiller

Answer 1

很可能问题在于授权是通过一个角色进行的。用户被授予权限后，在定义者权限下的存储过程中不可用（这是默认设置）。

在SQL Developer中，验证这个问题相对容易。如果运行以下命令：

SET ROLE none

如果您运行SELECT语句时出现相同的ORA-00942错误，那么解决方案通常是要求YYY架构中表的所有者直接向您授予对表的访问权限，而不是通过角色授予访问权限。如果不能实现这一点，您可以在声明中添加AUTHID CURRENT_USER，将存储过程定义为调用者权限的存储过程。这意味着过程的调用者需要访问底层对象，但它允许您的过程利用通过角色授予的特权。

如果您想创建一个调用者权限的存储过程，您还需要使用动态SQL引用表名，以使特权检查推迟到运行时。因此，您会有类似如下的代码：

CREATE OR REPLACE PROCEDURE PRC_SOMESP 
  AUTHID CURRENT_USER
AS 
  l_cnt pls_integer;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM yyy.TableA' INTO l_cnt;
END PRC_SOMESP;

如果您想要一个存储过程，该存储过程具有调用者的权限，并查询XXX模式下的TableA表。

- Justin Cave

---

是的，这正是Justin所说的问题... 你能否添加“AUTHID CURRENT_USER”语句并编写存储过程声明的示例？我阅读了另一个链接，但找不到示例：http://articles.techrepublic.com.com/5100-10878_11-6183799.html - user509925

@user509925 - 我在我的回答中添加了示例。 - Justin Cave

@JustinCave 我遇到了类似的问题，当我使用您的解决方案时，我最终会收到一个关于相对路径在绝对URI中的错误。这种技术常见出现这种问题吗？ - user3554664

@user3554664 - 我不确定我看到了什么相关性。听起来你在谈论的是一个Web应用程序中的错误，而不是数据库错误。你是否遇到了Oracle错误？如果是这样，错误编号和完整的错误信息是什么？ - Justin Cave

@Amir - 我猜你使用的是至少12c版本，Oracle在该版本中添加了INHERIT PRIVILEGES权限，并且过程的所有者需要被授予从调用者继承权限的能力。这是为了修补本文中所示的安全漏洞。https://oracle-base.com/articles/12c/control-invoker-rights-privileges-for-plsql-code-12cr1 - Justin Cave

Answer 2

在我的情况下

他们有一个名为YYY的命名空间，其中创建了一些表...... 他们授予XXX访问他们的表的权限，

但是对于YYY.tables，他们不授予XXX插入表的权限

所以我可以查询该表， 但是编译包中存储过程会出现错误。 ORA-00942表或视图不存在。

天哪，我怎么能碰到这个问题，已经11年5个月了， 但无论如何，希望我的答案对于有同样问题的人可能有用。

tldr; 请检查目标表对XXX用户的权限，并尝试添加插入权限。

- user3682728

Answer 3

我之前也遇到了同样的问题。虽然我不是DBA，但是有人向我解释过这个问题：“基本上，你的个人角色权限在存储过程中没有生效。”

有人建议我在存储过程的名称前加上表的所有者，像这样：

CREATE OR REPLACE PROCEDURE yyy.PRC_PROC1( PARAMETERS... ) etc

在我的开发环境中，这个方法对我起作用了。我的环境只有一个命名空间，所以我不确定这是否能解决提问者的问题，但希望能帮助下一批查看此问题的18K人推进这个问题的解决。

另外，当我将我的SP投入生产时，我需要删除限定符，并且我们的安装软件将在适当的权限下运行创建操作。

- jbgisser

Answer 4

如果您在模式XXX中的存储过程中访问来自YYY模式的表，请确保完全限定它们：

select count(1) from YYY.TableA;

另一个需要考虑的事情是大小写（如果您在Oracle标识符中混合使用大写和小写）。

最后一件事：发布您遇到的错误。这样更容易帮助您解决问题。

- Pablo Santa Cruz

---

如果你的数据库例如每个客户都有一个模式，这个解决方案可以工作但不太优雅。它需要为每个客户创建相同存储过程的版本才能为他们工作。 - Nicolas de Fontenay