Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 9, 2004

DB2 Tracking Database Changes - Page 2

By Marin Komadina

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

 -> "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


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
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.

DB2 Archives