作者：赵黎明，爱可生 MySQL DBA 团队成员，熟悉 Oracle、MySQL 等数据库，擅长数据库性能问题诊断、事务与锁问题的分析等，负责处理客户 MySQL 及我司自研 DMP 平台日常运维中的问题，对开源数据库相关技术非常感兴趣。爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。本文约 3100 字，预计阅读需要 10 分钟。

背景

最近在客户这边遇到一个故障，在 Oracle 和 OB Oracle 租户下调用存储过程时，两者表现并不一致，导致获取到的 SQL 文本拼接不完整，影响到了业务侧的功能测试。

客户的存储过程逻辑并不复杂，就是通过查询系统视图 user_tab_columns 来获取用户的表名，然后再进行 SQL 拼接，完成后续的业务逻辑。

本文将针对这个问题进行相关的测试和验证。

问题复现

Oracle 环境中验证

-- 创建测试用户并赋权
[root@localhost ~]# sqlplus / as sysdba
SQL> create user u1 identified by u1;
User created.
SQL> create user u2 identified by u2;
User created.
SQL> grant connect,resource to u1;
Grant succeeded.
SQL> grant create procedure to u1;
Grant succeeded.
SQL> grant connect,resource to u2;
Grant succeeded.
SQL> grant create synonym to u2;
Grant succeeded.
SQL> grant select any table to u2;
Grant succeeded.

-- 创建测试表并赋权
SQL> conn u1/u1
Connected.
SQL> create table t1(id int);
Table created.
SQL> insert into t1(id) values(1);
1 row created.

-- 创建表的同义词
SQL> conn u2/u2
Connected.
SQL> create synonym t1 for u1.t1;
Synonym created.

SQL> set lin 200
SQL> col owner for a5
SQL> col table_owner for a5
SQL> col db_link for a10
SQL> select * from all_synonyms where owner='U2';

OWNER SYNONYM_NAME          TABLE TABLE_NAME           DB_LINK
----- ------------------------------ ----- ------------------------------ ----------
U2  T1               U1  T1

-- 创建存储过程并赋权
SQL> conn u1/u1
Connected.
SQL> create or replace procedure proc_case1 as
v_str varchar2(10);
begin
select table_name into v_str from user_tab_columns where table_name='T1';
dbms_output.put_line(v_str);
end;
/  2    3    4    5    6    7  

Procedure created.
SQL> grant execute on proc_case1 to u2;
Grant succeeded.

-- 创建存储过程同义词
SQL> conn u2/u2
Connected.
SQL> create synonym proc_case1 for u1.proc_case1;
Synonym created.

SQL> select * from all_synonyms where owner='U2';
OWNER SYNONYM_NAME                   TABLE TABLE_NAME                     DB_LINK
----- ------------------------------ ----- ------------------------------ ----------
U2    PROC_CASE1                     U1    PROC_CASE1
U2    T1                             U1    T1

-- 验证
SQL> conn u1/u1
Connected.
SQL> select * from t1;
        ID
----------
         1

SQL> set serveroutput on;
SQL> call proc_case1();
T1
Call completed.

SQL> conn u2/u2
Connected.
SQL> select * from t1;
        ID
----------
         1

SQL> set serveroutput on;
SQL> call proc_case1();
T1
Call completed.
SQL>

由此可见，在 Oracle 中，无论是 u1 还是 u2 用户，调用存储过程时都能正确返回表名，说明两者查询 user_tab_columns 视图的返回结果是一致的，这也是符合预期的。

OB Oracle 环境中验证

-- 创建测试用户并赋权
SYS[SYS]> create user u1 identified by u1;
Query OK, 0 rows affected (0.04 sec)
SYS[SYS]> create user u2 identified by u2;
Query OK, 0 rows affected (0.04 sec)
SYS[SYS]> grant connect,resource to u1;
Query OK, 0 rows affected (0.04 sec)
SYS[SYS]> grant create procedure to u1;
Query OK, 0 rows affected (0.03 sec)
SYS[SYS]> grant connect,resource to u2;
Query OK, 0 rows affected (0.05 sec)
SYS[SYS]> grant create synonym to u2;
Query OK, 0 rows affected (0.03 sec)
SYS[SYS]> grant select any table to u2;
Query OK, 0 rows affected (0.03 sec)

-- 创建测试表并赋权
SYS[SYS]> conn u1
Connection id:  269006
Current database: U1
SYS[U1]> create table t1(id int);
Query OK, 0 rows affected (0.21 sec)
SYS[U1]> insert into t1(id) values(1);
Query OK, 1 row affected (0.03 sec)
SYS[U1]> commit;
Query OK, 0 rows affected (0.01 sec)

-- 创建表的同义词
SYS[U1]> conn u2
Connection id:  50837
Current database: U2
SYS[U2]> create synonym t1 for u1.t1;
Query OK, 0 rows affected (0.05 sec)
SYS[U2]> select * from all_synonyms where owner='U2';
+-------+--------------+-------------+------------+---------+
| OWNER | SYNONYM_NAME | TABLE_OWNER | TABLE_NAME | DB_LINK |
+-------+--------------+-------------+------------+---------+
| U2    | T1           | U1          | T1         | NULL    |
+-------+--------------+-------------+------------+---------+
2 rows in set (0.01 sec)

-- 创建存储过程并赋权
SYS[U2]> conn u1
Connection id:  269078
Current database: U1
SYS[U1]> create or replace procedure proc_case1 as
  -> v_str varchar2(10);
  -> begin
  -> select table_name into v_str from user_tab_columns where table_name='T1';
  -> dbms_output.put_line(v_str);
  -> end;
  -> /
Query OK, 0 rows affected (0.17 sec)
SYS[U1]> grant execute on proc_case1 to u2;
Query OK, 0 rows affected (0.06 sec)

-- 创建存储过程同义词
SYS[U1]> conn u2
Connection id:  50896
Current database: U2
SYS[U2]> create synonym proc_case1 for u1.proc_case1;
Query OK, 0 rows affected (0.05 sec)
SYS[U2]> select * from all_synonyms where owner='U2';
+-------+--------------+-------------+------------+---------+
| OWNER | SYNONYM_NAME | TABLE_OWNER | TABLE_NAME | DB_LINK |
+-------+--------------+-------------+------------+---------+
| U2    | PROC_CASE1   | U1          | PROC_CASE1 | NULL    |
| U2    | T1           | U1          | T1         | NULL    |
+-------+--------------+-------------+------------+---------+
2 rows in set (0.01 sec)

-- 验证
SYS[U2]> conn u1
Connection id:  269134
Current database: U1
SYS[U1]> select * from t1;
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.01sec)

SYS[U1]> set serveroutput on;
Query OK, 0 rows affected (0.41 sec)
SYS[U1]> call proc_case1();
Query OK, 0 rows affected (0.21 sec)
SYS[U1]> select table_name,column_name from user_tab_columns;
+------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+------------+-------------+
| C          | NAME        |
| C          | ADDRESS     |
+------------+-------------+
2 rows in set (0.08 sec)

此处其实已经可以发现一些端倪，在 OB 中虽然可以通过 conn 进行用户切换，切换后的用户也能访问自己的对象，但是在访问 USER_ 等视图时，返回结果与 Oracle 不同。

用户 u1 查询 user_tab_columns 表时，只能看到 SYS 用户下的表（ C 表是由 SYS 用户创建的），所以存储过程无法返回 T1 表的表名，其查询结果为空。

-- 直连 u1 用户验证
U1[U1]> select * from t1;
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.01sec)

U1[U1]> set serveroutput on;
Query OK, 0 rows affected (0.02sec)

U1[U1]> call proc_case1();
Query OK, 0 rows affected (0.08sec)

T1
U1[U1]>

-- 直连 u2 用户进行验证
U2[U2]> select * from t1;
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.03sec)

U2[U2]> set serveroutput on;
Query OK, 0 rows affected (0.44 sec)

U2[U2]> call proc_case1();
Query OK, 0 rows affected (0.43 sec)

U2[U2]> select * from user_tab_columns;
Empty set (0.08 sec)

# 同样地，u2 也无法从 user_tab_columns 视图中查询到 u1 创建的表，调用存储过程返回结果为空

-- 将 user_tab_columns 替换成 all_tab_columns 视图
U2[U2]> select table_name,column_name from all_tab_columns where owner='U1';
+------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+------------+-------------+
| T1         | ID          |
+------------+-------------+
1 row in set (0.08 sec)

U2[U2]> create or replace procedure proc_case2 as
    -> v_str varchar2(10);
    -> begin
    -> select table_name into v_str from all_tab_columns where table_name='T1' and owner='U1';
    -> dbms_output.put_line(v_str);
    -> end;
    -> /
Query OK, 0 rows affected (0.17ec)

U2[U2]> call proc_case2();
Query OK, 0 rows affected (0.16ec)

T1
U2[U2]>

-- 将 SELECT ANY TABLE 权限回收
SYS[SYS]> revoke select any table from u2;
Query OK, 0 rows affected (0.03 sec)

U2[U2]> select table_name,column_name from all_tab_columns where owner='U1';
Empty set (0.05 sec)

U2[U2]> set serveroutput on;
Query OK, 0 rows affected (0.01 sec)

U2[U2]> call proc_case2();
Query OK, 0 rows affected (0.05 sec)

当用户 u2 没有 SELECT ANY TABLE 系统权限后，即使查询 all_tab_columns 视图，也无法获取其他用户创建表的相关信息。

排查调用系统视图的相关对象

PL 对象

PL 对象，如：函数，存储过程等。


-- dba_source 视图中存放了各种 PL 对象的定义
SQL> select count(*),type from dba_source group by type;

 COUNT(*) TYPE
---------- ------------
  152202 PROCEDURE
   89318 PACKAGE
   31504 PACKAGE BODY
   1276 TYPE BODY
   2210 TRIGGER
   3895 FUNCTION
     7 JAVA SOURCE
   12338 TYPE

8 rows selected.

-- 创建测试存储过程（大小写各1个）

SQL> CREATE OR REPLACE PROCEDURE PROC_1 IS
V_N NUMBER :=0;
BEGIN
 SELECT COUNT(*) INTO V_N FROM USER_TAB_COLUMNS;
END;
/ 2  3  4  5  6 

Procedure created.

SQL> create or replace procedure proc_2 is
v_n number :=0;
begin
 select count(*) into v_n from user_tab_columns;
end;
/ 2  3  4  5  6 

Procedure created.

-- 查询常用系统视图名（此处只列举了几个与表相关的视图）
select owner,object_name,object_type from dba_objects where owner='SYS' and (object_name like 'USER_PART_%' or object_name like 'USER_T%' or object_name like 'ALL_PART_%' or object_name like 'ALL_T%' or object_name like 'DBA_PART_%' or object_name like 'DBA_T%');

-- 根据上一步获取到的系统视图名，通过模糊搜索，即可捕获到涉及查询这些系统视图的 PL 对象
SQL> set line 200 pages 9999 long 999999
SQL> col owner for a10
SQL> col name for a30
SQL> col text for a80
SQL> select owner,name,type,text from dba_source where owner not in('SYS', 'SYSTEM', 'SYSMAN', 'OUTLN', 'DIP', 'TSMSYS', 'DBSNMP',
     'ORACLE_OCM', 'WMSYS', 'EXFSYS', 'XDB', 'ANONYMOUS', 'ORDSYS',
     'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'MDSYS', 'MGMT_VIEW', 'PERFSTAT',
     'DMSYS', 'CTXSYS', 'OLAPSYS', 'MDDATA', 'APPQOSSYS', 'XS$NULL',
     'ORDDATA', 'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR',
     'OWBSYS', 'APEX_PUBLIC_USER', 'APEX_030200', 'FLOWS_FILES', 'SCOTT',
     'OMS', 'OWBSYS_AUDIT', 'DSG', 'DBMGR', 'PATROL', 'SPA', 'GOLDENGATE',
     'DBADM') and owner not like 'MYNET%' and (text like '%USER_TAB_COLUMNS%' or text like '%user_tab_columns%' or text like '%USER%TABLES%' or text like '%user%tables%' or text like '%ALL_TAB_COLUMNS%' or text like '%ALL_tab_columns%' or text like '%ALL%TABLES%' or text like '%ALL%tables%'); 2  3  4  5  6  7  8 

OWNER   NAME              TYPE     TEXT
---------- ------------------------------ ------------ --------------------------------------------------------------------------------

U1     PROC_CASE1           PROCEDURE  select table_name into v_str from user_tab_columns where table_name='T1';
ZLM    PROC_1             PROCEDURE   SELECT COUNT(*) INTO V_N FROM USER_TAB_COLUMNS;
ZLM    PROC_2             PROCEDURE   select count(*) into v_n from user_tab_columns;

视图对象


-- 创建测试视图1
SQL> create view view_1 as select * from user_tables;
View created.

-- 查询 dba_views 获取视图定义
SQL> select owner,view_name,text from dba_views where owner not in('SYS', 'SYSTEM', 'SYSMAN', 'OUTLN', 'DIP', 'TSMSYS', 'DBSNMP',
     'ORACLE_OCM', 'WMSYS', 'EXFSYS', 'XDB', 'ANONYMOUS', 'ORDSYS',
     'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'MDSYS', 'MGMT_VIEW', 'PERFSTAT',
     'DMSYS', 'CTXSYS', 'OLAPSYS', 'MDDATA', 'APPQOSSYS', 'XS$NULL',
     'ORDDATA', 'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR',
     'OWBSYS', 'APEX_PUBLIC_USER', 'APEX_030200', 'FLOWS_FILES', 'SCOTT',
     'OMS', 'OWBSYS_AUDIT', 'DSG', 'DBMGR', 'PATROL', 'SPA', 'GOLDENGATE',
     'DBADM') and (text like '%USER_TAB_COLUMNS%' or text like '%user_tab_columns%' or text like '%USER%TABLES%' or text like '%user%tables%' or text like '%ALL_TAB_COLUMNS%' or text like '%ALL_tab_columns%' or text like '%ALL%TABLES%' or text like '%ALL%tables%');
 2  3  4  5  6  7  8     'DBADM') and (text like '%USER_TAB_COLUMNS%' or text like '%user_tab_columns%' or text like '%USER%TABLES%' or text like '%user%tables%' or text like '%ALL_TAB_COLUMNS%' or text like '%ALL_tab_columns%' or text like '%ALL%TABLES%' or text like '%ALL%tables%')
                                                                                                                          *
ERROR at line 8:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

dba_source 视图中的 text 列是 varchar2 类型的，可以直接使用 like 进行模糊查询。
dba_views 视图中的 text 列是 long 类型的，无法直接使用 like 进行模糊查询，会报 ORA-00932 的错误。

workaround：先创建一张表，用 to_lob 函数将 text 字段转换为 clob 类型，然后将 dba_views 拷贝到该表中，再通过以上 SQL 进行查询。

-- 创建中间表并将系统视图 dba_views 内容拷贝到该表
SQL> create table my_views as select owner,view_name,to_lob(text) text from dba_views;

Table created.

-- 查询中间表捕获目标视图对象
SQL> select owner,view_name,text from my_views where owner not in('SYS', 'SYSTEM', 'SYSMAN', 'OUTLN', 'DIP', 'TSMSYS', 'DBSNMP',     'ORACLE_OCM', 'WMSYS', 'EXFSYS', 'XDB', 'ANONYMOUS', 'ORDSYS',     'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'MDSYS', 'MGMT_VIEW', 'PERFSTAT',     'DMSYS', 'CTXSYS', 'OLAPSYS', 'MDDATA', 'APPQOSSYS', 'XS$NULL',     'ORDDATA', 'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR',     'OWBSYS', 'APEX_PUBLIC_USER', 'APEX_030200', 'FLOWS_FILES', 'SCOTT',     'OMS', 'OWBSYS_AUDIT', 'DSG', 'DBMGR', 'PATROL', 'SPA', 'GOLDENGATE',     'DBADM') and (text like '%USER_TAB_COLUMNS%' or text like '%user_tab_columns%' or text like '%USER%TABLES%' or text like '%user%tables%' or text like '%ALL_TAB_COLUMNS%' or text like '%ALL_tab_columns%' or text like '%ALL%TABLES%' or text like '%ALL%tables%'); 2  3  4  5  6  7  8 
OWNER   VIEW_NAME           TEXT---------- ------------------------------ --------------------------------------------------------------------------------
ZLM    VIEW_1             select "TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FR                     EE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTEN                     TS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED                     _UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","A                     VG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TA                     BLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SE                     CONDARY","NESTED","BUFFER_POOL","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURA                     TION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","                     DROPPED" from user_tables

该方法可以满足需求，但每次有新的视图被创建时，需要 drop 并重建表，比较繁琐。

workaround：创建物化视图来代替中间表。

-- 创建物化视图
SQL> create materialized view my_mviews 
refresh force 
on demand 
start with sysdate
next sysdate + 10 /(24*60)
as 
select owner,view_name,to_lob(text) text from dba_views; 2  3  4  5  6  7 

Materialized view created.

-- 创建测试视图2
SQL> CREATE VIEW VIEW_2 AS SELECT * FROM USER_TABLES;
View created.

-- 查看是否捕获到 view_2 视图
SQL> select owner,view_name,text from my_mviews where owner not in('SYS', 'SYSTEM', 'SYSMAN', 'OUTLN', 'DIP', 'TSMSYS', 'DBSNMP',
     'ORACLE_OCM', 'WMSYS', 'EXFSYS', 'XDB', 'ANONYMOUS', 'ORDSYS',
     'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'MDSYS', 'MGMT_VIEW', 'PERFSTAT',
     'DMSYS', 'CTXSYS', 'OLAPSYS', 'MDDATA', 'APPQOSSYS', 'XS$NULL',
     'ORDDATA', 'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR',
     'OWBSYS', 'APEX_PUBLIC_USER', 'APEX_030200', 'FLOWS_FILES', 'SCOTT',
     'OMS', 'OWBSYS_AUDIT', 'DSG', 'DBMGR', 'PATROL', 'SPA', 'GOLDENGATE',
     'DBADM') and (text like '%USER_TAB_COLUMNS%' or text like '%user_tab_columns%' or text like '%USER%TABLES%' or text like '%user%tables%' or text like '%ALL_TAB_COLUMNS%' or text like '%ALL_tab_columns%' or text like '%ALL%TABLES%' or text like '%ALL%tables%'); 2  3  4  5  6  7  8 

OWNER   VIEW_NAME           TEXT
---------- ------------------------------ --------------------------------------------------------------------------------
ZLM    VIEW_1             select "TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FR
                     EE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTEN
                     TS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED
                     _UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","A
                     VG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TA
                     BLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SE
                     CONDARY","NESTED","BUFFER_POOL","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURA
                     TION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","
                     DROPPED" from user_tables

-- 查看物化视图刷新时间  
SQL> select owner,mview_name,last_refresh_type,last_refresh_date from user_mviews;
OWNER   MVIEW_NAME           LAST_REF LAST_REFRESH_DATE
---------- ------------------------------ -------- -------------------
ZLM    MY_MVIEWS           COMPLETE 2023-08-03 16:07:15

-- 手动刷新物化视图
SQL> exec dbms_mview.refresh('my_mviews');

PL/SQL procedure successfully completed.

SQL> select owner,mview_name,last_refresh_type,last_refresh_date from user_mviews;

OWNER   MVIEW_NAME           LAST_REF LAST_REFRESH_DATE
---------- ------------------------------ -------- -------------------
ZLM    MY_MVIEWS           COMPLETE 2023-08-03 16:21:45

-- 再次查询物化视图，此时 view_2 也能被捕获到了，这样就无需重复建表，当有新视图被创建的时候，只需手动刷新物化视图即可
SQL> select owner,view_name,text from my_mviews where owner not in('SYS', 'SYSTEM', 'SYSMAN', 'OUTLN', 'DIP', 'TSMSYS', 'DBSNMP',
     'ORACLE_OCM', 'WMSYS', 'EXFSYS', 'XDB', 'ANONYMOUS', 'ORDSYS',
     'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'MDSYS', 'MGMT_VIEW', 'PERFSTAT',
     'DMSYS', 'CTXSYS', 'OLAPSYS', 'MDDATA', 'APPQOSSYS', 'XS$NULL',
     'ORDDATA', 'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR',
     'OWBSYS', 'APEX_PUBLIC_USER', 'APEX_030200', 'FLOWS_FILES', 'SCOTT',
     'OMS', 'OWBSYS_AUDIT', 'DSG', 'DBMGR', 'PATROL', 'SPA', 'GOLDENGATE',
     'DBADM') and (text like '%USER_TAB_COLUMNS%' or text like '%user_tab_columns%' or text like '%USER%TABLES%' or text like '%user%tables%' or text like '%ALL_TAB_COLUMNS%' or text like '%ALL_tab_columns%' or text like '%ALL%TABLES%' or text like '%ALL%tables%'); 2  3  4  5  6  7  8 

OWNER   VIEW_NAME           TEXT
---------- ------------------------------ --------------------------------------------------------------------------------
ZLM    VIEW_1             select "TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FR
                     EE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTEN
                     TS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED
                     _UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","A
                     VG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TA
                     BLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SE
                     CONDARY","NESTED","BUFFER_POOL","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURA
                     TION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","
                     DROPPED" from user_tables

ZLM    VIEW_2             select "TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FR
                     EE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTEN
                     TS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED
                     _UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","A
                     VG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TA
                     BLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SE
                     CONDARY","NESTED","BUFFER_POOL","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURA
                     TION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","
                     DROPPED" from USER_TABLES

解决方案

将存储过程中的 user_tab_columns 视图替换成 all_tab_columns，虽然可作为临时方案，不过存在以下缺点：

需要修改业务代码，即替换存储过程中查询相关系统视图的部分。
使用同义词来访问对象的用户，需要有 SELECT ANY TABLE 的系统权限，否则即便使用 all_ 的视图，也查询不到目标对象。
赋予执行用户 dba 权限，并修改原有查询 SQL，增加 owner=‘XXX’ 的条件（存在安全隐患，不推荐）。
OB 能提供一个 hotfix patch 来彻底解决该问题。

问题总结

在 OB 中，普通用户查询 USER_TAB_COLUMNS 系统视图权限的逻辑与 Oracle 并不一致，导致查询结果有差异。

除了 USER_TAB_COLUMNS 视图，还有其他以 USER_ 开头的视图，也存在类似的问题，比如：USER_SYNONYMS、USER_TABLES 等。

对于系统中已有的对象，应尽快排查并确认在哪些对象中用到了这些系统视图，在该问题被彻底修复前，建议先对相关代码进行临时修改，使其能继续完成后续的功能验证。

OB Oracle 系统视图权限导致的故障一例

背景

问题复现

Oracle 环境中验证

OB Oracle 环境中验证

排查调用系统视图的相关对象

PL 对象

视图对象

解决方案

问题总结

相关文章

发布评论取消回复

共饮一杯

Oracle回收DBA权限注意事项

MongoDB 入门教学贴从术语到操作（约束怎么建立内部培训贴）

RisingWave 1.10 发布！新增用户自定义聚合函数

OB Oracle 系统视图权限导致的故障一例

背景

问题复现

Oracle 环境中验证

OB Oracle 环境中验证

排查调用系统视图的相关对象

PL 对象

视图对象

解决方案

问题总结

相关文章

发布评论 取消回复

共饮一杯

Oracle回收DBA权限注意事项

MongoDB 入门教学贴 从术语到操作 （约束怎么建立 内部培训贴）

RisingWave 1.10 发布！新增用户自定义聚合函数

发布评论取消回复

MongoDB 入门教学贴从术语到操作（约束怎么建立内部培训贴）