首页 » ORACLE » 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时,表名小写带””无审计,临时解决方法小写表名指定表名审计.在随后的版本中已修复。

打赏

对不起,这篇文章暂时关闭评论。