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 following Related links section.

When working in a multiple member database environment, such as a DB2 pureScale® environment or a partitioned database environment, many of the auditable events occur at the database member at which the user is connected (the coordinator member) or at the catalog member (if they are not the same database member). The implication of this is that audit records can be generated by more than one database member. Part of each audit record contains information aboutthe coordinator member and originating database member 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.

The default permissions for the archive log file you specify are read/write for the instance owner only. Do not change these permissions.

When you extract a file, the permissions for the extracted files are read/write for all users. You can specify a file name with the extract command keyword. By using a specific name, you can control where the file is located. To secure the extracted files, you can change the file permissions on the extracted files so read/write permissions are defined only for the instance owner.

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-'          |   
   '-syslog--facility.priority--+-----------+--+-----------------------+-'   
                                '-tag--word-'  '-splitrecordsize--byte-'     

>--+-+---------------------+---------------------------------+-->
   | '-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-'  '-member--+---------------+-'   
                                          '-member-number-'     

>--+------------------+-----------------------------------------|
   '-to--archive-path-'   

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. In a multiple member 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 member 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 quotation marks, they will generally need to be escaped, for example

db2audit configure datapath \"\" 

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

archivepath audit-archive-path

This is the default directory for the archive and extract options. In a multiple member database environment, it is recommended that this directory be an NFS shared directory accessible by all members. 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 member 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.

syslog

Integrates log data from many different types of systems into a central repository. This option is not supported in a Windows environment.

facility.priority

A mandatory parameter required by the syslog daemon (syslogd) to log the messages and must be one of the predefined syslog values. For more information on the predefined values for facility.priority pair and configuration, refer to "Configuring the System event log (syslog)".

It is the user's responsibility to check the syslogd and the configuration file (/etc/syslog.conf) to ensure:

• syslogd is running normally
• Find a selector from (/etc/syslog.conf) that applies to the db2audit command
• Select a proper facility.priority pair for the extract syslog to match the above selector
• Know the db2audit extract syslog's destination in the syslog.conf file

tag word

An optional parameter that allows you to specify a word that will be used as a tag for all the messages in the current batch. The specified word will be added to beginning of all the db2audit messages in the syslog batch. Searching for this word in the operating system's syslog file, you can uniquely identify this whole batch of the syslog messages. The following naming rules apply:

• Must be unique and a single word
• Maximum length cannot exceed 16
• The word can be a combination of letters (a-z or A-Z), numbers (0-9), underscore (_) and dash (-), excluding all other characters

Command example:

db2audit extract syslog user.info tag 131018_B05 ...

splitrecordsize byte

An optional parameter that allows you to specify the maximum length of a log message sent to the operating system's syslog, if a db2audit record is too long. The messages longer than the specified value will be split. The following rule exist for the byte value:

• The value must be a integer value in the range 32 and 8192 (inclusive)

If the message body is greater than the specified byte value then the message will be split and each part of the split message will be concatenated with a correlator string. A correlator string is expressed in the following format:

'corr' + time + db2audit process ID + correlator sequence number, associated with dash '-', then followed by ":#" and a sequence number within that correlation (#n).

Format example:

Oct 18 14:44:39 hotel37 user:info syslog: 131018_B05: corr-1018_170553-33292400-17:#1: This message is 2000 bytes long ... long (split)
Oct 18 14:44:39 hotel37 user:info syslog: 131018_B05: corr-1018_170553-33292400-17:#2: long long ... long ... long (split)
Oct 18 14:44:39 hotel37 user:info syslog: 131018_B05: corr-1018_170553-33292400-17:#3: long long ... long ... long

where 131018_B05 is the tag word, corr is the start of the correlator string, 1018_170553 is the time, 33292400 is the db2audit Process ID, 17 is the correlator sequence number and (#1, #2 & #3) is the sequence within that particular correlation.

Note: The real limit for the message body is around 995 in syslogd of AIX® 6.1 and 7.1. If the byte value is larger than the real limit, all the characters beyond the real limit will be truncated by syslogd. For example, if the byte value is 1200, and the syslogd limit is 995 bytes, 205 characters will be lost due to truncation by syslogd. If you want to log the leading part of every message, the byte value can be as large as 8192.

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.

member

Indicates that the archive command is to only be run on the current member, and that the node_number monitor element will indicate what the current member is.

Note: The use of current member-number is optional in a DB2 pureScale environment and in a partitioned database environment. If db2audit archive .... node command is passed and if DB2NODE is set, the node value will be used. If DB2NODE is not set, 0 will be used.

member-number

Informs the db2audit executable about which member it is currently running on.

Note: The use of current member-number is optional in a DB2 pureScale environment and in a partitioned database environment. If db2audit archive .... node X command is passed, regardless of whether DB2NODE is set or not, the node value (X) will be used.

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.member_number[.YYYYMMDDHHMMSS] for the instance log
• db2audit.db.dbname.log.member_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 member number in a single member database environment is 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 member option is not specified, then the audit log on all members 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 member, and the member option must be specified to indicate on which member archive is to be run (AUD0029).

The archive option will output the result and names of the files from each member 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 multiple member DB2 database instance, auditing will begin for instance and client level activities on all database members 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 multiple member DB2 database instance, auditing is stopped for instance and client level activities on all database members 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 following Related links section.
• 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 following description of the categories of events available for auditing, 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 following Related links section.

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