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.
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.
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.
Facilities provided by DBARS
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.
All read (SELECT) and write (DELETE, INSERT and UPDATE) access will be recorded.
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, 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 facilities, such as tracing. As a result, recording overhead will be less than 5% CPU usage.
DBARS can be integrated with external security systems, such as the Oracle AVDF©
each access to an audited table, DBARS inserts following data into the DBARS
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)
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)
Recorder Scan utility searches
the Recorder for specific access events. The user may supply following search criteria:
or more columns of the Recorder .
column names used in the recorded SQL statement. This will report all SQL statements that
reference the named table column.
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
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:
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.
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:
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, 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:
ALERT WHEN TABLE NTR.CUSTOMER_BALANCE
AND TIME BETWEEN 2000 AND 0800
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.
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.