DB2 for z/OS

Recording Access to Sensitive DB2 Tables


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:  

Facilities provided by DBARS

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

The Recorder 

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

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: 

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

Sample Recorder Queries:

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.

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.

Blocking access to DB2

The DBARS "RULES" dataset specifies the conditions for blocking DB2 accesses. When an SQL statement meets one of these conditions, DBARS abends the application and writes the statement to the Recorder with a "blocked" indicator. Blocking may be based on username, tablename, programname, jobname, IP-address, execution time, type of access or a combination of these parameters. Because the DBARS blocker executes in the DB2 address space, it is able to block any SQL access, whatever its origin.


Following rule ensures that only users in the accounts receivable department can update the customer_balance table:


Issuing Alerts for questionable DB2 accesses

The DBARS "RULES" dataset specifies the conditions for generating a DBARS alert. When an SQL statement meets these conditions, an entry is made into the DBARS Exception table. Alternatively, an installation may request that a user-provided REXX program be invoked to handle the alert. 

In addition, DBARS may be requested to transmit the alert to a Windows Event Log, where it can be exploited by third-party software, such as big data systems. 


Following rule will generate an alert when the customer_balance table is updated using dynamic SQL during the night shift:


Customizing DBARS 

An installation may wish to screen all data accesses to audited tables by examining, within the DBARS User Exit, the data captured by DBARS. The exit 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.

Connecting DBARS to an External Security Manager 

When connected to an external security manager (such as the Oracle Audit Vault and Database Firewall ®), DBARS will act as an auditing agent for DB2_for_z/OS. The DBARS FTP task will transfer the auditing data intercepted by DBARS to the ESM for further processing. The DBARS reporting, alerting and archiving functions are then performed by the ESM.