• SQL/AF performs its auditing functions in the database server. Therefore, it is capable of auditing:
• all DB2/VSE clients (including those that connect using DRDA)
• interactive and compiled program access
• SQL/AF audits all access to the tables defined as auditing candidates in the SQL/AF RULES dataset. Depending on the auditing rules defined, read (SQL SELECT) and write (SQL DELETE, INSERT and UPDATE) access is monitored.
• By default, auditing occurs at the table level and SQL statements are recorded, whenever they access the table. Alternatively, one or more table-columns can be defined in the RULES. Auditing then occurs when an SQL statement refers to one of the named columns.

For each access to an audited table or table-column, the Audit Processor writes a record to a file, called the SQL/AF audit log. An audit record contains:

• the context of statement execution (date, time, program name, user name, terminal name)
• the text of the SQL statement as executed by DB2/VSE. To achieve this, statement variables are replaced with their contents and additional transformations are carried out when needed (for example, when views are used to access audited tables).

The SQL/AF archiving function transfers the audit log to cartridge or tape, so that auditing results can be kept for a longer period of time. Archiving must be scheduled explicitly. Archiving does not disrupt the auditing process.

Inspecting the Audit Log

A part of the SQL/AF user interface, the Logscan program interactively searches the audit log or an audit archive tape for specific audit events. When performing the log scan, the user can formulate following search criteria:

One or more audit record fields

This provides for scan requests such as:

• Search all accesses made by a named user to a named table during a specified period.
• Search all updates made by a named program to a named table on a specified date.

Table column names used in the text of an audited SQL statement

This scan method selects statements that reference a named table-column, for example:

• Search all statements that selected the column CONFIDENTIAL_INFO in the employee table.
• Search all changes to the column BALANCE in the customer table, performed by a named user on a given date.

Table column values used in the text of an audited SQL statement

This scan method selects statements that reference a named table-column with a specified value. It can be used to trace all audit events for a given table "key", for example:

• Search all accesses made to the EMPLOYEE table for EMPNO = 100 during a specified period.
• Search all updates that made the column BALANCE in the customer table negative.

Benefits

Centralized auditing as implemented by SQL/AF offers the following benefits:

• SQL/AF controls all table access. It monitors accesses from both compiled applications and dynamic query environments.
• Auditing is done for all clients of DB2/VSE and other database platforms that can connect to DB2/VSE. In environments that integrate mainframe and PC applications, the security aspect of PC access to DB2/VSE tables can be controlled entirely using SQL/AF.
• The SQL/AF auditing rules are easy to implement, they can be modified at any time and they take immediate effect.
• The auditing rules provide for very granular auditing, up to the table column level.
• Auditing has no impact on application program coding.
• SQL/AF avoids the cost and the additional quality assurance of application implemented auditing procedures.
• SQL/AF stores its audit events in the form of SQL statements. Since the SQL/AF archives are regular VSE tapes, they can be exploited easily by user procedures. Moreover, the historical access information contained in the logs, may be valuable input for new or existing business applications.