在 Oracle 存储过程中访问另一个用户的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4399528/
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
Accessing another user's table within an Oracle Stored Procedure
提问by Thorsten
I'm writing a stored procedure to copy data from one user's table to another schema. Basically, it is a series of INSERT .. SELECT statements such as this:
我正在编写一个存储过程来将数据从一个用户的表复制到另一个模式。基本上，它是一系列 INSERT .. SELECT 语句，例如：
INSERT INTO GESCHAEFTE
SELECT *
FROM TURAT03.GESCHAEFTE
WHERE kong_nr = 1234;
This works fine when issueing from sqlplus (or TOAD for me ;-)) so I know that I have sufficient privileges, but when this is part of stored procedure like this:
当从 sqlplus（或 TOAD 对我来说；-)）发出时，这很好用，所以我知道我有足够的权限，但是当这是这样的存储过程的一部分时：
CREATE OR REPLACE FUNCTION COPY_KONG
(pKongNr IN NUMBER)
RETURN NUMBER
AUTHID CURRENT_USER
IS
BEGIN
INSERT INTO GESCHAEFTE
SELECT *
FROM TURAT03.GESCHAEFTE
WHERE kong_nr = pKongNr;
END;
I get an Oracle error:
我收到一个 Oracle 错误：
[Error] ORA-00942 (11: 22): PL/SQL: ORA-00942: table or view does not exist
As you can see, I've already inserted an AUTHID, but to no avail.
如您所见，我已经插入了AUTHID，但无济于事。
What else can I do? I'm pretty much at the end of my ideas here.
我还可以做些什么？我的想法差不多结束了。
回答by Vincent Malgrat
The owner of a procedure must be granted privilege to access the underlying objects directly, not through a role. To have the same level of access as your procedures, use the following commands:
过程的所有者必须被授予直接访问底层对象的权限，而不是通过角色。要与您的过程具有相同级别的访问权限，请使用以下命令：
SET ROLE NONE;
To access another table from a procedure, you need to be granted SELECT directly, not through a role:
要从过程访问另一个表，您需要直接被授予 SELECT 权限，而不是通过角色：
GRANT SELECT ON TURAT03.GESCHAEFTE TO <your_user>;
This article by Tom Kytecontains additional info.
Tom Kyte 撰写的这篇文章包含其他信息。
