How ZDT/Db2 determines whether audit log records should be written

The determination of whether audit records are to be written for a particular ZDT/Db2 function and a given TSO logonid follows this three step process:

  1. Step 1.
    • If auditing is to be controlled by means of parmlib, the HFMAUDIT specification of the HFM2PARM member is used as follows.

      The FMAUDIT specification setting in the HFM2PARM member (in SYS1.PARMLIB or any other library in the logical parmlib concatenation) is the "master" switch for SAF-rule controlled auditing. Note that there are facilities available to specify different settings in the HFM2PARM member for different TSO logonids, see Z Data Tools options specified in PARMLIB members for more information. For any given TSO logonid, there are two possibilities:

      SAF_CTRL=NO
      SAF-rule controlled auditing is not in effect. Auditing is determined by the settings in the HFM2POPT module, see Customizing the audit facility for ZDT/Db2.
      SAF_CTRL=YES
      SAF-rule controlled auditing is in effect. Processing continues to Step 2.
    • If auditing is being controlled using the method which does not access the logical parmlib concatenation, the TSO logonid has READ access to the SAF FACILITY rule FILEM.SAFAUDIT.DB2 for processing to continue to Step 2.
  2. Step 2.

    Does the user have access to write audit records?

    This is determined by the user's access to rules 1 and 2 in Table 1, the various outcomes are summarized in Table 1.

    Table 1. Determination of a user's ability to write audit log records
    TODSN access1 TOSMF access2 OPTION access3 Can write audit records? Demand logging? "Create audit trail" option4
    NONE NONE ANY No No Not visible
    READ NONE NONE Yes, data set only No Not visible
    READ NONE READ Yes, data set only No Visible
    UPDATE NONE NONE Yes, data set only Yes Not visible
    UPDATE NONE READ Yes, data set only Yes Visible
    NONE READ NONE Yes, SMF only No Not visible
    NONE READ READ Yes, SMF only No Visible
    READ READ NONE Yes, to data set and SMF No Not visible
    READ READ READ Yes, to data set and SMF No Visible
    UPDATE READ NONE Yes, to data set and SMF Yes Not visible
    UPDATE READ READ Yes, to data set and SMF Yes Visible
    Note:
    1. Refers to the level of access the user has to SAF FACILITY rule 1 in Table 1.
    2. Refers to the level of access the user has to SAF FACILITY rule 2 in Table 1.
    3. Refers to the level of access the user has to SAF FACILITY rule 3 in Table 1.
    4. The visibility of the "Create audit trail" option does not influence whether a user can write audit log records, although the user must have access to write audit log records (to either a data set or SMF), for the option to be visible.

    If the user does not have the ability to write audit log records, then no check of SAF resource names in Step 3 occurs.

    A user's access to write audit log records at Step 2 only indicates that auditing might occur, the final decision depends on the user's level of access to the XFACILIT resource name (or names) that apply to the particular ZDT/Db2 function.

  3. Step 3.

    Does the user have access to write audit records for the current function?

    The XFACILIT resource names used by ZDT/Db2 to determine whether audit records should be written depend on the ZDT/Db2 function being executed.

    The types of SQL statements and Db2® commands that might be issued by each ZDT/Db2 function are shown in Table 2.

    The relationship between various SQL statements or Db2 commands and XFACILIT resource names is shown in Table 3.

Table 2. Types of SQL (and Db2) statements issued by ZDT/Db2 functions
ZDT/Db2 function Option number SQL/DB2 statements
Browse B SELECT
View 1 SELECT
Edit 2 SELECT, DELETE, INSERT, UPDATE
Print 3.1 SELECT
Db2 Objects 3.2 CREATE, DROP
Copy 3.3

SELECT (source object)
DELETE (target object)
INSERT (target object)
UPDATE (target object)

Object List 3.4 DROP, GRANT, REVOKE, FREE, BIND, REBIND
Object Privileges 3.5 GRANT, REVOKE
Import 3.6 DELETE, INSERT, UPDATE
Export 3.7 SELECT
Create 3.8 INSERT
Basic SELECT prototyping 4.1 SELECT (any editor mode), DELETE, INSERT and UPDATE (only when the Editor option for "Arbitrary SQL Select Statements" is set to edit).
Advanced SELECT prototyping 4.2 SELECT (any editor mode), DELETE, INSERT and UPDATE (only when the Editor option for "Arbitrary SQL Select Statements" is set to edit).
Enter, Execute and Explain SQL 4.3 For SELECT statements as per "Basic SELECT prototyping". Any other SQL statement that can be issued.
Edit/Execute SQL (Data Set) 4.4 For SELECT statements as per "Basic SELECT prototyping". Any other SQL statement that can be issued.
Db2 commands 6

-ACCESS
-ALTER
-ARCHIVE
-CANCEL
-DISPLAY
-MODIFY
-RECOVER
-REFRESH
-RESET
-SET
-START
-STOP
-TERM

Table 3. Relationship between SQL statement type and SAF resource names
SQL Statement Type Audit resource name suffix (see note 1)
ALTER DDL.<object_type>7
COMMENT OTHER.ADHOCSQL
COMMIT OTHER.ADHOCSQL
CREATE DDL.<object_type>7
DELETE UPDATE.ADHOCSQL
DROP DDL.<object_type>7
EXCHANGE OTHER.ADHOCSQL
EXPLAIN OTHER.ADHOCSQL
GRANT AUTH.<auth_type>8
INSERT UPDATE.ADHOCSQL
LABEL OTHER.ADHOCSQL
LOCK OTHER.ADHOCSQL
MERGE OTHER.ADHOCSQL
REFRESH OTHER.ADHOCSQL
RENAME DDL.<object_type>7
REVOKE AUTH.<auth_type>8
ROLLBACK OTHER.ADHOCSQL
SELECT

READ.<object>2
UPDATE.<object>3
READ.ADHOCSQL4,6
UPDATE.ADHOCSQL5,6

SET OTHER.ADHOCSQL
TRUNCATE UPDATE.ADHOCSQL
UPDATE UPDATE.ADHOCSQL
Note:
  1. The prefix for all resource names in this table is FILEM.AUDIT.<ssid>, where ssid is the Db2 subsystem or group ID.
  2. This resource name is used when processing a Db2 object via options B and 1.
  3. This resource name is used when processing a Db2 object using option 2.
  4. This resource name is used when processing a Db2 object (or objects) using a SELECT statement entered via options 4.1, 4.2, 4.3 or 4.4, when ZDT/Db2 browse or view is used to display the result table for the SELECT.
  5. This resource name is used when processing a Db2 object (or objects) using a SELECT statement entered via options 4.1, 4.2, 4.3 or 4.4, when ZDT/Db2 edit is used to display the result table for the SELECT.
  6. The choice of ZDT/Db2 editor mode (browse, view or edit) is determined by the setting of the "Arbitrary SQL Select Statements", Editor option, which can be found on the second ZDT/Db2 system options panel. This is accessed by typing 0.0.2 from the ZDT/Db2 main menu.
  7. See Table 3.
  8. See Table 4.

For some ZDT/Db2 functions only a single SAF XFACILIT rule needs to be checked to determine whether audit log records should be written. An example is the ZDT/Db2 editor, which processes a single Db2 object in either READ or UPDATE modes.

For other ZDT/Db2 functions multiple SAF XFACILIT rules may be checked.

Example 1: ZDT/Db2 Copy utility

Example 2: ZDT/Db2 Edit/Execute SQL (Data Set) utility

This utility allows the execution of SQL statements coded in a data set. ZDT/Db2 checks the access to write audit records for each SQL statement in the data set as it is executed and audit records are written (or not) as appropriate.