DB2 Version 9.7 for Linux, UNIX, and Windows

db2audit - Audit facility administrator tool command

DB2® database systems provide an audit facility to assist in the detection of unknown or unanticipated access to data. The DB2 audit facility generates and permits the maintenance of an audit trail for a series of predefined database events.

The records generated from this facility are kept in audit log files. The analysis of these records can reveal usage patterns which would identify system misuse. Once identified, actions can be taken to reduce or eliminate such system misuse. The audit facility acts at both the instance and database levels, independently recording all activities in separate logs based on either the instance or the database.

DB2 database systems provide the ability to independently audit at both the instance and at the individual database level. The db2audit tool is used to configure audit at the instance level as well as control when such audit information is collected. The AUDIT SQL statement is used to configure and control the audit requirements for an individual database. The db2audit tool can be used to archive both instance and database audit logs as well as to extract from archived logs of either type. For additional information, see the Related links section below.

When working in a partitioned database environment, many of the auditable events occur at the database partition at which the user is connected (the coordinator partition) or at the catalog partition (if they are not the same database partition). The implication of this is that audit records can be generated by more than one database partition. Part of each audit record contains information on the coordinator partition and originating database partition identifiers.

The instance audit log (db2audit.instance.log.node_number[.timestamp]) is located in the instance's security/auditdata subdirectory, and the audit configuration file (db2audit.cfg) is located in the instance's security subdirectory. The database audit log is named db2audit.db.dbname.log.node_number[.timestamp]. At the time you create an instance, read/write permissions are set on these files, where possible, by the operating system. By default, the permissions are read/write for the instance owner only. It is recommended that you do not change these permissions.

Authorized users of the audit facility can control the following actions within the audit facility, using db2audit:
  • Start recording auditable events within the DB2 instance. This does not include database level activities.
  • Stop recording auditable events within the DB2 instance.
  • Configure the behavior of the audit facility at the instance level only.
  • Select the categories of the auditable events to be recorded at the instance level only.
  • Request a description of the current audit configuration for the instance.
  • Flush any pending audit records from the instance and write them to the audit log.
  • Archive audit records from the current audit log for either the instance or a database under the instance.
  • Extract audit records from an archived audit log by formatting and copying them to a flat file or ASCII delimited file. Extraction is done in preparation for analysis of log records.

Authorization

SYSADM

Required Connection

None

Command syntax

Read syntax diagramSkip visual syntax diagram
>>-db2audit--+-configure--+-reset-------------------+-+--------><
             |            '-| Audit Configuration |-' |   
             +-describe-------------------------------+   
             +-extract--| Audit Extraction |----------+   
             +-flush----------------------------------+   
             +-archive--| Audit Log Archive |---------+   
             +-start----------------------------------+   
             +-stop-----------------------------------+   
             '-?--------------------------------------'   

Audit Configuration

|--+--------------------------------------------------+--------->
   |        .-,-------------------------------------. |   
   |        V                                       | |   
   '-scope------+-all------+--status--+-both----+---+-'   
                +-audit----+          +-none----+         
                +-checking-+          +-failure-+         
                +-context--+          '-success-'         
                +-objmaint-+                              
                +-secmaint-+                              
                +-sysadmin-+                              
                '-validate-'                              

>--+-----------------------+--+---------------------------+----->
   '-errortype--+-audit--+-'  '-datapath--audit-data-path-'   
                '-normal-'                                    

>--+---------------------------------+--------------------------|
   '-archivepath--audit-archive-path-'   

Audit Extraction

   .-file--output-file------------------------------------------.   
|--+------------------------------------------------------------+-->
   '-delasc--+---------------------------+--+-----------------+-'   
             '-delimiter--load-delimiter-'  '-to--delasc-path-'     

>--+-+---------------------+---------------------------------+-->
   | '-status--+-failure-+-'                                 |   
   |           '-success-'                                   |   
   |           .-,-----------------------------------------. |   
   |           V                                           | |   
   '-category------+-audit----+--+---------------------+---+-'   
                   +-checking-+  '-status--+-failure-+-'         
                   +-context--+            '-success-'           
                   +-execute--+                                  
                   +-objmaint-+                                  
                   +-secmaint-+                                  
                   +-sysadmin-+                                  
                   '-validate-'                                  

>--from--+--------------------+--files--input-log-files---------|
         '-path--archive-path-'                           

Audit Log Archive

|--+-------------------------+---------------------------------->
   '-database--database-name-'   

>--+-------------------------------+--+------------------+------|
   '-node--+---------------------+-'  '-to--archive-path-'   
           '-current-node-number-'                           

Command parameters

configure
This parameter allows the modification of the db2audit.cfg configuration file in the instance's security subdirectory. Updates to this file can occur even when the instance is stopped. Updates, occurring when the instance is active, dynamically affect the auditing being done by the DB2 instance. The configure action on the configuration file causes the creation of an audit record if the audit facility has been started and the audit category of auditable events is being audited. All configure options, except the data path and archive path, only apply to instance level audit events, and not to database level audit events. The path options apply to the instance and all databases within the instance.
The following are the possible actions on the configuration file:
reset
This action causes the configuration file to revert to the initial configuration (where scope is all of the categories except context, status for each category is failure, errortype is normal, and the auditing of instance level events is off). This action will create a new audit configuration file if the original has been lost or damaged. The audit data path and archive path will be blank. This option does not reset any of the audit policies or use of those policies at the database level.
scope
This action specifies which categories will be audited, and the status of each of those categories.
status
This action specifies whether only successful or failing events, or both successful and failing events, should be logged. status has the following options:
both
Successful and failing events will be audited.
none
No events for this category will be audited.
failure
Only failing events will be audited.
success
Only successful events will be audited.
Only the categories specified on the configure statement will be modified. All other categories will have their status preserved.
Note:
  • The default scope is all categories except context and may result in records being generated rapidly. In conjunction with the mode (synchronous or asynchronous), the selection of the categories may result in a significant performance reduction and significantly increased disk requirements. It is recommended that the number and type of events being logged be limited as much as possible, otherwise the size of the audit log will grow rapidly. This action also allows a particular focus for auditing and reduces the growth of the log.
  • context events occur before the status of an operation is known. Therefore, such events are logged regardless of the value associated with this parameter, unless the status is none.
  • If the same category is repeated, or categories are also specified with the all keyword, a syntax error will be returned.
errortype
This action specifies whether audit errors are returned to the user or are ignored. The value for this parameter can be:
audit
All errors including errors occurring within the audit facility are managed by DB2 database and all negative SQLCODEs are reported back to the caller.
normal
Any errors generated by db2audit are ignored and only the SQLCODEs for the errors associated with the operation being performed are returned to the application.
datapath audit-data-path
This is the directory to which the audit logs produced by the DB2 database system will be written. The default is sqllib/security/auditdata (instance path\instance\security\auditdata on Windows). This parameter affects all auditing within an instance, including database level auditing. This must be a fully qualified path and not a relative path. The instance owner must have write permission on this directory. On Windows, the user issuing a local instance command, for example, db2start, db2audit, and db2 update dbm cfg, must also have write permission on this directory if the command is required to be audited. On a partitioned database environment, this directory does not need to be an NFS shared directory, although that is possible. A non-shared directory will result in increased performance as each node is writing to a unique disk. The maximum length of the path is 971 bytes for UNIX or Linux and 208 bytes for Windows operating systems.
If the path is provided as "", then the path will be updated to be the default. db2audit describe will show no path as being set and the default path will be used. Note, to prevent the shell from interpreting the quotes, they will generally need to be escaped, for example
db2audit configure datapath \"\" 

The data path must exist. In a partitioned database environment, the same data path will be used on each node. There is no way to specify a unique set of data paths for a particular node unless database partition expressions are used as part of the data path name. Doing this allows the node number to be reflected in the storage path such that the resulting path name is different on each database partition. See "Automatic storage databases" for information about database partition expressions.

archivepath audit-archive-path
This is the default directory for the archive and extract options. In a partitioned database environment, it is recommended that this directory be an NFS shared directory accessible by all nodes. The default is sqllib/security/auditdata (sqllib\instance\security\auditdata on Windows). This must be a fully qualified path and not a relative path. The instance owner must have write permission on this directory. The maximum length of the path is 971 bytes for UNIX or Linux and 208 bytes for Windows operating systems.

The archive path must exist, and database partition expressions are NOT allowed for the archive path.

describe
This parameter displays to standard output the current instance level audit configuration information and status.
The following items are displayed:
  • If audit is active.
  • The status for each category.
  • The error type in the form of whether or not an SQLCA is returned on errors.
  • The data and archive paths.

This is an example of what the describe output looks like:

DB2 AUDIT SETTINGS:

Audit active: "FALSE "
Log audit events: "SUCCESS"
Log checking events: "FAILURE"
Log object maintenance events: "BOTH"
Log security maintenance events: "BOTH "
Log system administrator events: "NONE"
Log validate events: "FAILURE"
Log context events: "NONE"
Return SQLCA on audit error: "TRUE "
Audit Data Path: "/auditdata"
Audit Archive Path: "/auditarchive"

AUD0000I  Operation succeeded.
extract
This parameter allows the movement of audit records from the audit log to an indicated destination. The audit log will be created in the database code page. All of the fields will be converted to the current application code page when extract is run.
The following are the options that can be used when extracting:
file output-file
The extracted audit records are placed in output-file. If the directory is not specified, output-file is written to the current working directory. If the file already exists the output will be appended to it. If a file name is not specified, records are written to the db2audit.out file in the archive path specified in the audit configuration file.
delasc
The extracted audit records are placed in a delimited ASCII format suitable for loading into DB2 database relational tables. The output is placed in separate files, one for each category. In addition, the file auditlobs will also be created to hold any lobs that are included in the audit data. The filenames are:
  • audit.del
  • checking.del
  • objmaint.del
  • secmaint.del
  • sysadmin.del
  • validate.del
  • context.del
  • execute.del
  • auditlobs

If the files already exist the output will be appended to them. The auditlobs file will be created if the context or execute categories are extracted. LOB Location Specifiers are included in the .del files to reference the LOBS in the auditlobs file.

delimiter load-delimiter
Allows you to override the default audit character string delimiter, which is the double quote ("), when extracting from the audit log. You would use delimiter followed by the new delimiter that you want to use in preparation for loading into a table that will hold the audit records. The new load delimiter can be either a single character (such as !) or a four-character string representing a hexadecimal number (such as 0x3b).
to delasc-path
Allows you to specify the path to which the delimited files are written. If it is not specified, then the files are written to the directory indicated by the audit archive path option specified in the audit configuration file.
category
The audit records for the specified categories of audit events are to be extracted. If not specified, all categories are eligible for extraction.
status
The audit records for the specified status are to be extracted. If not specified, all records are eligible for extraction.
path
The path to the location of the archived audit logs. If this is not specified, the archive path in the audit configuration will be used. The path is not used if the filename contains a fully qualified path.
files
The list of audit log files that will be extracted. This may be a single file or a list of files. These files are not altered during an extract. The filenames will be combined with path to get the fully qualified filenames if they are not already fully qualified. The list may included standard shell wild cards to specify multiple files.
flush
This parameter forces any pending audit records to be written to the audit log. Also, the audit state is reset from "unable to log" to a state of "ready to log" if the audit facility is in an error state.
archive
This parameter moves the current audit log for either an individual database or the instance to a new location for archiving and later extraction. The current timestamp will be appended to the filename. All records that are currently being written to the audit log will complete before the log is archived to ensure full records are not split apart. All records that are created while the archive is in progress will be written to the current audit log, and not the archived log, once the archive has finished.
The following are the options that can be used when archiving:
database database-name
The name of the database for which you would like to archive the audit log. If the database name is not supplied, then the instance level audit log is archived.
node
Indicates that the archive command is to only be run on the current node, and that the node_number monitor element will indicate what the current node is. This is only required on a partitioned database environment.
current-node-number
Informs the db2audit executable about which node it is currently running on. This parameter is required if the DB2NODE environment variable does not contain the current node.
to archive-path
The directory where the archived audit log should be created. The directory must exist and the instance owner must have create permission on this directory. If this is not provided, the archive path in the audit configuration will be used.
The format of the filename that is created is:
  • db2audit.instance.log.node_number[.YYYYMMDDHHMMSS] for the instance log
  • db2audit.db.dbname.log.node_number[.YYYYMMDDHHMMSS] for the database log

where YYYY is the year, MM is the month, DD is the day, HH is the hour, MM is the minute, and SS is the seconds. The time will be the local time. The database name portion will not be present for instance audit logs. The node number in a non-partitioned database environment will be 0. If the file already exists, an append will be performed.

The timestamp will not reflect the last record in the log with 100% accuracy. The timestamp represents when the archive command was run. Entries that are currently being written to the log file must finish before it can be moved, and these entries may have timestamps that are later than the timestamp given to the filename.

If the node option is not specified, then the audit log on all nodes will be archived. The database server must be started in this case. If the database server has not been started, then archive must be run on each node, and the node option must be specified to indicate on which node archive is to be run (AUD0029).

The archive option will output the result and names of the files from each node that archive was run on.

start
This parameter causes the audit facility to begin auditing events based on the contents of the db2audit.cfg file for the instance only. In a partitioned DB2 database instance, auditing will begin for instance and client level activities on all database partitions when this clause is specified. If the audit category of events has been specified for auditing, then an audit record will be logged when the audit facility is started. This has no effect on database level auditing, which is controlled through the AUDIT DDL statement.
stop
This parameter causes the audit facility to stop auditing events for the instance only. In a partitioned DB2 database instance, auditing will be stopped for instance and client level activities on all database partitions when this clause is specified. If the audit category of events has been specified for auditing, then an audit record will be logged when the audit facility is stopped. This has no effect on database level auditing, which is controlled through the AUDIT DDL statement.
?
This parameter displays the help information for the db2audit command.

Examples

This is a typical example of how to archive and extract a delimited ASCII file in a partitioned database environment. The Windows remove (rm) command deletes the old delimited ASCII files.
rm /auditdelasc/*.del
db2audit flush
db2audit archive database mydb to /auditarchive
(files will be indicated for use in next step)
db2audit extract delasc to /auditdelasc from files /auditarchive
/db2audit.db.mydb.log.*.20070514102856
Load the .del files into a DB2 table.

Usage notes