12.7.6 Investigating security incidents

This subsection provides an example of the investigation that might take place when the following security incident occurs:

Circumstances of security incident: Company A is notified that the personal information of users of the eCommerce site operated by Company A might have leaked onto the Internet. It quickly becomes clear that the information is customer information managed by Company A's information analysis system. Company A begins an investigation led by the DB administrator and audit trail facility administrator, who investigate the channels through which the leak occurred, when it occurred, and the extent of the leak. An example of this investigation is explained in the following subsections.

Organization of this subsection

(1) Identifying the source of the leak
(2) Investigating which HADB users accessed the data which was leaked
(3) Investigating whether an HADB user responsible for unauthorized access has accessed other tables
(4) Reporting findings

(1) Identifying the source of the leak

The investigation shows that the information leaked onto the Internet includes information such as the gender and date of birth of customers. Because this information is stored in the CUSTOMERS_ANALYSIS table of the database of the information analysis system, the auditors were able to identify this table as the source of the leaked data.

To Page Top

(2) Investigating which HADB users accessed the data which was leaked

The auditors investigate which HADB users accessed the CUSTOMERS_ANALYSIS table that stored the data that was leaked, the IP address from which the table was accessed, the date and time it was accessed, and the number of accessed rows.

Executing the following SQL statement shows the CUSTOMERS_ANALYSIS table access information, such as HADB users who accessed the table:

Example of SQL statement execution

SELECT "USER_NAME","CLIENT_IP_ADDRESS","EXEC_TIME","ACCESS_COUNT"                     ...1
  FROM TABLE(ADB_AUDITREAD(MULTISET['/mnt/audittrail/savearea/audit_bak/*.aud',       ...2
                                    '/mnt/audittrail/outputarea/audit/*.aud'])) "DT"  ...2
    WHERE "OBJECT_SCHEMA_NAME"='ANALYSIS_SYSTEM'                                      ...3
      AND "OBJECT_NAME"='CUSTOMERS_ANALYSIS'                                          ...3
      AND ("EVENT_SUBTYPE"='SELECT' OR "EVENT_SUBTYPE"='ADBEXPORT')                   ...4
      AND "EVENT_RESULT"='SUCCESS'                                                    ...5

Explanation

This SQL statement outputs the access information of the CUSTOMERS_ANALYSIS table in a list format. This list consists of the following types of information: Authorization identifiers of the HADB users who accessed the table (USER_NAME), the IP address of the machine that requested access to the table (CLIENT_IP_ADDRESS), the access date and time (EXEC_TIME), and the number of accessed rows (ACCESS_COUNT).
Because it is still unknown when the unauthorized access took place, the scope of the investigation includes all audit trail data. Therefore, all audit trail files in the following directories are used as input information:
- Audit trail storage directory (/mnt/audittrail/savearea/audit_bak)
- Audit trail directory (/mnt/audittrail/outputarea/audit)
The SQL statement specifies the schema name and table identifier of the leaked CUSTOMERS_ANALYSIS table as retrieval conditions.
The SQL statement specifies the operations in question (data retrieval using a SELECT statement and exporting table data using an adbexport command) as retrieval conditions.
The SQL statement specifies the success of the operation (event) in step 4 as a retrieval condition.

Important

The DB person responsible for auditing performs this operation after connecting to the HADB server using the authorization identifier (ADBAUDITVIEWER) of an HADB user who has the audit viewer privilege.
The auditor can include the audit trail data in the current audit trail file in the investigation by using the adbaudittrail --swap command to swap the current audit trail file before executing the SQL statement.

To Page Top

(3) Investigating whether an HADB user responsible for unauthorized access has accessed other tables

After identifying the HADB user who accessed data without authorization in (2) Investigating which HADB users accessed the data which was leaked, the next step is to investigate whether there is any evidence of that HADB user accessing other tables. If the HADB user has accessed other tables, the information stored in those tables might also have leaked.

Executing the following SQL statement shows which tables have been accessed by the HADB user.

Example of SQL statement execution

This example assumes the authorization identifier of the HADB user responsible for unauthorized access is ADBUSER05.

SELECT "EXEC_TIME","OBJECT_OWNER_NAME","OBJECT_SCHEMA_NAME",                          ...1
                 "OBJECT_NAME","ACCESS_COUNT"                                         ...1
  FROM TABLE(ADB_AUDITREAD(MULTISET['/mnt/audittrail/savearea/audit_bak/*.aud',       ...2
                                    '/mnt/audittrail/outputarea/audit/*.aud'])) "DT"  ...2
    WHERE "USER_NAME"='ADBUSER05'                                                     ...3
     AND ("OBJECT_TYPE"='TABLE' OR "OBJECT_TYPE"='VIEW')                              ...4
     AND ("EVENT_SUBTYPE"='SELECT' OR "EVENT_SUBTYPE"='ADBEXPORT')                    ...5
     AND "EVENT_RESULT"='SUCCESS'                                                     ...6
     AND "OBJECT_NAME"!='CUSTOMERS_ANALYSIS'                                          ...7

Explanation

This SQL statement outputs a list of the following items:
- The date and time (EXEC_TIME) at which the HADB user responsible for unauthorized access accessed tables (including viewed tables)
- The following information about the tables accessed by the HADB user responsible for unauthorized access:
  - The authorization identifier of the table owner (OBJECT_OWNER_NAME)
  - The schema name of the table (OBJECT_SCHEMA_NAME)
  - The table identifier (OBJECT_NAME)
- The number of accessed rows (ACCESS_COUNT)
Because it is still unknown when the unauthorized access took place, the scope of the investigation includes all audit trail data. Therefore, all audit trail files in the following directories are used as input information:
- Audit trail storage directory (/mnt/audittrail/savearea/audit_bak)
- Audit trail directory (/mnt/audittrail/outputarea/audit)
The SQL statement specifies the authorization identifier of the HADB user responsible for unauthorized access (ADBUSER05) as a retrieval condition.
The SQL statement specifies the object types (table and viewed table) as retrieval conditions.
The SQL statement specifies the operations in question (data retrieval using a SELECT statement and exporting table data using an adbexport command) as retrieval conditions.
The SQL statement specifies the success of the operation (event) in step 5 as a retrieval condition.
The SQL statement specifies a retrieval condition that excludes the CUSTOMERS_ANALYSIS table from the result. This condition is specified because the auditor already knows that the CUSTOMERS_ANALYSIS table was accessed without authorization.

Important

The DB person responsible for auditing performs this operation after connecting to the HADB server using the authorization identifier (ADBAUDITVIEWER) of an HADB user who has the audit viewer privilege.
The auditor can include the audit trail data in the current audit trail file in the investigation by using the adbaudittrail --swap command to swap the current audit trail file before executing the SQL statement.

To Page Top

(4) Reporting findings

The investigators at Company A summarize and report their findings as follows:

HADB user who leaked the information

The HADB user identified in (2) Investigating which HADB users accessed the data which was leaked might be the HADB user who leaked the information.
Date and time when leak occurred

The date and time identified in (2) Investigating which HADB users accessed the data which was leaked and (3) Investigating whether an HADB user responsible for unauthorized access has accessed other tables might be the date and time when the information was leaked.
Leaked information

The information in the tables identified in (1) Identifying the source of the leak and (3) Investigating whether an HADB user responsible for unauthorized access has accessed other tables might have been leaked.
Number of items of leaked information (extent of harm)

The access counts identified in (2) Investigating which HADB users accessed the data which was leaked and (3) Investigating whether an HADB user responsible for unauthorized access has accessed other tables might reflect the number of pieces of information that were leaked.

To Page Top