Oracle远程数据库视图配置异常：无底层权限用户无法访问

问题场景 #

我通过公共数据库链接创建嵌套视图，本意是让用户无需直接访问远程表就能获取数据，示例代码如下：

create view MYSCHEMA.MYVIEW1 as
select field1, field2
from SOMETABLE@OTHERDB;

create view MYSCHEMA.MYVIEW2 as
select field1, field2
from MYSCHEMA.MYVIEW1;

grant select on MYVIEW2 to ROLE1;

出现的问题：

• 我（视图创建者）访问所有视图都正常
• 属于ROLE1的USER1因为有SOMETABLE@OTHERDB访问权限，查询MYVIEW2无异常
• 同属ROLE1的USER2没有SOMETABLE@OTHERDB权限，查询MYVIEW2时报错：ORA-00942: table or view does not exist ORA-02063: preceding line from OTHERDB
• USER2能在SQL Developer里看到MYVIEW2，但数据加载不出来；USER2在OTHERDB有账号，但没SOMETABLE的查询权限
• 同一个公共数据库链接下的其他视图都正常，只有MYSCHEMA下的这套视图出问题

核心原因与修复方案 #

1. 视图权限执行模式是关键 #

Oracle视图默认用AUTHID CURRENT_USER模式，也就是用当前查询用户的权限去访问底层对象。USER2查MYVIEW2时，会用自己的权限去碰SOMETABLE@OTHERDB，但他没这个权限，自然报错。

解决办法是用AUTHID DEFINER（定义者权限）创建视图，这样视图会以创建者MYSCHEMA的权限去访问远程表，用户只要有视图的SELECT权限就行，不用管底层远程表的权限：

-- 重建MYVIEW1，指定定义者权限
create or replace view MYSCHEMA.MYVIEW1
authid definer
as
select field1, field2
from SOMETABLE@OTHERDB;

-- 重建MYVIEW2，建议也统一用定义者权限
create or replace view MYSCHEMA.MYVIEW2
authid definer
as
select field1, field2
from MYSCHEMA.MYVIEW1;

⚠️ 要确保MYSCHEMA本身有SOMETABLE@OTHERDB的SELECT权限，而且这个权限得是直接授予的，不能是通过角色继承的。

2. 检查公共数据库链接的权限配置 #

确认公共DB链接OTHERDB是公开可用的，或者MYSCHEMA能正常使用这个链接：

-- 查看DB链接的归属和配置
select owner, db_link, username, host from all_db_links where db_link = 'OTHERDB';

如果是私有链接，得保证MYSCHEMA有使用权限，不然定义者模式也没法正常访问。

3. 验证MYSCHEMA的远程表权限 #

如果MYSCHEMA的SOMETABLE@OTHERDB权限是通过角色拿到的，定义者模式下视图没法用这个权限，必须直接给MYSCHEMA授权：

-- 检查MYSCHEMA对远程表的直接权限
select grantee, privilege, table_name, owner from all_tab_privs where table_name = 'SOMETABLE' and grantee = 'MYSCHEMA';

如果查不到结果，就需要在远程库给MYSCHEMA对应的远程用户直接授予SELECT权限：

-- 在OTHERDB执行
grant select on SOMETABLE to MYSCHEMA_REMOTE_USER;

4. 刷新视图依赖（可选） #

要是视图的依赖关系出了问题，编译一下视图所在的schema：

exec dbms_utility.compile_schema('MYSCHEMA');

内容的提问来源于stack exchange，提问作者user22258555