另一個“ORA-06512：表或視圖不存在”場景的權限

請參閱下面的程式碼。我正在使用模式 y 中的使用者 x 執行程式碼。如果我由模式 x 中的使用者 x 執行完全相同的程式碼，但不能由模式 y 中的使用者 x 執行。我得到錯誤ORA-06512: table or view does not exist。為什麼會這樣？我將架構明確設置為 y。當只在模式 y 中使用使用者 x 執行 create 語句時，它執行良好。當只在模式 x 中使用使用者 x 執行 create 語句時，它也執行良好。

模式 y 中有兩個同義詞，稱為 aaaaa 和 bbbbb。請參閱 create 語句中的 y.aaaaa 和 y.bbbbb。同義詞 y.aaaaa 從模式 z 中的表 z.aaaaa 中選擇數據。同義詞 y.bbbbb 從模式 z 中的表 z.bbbbb 中選擇數據。使用者 x 目前對模式 z 中的這兩個表具有 SELECT 權限。我在模式 y 中重命名了同義詞（y.aaaaa 和 y.bbbbb），以便更容易找到它們。

XSQL 是罪魁禍首，但不確定我們需要什麼權限。當XSQL(sql_code)被註釋掉時，它不會拋出錯誤。

這不起作用：

更改會話集 current_schema=y;

DECLARE sql_code VARCHAR2(4000) :=
'create table basis as '||
'(select my_basecode,c_fullname,encounter_num,concept_cd from aaaaa basis '||
'        inner join enc on enc.patid = basis.patient_num and enc.encounterid = basis.encounter_num '||
'     join bbbbb basiscode  '||
'        on basis.modifier_cd = basiscode.c_basecode '||
'        and basiscode.c_fullname like ''\BASIS\%'') ';
BEGIN
 DROPSQL('DROP TABLE basis');
 XSQL(sql_code);
END;

這有效：

alter session set current_schema=y;

create table basis as
(select my_basecode,c_fullname,encounter_num,concept_cd from aaaaa basis
       inner join enc on enc.patid = basis.patient_num and enc.encounterid = basis.encounter_num
    join bbbbb basiscode
       on basis.modifier_cd = basiscode.c_basecode '||
       and basiscode.c_fullname like '\BASIS\%');

供參考：

create or replace PROCEDURE XSQL(sqlstring VARCHAR2) AS 
BEGIN
 EXECUTE IMMEDIATE sqlstring;
 dbms_output.put_line(sqlstring);
END XSQL;

好吧，我已經在我的測試環境中模擬了您的問題。我發現表名（aaaa屬於bbbbb模式z）的問題。

儘管您對這些表具有選擇權限，但您必須顯式使用模式名稱（z.aaaaa和z.bbbbb）。以下是測試案例。

SQL> create user z identified by z;

User created.

SQL> create user y identified by y;

User created.

SQL> create user x identified by x;

User created.

SQL> grant connect, resource to x,y,z;

Grant succeeded.

SQL> grant create any table to x;

Grant succeeded.


SQL> conn z/z
Connected.
SQL> create table aaaaa(id number, name varchar2(20));
Table created.

SQL> create table bbbbb(id number, address varchar2(20));
Table created.

SQL> grant select on bbbbb to y;
Grant succeeded.

SQL> grant select on aaaaa to y;
Grant succeeded.

SQL> grant select on aaaaa to x;
Grant succeeded.

SQL> grant select on bbbbb to x;
Grant succeeded.

SQL> conn / as sysdba
Connected.
SQL> grant create synonym to y;

Grant succeeded.

SQL> conn y/y
Connected.
SQL> create synonym aaaaa for z.aaaaa;

Synonym created.

SQL> create synonym bbbbb for z.bbbbb; 

Synonym created. 

//As you said you have renamed the synonyms.

SQL> rename aaaaa to a1;

Table renamed.

SQL> rename bbbbb to b1;

Table renamed.

SQL> conn x/x
Connected.
SQL> create or replace procedure xsql(sqlstring varchar2) as 
begin
execute immediate sqlstring;
dbms_output.put_line(sqlstring);
end xsql; 
/

Procedure created.

SQL> alter session set current_schema=y;

Session altered.

SQL> declare sql_code varchar2(2000):=
'create table basis as '|| 
'select basis.id, name, address from aaaaa basis join bbbbb basis_code on(basis.id=basis_code.id)';
begin
x.xsql(sql_code);
end;  
/
declare sql_code varchar2(2000):=
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "X.XSQL", line 3
ORA-06512: at line 5


SQL> declare sql_code varchar2(2000):=
'create table basis as '|| 
'select basis.id, name, address from z.aaaaa basis join z.bbbbb basis_code on(basis.id=basis_code.id)';
begin
x.xsql(sql_code);
end; 
/

PL/SQL procedure successfully completed.

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