index$_join$ or Other Internal Table Appears in Audit Trail Instead of Base Table. (Doc ID 955968.1)

最新推荐文章于 2021-11-20 20:47:28 发布

mybluetiankong

最新推荐文章于 2021-11-20 20:47:28 发布

阅读量1.5k

点赞数

分类专栏：网络资源_ORACLE_调优

网络资源_ORACLE_调优专栏收录该内容

109 篇文章 0 订阅

订阅专栏

 
 In this Document
   Symptoms
   Cause
   Solution
   References
 
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
 APPLIES TO: Oracle Server - Enterprise Edition - Version: 10.2.0.4
 This problem can occur on any platform.
 SYMPTOMSWhen checking the audit trail (DBA_AUDIT_TRAIL or AUD$), the object name on which the statement is supposedly run is index$_join$_xxx or similar temporary objects (eg: from$_subquery$) instead of the actual object.
 Reproducible testcase:
 
      conn / as sysdba 
 truncate table aud$; 
 
 drop user test cascade; 
 
 create user test identified by test; 
 grant connect,resource to test; 
 audit select table by session; 
 
 connect test/test 
 
 create table test.emp 
 (empno number(4,0), 
 ename varchar2(10), 
 job varchar2(9), 
 mgr number(4,0), 
 hiredate date, 
 sal number(7,2), 
 comm number(7,2), 
 deptno number(2,0), 
 constraint pk_emp primary key (empno); 
 ); 
 
 create index emp_mgr_idx on emp (mgr); 
 create index emp_dptno_idx on emp (deptno); 
 
 connect test/test 
 
 select /*+ index_join(E EMP_MGR_IDX EMP_DPTNO_IDX) */ DEPTNO 
 from test.EMP E 
 where MGR < 110 
 and DEPTNO < 50; 
 
 CONN / AS SYSDBA 
 set linesize 150 
 set pagesize 300 
 column owner format a10 
 column username format a10 
 column obj_name format a20 
 column sql_text format a70 
 select username,owner,obj_name,sql_text from dba_audit_trail where username='TEST'; 
 
 USERNAME OWNER  OBJ_NAME         SQL_TEXT 
 -------- ------ ---------------  -------------------------------------------
 TEST     TEST   index$_join$_001 SELECT /*+ INDEX_JOIN 
                                  (e emp_mgr_idx emp_dptno_idx) */ deptno 
                                  TEST.emp e 
                                  mgr < 110 
                                  deptno < 50 
     

  
 CAUSEthe problem is caused by unpublished 
Bug 7196409 INTERNAL TABLE IS OUTPUT TO AUDIT RECORD USING INDEX JOIN
 
 
 SOLUTIONThe bug is permanently fixed in the 11.2 release. 
 Backport requests can be created for previous releases, ie 10.2.0.4 and 11.1.0.7. 
 
 REFERENCESBUG:9002241 - AUDIT TRAIL DISPLAYS INDEX$_JOIN$_001 INSTEAD OF BASE TABLE.

 

   
            Related 
          
              Products 
            
       Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Security
 
              Keywords 
            
     AUDIT; 
      
     AUDIT TRAIL; 
      
     DBA_AUDIT_TRAIL