DBARS: Recording Access to Sensitive DB2 Tables

INTRODUCTION

Most corporations maintain sensitive data that are confidential or vital to corporate operations. In today’s distributed IT environments, these data need special protection. As the initial  security measure, DB2 privileges are granted to users who are allowed to access these data. As a further measure, all access to sensitive data should be recorded, to verify that accesses are made for legitimate reasons and not for trivial or dishonest motives.

Today, there is a growing concern (and legislation) about data privacy. Databases often contain confidential information about individuals. This information has been entrusted by the individual to the corporation, in the expectation that the information will be handled with respect to personal privacy.Legislations may state that companies owning personal information, implement procedures to ensure that personal data are accessed by legitimate sources only. Legislation may also impose that accesses to confidential information can be retraced whenever needed.  Once table privileges have been granted to a user, that user has access to the entire table. For reasons of security and privacy, it is necessary to control data access at the table-row level. It is therefore advisable to maintain an access log, either by application programming or by means of a system tool. The access log should record which data have been accessed, when and by whom. The log will be a valuable source of information for auditors or security officers.

DB2 Auditing Facilities

Sensitive data are normally stored in DB2 tables created with the AUDIT clause of the CREATE TABLE statement. A DB2 audit trace can be activated to verify that only authorized users access auditable tables and to record the SQL statements that access these tables. Finally, DB2 log analysis can be used to view the actual modifications to the data.However, these DB2 facilities are not sufficient to fully record all access to sensitive data, for the following reasons:  

Within a logical unit of work, the audit trace records for a given table, only the first read or write SQL statement.
The trace records do not provide the contents of the input variables submitted with the SQL statement. Without this  information, access recording is incomplete.
DB2 auditing requires that an auditing trace be enabled. If many tables are audited or intensely used, the operating cost of tracing may be excessive.

OVERVIEW

DB2 Access Recording Services - "DBARS" - is a product developed by Software Product Research.

DBARS records all accesses to sensitive data in auditable DB2 tables, by recording the SQL statements that perform the access. An auditable table is a DB2 table created with the AUDIT ALL or AUDIT CHANGE attribute. Depending on the degree of auditing defined when the table was created, read (SELECT) and write (DELETE, INSERT and UPDATE) access will be recorded.

• As an alternative, DBARS can be requested to record accesses to all DB2 tables by specifying the ALLTABLES startup argument.

• DBARS records all input variables associated with the SQL statements.

• DBARS stores the intercepted SQL statements into the Recorder. At the user's choice, the Recorder is a DB2 table or a sequential VSAM cluster. The VSAM file structure should be chosen when intensive access recording is expected.

• DBARS provides a powerful utility to scan the Recorder for recorded events.

• DBARS has its own interface to DB2 and does not depend on DB2 tracing. As a result, recording overhead will be acceptable.

The Recorder Table

For each access to an audited table, DBARS inserts following data into the DBARS Recorder:    

The context of statement execution:

the date and time of access
the name of the DB2 application server accessed
the name of the DB2 application requester (identical to the application server for local access)
the DB2 and z/OS userid
the correlation ID (e.g. the z/OS job name for batch access)
the type of connection (e.g. batch, CICS)
the type of access (dynamic or static)
the LUW_id
the external application name for distributed access
the workstation name for distributed access
the name of the program used for access
the number of rows modified by the statement
the statement's SQLCODE (indicating successful or failing access)

The text of the SQL statement executed, with all input variables in the statement replaced by their contents.

        For example:

        If an application codes EXEC SQL INSERT INTO T1 (C1, C2) VALUES (:hostvar_1, :hostvar_2)

        and the execution-time values of hostvar_1 and hostvar_2 are 40 and 765,

        the statement will be recorded as INSERT INTO T1 (C1, C2) VALUES (40, 765)

Inspecting the Recorder

The Recorder Scan utility searches the Recorder for specific access events. The user may supply following search criteria:

One or more columns of the Recorder.
Table column names used in the recorded SQL statement. This will report all SQL statements that reference the named table column.
Table column names with a specified value. This will report all statements that reference the table column with the specified value. This option will report all recorded access for a given table “key”.

The Recorder Scan program executes under TSO or using the DBARSGUI interface. The scan program includes following additional facilities:

The search values specified for the recorder table columns may contain DB2 expressions. For example: Program Name  NOT LIKE DSQ%
The program can be used to scan the archive result table, described in the paragraph "Archiving the Recorder".
The search arguments supplied by the user are saved in a DB2 table and may be recalled in a later scan session. The USERPARMS table saves up to 32 sets of search arguments.

    Sample Recorder Queries:

      Report all accesses to the EMPLOYEE table by distributed applications during the last 2 months.

      Report all updates of the SALARY column in the EMPLOYEE table.

      Report all accesses using QMF to the EMPLOYEE table for EMPNO 100.

Access Summary Report

The Access Summary program accepts the same search criteria as the Recorder scan. The program provides an hierarchical view of recorded access events.

Typically, access summary is requested to display, for a given table, the list of all users that have accessed the table in a defined period of time.
For a designated user in the user list, a program list may be requested, to list all programs by which that user has accessed the table.
In the program list, a statement list may be requested for a selected program. The list will show the program's SQL statements, executed by the user against the DB2 table.

Reporting Access Exceptions

An installation may wish to screen all data accesses to audited tables by examining, within the DBARS User Exit, the data captured by DBARS and presented to the Exit as entry arguments.

Detecting an access exception

Access exceptions may be detected:

By checking combined values in the exit entry arguments, using REXX statements. For example:

          If (Table_Name = "CUSTOMER_TABLE") & (Operation <> "SELECT")

By scanning the SQL statement text for occurrence of unusual values in specified table columns. To facilitate SQL text scanning, DBARS provides the DBARSRXI function.

Signalling an access exception

On request of the user exit, an access exception will be stored into the DBARS EXCEPTIONS table.
When an exception is detected, the exit may send a message to the z/OS console or to a designated z/OS userid, using the DBARSRXI function.

Archiving the Recorder

The DBARS archiving function transfers the Recorder table to a sequential disk or tape dataset, so that recorded information can be kept for a longer period of time. An archive operation does not disrupt the recording process.

DBARS supplies a utility to scan an archived Recorder using the search criteria, described above. At the choice of the user, the archive scan produces a printed report or a result DB2 table. The latter table can be used as input for an interactive Recorder scan.

Operational Considerations

DBARS is composed of 3 components, all executing asynchronously in the same z/OS address space.

The master task which also provides the operator command interface.
The DB2 interface task that intercepts and queues all SQL statements executed.
The writer task that inserts the queued SQL statements into the Recorder table.

Customizing DBARS

The Recorder is a normal DB2 table and can be processed by customer procedures.
An installation may provide a user exit that will be invoked whenever an access is stored in the Recorder. The exit is written in REXX and receives all the Recorder table columns as its input arguments.

Software Prerequisites

z/OS version 1.4 or later.
DB2 Server for z/OS version 7 or later. DB2 Version 9 is supported.

MORE RESOURCES

Contact us for:

More technical information
Free trial

DBARS is s Software Product Research product.