怎么做系统网站,软考网络规划设计师,做外贸雨伞到什么网站,加强学校就业信息网站建设和管理在 Oracle 和 OB Oracle 租户下调用存储过程时#xff0c;两者表现并不一致#xff0c;导致获取到的 SQL 文本拼接不完整#xff0c;影响到了业务侧的功能测试。本文将针对这个问题进行相关的测试和验证。 作者#xff1a;赵黎明#xff0c;爱可生 MySQL DBA 团队成员两者表现并不一致导致获取到的 SQL 文本拼接不完整影响到了业务侧的功能测试。本文将针对这个问题进行相关的测试和验证。 作者赵黎明爱可生 MySQL DBA 团队成员熟悉 Oracle、MySQL 等数据库擅长数据库性能问题诊断、事务与锁问题的分析等负责处理客户 MySQL 及我司自研 DMP 平台日常运维中的问题对开源数据库相关技术非常感兴趣。 爱可生开源社区出品原创内容未经授权不得随意使用转载请联系小编并注明来源。 本文约 3100 字预计阅读需要 10 分钟。 背景
最近在客户这边遇到一个故障在 Oracle 和 OB Oracle 租户下调用存储过程时两者表现并不一致导致获取到的 SQL 文本拼接不完整影响到了业务侧的功能测试。
客户的存储过程逻辑并不复杂就是通过查询系统视图 user_tab_columns 来获取用户的表名然后再进行 SQL 拼接完成后续的业务逻辑。
本文将针对这个问题进行相关的测试和验证。
问题复现
Oracle 环境中验证
-- 创建测试用户并赋权
[rootlocalhost ~]# 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 ownerU2;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_nameT1;
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 ownerU2;
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
----------1SQL set serveroutput on;
SQL call proc_case1();
T1
Call completed.SQL conn u2/u2
Connected.
SQL select * from t1;ID
----------1SQL 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 ownerU2;
-------------------------------------------------------
| 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_nameT1;- 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 ownerU2;
-------------------------------------------------------
| 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 ownerU1;
-------------------------
| 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_nameT1 and ownerU1;- 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 ownerU1;
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 PROCEDURE89318 PACKAGE31504 PACKAGE BODY1276 TYPE BODY2210 TRIGGER3895 FUNCTION7 JAVA SOURCE12338 TYPE8 rows selected.-- 创建测试存储过程大小写各1个SQL CREATE OR REPLACE PROCEDURE PROC_1 IS
V_N NUMBER :0;
BEGINSELECT 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;
beginselect 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 ownerSYS 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_nameT1;
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 LONGdba_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_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,DEGREE,INSTANCES,CACHE,TABLE_LOCK,SAMPLE_SIZE,LAST_ANALYZED,PARTITIONED,IOT_TYPE,TEMPORARY,SECONDARY,NESTED,BUFFER_POOL,ROW_MOVEMENT,GLOBAL_STATS,USER_STATS,DURATION,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_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,DEGREE,INSTANCES,CACHE,TABLE_LOCK,SAMPLE_SIZE,LAST_ANALYZED,PARTITIONED,IOT_TYPE,TEMPORARY,SECONDARY,NESTED,BUFFER_POOL,ROW_MOVEMENT,GLOBAL_STATS,USER_STATS,DURATION,SKIP_CORRUPT,MONITORING,CLUSTER_OWNER,DEPENDENCIES,COMPRESSION,DROPPED from user_tablesZLM VIEW_2 select TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,DEGREE,INSTANCES,CACHE,TABLE_LOCK,SAMPLE_SIZE,LAST_ANALYZED,PARTITIONED,IOT_TYPE,TEMPORARY,SECONDARY,NESTED,BUFFER_POOL,ROW_MOVEMENT,GLOBAL_STATS,USER_STATS,DURATION,SKIP_CORRUPT,MONITORING,CLUSTER_OWNER,DEPENDENCIES,COMPRESSION,DROPPED from USER_TABLES解决方案
将存储过程中的 user_tab_columns 视图替换成 all_tab_columns虽然可作为临时方案不过存在以下缺点
需要修改业务代码即替换存储过程中查询相关系统视图的部分。使用同义词来访问对象的用户需要有 SELECT ANY TABLE 的系统权限否则即便使用 all_ 的视图也查询不到目标对象。赋予执行用户 dba 权限并修改原有查询 SQL增加 ownerXXX 的条件存在安全隐患不推荐。OB 能提供一个 hotfix patch 来彻底解决该问题。
问题总结
在 OB 中普通用户查询 USER_TAB_COLUMNS 系统视图权限的逻辑与 Oracle 并不一致导致查询结果有差异。
除了 USER_TAB_COLUMNS 视图还有其他以 USER_ 开头的视图也存在类似的问题比如USER_SYNONYMS、USER_TABLES 等。
对于系统中已有的对象应尽快排查并确认在哪些对象中用到了这些系统视图在该问题被彻底修复前建议先对相关代码进行临时修改使其能继续完成后续的功能验证。
更多技术文章请访问https://opensource.actionsky.com/
关于 SQLE
爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者支持多场景审核支持标准化上线流程原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。
SQLE 获取
类型地址版本库https://github.com/actiontech/sqle文档https://actiontech.github.io/sqle-docs/发布信息https://github.com/actiontech/sqle/releases数据审核插件开发文档https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse
