PLSQL：包儲存過程無法呼叫 exe 即時語句

我正在使用 oracle 11g R2。

問題：

我無法在 SchemaB 包中針對 SchemaA 表執行動態 SQL。SchemaB 的所有者正在呼叫 SchemaB。

背景：

我有一個包/過程有權在另一個模式的表上執行選擇語句。

所以我可以毫無問題地撥打以下電話。

select * from SchemaA.TableA where blah

我需要能夠從動態 SQL 執行此呼叫，因為來自 SchemaA 的表可以是 10 個中的任何一個。我創建了以下 SQL。我收到一個錯誤

ORA-01031: 權限不足when I run the same statement but with立即執行

程式碼：

v_query varchar2(2000); 
v_query := 'select id, value1, value2 from SchemaA. ' || TableName || ' where id = ' || id;

execute immediate v_query ;

我知道包沒有獲取使用者角色，所以我什至將以下程式碼放入我的包 AUTHID CURRENT_USER 的簽名中；這沒有改變。

我需要設置哪些其他權限才能執行此操作？

或者，如果您有另一種動態表選擇解決方案，那也很棒。

你做錯了什麼。測試案例如下…

創建schemaa和schemab：

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 21 14:28:47 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> create user schemaa identified by "schemaa";
SP2-0640: Not connected
SQL> conn / as sysdba
Connected.
SQL> create user schemaa identified by "schemaa";

User created.

SQL> grant connect, resource to schemaa;

Grant succeeded.

SQL> create user schemab identified by schemab;

User created.

SQL> grant connect, resource to schemab;

Grant succeeded.

登錄為schemaa，創建兩個表並select在第一個表上授予schemab，但不要在第二個表上授予任何內容：

SQL> conn schemaa/schemaa;
Connected.

SQL> create table satable ( aaa integer ) ;

Table created.

SQL> insert into satable values ( 1 );

1 row created.

SQL> grant select on satable to schemab;

Grant succeeded.

SQL> create table satablenograntsforb( aaa integer ) ;

Table created.

以 身份登錄schemab，創建儲存過程，然後使用我們有權訪問的表對其進行測試select：

SQL> conn schemab/schemab;
Connected.

SQL> CREATE OR REPLACE PROCEDURE schemabproc(TableName IN VARCHAR2)
 2  IS
 3  v_query varchar2(2000);
 4  BEGIN
 5
 6  v_query := 'select aaa from schemaa. ' || TableName ;
 7  execute immediate v_query ;
 8
 9  END;
10  /

Procedure created.

SQL> exec schemabproc('satable');

PL/SQL procedure successfully completed.

SQL>

如您所見，它執行正常。

讓我們試試我們沒有權限的表：

SQL> exec schemabproc('satablenograntsforb');
BEGIN schemabproc('satablenograntsforb'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCHEMAB.SCHEMABPROC", line 7
ORA-06512: at line 1

讓我們grant重新測試：

SQL> conn schemaa/schemaa
Connected.

SQL> grant select on satablenograntsforb to schemab;

Grant succeeded.

現在我們應該能夠select從第二個表中正確地進行：

SQL> conn schemab/schemab 

SQL> exec schemabproc('satablenograntsforb');

PL/SQL procedure successfully completed.

SQL>

結論：還有其他事情發生。請注意，上面的過程使用AUTHID DEFINER預設值執行。

引用自：https://dba.stackexchange.com/questions/152991