Server Administration :: Record Audit Info About Sql Statement Run By User

I am trying to record audit info about sql statement run by user (only one audit entry per specific type of operation such as create table, or insert table). Such as if a user create three tables, but database record only one entry of create table type per session.

I am giving you all the statement I issued...

SQL> create user saimon identified by abc1;

User created.

SQL> grant connect, resource to saimon;

Grant succeeded.

SQL> audit table, insert table by saimon by session;

Audit succeeded.

SQL> show parameter audit

NAME TYPE VALUE
-------------------- ----------- -------------
audit_file_dest string /u01/app/oracle/admin/orcl/adum
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DBSQL>

[oracle@DBTEST ~]$ sqlplus saimon/abc1

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 21:45:09 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

SQL> create table TB1 (id number, name varchar2(20));

Table created.

SQL> create table TB3 (id number, name varchar2(20));

Table created.

SQL> select USERNAME, TERMINAL, OS_USERNAME, USERHOST, ACTION, action_name, OBJ_NAMe
2 from user_audit_trail;

USERNAME TERMINAL OS_USERNAM USERHOST ACTION ACTION_NAME OBJ_NAME
---------- ---------- ---------- ----------------- ----------------------- ----------
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB3
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB1
conn / as sysdba

Now my question is I have enabled statement auditing for session not by access. So only one audit entry should have been recorded for two table creation. Why database is recording every create statement?

SQL> show user
USER is "SYS"

SQL> SELECT audit_option, failure, success, user_name
2 FROM dba_stmt_audit_opts;

AUDIT_OPTION FAILURE SUCCESS USER_NAME
----------------------------------- ---------- ---------- ------------------------------
TABLE BY SESSION BY SESSION SAIMON
INSERT TABLE BY SESSION BY SESSION SAIMON