12.7.5 Performing regular auditing

Based on the auditing policy determined in (1) Determining the auditing policy under 12.7.2 Design, the auditors of Company A check the following as part of regular auditing:

Whether any suspicious connections have been made to the HADB server

Most connections to the HADB server in Company A come from BI tools. If the DB administrator has reason to connect to the HADB server, such as database maintenance, he or she submits an application to that effect in advance. Therefore, this check identifies whether any connections have been made to the HADB server by other means. The auditors also check whether connections to the HADB server are made in a way that matches the submitted applications.
Whether any unauthorized database operations have been performed

Database operations are performed using SQL statements generated automatically by the BI tools. This means that the SQL statements executed on the HADB server are generally typical ones, and tend to conform to a limited number of patterns. The auditors check whether any complex SQL statements have been executed that do not fit the usual patterns. The presence of SQL statements of this nature might indicate that unauthorized operations have been performed.
Whether the configuration of the audit trail facility has been modified without authorization

Changes to the configuration of the audit trail facility are subject to a predetermined internal application process, and are implemented only after permission is granted. The auditors must therefore check whether any changes have been made to the configuration without permission.

The following subsections provide examples of how to check these audit items.

Organization of this subsection

(1) Checking for suspicious connections to the HADB server
(2) Checking whether any unauthorized database operations have been performed
(3) Checking whether the audit trail facility configuration has been modified without authorization

(1) Checking for suspicious connections to the HADB server

In the information analysis system operated by Company A, the authorization identifier and source IP address for connections from BI tools to the HADB server are fixed as follows:

Authorization identifier: BI_ANALYSIS
Source IP address: 11.168.xx.32

If a different authorization identifier or IP address is used for a connection to the HADB server, this might indicate that that connection lacks the proper authorization.

It is also Company A policy for the DB administrator to submit an application before performing database maintenance or other work. This application includes information like the reason for maintenance, the nature of the work, the machine the DB administrator will use, when the work will begin, and the authorization identifier of the HADB user. If the DB administrator performs any work that was not mentioned in the application, he or she follows up with the application recipient by reporting the additional work and the reason for it. Because access to the HADB server is governed by these rules, when a connection is made to the HADB server that involves operations that are not mentioned in an application or later report, that connection potentially involves unauthorized activity.

Accordingly, the following are checked as part of regular auditing:

Whether any HADB users have made failed attempts to connect to the HADB server
Whether any connections have been made to the HADB server whose attributes (authorization identifier, IP address, and working time period) do not appear in an application

The following explains how to look for each of these irregularities.

■ Investigating whether any HADB users have made failed attempts to connect to the HADB server

Connections from BI tools to the HADB server are unlikely to fail. This means that the auditor can focus on whether any HADB users have made failed attempts to connect to the HADB server. An auditor can check for HADB users who have made failed attempts to connect to the HADB server by executing the following SQL statement:

Example of SQL statement execution

SELECT "USER_NAME","CLIENT_IP_ADDRESS","EXEC_TIME"                                   ...1
  FROM TABLE(ADB_AUDITREAD(MULTISET['/mnt/audittrail/outputarea/audit/*.aud'])) "DT" ...2
    WHERE "EVENT_SUBTYPE"='CONNECT' AND "EVENT_RESULT"='FAILURE'                     ...3

Explanation

This SQL statement outputs a list of failed connections to the HADB server. This list contains the authorization identifiers (USER_NAME) of HADB users who made failed attempts, the IP address of the connection source machine (CLIENT_IP_ADDRESS), and the date and time of the connection attempt (EXEC_TIME).
Because regular auditing targets the past three months of audit trail data, the input information is all audit trail files under the audit trail directory (/mnt/audittrail/outputarea/audit).
The SQL statement specifies conditions that extract records of operations that involve failed connections to the HADB server.

The auditor checks the results of executing the preceding SQL statement. Specifically, the auditor makes sure that the authorization identifiers (USER_NAME), IP addresses of the connection source machines (CLIENT_IP_ADDRESS), and the dates and times of connection attempts (EXEC_TIME) match those in submitted applications or later reports. If the auditor identifies an operation that is not mentioned in an application or later report, due to potentially signifying unauthorized activity, it is reported as an audit observation.

■ Investigating whether any connections have been made to the HADB server whose attributes (authorization identifier, IP address, and working time period) do not appear in an application

The auditor investigates whether any connections have been made to the HADB server that do not involve work submitted in an application. The auditor can check for connections to the HADB server that do not relate to such work by executing the following SQL statement:

Example of SQL statement execution

SELECT "USER_NAME","CLIENT_IP_ADDRESS","EXEC_TIME"                                   ...1
  FROM TABLE(ADB_AUDITREAD(MULTISET['/mnt/audittrail/outputarea/audit/*.aud'])) "DT" ...2
    WHERE "EVENT_SUBTYPE"='CONNECT'                                                  ...3
     AND ("CLIENT_IP_ADDRESS"!='10.196.xx.122'                                       ...4
       OR "USER_NAME"!='MAINTENANCE_USER25239'                                       ...4
       OR "EXEC_TIME" NOT BETWEEN TIMESTAMP'2017/06/08 00:00:00.000000'              ...4
                              AND TIMESTAMP'2017/06/08 00:30:00.000000')             ...4

Explanation

This SQL statement outputs a list that contains the authorization identifiers (USER_NAME) of HADB users who attempted to connect to the HADB server, the IP address of the connection source machine (CLIENT_IP_ADDRESS), and the date and time of the connection (EXEC_TIME).
Because regular auditing targets the past three months of audit trail data, the input information is all audit trail files under the audit trail directory (/mnt/audittrail/outputarea/audit).
The SQL statement specifies conditions that extract records of connections to the HADB server.
The SQL statement specifies conditions that do not match the contents of applications. The IP address, authorization identifier, and work time in the application are specified as negated conditions.

The preceding SQL statement is for an example where an application has only been submitted in relation to one task. If applications were submitted for multiple tasks, add the conditions of those applications as search conditions.

The execution results of the preceding SQL statement contain information such as the authorization identifiers of HADB users who attempted to connect to the HADB server outside the parameters of any submitted application. These connections to the HADB server, due to potentially signifying unauthorized activity, are reported as audit observations.

To Page Top

(2) Checking whether any unauthorized database operations have been performed

In the information analysis system operated by Company A, the following database operations take place:

Importing data into tables (every 30 minutes)
Using BI tools to retrieve data from the database
Performing database operations in relation to work for which an application has been submitted

Because any database operations other than the preceding potentially involve unauthorized activity, the following aspects are checked during regular auditing:

Whether data has been imported in a way that appears suspicious
Whether data has been retrieved from the database by a method other than a BI tool
Whether database operations have been performed for which there is no corresponding application

The following explains how to look for each of these irregularities.

■ Investigating whether data has been imported in a way that appears suspicious

By executing the following SQL statement, the auditor can review records of operations that imported data into tables.

Example of SQL statement execution

SELECT "USER_NAME","EXEC_TIME"                                                       ...1
  FROM TABLE(ADB_AUDITREAD(MULTISET['/mnt/audittrail/outputarea/audit/*.aud'])) "DT" ...2
    WHERE "EVENT_SUBTYPE"='ADBIMPORT'                                                ...3

Explanation

This SQL statement outputs a list that contains the authorization identifiers (USER_NAME) of HADB users who imported data, and the date and time (EXEC_TIME) when the data was imported.
Because regular auditing targets the past three months of audit trail data, the input information is all audit trail files under the audit trail directory (/mnt/audittrail/outputarea/audit).
The SQL statement specifies conditions that extract records of operations that involve executing the adbimport command.

In the result of the preceding SQL statement, the auditor checks the EXEC_TIME column (the date and time when data was imported). At Company A, data is imported on the hour and half past every hour. If data is imported at a time that clearly does not match this pattern, it is possible that this activity is not part of the regular import of data. Because data import taking place without a corresponding application being submitted might also signify unauthorized activity, this is also reported as an audit observation.

■ Investigating whether data has been retrieved from the database other than by a BI tool

By executing the following SQL statement, the auditor can review records of data retrieval from the database that exclude those associated with BI tools.

Example of SQL statement execution

SELECT "USER_NAME","EXEC_TIME"                                                       ...1
  FROM TABLE(ADB_AUDITREAD(MULTISET['/mnt/audittrail/outputarea/audit/*.aud'])) "DT" ...2
    WHERE "EVENT_SUBTYPE"='SELECT'                                                   ...3
     AND ("CLIENT_IP_ADDRESS"!='11.168.xx.32' OR "USER_NAME"!='BI_ANALYSIS')         ...3

Explanation

This SQL statement outputs a list that contains of the authorization identifiers (USER_NAME) of HADB users who retrieved data (excluding retrieval using BI tools), and the date and time (EXEC_TIME) when the data was retrieved.
Because regular auditing targets the past three months of audit trail data, the input information is all audit trail files under the audit trail directory (/mnt/audittrail/outputarea/audit).
The SQL statement specifies conditions that extract records of data retrieval from the database (excluding retrieval using BI tools).

When a BI tool searches the database, it always uses the authorization identifier BI_ANALYSIS when connecting to the HADB server. The IP address of the client that connects to the HADB server is always 11.168.xx.32. Accordingly, a connection to the HADB server using a different authorization identifier or IP address means that data was retrieved without using a BI tool. Such data retrieval is reported as an audit observation due to potentially signifying unauthorized activity.

The auditor also checks whether the data retrieval operation is associated with an application submitted in advance.

■ Investigating whether database operations have been performed without a corresponding application

One approach an auditor might take to identify this kind of activity is to output a list of database operations and compare it to the submitted applications.

To Page Top

(3) Checking whether the audit trail facility configuration has been modified without authorization

Changes to the configuration of the audit trail facility are subject to an internal application process. The requested changes are only made after permission is granted at the end of this process. Accordingly, changes to the configuration of the audit trail facility made without undergoing this application process might signify unauthorized activity.

Even when a user has gone through the application process, if changes are made that differ from those in the application, this could also signify unauthorized activity. This potentially unauthorized activity is reported as audit observations.

By executing the following SQL statement, the auditor can review records of operations that change the configuration of the audit trail facility.

Example of SQL statement execution

SELECT "USER_NAME","CLIENT_IP_ADDRESS","EXEC_TIME"                                   ...1
  FROM TABLE(ADB_AUDITREAD(MULTISET['/mnt/audittrail/outputarea/audit/*.aud'])) "DT" ...2
    WHERE "EVENT_TYPE"='AUDIT'                                                       ...3

Explanation

This SQL statement outputs a list that contains the authorization identifiers (USER_NAME) of HADB users who changed the configuration of the audit trail facility, the IP address of the machine from which the user requested the configuration change (CLIENT_IP_ADDRESS), and the date and time of the configuration change (EXEC_TIME).
Because regular auditing targets the past three months of audit trail data, the input information is all audit trail files under the audit trail directory (/mnt/audittrail/outputarea/audit).
The SQL statement specifies conditions that extract records of operations that change the configuration of the audit trail facility.

The auditor checks the results of executing the preceding SQL statement. If configuration changes have been made to the audit trail facility without a corresponding application, due to potentially signifying unauthorized activity, it is reported as an audit observation.

To Page Top