[20260408]再遇ORA-01031 insufficient privileges报错.txt

[20260408]再遇ORA-01031 insufficient privileges报错.txt

--//以前在访问hist_head$表时遇到1次，今天在访问WRI$_OPTSTAT_HISTHEAD_HISTORY时遇到。还是一样的问题，做一个记录：

1.环境：
SYS@book> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.问题提出：
SYS@book> select * from sys.WRI$_OPTSTAT_HISTHEAD_HISTORY  where rownum=1;
select * from sys.WRI$_OPTSTAT_HISTHEAD_HISTORY  where rownum=1
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

SYS@book> @ descvv sys.WRI$_OPTSTAT_HISTHEAD_HISTORY  "^minimum$|^MAXIMUM$|^lowval$|^HIVAL$"
eXtended describe of sys.WRI$_OPTSTAT_HISTHEAD_HISTORY

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME  <filters>
SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" .


Owner      Table_Name                     Col# Column Name                    Null?      Type
---------- ------------------------------ ---- ------------------------------ ---------- --------------------
SYS        WRI$_OPTSTAT_HISTHEAD_HISTORY     6 MINIMUM                                   NUMBER(,)
                                             7 MAXIMUM                                   NUMBER(,)
                                            10 LOWVAL                                    RAW(1000)
                                            11 HIVAL                                     RAW(1000)

--//还是类似hist_head$表，存在4个这样的字段。

SELECT
 obj#
,intcol#
,savtime
,flags
,null_cnt
--,minimum
--,maximum
,distcnt
,density
--,lowval
--,hival
,avgcln
,sample_distcnt
,sample_size
,timestamp#
,expression
,colname
,savtime_date
,spare1
,spare2
,spare3
,spare4
,spare5
,spare6
,minimum_enc
,maximum_enc
FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

--//只要不访问这4个字段就不存在这个问题。
--//上网查询找到一个类似链接：
https://forums.oracle.com/ords/apexds/post/security-on-table-wri-optstat-histgrm-history-7414

--//Jonathan Lewis也遇到类似的问题，不过并没有找到为什么？
--//对应的表是WRI$_OPTSTAT_HISTGRM_HISTORY，应该是直方图的历史记录。
--//对应的有问题字段是EPVALUE，ENDPOINT，EPVALUE_RAW。
SYS@book> @ descvv sys.WRI$_OPTSTAT_HISTGRM_HISTORY EPVALUE|ENDPOINT|EPVALUE_RAW
eXtended describe of sys.WRI$_OPTSTAT_HISTGRM_HISTORY

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME  <filters>
SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" .

Owner      Table_Name                     Col# Column Name                    Null?      Type
---------- ------------------------------ ---- ------------------------------ ---------- --------------------
SYS        WRI$_OPTSTAT_HISTGRM_HISTORY      5 ENDPOINT                       NOT NULL   NUMBER(,)
                                             6 EPVALUE                                   VARCHAR2(1000)
                                             9 EPVALUE_RAW                               RAW(1000)
                                            17 ENDPOINT_ENC                              RAW(1000)

SYS@book> @ col_list sys.WRI$_OPTSTAT_HISTGRM_HISTORY EPVALUE

SELECT
 epvalue
,epvalue_raw
FROM sys.WRI$_OPTSTAT_HISTGRM_HISTORY


SYS@book> /
FROM sys.WRI$_OPTSTAT_HISTGRM_HISTORY
         *
ERROR at line 4:
ORA-01031: insufficient privileges


SYS@book> @ col_list sys.WRI$_OPTSTAT_HISTGRM_HISTORY EPVALUE_RAW

SELECT
 epvalue_raw
FROM sys.WRI$_OPTSTAT_HISTGRM_HISTORY


SYS@book> /
FROM sys.WRI$_OPTSTAT_HISTGRM_HISTORY
         *
ERROR at line 3:
ORA-01031: insufficient privileges

SYS@book> @ col_list sys.WRI$_OPTSTAT_HISTGRM_HISTORY ENDPOINT$

SELECT
 endpoint
FROM sys.WRI$_OPTSTAT_HISTGRM_HISTORY


SYS@book> /
FROM sys.WRI$_OPTSTAT_HISTGRM_HISTORY
         *
ERROR at line 3:
ORA-01031: insufficient privileges

SYS@book> @ col_list sys.WRI$_OPTSTAT_HISTGRM_HISTORY ENDPOINT_ENC$

SELECT
 endpoint_enc
FROM sys.WRI$_OPTSTAT_HISTGRM_HISTORY
SYS@book> /
no rows selected
--//ENDPOINT_ENC可以显示。

--//看来oracle故意为之，好像对应都是最大，最小，端点值，以及对应的类型raw的值无法显示。不知道如何实现的。

3.带来的问题：
--//实际上这个是不是问题的问题。拿sys.WRI$_OPTSTAT_HISTHEAD_HISTORY表说明：

SYS@book> @ depx sys WRI$_OPTSTAT_HISTHEAD_HISTORY
OWNER            DEPENDENT_NAME                 DEPENDENT_TY REF_OWNER        REF_NAME                       REF_TYPE     HARDSOFT
---------------- ------------------------------ ------------ ---------------- ------------------------------ ------------ --------
SYS              USER_TAB_COL_STATISTICS        VIEW         SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY  TABLE        HARD
SYS              ALL_TAB_COL_STATISTICS         VIEW         SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY  TABLE        HARD
SYS              DBA_TAB_COL_STATISTICS         VIEW         SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY  TABLE        HARD
SYS              ALL_COL_PENDING_STATS          VIEW         SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY  TABLE        HARD
SYS              DBA_COL_PENDING_STATS          VIEW         SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY  TABLE        HARD
SYS              USER_COL_PENDING_STATS         VIEW         SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY  TABLE        HARD
SYS              _user_stat                     VIEW         SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY  TABLE        HARD
SYS              DBMS_STATS_INTERNAL            PACKAGE BODY SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY  TABLE        HARD
8 rows selected.

--//它关联许多视图，问题在于许多开发工具会访问这些视图，前台的执行显示正常不会报错，但是在AUDSYS.AUD$UNIFIED表会留下记录。
--//我开始关注这个问题，就是发现生产系统的AUDSYS.AUD$UNIFIED存在大量类似记录。

--//通过例子说明问题：
SYS@book> @ cnt AUDSYS.AUD$UNIFIED
select count(*) from AUDSYS.AUD$UNIFIED;
COUNT(*)
----------
53

SYS@book> select * from USER_TAB_COL_STATISTICS where rownum=1;
TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE GLO USE
------------------------------ ------------------------------ ------------ ---------- ---------- ---------- ---------- ----------- ------------------- ----------- --- ---
NOTES                                                                                               AVG_COL_LEN HISTOGRAM       SCOPE
--------------------------------------------------------------------------------------------------- ----------- --------------- ------------------------------
ACCESS$                        COLUMNS                                1315 0000000000 FFFFFF0500 .000760456      60872           1 2024-08-10 10:20:37       11003 YES NO
                                                                           0000040000 0000000000
                                                                           0000000000 0000000000
                                                                           0000000000 0000000000
                                                                           0000000000 0000000000
                                                                           0000000000 0000000000
                                                                           0000000000 0000000000
                                                                           0000000000 0000000000
                                                                           0000000000 0000000000
                                                                           0000000000 0000000000
                                                                           0000000000 0000000000
                                                                           0000000000 0000000000
                                                                           00000000   00000000
                                                                                                             21 NONE            SHARED


SYS@book> @ cnt AUDSYS.AUD$UNIFIED
select count(*) from AUDSYS.AUD$UNIFIED;
COUNT(*)
----------
57

--//仅仅访问1次USER_TAB_COL_STATISTICS就触发插入4条记录。

SYS@book> select TABLE_NAME from USER_TAB_COL_STATISTICS where rownum=1;
TABLE_NAME
------------------------------
ACCESS$

SYS@book> @ cnt AUDSYS.AUD$UNIFIED
select count(*) from AUDSYS.AUD$UNIFIED;
COUNT(*)
----------
57
--//如果执行语句不涉及LOW_VALUE，HIGH_VALUE字段就不会触发插入记录，大家可以自行测试。
--//不理解oracle为什么这样设置，想隐藏这些信息实际上一些视图可以查询，除了触发审计的需求外，搞不明白oracle为什么这样设计。