9.2.1 Overview of the security audit facility

Organization of this subsection
(1) Functional overview
(2) Audit trail collection times
(3) Audit trail collection examples
(4) Information collected in audit trails
(5) Accessing an audit trail

(1) Functional overview

HiRDB security is protected by means of privileges. The information that can be accessed or updated, and the objects that can be manipulated (tables, indexes, and so on), are controlled with privileges. To check whether these privileges are being applied properly, HiRDB can record a variety of actions that are performed on the databases. This functionality is called the security audit facility, and the operations record that it outputs is called an audit trail. By examining the output audit trail, you can check whether there has been fraudulent access. This check is performed by users, called auditors, who have been assigned the audit privilege. The following figure provides an overview of the security audit facility.

Figure 9-2 Overview of the security audit facility

[Figure]

The security audit facility collects information about who is using privileges, which privileges they are using, and the objects on which they are using the privileges to perform operations. The auditor uses the CREATE AUDIT statement to specify the operations on which the security audit facility is to collect information. Once specified, an audit trail is collected whenever an operation for which an audit trail is specified to be collected is performed.

Reference note
  • The purpose of the security audit facility is not to enhance security. It is designed simply to output an operation log that enables checking of whether privileges are being used correctly.
  • You can link HiRDB to JP1/NETM/Audit and have JP1/NETM/Audit collect and centrally manage HiRDB's audit trails. For details, see 2.4.8 JP1/NETM/Audit.

(2) Audit trail collection times

HiRDB collects an audit trail when any of the following events occurs:

The security audit facility does not collect an audit trail when an SQL syntax error occurs or when an incorrectly keyed command is entered.

For details about audit trail collection triggers, see the HiRDB Version 9 System Operation Guide.

(3) Audit trail collection examples

Examples of audit trail collection are provided in this subsection.

Example 1: Collecting an audit trail when a table is searched
The table access privilege (SELECT privilege) is used when a table is searched, so an audit trail is collected.
Item searched
(SQL specification)
Contents of audit trail
UserPrivilege usedType of manipulated objectName of manipulated objectOperation type
A user (USR1) issues the following SELECT statement:
SELECT C1 FROM USR1.T1
PrivilegeUSR1Table access privilege (SELECT privilege)TableUSR1.T1Table access (SELECT)
EndUSR1--TableUSR1.T1Table access (SELECT)
A user (USR2) issues the following SELECT statement:
SELECT T1.C1,T2.C1
FROM USR1.T1 T1,USR2.T2 T2
WHERE T1.C1=T2.C1
PrivilegeUSR2Table access privilege (SELECT privilege)TableUSR1.T1Table access (SELECT)
USR2Table access privilege (SELECT privilege)TableUSR2.T2Table access (SELECT)
EndUSR2--TableUSR1.T1Table access (SELECT)
USR2--TableUSR2.T2Table access (SELECT)
Legend:
Privilege: Audit trail is collected at time of privilege checking.
End: Audit trail is collected when the event terminates.
--: Not applicable
Example 2: Collecting an audit trail when a table is defined or deleted
The schema owner privilege, table owner privilege, and RDAREA owner privilege are used when a table is defined or deleted, so an audit trail is collected.
Item searched
(SQL specification)
Contents of audit trail
UserPrivilege usedType of manipulated objectName of manipulated objectOperation type
A user (USR1) issues the following CREATE TABLE:
CREATE TABLE
T1(C1 INT) IN RDAREA1
PrivilegeUSR1RDAREA usage privilegeRDAREARDAREA1Definition creation
USR1OwnerSchemaUSR1Definition creation
USR1OwnerTableUSR1.T1Definition creation
EndUSR1--TableUSR1.T1Definition creation
A user (USR2) issues the following DROP TABLE:
DROP TABLE T1
PrivilegeUSR2OwnerTableUSR2.T1Definition deletion
EndUSR2--TableUSR2.T1Definition deletion
Legend:
Privilege: Audit trail is collected at time of privilege checking.
End: Audit trail is collected when the event terminates.
--: Not applicable

(4) Information collected in audit trails

The following table lists and describes the information collected in audit trails.

Table 9-2 Information collected in audit trails

Information collectedDescription
User identifierAuthorization identifier of the executor of the audit event
Event execution dateYear, month, and date the event was executed
Event execution timeTime the event was executed
Event execution durationAmount of time it took for the event to execute (in microseconds)
Event typeEvent type
Event subtypeEvent subtype
Event resultExecution results of the event (whether the privilege check was successful)
Privilege usedPrivilege used when the event was executed
UAP nameUAP name specified in the PDCLTAPNAME operand of the client environment definition
Service nameService name requested by the UAP that issued the event.
This is the item that corresponds to the service name when an OpenTP1 SUP (service using program) requests a service from an SPP (service providing program), or when TP1/Message Control requests a service from an MHP (message handling program).
IP addressClient IP address at which the UAP that issued the event is running#
Process numberProcess ID from the UAP that issued the event#
Thread numberThread ID from the UAP that issued the event#
Host nameName of the host to which the UAP that issued the event is connected
Unit identifierIdentifier of the unit to which the UAP that issued the event is connected
User nameName of the front-end server or single server to which the UAP that issued the event is connected
Connection sequence numberConnection sequence number of the event issuer
SQL sequence numberSQL sequence number of the event
Object owner nameName of the owner of the object on which the event privilege check is performed
Object nameName of the object on which the event privilege check is performed
Object typeType of the object on which the event privilege check is performed
Assigned, revoked, or modified privilegePrivilege that was assigned, revoked, or modified due to the event
Identifier of the user who assigned, revoked, or modified a privilege, and the user identifier for the eventIdentifier of the user who assigned, revoked, or modified the privilege with the event and the authorization identifier for the event
Values of security audit facility-related operandsValues of operands related to the security audit facility (values at HiRDB startup)
Audit trail typeIndicator of privilege check or event end
SQL code or end codeCode issued when the SQL, utility, or command ends
Swap source audit trail file nameName of audit trail file at swap source when a swap occurs
Swap target audit trail file nameName of audit trail file at swap target when a swap occurs
Configuration change type of connection security facilityConfiguration change type set in the connection security facility (a change type is also set when the password is changed)
Values of operands related to connection security facility (before change)Values of operands related to the connection security facility before they have been changed
Values of operands related to connection security facility (after change)Values of operands related to the connection security facility after they have been changed
Audit trail table optionsFlag for handling events that target an audit trail table, a view base table of an audit trail table, or a list base table of an audit trail table
Access countNumber of rows that are retrieved from, inserted into, updated in, or deleted from objects (base tables, view tables, and lists) by an event
SQL statementSQL statement that was executed
SQL dataData of the executed SQL
User-added information 1Additional information that is set up by users
User-added information 2
User-added information 3
Related product-added information 1Additional information that is set up by products related to Cosminexus or Hitachi Application Server.
Note
The information items that are collected depend on the event. For a list of the types of information that are collected for each event, see the HiRDB Version 9 System Operation Guide.
#: For events provided via an application running under OpenTP1 or provided via a Web server or similar product, information is collected from the application to which HiRDB is connected, not from the application that the end user is running.

(5) Accessing an audit trail

Audit trails are output to an audit trail file. Data in an audit trail file can be accessed using SQL once the data has been loaded into an audit trail table by the database load utility (pdload command). Note that the auditor can access (but not update) this audit trail table. Users other than the auditor can access (but not update) an audit trail table only if they are granted access privilege by the auditor. The following figure shows how to access audit trails.

Figure 9-3 Accessing audit trails

[Figure]

Explanation
  1. When an audit event occurs, an audit trail is output to an audit trail file. The audit trail file is created in a HiRDB file system area allocated for audit trail files. For details about audit events, see 9.2.2 Audit events.
  2. Using as the input audit trails that were output to the audit trail file, the database load utility (pdload command) is used to load the data into a table. When the facility for automatically loading audit trail table data is applied, HiRDB automatically executes the database load utility.
  3. The auditor uses the audit trail table to perform an audit.