时间戳

首页 > 解决方案 > 为什么我的查询在程序 (PL/SQL) 中不返回任何记录，但在 SQL 中手动运行时却返回？

sql - 为什么我的查询在程序 (PL/SQL) 中不返回任何记录，但在 SQL 中手动运行时却返回？

问题描述

大家好，感谢您抽出宝贵时间帮助我。

我有以下查询：

SELECT   owner, object_name
FROM     all_objects
WHERE    owner IN ('EDI')
ORDER BY object_type, object_name;

手动查询结果

正如您在屏幕截图中看到的，它返回一些值。

当我从程序内部调用查询时，它没有返回任何值（参见第二个屏幕截图）。

存储过程结果

说明这一点的测试代码是：

CREATE OR REPLACE PROCEDURE my_test
AS
BEGIN
    DBMS_OUTPUT.put_line('Pre-Loop');

    FOR indx IN (SELECT   owner, object_name
                 FROM     all_objects
                 WHERE    owner IN ('EDI')
                 ORDER BY object_type, object_name)
    LOOP
        DBMS_OUTPUT.put_line('Object: ' || indx.owner || '.' || indx.object_name);
    END LOOP;

    DBMS_OUTPUT.put_line('Post-Loop');
END;
/

BEGIN
    my_test();
END;
/

EDI 模式是全新的，所以我怀疑这是一个授权/特权问题，但我似乎无法找到我可能缺少的东西以使其正常工作。我尝试以 EDI 用户和 SYS 的身份运行它。

得到答案后编辑：

我在评论中提到要找到该问题的官方答案的替代方案，并希望确保将其分享给稍后阅读此内容的任何人，以便他们可以权衡相同的决定。

EXECUTE ANY PROCEDURE向预期运行代码的用户应用授权SELECT ANY TABLE是可行的，但我相信有理由不给予如此广泛的授权。

标签： sqloracleplsql

解决方案

您的存储过程是定义者权限存储过程。这意味着它无法访问通过角色授予的权限，只有那些直接授予过程所有者的权限。另一方面，Ad hoc SQL 除了用户的直接授权外，还使用为当前会话启用的任何角色的权限运行。最有可能的是，该过程的所有者可以通过角色而不是通过直接授权访问相关表。

您可以通过运行来测试它

set role none;

然后运行即席 SQL 语句。如果我的赌注是正确的，那么临时 SQL 现在将返回 0 行，因为您已禁用会话的所有角色。

根据您要对该过程执行的操作，您可以通过将其转换为调用者权限存储过程来解决问题。

CREATE OR REPLACE PROCEDURE my_test
  AUTHID CURRENT_USER
AS

这将导致过程以调用者会话的特权（包括通过角色授予的特权）而不是定义者的特权运行。假设您要调用该过程的所有用户都可以访问这些EDI表，这就足够了。

推荐阅读