oracle“表或视图不存在"从存储过程内部 [英] oracle "table or view does not exist" from inside stored procedure

问题描述

剧情是这样的……

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

I HAVE a namespace XXX where I created some tables and some stored procedures...

他们有一个命名空间 YYY 在那里他们创建了一些表...

THEY HAVE a namespace YYY where they created some tables...

他们授予 XXX 访问他们的表的权限，所以当我使用 XXX 连接连接到 SQL Developer 时，我可以这样做:

THEY GRANTED XXX access to their tables, so when I conect to SQL Developer using XXX connection, I can do:

SELECT * FROM YYY.TableA

但是如果我尝试从存储过程(简单存储过程或包)内部运行相同的语句，则存储过程不会编译.它发生在很多 sp 身上.是否有任何其他许可我必须要求???我正在像这样运行 sp:

But if I try to run that very same statement from inside a stored procedure (either simple stored procedure or a package), the stored procedure does not compile. It happens to a lot of sp's. Is there any other permission that I have to ask for??? I'm running sp's like this:

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

不访问 YYY 表的过程编译良好.

Procedures that does not access YYY tables do compile well.

提前致谢.

在 Justin Cave 响应之后，我试图将AUTHID CURRENT_USER"句子添加到 sp 的但得到相同的表或视图不存在"结果:

After Justin Cave response, I'm trying to add "AUTHID CURRENT_USER" sentence to the sp's but getting the same "table or view does not exist" result:

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  

我还需要检查什么吗???

Should I check anything else???

推荐答案

最有可能的问题是授权是通过角色完成的.授予用户的权限在定义者的权限存储过程中不可用(默认).

Most likely, the problem is that the grant was done via a role. Privileges granted to a user are not available in a definer's rights stored procedure (the default).

在 SQL Developer 中，比较容易验证这是问题所在.如果你运行命令

In SQL Developer, it is relatively easy to verify that this is the problem. If you run the command

SET ROLE none

然后运行 ​​SELECT 语句，我预计您会得到相同的 ORA-00942 错误.

and then run the SELECT statement, I would expect that you would get the same ORA-00942 error.

假设是这种情况，解决方案通常是要求 YYY 模式中表的所有者直接向您授予对表的访问权限，而不是通过角色授予访问权限.除此之外，您可以通过将 AUTHID CURRENT_USER 添加到声明中，将您的存储过程定义为调用者权限的存储过程.这意味着过程的调用者需要有权访问底层对象，但它会允许您的过程使用通过角色授予的权限.

Assuming that is the case, the solution would generally be to ask that the owners of the tables in the YYY schema to grant access to the tables directly to you rather than granting access via a role. Barring that, you could define your stored procedure as an invoker's rights stored procedure by adding AUTHID CURRENT_USER to the declaration. That would that the caller of the procedure would need to have access to the underlying objects but it would allow your procedures to make use of privileges granted through a role.

如果要创建调用者权限存储过程，还需要使用动态 SQL 引用表名，以便将权限检查推迟到运行时.所以你会有类似的东西

If you want to create an invoker's rights stored procedure, you will also need to refer to the table name using dynamic SQL in order to defer the privilege check to runtime. So you would have something like

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 表.

if you wanted an invoker's rights stored procedure that queried the TableA table in schema XXX.

这篇关于oracle“表或视图不存在"从存储过程内部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！