同义词嵌套场景下会导致视图访问异常

问题描述

在 OceanBase 数据库中，存在同义词嵌套的场景下，视图访问会出现异常。

示例：使用其它用户创建的视图，且视图中包含了同义词，查询报错。

1. 创建测试用户。

   obclient [SYS]> create user test01 identified by "****";
   test02 identified by "123456";
   

   obclient [SYS]> create user test03 identified by "****";
   Query OK, 0 rows affected (0.033 sec)
   

   obclient [SYS]> create user test02 identified by "****";
   Query OK, 0 rows affected (0.012 sec)
   

   obclient [SYS]> create user test03 identified by "****";
   Query OK, 0 rows affected (0.015 sec)
   

2. 赋权限。

   obclient [SYS]> grant connect,resource to test01;
   connect,resource to test02;
   

   obclient [SYS]> grant connect,resource to test03;
   Query OK, 0 rows affected (0.044 sec)
   

   obclient [SYS]> grant connect,resource to test02;
   Query OK, 0 rows affected (0.020 sec)
   

   obclient [SYS]> grant connect,resource to test03;
   Query OK, 0 rows affected (0.016 sec)
   

3. 用户 test01 创建表与视图 v1 并赋权。

   obclient [SYS]> connect test01;
   Connection id:    3221697034
   Current database: TEST01
   

   obclient [TEST01]> create table t1(a number);
   Query OK, 0 rows affected (0.029 sec)
   

   obclient [TEST01]> create view v1 as select * from t1;
   Query OK, 0 rows affected (0.029 sec)
   

   obclient [TEST01]> grant select on v1 to test02 with grant option;
   Query OK, 0 rows affected (0.029 sec)
   

4. 用户 test02 基于用户 test01 的视图 v1 创建同义词创建一个外层视图 mv1 并赋权。

   obclient [TEST01]> connect test02;
   Connection id:    3221697476
   Current database: TEST02
   

   obclient [TEST02]> create synonym v1 for test01.v1;
   Query OK, 0 rows affected (0.018 sec)
   

   obclient [TEST02]> create view mv1 as select * from v1;
   Query OK, 0 rows affected (0.012 sec)
   

   obclient [TEST02]> grant select on test02.mv1 to test03;
   Query OK, 0 rows affected (0.010 sec)
   

5. 用户3创建用户 test02 的基于外层视图 mv1 的同义词。

   obclient [TEST02]> connect test03;
   Connection id:    3221697642
   Current database: TEST03
   

   obclient [TEST03]> create synonym mv1 for test02.mv1;
   Query OK, 0 rows affected (0.014 sec)
   

6. 用户 test03 无法访问用户 test02 的视图 mv1。

   obclient [TEST03]> select * from mv1;
   ERROR 4063 (42S22): ORA-04063: view 'TEST02.MV1' has errors
   

   obclient [TEST03]> select * from TEST02.MV1;
   ERROR 4063 (42S22): ORA-04063: view 'TEST02.MV1' has errors
   

7. 需要再给 test02.v1 建个同义词才可以访问。

   obclient [TEST02]> connect test03;
   Connection id:    3221704679
   Current database: TEST03
   

   obclient [TEST03]> create synonym v1 for test02.v1;
   Query OK, 0 rows affected (0.018 sec)
   

   obclient [TEST03]> select * from mv1;
   Empty set (1.515 sec)
   

   obclient [TEST03]> select * from TEST02.MV1;
   Empty set (1.006 sec)
   

适用版本

OceanBase 数据库 v3.x 版本。

问题原因

该问题是由于同义词反拼时的一个 bug 引起，反拼时没有带上 database_name。

解决方法

升级至问题已修复版本。

目前已修复的版本包括 OceanBase 数据库 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 之后版本、V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 之后版本。