Audit by access VS by session

ENV

[oracle@daidai ~]$ uname -aLinux daidai.com 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux[oracle@daidai ~]$ lsb_release -aLSB Version:    :core-3.1-amd64:core-3.1-ia32:core-3.1-noarch:graphics-3.1-amd64:graphics-3.1-ia32:graphics-3.1-noarchDistributor ID: RedHatEnterpriseServerDescription:    Red Hat Enterprise Linux Server release 5.5 (Tikanga)Release:        5.5Codename:       TikangaSQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - Production

Audit by access

SQL> show parameter auditNAME                                 TYPE                           VALUE------------------------------------ ------------------------------ ------------------------------audit_file_dest                      string                         /u01/oracle/admin/ocp11g/adumpaudit_sys_operations                 boolean                        TRUEaudit_syslog_level                   stringaudit_trail                          string                         DB, EXTENDEDSQL> audit select on daidai.t by access;Audit succeeded.SQL> select * from DBA_OBJ_AUDIT_OPTS;OWNER                          OBJECT_NAME                    OBJECT_TYPE             ALT   AUD   COM   DEL   GRA   IND   INS   LOC   REN   SEL------------------------------ ------------------------------ ----------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----UPD   REF EXE   CRE   REA   WRI   FBK----- --- ----- ----- ----- ----- -----DAIDAI                         T                              TABLE                   -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   A/A-/-   -/- -/-   -/-   -/-   -/-   -/-SQL> select * from t;   --conn daidai user,执行两次

select * from dba_common_audit_trail where object_schema='DAIDAI' and object_name='T' and audit_type='Standard Audit' order by extended_timestamp desc;

audit by access产生两条记录。

Audit by Session

SQL> audit select on daidai.t by session;Audit succeeded.SQL> select * from DBA_OBJ_AUDIT_OPTS;OWNER                          OBJECT_NAME                    OBJECT_TYPE             ALT   AUD   COM   DEL   GRA   IND   INS   LOC   REN   SEL------------------------------ ------------------------------ ----------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----UPD   REF EXE   CRE   REA   WRI   FBK----- --- ----- ----- ----- ----- -----DAIDAI                         T                              TABLE                   -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   S/S-/-   -/- -/-   -/-   -/-   -/-   -/-SQL> truncate table aud$;    --sys userTable truncated.

SQL> select * from t;   --conn daidai user,执行两次select * from dba_common_audit_trail where object_schema='DAIDAI' and object_name='T' and audit_type='Standard Audit' order by extended_timestamp desc;

同样audit by session产生两条记录。

Audit 缺省情况

SQL> select * from dba_obj_audit_opts;no rows selectedSQL> audit select on daidai.t;Audit succeeded.SQL> select * from dba_obj_audit_opts;OWNER                          OBJECT_NAME                    OBJECT_TYPE             ALT   AUD   COM   DEL   GRA   IND   INS   LOC   REN   SEL------------------------------ ------------------------------ ----------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----UPD   REF EXE   CRE   REA   WRI   FBK----- --- ----- ----- ----- ----- -----DAIDAI                         T                              TABLE                   -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   S/S-/-   -/- -/-   -/-   -/-   -/-   -/-

Audit 缺省是by session

综上，针对同一SQL在同一SESSION中，Access & Session都会产生多条记录，并且audit缺省是by session。