在 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屋！