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 or not 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. Figure 9-2 provides an overview of the security audit facility.

Figure 9-2 Overview of the security audit facility

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 or not privileges are being used correctly.

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

• When a privilege check is performed because a command or SQL statement is executed
• When an event ends

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 8 System Operation Guide.

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
		User	Privilege used	Type of manipulated object	Name of manipulated object	Operation type
A user (USR1) issues the following SELECT statement: SELECT C1 FROM USR1.T1	Privilege	USR1	Table access privilege (SELECT privilege)	Table	USR1.T1	Table access (SELECT)
	End	USR1		Table	USR1.T1	Table 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	Privilege	USR2	Table access privilege (SELECT privilege)	Table	USR1.T1	Table access (SELECT)
		USR2	Table access privilege (SELECT privilege)	Table	USR2.T2	Table access (SELECT)
	End	USR2		Table	USR1.T1	Table access (SELECT)
		USR2		Table	USR2.T2	Table 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
		User	Privilege used	Type of manipulated object	Name of manipulated object	Operation type
A user (USR1) issues the following CREATE TABLE: CREATE TABLE T1(C1 INT) IN RDAREA1	Privilege	USR1	RDAREA usage privilege	RDAREA	RDAREA1	Definition creation
		USR1	Owner	Schema	USR1	Definition creation
		USR1	Owner	Table	USR1.T1	Definition creation
	End	USR1		Table	USR1.T1	Definition creation
A user (USR2) issues the following DROP TABLE: DROP TABLE T1	Privilege	USR2	Owner	Table	USR2.T1	Definition deletion
	End	USR2		Table	USR2.T1	Definition deletion

Legend:

Privilege: Audit trail is collected at time of privilege checking.

End: Audit trail is collected when the event terminates.

: Not applicable

Table 9-2 lists and describes the information collected in audit trails.

Table 9-2 Information collected in audit trails

Information collected	Description
User identifier	Authorization identifier of the executor of the audit event
Event execution date	Year, month, and date the event was executed
Event execution time	Time the event was executed
Event execution duration	Amount of time it took for the event to execute (in microseconds)
Event type	Event type
Event subtype	Event subtype
Event result	Execution results of the event (whether or not the privilege check was successful)
Privilege used	Privilege used when the event was executed
UAP name	UAP name specified in the PDCLTAPNAME operand of the client environment definition
Service name	Service 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 address	Client IP address at which the UAP that issued the event is running*
Process number	Process ID from the UAP that issued the event*
Thread number	Thread ID from the UAP that issued the event*
Host name	Name of the host to which the UAP that issued the event is connected
Unit identifier	Identifier of the unit to which the UAP that issued the event is connected
User name	Name of the front-end server or single server to which the UAP that issued the event is connected
Connection sequence number	Connection sequence number of the event issuer
SQL sequence number	SQL sequence number of the event
Object owner name	Name of the owner of the object on which the event privilege check is performed
Object name	Name of the object on which the event privilege check is performed
Object type	Type of the object on which the event privilege check is performed
Assigned, revoked, or modified privilege	Privilege 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 event	Identifier 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 operands	Values of operands related to the security audit facility (values at HiRDB startup)
Audit trail type	Indicator of privilege check or event end
SQL code or end code	Code issued when the SQL, utility, or command ends
Swap source audit trail file name	Name of audit trail file at swap source when a swap occurs
Swap target audit trail file name	Name of audit trail file at swap target when a swap occurs
Configuration change type of connection security facility	Configuration 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 options	Flag 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

Note

The information items that are collected depends on the event. For a list of the types of information that are collected for each event, see the HiRDB Version 8 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.

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. Figure 9-3 shows how to access audit trails.

Figure 9-3 Accessing audit trails

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.
3. The auditor uses the audit trail table to perform an audit.