Friday, February 11, 2011

10g & 11g: In new patchsets auditing by session is "obsolete"

We create a table:
CREATE TABLE HELPDESK.AUD_TEST(TEST NUMBER);

We start auditing user helpdesk "insert table" statements by session:
AUDIT INSERT TABLE BY HELPDESK BY SESSION;

We verify it:
SELECT *
  FROM DBA_STMT_AUDIT_OPTS A
 WHERE A.USER_NAME = 'HELPDESK';

  
USER_NAMEPROXY_NAMEAUDIT_OPTIONSUCCESSFAILURE
HELPDESKINSERT TABLEBY SESSIONBY SESSION

We open a sqlplus, connect as helpdesk and insert 6 rows in aud_test table:
SQL> CONN HELPDESK@ECDB1
Enter password: ********
Connected.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> COMMIT;
Commit complete.

We query aud$:
SELECT SESSIONID,
       USERID,
       ACTION#,
       OBJ$CREATOR,
       OBJ$NAME,
       SES$ACTIONS,
       NTIMESTAMP#
  FROM SYS.AUD$
 WHERE USERID = 'HELPDESK';

SESSIONIDUSERIDACTION#OBJ$CREATOROBJ$NAMESES$ACTIONSNTIMESTAMP#
290823972HELPDESK103HELPDESKAUD_TEST------S---------11/2/2011 08:48:17
290823972HELPDESK103HELPDESKAUD_TEST------S---------11/2/2011 08:48:19
290823972HELPDESK103HELPDESKAUD_TEST------S---------11/2/2011 08:48:20
290823972HELPDESK103HELPDESKAUD_TEST------S---------11/2/2011 08:48:21
290823972HELPDESK103HELPDESKAUD_TEST------S---------11/2/2011 08:48:21
290823972HELPDESK103HELPDESKAUD_TEST------S---------11/2/2011 08:47:49

You would expect to see only one row, for the 6 insert statements executed by one session in the same table.
But, instead, we get 6 rows in the AUD$ table, as if we had chosen the "by access" auditing method.
According to Oracle Note 6023472.8:

    Bug 6023472 Collection of changes to auditing

    This issue is fixed in:
    10.2.0.5 (Server Patch Set)
    11.1.0.7 (Server Patch Set)
    11.2.0.1 (Base Release)

    This fix covers several changes to audit behaviour:

    1. The LOGON (action# = 100) audit records are no longer updated. So, when a session ends and if sessions are audited, a LOGOFF (action# = 101) audit record will be written as a separate audit record.
    2. Auditing configured as 'By Session' will no longer produce one audit record per session. Each occurrence of the audit event will result in newer audit records since no audit record is updated.
    3. The index SYS.I_AUD1 on the AUD$ table is dropped since there are no longer any updates to AUD$ with this fix.
    4. FGA_LOG$ will not have PLHOL column.
    5. OS/XML Audit filenames will have a filename format of the form:
    ___.
    6. The default value for Max Audit FileSize is 1000KB and Max Audit FileAge is 1 Day. This means that once one of these max limits are reached, the audit records will be written in a new audit file with same "___" part, but with a new "".

According to (2), auditing by session is, in essence, no longer supported for these patchsets and onwards.

No comments:

Post a Comment