12.7.2 Design

(1) Determining the auditing policy

First, the entity using the audit trail facility needs to determine their auditing policy. This covers aspects such as the operations to be audited and the interval at which auditing takes place.

The information analysis system operated by Company A handles personal information. Company A needs to be sure that employees are not accessing personal information inappropriately. Because the database also contains commercially sensitive information such as sales histories and revenue information, they also need to make sure that employees are not accessing this information without the proper authorization.

Because sales information and visitor counts are calculated monthly, Company A has chosen to conduct auditing using the same schedule.

Based on the preceding factors, the operations that are subject to auditing and the interval at which auditing is conducted are determined as follows:

Operations subject to auditing

• Access to the database that stores personal information

• Access to the database that stores commercially sensitive information such as sales histories and revenue information

Frequency of auditing

• Auditing takes place monthly

To Page Top

(2) Determining the events for which to output audit trails

Company A determines the events for which to output audit trails based on the audit policy formulated in (1) Determining the auditing policy.

The auditing process requires that a record is kept of access to the database that stores the data subject to auditing. To allow the auditor to identify the pathway the user took to connect to the HADB server, a record must also be kept of connections to and disconnections from the HADB server.

To output a record of database access, you need to designate data manipulation SQL events as subject to audit trail output. To output a record of HADB server connections and disconnections, you need to designate session events as subject to audit trail output. If you want the audit trail facility output audit trails when data manipulation SQL events and session events occur, you must use a CREATE AUDIT statement to define optional audit events as subject to auditing.

Note

For details about event types and the relationship between event types and operations, see 12.9.1 List of audit target events and output items.

To Page Top

(3) Designing the directories used by the audit trail facility

Because personal information is included in the data handled by the information analysis system operated by Company A, the rules for storing audit trail files were determined as follows:

• The past three months of audit trail files are stored in the audit trail directory

• Audit trail files that were output more than three months ago are moved to the audit trail storage directory

• Audit trail files are kept in the audit trail storage directory until one year has elapsed since they were output

• Audit trail files that were output more than a year ago are deleted from the audit trail storage directory

In the information analysis system operated by Company A, data is imported into the database every 30 minutes. The BI tools used by Company A generate approximately 2,400 search queries per day. The directories used by the audit trail facility are designed with these conditions in mind.

■ Designing the audit trail directory

The size of the audit trail directory must be sufficient to store three months of audit trail files. Estimate the size of the audit trail directory as follows:

Formula

{2,400 queries × {2 KB + 16 KB (SQL statement length)} + 24 × 2 × (1 KB + 1 KB + 1 KB)} × 31 days × 3 months
={2,400 × (2 + 16) + 24 × 2 × (1 + 1 + 1)} × 31 × 3 = 4,030,992 KB
≈ 4 GB

Based on the result of this formula, after allowing for a certain amount of leeway, a size of 10 GB is selected for the disk area where the audit trail directory is created.

This disk area is mounted to /mnt/audittrail/outputarea, giving the audit trail directory the following path:

• /mnt/audittrail/outputarea/audit

■ Designing the audit trail storage directory

The size of the audit trail storage directory must be sufficient to store nine months of audit trail files. Estimate the size of the audit trail storage directory as follows:

Formula

{2,400 queries × {2 KB + 16 KB (SQL statement length)} + 24 × 2 × (1 KB + 1 KB + 1 KB)} × 31 days × 9 months
= {2,400 × (2 + 16) + 24 × 2 × (1 + 1 + 1)} × 31 × 9 = 12,092,976 KB
≈ 12 GB

Based on the result of this formula, after allowing for a certain amount of leeway, a size of 20 GB is selected for the disk area where the audit trail storage directory is created.

This disk area is mounted to /mnt/audittrail/savearea, giving the audit trail storage directory the following path:

• /mnt/audittrail/savearea/audit_bak

Note

After audit trail files older than three months are moved to the audit trail storage directory, those that are more than a year old are deleted. This means that the audit trail storage directory must have enough space to store nine months of audit trail files.

To Page Top

(4) Appointing auditors

You cannot use the audit trail facility without appointing auditors. The responsibilities of an auditor can include managing and operating the audit trail facility, and conducting the audit process itself.

The database operator cannot be the same person as the auditor who reviews the activity of the database operator. In Company A, the division of roles in relation to the audit trail facility among those responsible for the information analysis system is as follows:

Figure 12‒6: Division of roles among those responsible for information analysis system in Company A

• DB administrator

  A person who manages and operates the HADB database. The DB administrator is primarily responsible for database maintenance and other tasks. When logging in to the OS, the DB administrator uses the OS account of the HADB administrator. To connect to the HADB server, the DB administrator uses the authorization identifier ADBADMIN of an HADB user who has the DBA privilege.

• Audit trail facility administrator

  A person who manages and operates the audit trail facility. The responsibilities of the audit trail facility administrator include enabling and disabling the audit trail facility, and defining audit targets. When logging in to the OS, the audit trail facility administrator uses the account of an OS user who belongs to the HADB administrators group. To connect to the HADB server, the audit trail facility administrator uses the authorization identifier ADBAUDITADMIN of an HADB user who has the audit admin privilege.

• DB person responsible for auditing

  A person responsible for the auditing process itself. The DB person responsible for auditing reviews records of database operations and other activity by referencing the audit trail data. When logging in to the OS, the DB person responsible for auditing uses the account of an OS user who belongs to the HADB administrators group. To connect to the HADB server, the DB person responsible for auditing uses the authorization identifier ADBAUDITVIEWER of an HADB user who has the audit viewer privilege.

  Two DB persons responsible for auditing are appointed who share use of an OS user account and HADB user authorization identifier.

To Page Top

(5) Selecting an approach for when attempts to write to the audit trail file fail

Company A must select how the system behaves when the HADB server cannot write to the audit trail file, such as when the disk is full or affected by a failure. The following approaches are available:

1. When an attempt to write to the audit trail file fails, the HADB server is stopped (terminated abnormally).

2. When an attempt to write to the audit trail file fails, the data that could not be written is discarded. The HADB server continues to operate.

When the second of these methods is selected, audit trail data might be lost. This can make it impossible to fully investigate the cause of any security incidents that occurred. It might also give the impression that security control measures are not being followed, leading to a loss of reputation for the company and potentially causing considerable losses.

The information analysis system is fully isolated from the eCommerce site that Company A runs 24 hours a day. As such, interruptions to the operation of the information analysis system have no impact on the operation of the eCommerce site.

For these reasons, the first approach was selected in which the HADB server is terminated when an attempt to write to the audit trail file fails.

Company A also decided to monitor the KFAA51404-E message output when writing to the audit trail file fails, and have the HADB server administrator and audit trail facility administrator notified by email when a failure to write to the file is detected.

To Page Top