db2audit - Audit facility administrator tool command

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.

Authorization

SYSADM

Required Connection

None

Command syntax

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

Usage notes

• Database level auditing is controlled with the AUDIT statement. For additional information, see the Related links section below.
• The instance level audit facility must be stopped and started explicitly. When starting, the audit facility uses existing audit configuration information. Since the audit facility is independent of the DB2 database server, it will remain active even if the instance is stopped. In fact, when the instance is stopped, an audit record may be generated in the audit log.
• Ensure that the audit facility has been turned on by issuing the db2audit start command before using the audit utilities.
• There are different categories of audit records that may be generated. In the description of the categories of events available for auditing (below), you should notice that following the name of each category is a one-word keyword used to identify the category type. The categories of events available for auditing are:
  • Audit (audit). Generates records when audit settings are changed or when the audit log is accessed.
  • Authorization Checking (checking). Generates records during authorization checking of attempts to access or manipulate DB2 database objects or functions.
  • Object Maintenance (objmaint). Generates records when creating or dropping data objects.
  • Security Maintenance (secmaint). Generates records when granting or revoking: object or database privileges, or DBADM authority. Records are also generated when the database manager security configuration parameters sysadm_group,sysctrl_group, or sysmaint_group are modified.
  • System Administration (sysadmin). Generates records when operations requiring SYSADM, SYSMAINT, or SYSCTRL authority are performed.
  • User Validation (validate). Generates records when authenticating users or retrieving system security information.
  • Operation Context (context). Generates records to show the operation context when an instance operation is performed. This category allows for better interpretation of the audit log file. When used with the log's event correlator field, a group of events can be associated back to a single database operation.
  • You can audit failures, successes, both or none.

  For additional information, see the Related links section below.

• Any operation on the instance may generate several records. The actual number of records generated and moved to the audit log depends on the number of categories of events to be recorded as specified by the audit facility configuration. It also depends on whether successes, failures, or both, are audited. For this reason, it is important to be selective of the events to audit.
• To clean up and/or view audit logs, run archive on a regular basis, then run extract on the archived file to save what is useful. The audit logs can then be deleted with standard file system delete commands.