主页 / dba / 问题 / 132671
Accepted
MacGyver
Asked: 2016-03-19 08:56:57 +0800 CST

另一个“ORA-06512：表或视图不存在”场景的权限

  • 772

请参阅下面的代码。我正在使用模式 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;
oracle permissions

1 个回答

  1. Best Answer

    好吧，我已经在我的测试环境中模拟了您的问题。我发现表名（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.
    • 1

相关问题