在Oracle存储过程中访问另一个用户的表 [英] Accessing another user's table within an Oracle Stored Procedure
问题描述
我正在编写一个存储过程,以将数据从一个用户的表复制到另一种模式.基本上,它是一系列INSERT .. SELECT语句,例如:
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 INTO GESCHAEFTE
SELECT *
FROM TURAT03.GESCHAEFTE
WHERE kong_nr = 1234;
从sqlplus(或对我来说是TOAD ;-)发出问题时,此方法工作正常,因此我知道我具有足够的特权,但是当它是这样的存储过程的一部分时:
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:
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;
我收到一个Oracle错误:
I get an Oracle error:
[Error] ORA-00942 (11: 22): PL/SQL: ORA-00942: table or view does not exist
如您所见,我已经插入了AUTHID
,但无济于事.
As you can see, I've already inserted an AUTHID
, but to no avail.
我还能做什么?我在这里的想法几乎完结了.
What else can I do? I'm pretty much at the end of my ideas here.
推荐答案
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;
要从一个过程访问另一个表,您需要直接被授予SELECT权限,而不是通过角色来获得权限:
To access another table from a procedure, you need to be granted SELECT directly, not through a role:
GRANT SELECT ON TURAT03.GESCHAEFTE TO <your_user>;
汤姆·凯特(Tom Kyte)撰写的这篇文章包含其他信息.
这篇关于在Oracle存储过程中访问另一个用户的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!