Oracle DBA – A lifelong learning experience

The value of audit_trail=DB,EXTENDED

Posted by John Hallas on July 15, 2014

I was recently reading a blog entry by Dominic Brooks regarding auditing and I was intrigued by the line referring to the audit_trail parameter being set to DB, EXTENDED

Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available.

Nothing wrong with that and straight from the manual but I was surprised that they were CLOBS. However on looking at them they are NVARCHAR2(2000) so CLOB-type. No issues there.

I have long thought that there were 3 benefits of having the extended value set in audit_trail

1)      Adds the DBID to the syslog file when using OS auditing for SYSDBA and SYSOPER activities. No real issue if you only have a single database on the server but if running several databases it is really mandatory otherwise you cannot tell the entries apart.

My research shows that this is no longer true from 11GR2 onwards and the DBID is always written to the syslog file now

2)      To capture the sql binds and text of any statement run as SYSDBA. This is fundamentally the purpose of using a syslog file, otherwise the DBA can do bad things and delete the entries from the AUD$ table. Having the syslog file set to capture that information and not be editable by other than the root user means that a record of all activity is kept.

Doing some testing, again with the jolt provided by Dom’s blog I found that it did not matter whether the audit_trail was DB or DB,EXTENDED, all the activity and values carried out by SYSDBA were written to that file.

From the documentation

Auditing User SYS and Users Who Connect as SYSDBA and SYSOPER
#############################################################
You can fully audit sessions for users who connect as SYS, including all users connecting using the SYSDBA or SYSOPER privileges. This enables you to write the actions of administrative users to an operating system file, even if the AUDIT_TRAIL parameter is set to NONE, DB, or DB, EXTENDED. Writing the actions of administrator users to an operating system audit file is safer than writing to the SYS.AUD$ table, because administrative users can remove rows from this table that indicate their bad behavior.

 

3)      To catch the activities of ‘ordinary’ users and to record the values of their sql statements, provided sufficient auditing has been enabled

A simple example

User AUDIT_TEST owns a table TEST1

 audit update table, delete table, insert table by AUDIT_TEST by access

declare

Begin

For i in 1..5loop

Insert into test1 values ('Peter',i);

End loop;

commit;

End;

/

select sql_bind,sql_text from dba_audit_trail where username='AUDIT_TEST'
 ACTION_NAME SQL_BIND   SQL_TEXT
------------ ---------- ----------------------------------------
INSERT       #1(1):1   INSERT INTO TEST1 VALUES ('Peter',:B1 )
INSERT       #1(1):2   INSERT INTO TEST1 VALUES ('Peter',:B1 )
INSERT       #1(1):3   INSERT INTO TEST1 VALUES ('Peter',:B1 )
INSERT       #1(1):4   INSERT INTO TEST1 VALUES ('Peter',:B1 )
INSERT       #1(1):5   INSERT INTO TEST1 VALUES ('Peter',:B1 )

Setting the audit_trail parameter to DB we do not see the values used which really makes whole exercise of auditing rather pointless.

INSERT
INSERT
INSERT
INSERT
INSERT
INSERT

 

So in summary, 2 out of my 3 reasons for enabling EXTENDED auditing have been blown out of the water. However the remaining one is justification in itself and I see no reason why every production system should not have it enabled.

At my site ENABLED and writing to a  SYSLOG  file is enabled by default as part of the build on every database we have . We have gone a little too far as the DBAs do not have access to even read the file never mind delete entries but as I sit down with external auditors a couple of times a year I do know they are always impressed that I can demonstrate that even with SYSDBA privileges all my activities are securely audited.

About these ads

2 Responses to “The value of audit_trail=DB,EXTENDED”

  1. Dom Brooks said

    Hi John,

    The two columns you mention are CLOBs in SYS.AUD$.
    Whereas in the DBA_AUDIT_TRAIL view, theses are modified as follows:
    to_nchar(substr(sqlbind,1,2000)) /* SQL_BIND */,
    to_nchar(substr(sqltext,1,2000)) /* SQL_TEXT */,

    This is something that tripped me up when investigating my own audit issue whem querying max lengths of sql_text via the view.

    Cheers,
    Dominic.

    • John Hallas said

      Cheers Dom, I must admit I always use the DBA_AUDIT_TRAIL view rather than aud$.
      It is interesting how reading a blog entry on one thing sends you off on a tangent looking at something else. Not the first time it has happened with me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 207 other followers

%d bloggers like this: