DB2 Audit Management Tool
The DB2 audit facility is completely managed and
controlled by the DB2 system command, "db2audit." This command has
several options and parameters. The four main groups are:
-
audit command
-
audit
configuration
-
error handling
-
audit
extraction
db2audit->
-> "audit command" ->
-> "audit configuration" (scope, status) ->
-> "error handling" (errortype) ->
-> "audit extraction" (formatted data)
Listing 3: db2audit parameter groups
Audit command
The audit command has the following options:
|>- db2audit |- configure - audit configuration change
|- describe - current audit settings
|- extract - extracting audit data from audit file
|- flush - flushing audit data from memory to the audit file
|- prune - clean audit data from audit file
|- start - start audit session
|- stop - stop audit session
Listing 4: db2audit - "audit command" option
IBM has provided a variety of options for controlling the
instance audit facility, leaving no reason to manually hack the instance audit
log.
Audit configuration (Scope)
A scope option is used to limit DB2 instance
audit data collection. It is very important to plan the audit scope carefully, before implementation, to prevent overloading
the system and database. IBM provides the following audit scopes (categories):
|- scope --- all ---------| |- status --- both ----|
| V- , ----| | |- success -|
|--- audit -----| |- failure -|
|--- checking --|
|--- objmaint --|
|--- secmaint --|
|--- sysadmin --|
|--- validate --|
|--- context -|
Listing 5: db2audit - "audit configuration" option
a.) ALL scope - includes all scopes (AUDIT, CHECKING,
OBJMAINT, SECMAINT, SYSADMIN, VALIDATE, CONTEX)
b.) AUDIT scope - includes changes in auditing
configuration and audit log administration
c.) CHECKING scope - includes events corresponding to
authority checking
d.) OBJMAINT (object maintenance) scope - includes the
creation and deletion of DB2 objects and databases
e.) SECMAINT (security maintenance) scope - includes
changes in database security (GRANT, REVOKE...)
f.) SYSADMIN (sysadmin events) scope - includes changes
with SYSADM authority
g.) VALIDATE (validate events) scope - includes user validation
processing
h.) CONTEXT (context events) scope - includes SQL
statements and operational context
>> db2audit describe
DB2 AUDIT SETTINGS:
Audit active: "FALSE "
Log errors: "TRUE "
Log success: "FALSE "
Log audit events: "TRUE "
Log checking events: "TRUE "
Log object maintenance events: "TRUE "
Log security maintenance events: "TRUE "
Log system administrator events: "TRUE "
Log validate events: "TRUE "
Log context events: "FALSE "
Return SQLCA on audit error: "FALSE "
AUD0000I Operation succeeded.
Listing 6: db2audit default audit configuration
The default scope includes all categories
except the CONTEXT category. For high level auditing, CONTEXT would be
used to get a complete trace of
the executed command.
Error handling
>----------------------------|
|- errortype --- audit --|
|- normal -|
Listing 7: db2audit - "error handlig" option
The ERRORTYPE parameter defines
errors that either are returned to the user or are
ignored. The following
options are defined for the ERRORTYPE option:
AUDIT - the transaction
will succeed only if the appropriate audit record is written to the audit log.
NORMAL - the transaction will succeed regardless
of the audit status, e.g. the application will continue with normal processing
and programmatically defined termination.
Audit Extraction
Audit Extraction:
|----------------------------------------------------------------------->
|- file FILE NAME --------------------| | V- , ------| |
|- delasc ----------------------------| |- category --- audit -----|
|- delimiter LOAD DELIMITER-| |- checking --|
|- objmaint --|
|- secmaint --|
|- sysadmin --|
|- validate --|
|- context -|
Listing 8: db2audit - "audit extraction" option
The instance audit records can be extracted in different
formats from the audit log. By default, extraction creates separate files, with
predefined names, for each audit category, regardless of the actual audit
settings.
Example of the generated files after extraction:
>> db2audit extract delasc
AUD0000I Operation succeeded.
$ ls -lrt | grep *.del
-rw-rw-rw- 1 db2inst1 db2admin 11403 Oct 7 18:50 validate.del
-rw-rw-rw- 1 db2inst1 db2admin 0 Oct 7 18:50 sysadmin.del
-rw-rw-rw- 1 db2inst1 db2admin 0 Oct 7 18:50 secmaint.del
-rw-rw-rw- 1 db2inst1 db2admin 0 Oct 7 18:50 objmaint.del
-rw-rw-rw- 1 db2inst1 db2admin 0 Oct 7 18:50 context.del
-rw-rw-rw- 1 db2inst1 db2admin 0 Oct 7 18:50 checking.del
-rw-rw-rw- 1 db2inst1 db2admin 0 Oct 7 18:50 audit.del
Listing 9: Audit data extracted in the ASCII format
In this example, the audit facility has been activated
only for collecting "validate type" audit records.
» 