OceanBase Oracle 模式下系统视图权限导致的故障一例

2023-10-18 299

在 Oracle 和 OB Oracle 租户下调用存储过程时，两者表现并不一致，导致获取到的 SQL 文本拼接不完整，影响到了业务侧的功能测试。本文将针对这个问题进行相关的测试和验证。

作者：赵黎明，爱可生 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 等。

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

更多技术文章，请访问：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

微信关注我们

原文链接：https://my.oschina.net/actiontechoss/blog/10120128

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

新晋技术管理者如何推动组织变革？

技术管理者需要不断地努力改善团队状况，比如提升研发效能、帮助成员成长，或者优化组织结构等等。可以说，推动变革是「技术管理者」这一角色的重要使命之一。关于变革的挑战总是复杂，而如何在不同的环境和问题中影响团队也是一项艰巨的任务。在实践中，我发现以更加实际的视角看待问题会有所帮助。 # 技术管理者的工具箱尽管公司和技术团队各不相同，但大多数情况下，技术管理者有权决定团队如何开展工作，以及如何评估团队成员。这意味着他们拥有三个重要的变革工具：系统流程、行为和奖励。技术管理者可以通过改进内部流程（System），提升团队的研发效能；通过自身的行为（Behavior），影响成员行动；使用奖励机制（Reward），激励成员做出正确行为。它们在应对不同挑战时很有帮助。技术管理者了解自己能够撬动哪些杠杆，在行动时也会目标更明确，方向更清晰。那么，在软件开发领域，有关系统流程、行为和奖励层面的变革如何落地？ 01 系统流程层面系统和流程是最常见且讨论得最多的实践方法。每个团队都有很多正式的、非正式的工作流程。技术管理者可以对流程进行优化，以改善预期结果。尽管我们都知道优化流程非常有效，但实践...

2023-10-18

348

ModStart 是一个基于 Laravel 模块化极速开发框架。模块市场拥有丰富的功能应用，支持后台一键快速安装，让开发者能快的实现业务功能开发。系统完全开源，基于 Apache 2.0 开源协议，免费且不限制商业使用。功能特性丰富的模块市场，后台一键快速安装会员模块通用且完整，支持完整的API调用大文件分片上传，进度条显示，已上传文件管理强大的模块扩展功能，所有模块可以无缝集成，支持在线安装、卸载模块完善的开发助手，实现模块、主题的的一键创建完善的后台权限管理，支持基于RBAC的权限管理系统后台管理支持使用手机、平板、PC，无论何时何地都可方便管理第三方登录（QQ、微信、微博、支付宝、微信小程序）第三方支付支持（微信、支付宝、支付宝当面付、微信扫码、微信小程序）第三方云存储支持，支持云储存分片上传（阿里云、百度云、华为云、腾讯云、FTP、七牛云、UCloud、又拍云）第三方短信支持（阿里云、腾讯云、华为云、百度云、253云通讯、聚合、七牛云、融云、赛邮、UCloud、云片、网易云） V7.4.0版本更新 2023年10月17日ModStartCMS发布v7...

2023-10-17

364

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。