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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

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


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.



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 



















Thanks for your registration, follow us on our social networks to keep up-to-date