permissions for yet another "ORA-06512: table or view does not exist" scenario

Question

See the code below. I'm running the code with user x in schema y. The exact same code works if I run it by user x in schema x, but not with user x in schema y. I get the error ORA-06512: table or view does not exist. Why would this be? I'm setting the schema explicitly to y. When running JUST the create statement with user x in schema y, it runs fine. And when running JUST the create statement with user x in schema x, it also runs fine.

There are two synonyms in schema y, called aaaaa and bbbbb. See y.aaaaa and y.bbbbb in the create statement. Synonym y.aaaaa selects data from table z.aaaaa in schema z. Synonym y.bbbbb selects data from table z.bbbbb in schema z. user x currently has SELECT permissions on these two tables in schema z. I renamed the synonyms (y.aaaaa and y.bbbbb) in schema y so they're easier to find.

The XSQL is the culprit, but not sure what permissions we need. When XSQL(sql_code) is commented out, it doesn't throw the error.

This doesn't work:

alter session set 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;

This works:

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\%');

For reference purposes:

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

Answer 1

Well I have simulated your problem in my test environment. I found the issue with the table name(aaaa and bbbbb which belongs to z schema).
Though you have select privilege on these tables you must use schema name explicitly(z.aaaaa and z.bbbbb). The following is the test case.

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.