OB Oracle系統(tǒng)視圖權(quán)限導(dǎo)致的故障解決案例
更新時(shí)間：2023年10月25日 09:50:02 作者：愛可生開源社區(qū)
在 Oracle 和 OB Oracle 租戶下調(diào)用存儲(chǔ)過程時(shí),兩者表現(xiàn)并不一致,導(dǎo)致獲取到的 SQL 文本拼接不完整,影響到了業(yè)務(wù)側(cè)的功能測(cè)試,本文將針對(duì)這個(gè)問題進(jìn)行相關(guān)的測(cè)試和驗(yàn)證
背景
最近在客戶這邊遇到一個(gè)故障，在 Oracle 和 OB Oracle 租戶下調(diào)用存儲(chǔ)過程時(shí)，兩者表現(xiàn)并不一致，導(dǎo)致獲取到的 SQL 文本拼接不完整，影響到了業(yè)務(wù)側(cè)的功能測(cè)試。
客戶的存儲(chǔ)過程邏輯并不復(fù)雜，就是通過查詢系統(tǒng)視圖 user_tab_columns 來獲取用戶的表名，然后再進(jìn)行 SQL 拼接，完成后續(xù)的業(yè)務(wù)邏輯。
本文將針對(duì)這個(gè)問題進(jìn)行相關(guān)的測(cè)試和驗(yàn)證。
問題復(fù)現(xiàn)
Oracle 環(huán)境中驗(yàn)證
-- 創(chuàng)建測(cè)試用戶并賦權(quán)
[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.
-- 創(chuàng)建測(cè)試表并賦權(quán)
SQL> conn u1/u1
Connected.
SQL> create table t1(id int);
Table created.
SQL> insert into t1(id) values(1);
1 row created.
-- 創(chuàng)建表的同義詞
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
-- 創(chuàng)建存儲(chǔ)過程并賦權(quán)
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.
-- 創(chuàng)建存儲(chǔ)過程同義詞
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
-- 驗(yàn)證
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 用戶，調(diào)用存儲(chǔ)過程時(shí)都能正確返回表名，說明兩者查詢 user_tab_columns 視圖的返回結(jié)果是一致的，這也是符合預(yù)期的。
OB Oracle 環(huán)境中驗(yàn)證
-- 創(chuàng)建測(cè)試用戶并賦權(quán) 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) -- 創(chuàng)建測(cè)試表并賦權(quán) 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) -- 創(chuàng)建表的同義詞 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) -- 創(chuàng)建存儲(chǔ)過程并賦權(quán) 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) -- 創(chuàng)建存儲(chǔ)過程同義詞 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) -- 驗(yàn)證 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)
此處其實(shí)已經(jīng)可以發(fā)現(xiàn)一些端倪，在 OB 中雖然可以通過 conn 進(jìn)行用戶切換，切換后的用戶也能訪問自己的對(duì)象，但是在訪問 USER_ 等視圖時(shí)，返回結(jié)果與 Oracle 不同。
用戶 u1 查詢 user_tab_columns 表時(shí)，只能看到 SYS 用戶下的表（ C 表是由 SYS 用戶創(chuàng)建的），所以存儲(chǔ)過程無法返回 T1 表的表名，其查詢結(jié)果為空。
-- 直連 u1 用戶驗(yàn)證
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 用戶進(jìn)行驗(yàn)證
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 創(chuàng)建的表，調(diào)用存儲(chǔ)過程返回結(jié)果為空
-- 將 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 權(quán)限回收
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)當(dāng)用戶 u2 沒有 SELECT ANY TABLE 系統(tǒng)權(quán)限后，即使查詢 all_tab_columns 視圖，也無法獲取其他用戶創(chuàng)建表的相關(guān)信息。
排查調(diào)用系統(tǒng)視圖的相關(guān)對(duì)象
PL 對(duì)象
PL 對(duì)象，如：函數(shù)，存儲(chǔ)過程等。
-- dba_source 視圖中存放了各種 PL 對(duì)象的定義
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.
-- 創(chuàng)建測(cè)試存儲(chǔ)過程（大小寫各1個(gè)）
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.
-- 查詢常用系統(tǒng)視圖名（此處只列舉了幾個(gè)與表相關(guān)的視圖）
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%');
-- 根據(jù)上一步獲取到的系統(tǒng)視圖名，通過模糊搜索，即可捕獲到涉及查詢這些系統(tǒng)視圖的 PL 對(duì)象
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;視圖對(duì)象
-- 創(chuàng)建測(cè)試視圖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 進(jìn)行模糊查詢。dba_views視圖中的 text 列是 long 類型的，無法直接使用 like 進(jìn)行模糊查詢，會(huì)報(bào) ORA-00932 的錯(cuò)誤。
workaround：先創(chuàng)建一張表，用 to_lob 函數(shù)將 text 字段轉(zhuǎn)換為 clob 類型，然后將 dba_views 拷貝到該表中，再通過以上 SQL 進(jìn)行查詢。
-- 創(chuàng)建中間表并將系統(tǒng)視圖 dba_views 內(nèi)容拷貝到該表
SQL> create table my_views as select owner,view_name,to_lob(text) text from dba_views;
Table created.
-- 查詢中間表捕獲目標(biāo)視圖對(duì)象
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該方法可以滿足需求，但每次有新的視圖被創(chuàng)建時(shí)，需要 drop 并重建表，比較繁瑣。
workaround：創(chuàng)建物化視圖來代替中間表。
-- 創(chuàng)建物化視圖
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.
-- 創(chuàng)建測(cè)試視圖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
-- 查看物化視圖刷新時(shí)間
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
-- 手動(dòng)刷新物化視圖
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
-- 再次查詢物化視圖，此時(shí) view_2 也能被捕獲到了，這樣就無需重復(fù)建表，當(dāng)有新視圖被創(chuàng)建的時(shí)候，只需手動(dòng)刷新物化視圖即可
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解決方案
將存儲(chǔ)過程中的 user_tab_columns 視圖替換成 all_tab_columns，雖然可作為臨時(shí)方案，不過存在以下缺點(diǎn)：
- 需要修改業(yè)務(wù)代碼，即替換存儲(chǔ)過程中查詢相關(guān)系統(tǒng)視圖的部分。
- 使用同義詞來訪問對(duì)象的用戶，需要有 SELECT ANY TABLE 的系統(tǒng)權(quán)限，否則即便使用
all_的視圖，也查詢不到目標(biāo)對(duì)象。 - 賦予執(zhí)行用戶 dba 權(quán)限，并修改原有查詢 SQL，增加 owner='XXX' 的條件（存在安全隱患，不推薦）。
- OB 能提供一個(gè) hotfix patch 來徹底解決該問題。
問題總結(jié)
在 OB 中，普通用戶查詢 USER_TAB_COLUMNS 系統(tǒng)視圖權(quán)限的邏輯與 Oracle 并不一致，導(dǎo)致查詢結(jié)果有差異。
除了 USER_TAB_COLUMNS 視圖，還有其他以 USER_ 開頭的視圖，也存在類似的問題，比如：USER_SYNONYMS、USER_TABLES 等。
對(duì)于系統(tǒng)中已有的對(duì)象，應(yīng)盡快排查并確認(rèn)在哪些對(duì)象中用到了這些系統(tǒng)視圖，在該問題被徹底修復(fù)前，建議先對(duì)相關(guān)代碼進(jìn)行臨時(shí)修改，使其能繼續(xù)完成后續(xù)的功能驗(yàn)證。
以上就是OB Oracle系統(tǒng)視圖權(quán)限導(dǎo)致的故障解決案例的詳細(xì)內(nèi)容，更多關(guān)于OB Oracle視圖權(quán)限故障解決的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
Oracle查看執(zhí)行計(jì)劃的實(shí)現(xiàn)
查看Oracle 的執(zhí)行計(jì)劃的目的是為了了解 SQL 查詢或語句在數(shù)據(jù)庫中的執(zhí)行方式和性能表現(xiàn),本文主要介紹了Oracle查看執(zhí)行計(jì)劃的實(shí)現(xiàn),感興趣的可以了解一下2024-02-02
decode()函數(shù)是ORACLE PL/SQL是功能強(qiáng)大的函數(shù)之一，目前還只有ORACLE公司的SQL提供了此函數(shù)，其他數(shù)據(jù)庫廠商的SQL實(shí)現(xiàn)還沒有此功能2013-05-05
這篇文章主要介紹了如何在Oracle?12c中忘記用戶名和密碼時(shí)找回或重置用戶賬戶信息,文中通過代碼介紹的非常詳細(xì),對(duì)同樣遇到這個(gè)問題的同學(xué)具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2025-03-03
Oracle數(shù)據(jù)庫創(chuàng)建本地用戶、授予權(quán)限、創(chuàng)建表并插入數(shù)據(jù)實(shí)例代碼
這篇文章主要介紹了Oracle多租戶架構(gòu)中用戶類型的分類、創(chuàng)建用戶的方法、表空間管理、用戶權(quán)限分配、登錄配置以及腳本創(chuàng)建等操作的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-02-02
Oracle 數(shù)據(jù)庫自動(dòng)存儲(chǔ)管理-安裝配置
關(guān)于ASM的討論很多，但是到底什么是ASM?ASM是一個(gè)有效的抽象層，使你的Oracle數(shù)據(jù)庫可以與叫做diskgroups的抽象空間一起使用，而不是直接使用datafiles。2009-05-05
ROWNUM是一個(gè)序列,是oracle數(shù)據(jù)庫從數(shù)據(jù)文件或緩沖區(qū)中讀取數(shù)據(jù)的順序,這篇文章主要給大家介紹了關(guān)于Oracle使用rownum分頁的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-07-07
Oracle 數(shù)據(jù)顯示 橫表轉(zhuǎn)縱表
橫表轉(zhuǎn)縱表亦可用與decode意義相似的case語句實(shí)現(xiàn)，原理同該語句，這里不再過多描述。2009-07-07
Oracle數(shù)據(jù)庫密碼復(fù)雜度校驗(yàn)?zāi)_本utlpwdmg.sql詳細(xì)解析
我們都知道密碼策略加固的參數(shù)一般包括密碼長(zhǎng)度、復(fù)雜度檢測(cè)、最大最小使用時(shí)間、過期警報(bào)時(shí)間、最大登錄失敗次數(shù)以及鎖定時(shí)間等設(shè)置,下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫密碼復(fù)雜度校驗(yàn)?zāi)_本utlpwdmg.sql詳細(xì)解析的相關(guān)資料,需要的朋友可以參考下2024-04-04
PL/SQL是Oracle數(shù)據(jù)庫的編程語言,用于編寫存儲(chǔ)過程、觸發(fā)器、函數(shù)等,今天用plsql想查看表的屬性,看看各個(gè)字段的注釋,可是打開一看,居然是亂碼的,所以接下來本文給大家介紹了使用PLSQL查看表屬性亂碼的解決方法,需要的朋友可以參考下2024-03-03
Oracle中Union與Union All的區(qū)別(適用多個(gè)數(shù)據(jù)庫)
如果我們需要將兩個(gè)select語句的結(jié)果作為一個(gè)整體顯示出來，我們就需要用到union或者union all關(guān)鍵字。union(或稱為聯(lián)合)的作用是將多個(gè)結(jié)果合并在一起顯示出來2012-07-07