oracle“表或视图不存在”来自存储过程内部
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4198052/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle "table or view does not exist" from inside stored procedure
提问by user509925
the scenarion is like this...
剧情是这样的……
I HAVE a namespace XXXwhere I created some tables and some stored procedures...
我有一个命名空间XXX，我在其中创建了一些表和一些存储过程...
THEY HAVE a namespace YYYwhere they created some tables...
他们有一个命名空间YYY，在那里他们创建了一些表......
THEY GRANTED XXXaccess to their tables, so when I conect to SQL Developer using XXXconnection, I can do:
他们XXX授予了对他们表的访问权限，因此当我使用XXX连接连接到 SQL Developer 时，我可以执行以下操作：
SELECT * FROM YYY.TableA
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:
但是，如果我尝试从存储过程（简单存储过程或包）内部运行相同的语句，则存储过程不会编译。它发生在很多 sp 身上。是否有任何其他许可我必须要求？？？我正在像这样运行 sp：
CREATE OR REPLACE PROCEDURE PRC_SOMESP(
) AS BEGIN
END PRC_SOMESP;
Procedures that does not access YYY tables do compile well.
不访问 YYY 表的过程编译良好。
Thanks in advance.
提前致谢。
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:
在 Justin Cave 响应之后，我尝试将“AUTHID CURRENT_USER”语句添加到 sp 的语句中，但得到相同的“表或视图不存在”结果：
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???
我还需要检查什么吗？？？
回答by Justin Cave
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).
最有可能的问题是授权是通过角色完成的。授予用户的权限在定义者的权限存储过程中不可用（默认）。
In SQL Developer, it is relatively easy to verify that this is the problem. If you run the command
在 SQL Developer 中，比较容易验证这是问题所在。如果你运行命令
SET ROLE none
and then run the SELECT statement, I would expect that you would get the same ORA-00942 error.
然后运行 SELECT 语句，我希望您会得到相同的 ORA-00942 错误。
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.
假设是这种情况，解决方案通常是要求 YYY 模式中表的所有者直接向您授予对表的访问权限，而不是通过角色授予访问权限。除此之外，您可以通过将 AUTHID CURRENT_USER 添加到声明中，将您的存储过程定义为调用者权限的存储过程。这意味着过程的调用者需要访问底层对象，但它允许您的过程使用通过角色授予的特权。
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
如果要创建调用者权限存储过程，还需要使用动态 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;
if you wanted an invoker's rights stored procedure that queried the TableA table in schema XXX.
如果您想要一个调用者权限存储过程来查询模式 XXX 中的 TableA 表。
回答by Pablo Santa Cruz
In your stored procedures on schema XXXif you access tables from YYYschema, make sure you fully qualify them:
在架构上的存储过程中，XXX如果您从YYY架构访问表，请确保您完全限定它们：
select count(1) from YYY.TableA;
Other thing to consider is casing (in case you are mixing upper and lowercase in your Oracle identifiers).
其他要考虑的事情是大小写（以防您在 Oracle 标识符中混合使用大写和小写字母）。
One last thing: post the error you are getting. It will be easier to help you that way.
最后一件事：发布您遇到的错误。以这种方式帮助您会更容易。
回答by jbgisser
I just had this same problem. I'm not a DBA but the way it was explained to me is "The basic thing is that your personal role privileges aren't in effect within the stored procedure."
我刚刚遇到了同样的问题。我不是 DBA，但向我解释的方式是“基本的事情是您的个人角色权限在存储过程中无效。”
I was advised to qualify the name of the SP with the owner of tables, like this:
有人建议我用表的所有者限定 SP 的名称，如下所示：
CREATE OR REPLACE PROCEDURE yyy.PRC_PROC1( PARAMETERS... ) etc
This worked in my case in my dev environment. My environment only has one namespace so I'm not sure this will solve the OP's question but hopefully will help to move this issue forward for the next 18K people that look up this question ;-).
这在我的开发环境中有效。我的环境只有一个命名空间，所以我不确定这是否能解决 OP 的问题，但希望能帮助下一个 18K 人解决这个问题；-)。
Also, when I put my SP into production, I will need to remove the qualifier and our installation software will run create under the proper authority.
此外，当我将我的 SP 投入生产时，我将需要删除限定符，我们的安装软件将在适当的权限下运行 create。
