首页 » ORACLE 9i-23ai » The lowercase and quotes table names may not be able to audit(小写表名可能不会审计)
The lowercase and quotes table names may not be able to audit(小写表名可能不会审计)
昨一好朋友问我开了审计，有些表但无审计记录。
下面我来还原这个问题 db_version oracle 10201 for linux
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u1/oracle/admin/ORCL/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB, EXTENDED
audit select any table by access;
SQL> select os_username,username,terminal,timestamp,owner,obj_name,audit_option from DBA_AUDIT_TRAIL order by 4;
OS_USERNAM USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME AUDIT_OPTION
---------- ---------- ---------- ------------------- ------------------------------ ------------------------------ --------------------
oracle TAMS1_0_0 pts/1 2013-01-25 01:47:07 SYS AUD$
SQL> select count(*) from "store_tier_info";
COUNT(*)
----------
40410
SQL> select os_username,username,terminal,timestamp,owner,obj_name,audit_option from DBA_AUDIT_TRAIL order by 4;
OS_USERNAM USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME AUDIT_OPTION
---------- ---------- ---------- ------------------- ------------------------------ ------------------------------ --------------------
oracle TAMS1_0_0 pts/1 2013-01-25 01:47:07 SYS AUD$
SQL> select count(*) from store_tier_info;
select count(*) from store_tier_info
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select os_username,username,terminal,timestamp,owner,obj_name,audit_option from DBA_AUDIT_TRAIL order by 4;
OS_USERNAM USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME AUDIT_OPTION
---------- ---------- ---------- ------------------- ------------------------------ ------------------------------ --------------------
oracle TAMS1_0_0 pts/1 2013-01-25 01:47:07 SYS AUD$
oracle TAMS1_0_0 pts/1 2013-01-25 01:47:54 TAMS1_0_0 STORE_TIER_INFO
TIP:
“store_tier_info” 表名带引号小写审计无记录，并且审计是起作用的
SQL> create table "UP_TAB" (ID INT); Table created. SQL> select * from "UP_TAB"; no rows selected SQL> select username,terminal,timestamp,owner,obj_name from DBA_AUDIT_TRAIL order by 4; USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME ------------------------------ ---------- ------------------- ------------------------------ ---------- TAMS1_0_0 pts/1 2013-01-25 02:04:23 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 01:47:07 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 02:02:55 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 02:08:00 TAMS1_0_0 UP_TAB
TIP:
带引号但是大写表名是被记录的。
SQL> create table "lo_tab"(id int); Table created. SQL> delete sys.aud$; SQL> commit; SQL> select * from "lo_tab"; no rows selected SQL> select username,terminal,timestamp,owner,obj_name from DBA_AUDIT_TRAIL order by 4; USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME ------------------------------ ---------- ------------------- ------------------------------ ------------------------------ TAMS1_0_0 pts/1 2013-01-25 02:53:58 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 01:47:07 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 02:04:23 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 02:02:55 SYS AUD$ SQL> audit select on "lo_tab" by access; Audit succeeded. SQL> select * from "lo_tab"; no rows selected SQL> select username,terminal,timestamp,owner,obj_name from DBA_AUDIT_TRAIL order by 4; USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME ------------------------------ ---------- ------------------- ------------------------------ ------------------------------ TAMS1_0_0 pts/1 2013-01-25 02:53:58 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 02:02:55 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 01:47:07 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 02:04:23 SYS AUD$ TAMS1_0_0 pts/1 2013-01-25 02:56:21 TAMS1_0_0 lo_tab
TIP:
单独指定带引号的小写表名是被记录的
这应该是个BUG，随后在10205,11GR2我做了测试发现小写引号表名已可以正常审计，说明已修复。
如果有感兴趣的可以看看10201上表名有没有得到，从10046 event trace中
方法
---##### session 1######---
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /ora11g/app/oracle/admin/anbob
/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
SQL> audit select any table by access;
Audit succeeded.
SQL> create table anbob."lo_t" (id int);
SQL> insert into anbob."lo_t" values(10);
SQL> commit;
SQL> conn system/oracle
Connected.
SQL> select count(*) from anbob."lo_t";
COUNT(*)
----------
1
SQL> alter session set nls_date_format='RRRR-mm-dd hh24:mi:ss';
Session altered.
SQL> select username, TIMESTAMP,owner,obj_name from dba_audit_trail;
USERNAME TIMESTAMP OWNER OBJ_NAME
------------------------------ ------------------- ------------------------------ ----------
SYSTEM 2013-01-24 04:56:10
SYSTEM 2013-01-24 04:56:18 ANBOB lo_t
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
39 0 0
----####### session 2 #######
sys@ANBOB> select s.username,p.spid from v$session s,v$process p where s.paddr=p.addr and s.sid=39;
USERNAME SPID
------------------------------ ------------------------
SYSTEM 5069
sys@ANBOB> oradebug setospid 5069
Oracle pid: 23, Unix process pid: 5069, image: oracle@ora11 (TNS V1-V3)
sys@ANBOB> oradebug event 10046 trace name context forever,level 12;
Statement processed.
--- ######## session 1 ########
SQL> select count(*) from anbob."lo_t";
COUNT(*)
----------
1
SQL> select username, TIMESTAMP,owner,obj_name from dba_audit_trail;
USERNAME TIMESTAMP OWNER OBJ_NAME
------------------------------ ------------------- ------------------------------ ----------
SYSTEM 2013-01-24 04:56:10
SYSTEM 2013-01-24 04:56:18 ANBOB lo_t
SYSTEM 2013-01-24 05:45:15 ANBOB lo_t
--- ######## session 2 ########
sys@ANBOB> oradebug event 10046 trace name context off;
Statement processed.
sys@ANBOB> oradebug tracefile_name;
/ora11g/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_5069.trc
[oracle@ora11 ~]$ egrep "select|insert|update" /ora11g/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_5069.trc
select count(*) from anbob."lo_t"
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,terminal,action#,returncode, obj$creator,obj$name,auth$privileges,auth$grantee, new$owner,new$name,ses$actions,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,comment$text,spare1,spare2, priv$used,clientid,sessioncpu,proxy$sid,user$guid, instance#,process#,xid,scn,auditid, sqlbind,sqltext,obj$edition,dbid) values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,:12, :13,:14,:15,:16,:17, :18,:19,:20,:21,:22, :23,:24,:25,:26,:27, :28,:29,:30,:31,:32, :33,:34,:35,:36)
select text from view$ where rowid=:1
select username, TIMESTAMP,owner,obj_name from dba_audit_trail
[oracle@ora11 ~]$ vi /ora11g/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_5069.trc
...
Bind#8
oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0
kxsbbbfp=6e94cfda bln=32 avl=05 flg=09
value="ANBOB"
Bind#9
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0
kxsbbbfp=6e94cfc2 bln=32 avl=04 flg=09
value="lo_t"
...
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Summary:
1,规范表名，误用带引号或引号小写
2，在10201版本中存在在audit select any table时，表名小写带””无审计，临时解决方法小写表名指定表名审计.在随后的版本中已修复。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。