Bypass“表或视图不存在”在包编译 [英] Bypass "table or view does not exist" in package compilation

问题描述

在Oracle数据库中有两个模式。

MYSCHEMA >

OTHERSCHEMA 不受我控制。

知道我可以从 select * from OTHERSCHEMA.OTHEROBJECT 获得结果。但是 OTHEROBJECT 是一个同义词。

如

 插入到MYSCHEMA.MYTABLE（COL1）中，从OTHERSCHEMA.OTHEROBJECT中选择COL1; 
  

但它给了我表或视图不存在。

如何解决或绕过这个问题？感谢！

解决方案

我假设您获得了 otherschema.otherobject grant all on otherschema.batheme to myschema ），通过 如果是这种情况，则此角色中的权限不会用于确定在PL / SQL块中具有什么权限。

另请参见角色在PL / SQL块中的工作原理（Oracle Docu，其中在使用Definer权限的命名块中使用的角色下：

在任何命名的PL / SQL中禁用所有角色块）（存储过程，函数或触发器），它们以定义者的权限执行。角色不用于权限检查，您不能在定义者的权限过程中设置角色。）

There are two schemas in a Oracle database.

MYSCHEMA that is controlled by me.

OTHERSCHEMA that is not controlled by me.

I just know I can get result from select * from OTHERSCHEMA.OTHEROBJECT. However, OTHEROBJECT is a synonym.

In my package, I have a statement like

insert into MYSCHEMA.MYTABLE(COL1) select COL1 from OTHERSCHEMA.OTHEROBJECT;

But it gave me Table or view does not exist.

How can I solve or bypass this problem? Thanks!

解决方案

I assume you received the privilege to select from otherschema.otherobject by means of a role as opposted to a direct grant (such as grant all on otherschema.otherobject to myschema). If this is the case, the privileges within this role will not be used to determine what rights you have within a PL/SQL block.

See also How Roles Work in PL/SQL Blocks (Oracle Docu, where it says under Roles Used in Named Blocks with Definer's Rights:

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.)

这篇关于Bypass“表或视图不存在”在包编译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！