DB2 for z/OS

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.  

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, the DB2 log 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 DB2 log does not show SELECT accesses.

• DB2 auditing requires that DB2 tracing be enabled. If many tables are audited or intensely accessed, the operating cost of tracing may be unacceptable. 

• To provide the contents of the input variables associated with an SQL statement, additional DB2 tracing is required.

DBARS Highlights

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 DB2 table is a table specified in the DBARS startup parameters. As an alternative, DBARS can be requested to record accesses to all DB2 tables, by specifying the ALLTABLES startup parameter.

• DBARS records all read (SELECT) and write (DELETE, INSERT and UPDATE) accesses.

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

• If requested, DBARS will mask the data values in the recorded SQL statement text.

• DBARS stores the intercepted SQL statements into its Recorder. At the user's choice, the Recorder is a DB2 table, a sequential (BSAM) dataset or a VSAM cluster. The BSAM or 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 facilities. As a result, DBARS overhead is very low.

• DBARS provides access blocking and alerting facilities.

• DBARS can be integrated with external security systems.

The Recorder 

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 table accessed
• the name of the DB2 application server
• the name of the DB2 application requester
• 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
• if remote access:
  • the IP address 
  • the external application name 
  • the workstation name 
• 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.

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 in batch, under TSO or using the DBARSGUI interface on Windows.

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 longer periods 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.  

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.

Example: 

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

BLOCK WHEN TABLE NTR.CUSTOMER_BALANCE 
AND ACCESS NOT SELECT 
AND USER NOT ACR 

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. 

Example: 

Following rule will generate an alert when the customer table is accessed during the night shift by a remote application (such as EXCEL):

ALERT WHEN TABLE NTR.CUSTOMER 
AND EXTAPPL ANY 
AND TIME BETWEEN 2000 AND 0800 

SQL Reduction Facility 

For sequences of structurally identical SQL statements, SQL Reduction will reduce those sequences to a single occurrence in the DBARS Recorder.

For example: if a batch job issues multiple INSERTs where only the insert data values are changing, a single INSERT will be recorded, with the data values masked.

SQL reduction is performed using a reduction "key", which consists of the execution date, the userid and the program name.

Centralized Recorder Service

When DBARS monitors multiple Db2 subsystems, a DBARS Recorder is normally required for each Db2 subsystem. With centralized recording, the individual DBARS instances send their recorded data to the Centralized Recorder Processor, using TCP/IP sockets.

The Centralized Recorder Processor executes in a dedicated z/OS address space and writes the received recorder data to the Recorder dataset.

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, DBARS will act as an auditing agent for DB2 on z/OS. Communication between DBARS and the external security software will be achieved using FTP or by TCP/IP sockets.