7.15.2 ADB_AUDITREAD function

Converts the audit trails in an audit trail file into a dataset in a table format such that the data can be retrieved by the HADB server.

Note

For an overview of the audit trail facility, see Audit trail facility in the HADB Setup and Operation Guide.
For details about the operations for searching audit trails, see Scheduled operations for audit trail facility in the HADB Setup and Operation Guide.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Privileges required at execution
(4) Rules
(5) Notes
(6) Examples

(1) Specification format

ADB_AUDITREAD-function::=
    [MASTER.]ADB_AUDITREAD([audit-trail-file-path-name-specification])
 
        audit-trail-file-path-name-specification::=multiset-value-expression

To Page Top

(2) Explanation of specification format

audit-trail-file-path-name-specification:

Specifies the path names of the audit trail files containing the input data for the ADB_AUDITREAD function. The path names are specified in the form of a multiset value expression. For details about multiset value expressions, see 7.16 Multiset value expression.

The following rules apply:

The data type of the result of the multiset value expression must be character string data.
Absolute paths must be specified for the path names of the audit trail files specified in audit-trail-file-path-name-specification.
Existing files must be specified in the path names of the audit trail files specified in audit-trail-file-path-name-specification.

About the multi-node function:

If the multi-node function is enabled, audit-trail-file-path-name-specification cannot be omitted.

■ Examples of audit-trail-file-path-name-specification

In the following examples, the underlined portions are audit-trail-file-path-name-specification.

Note that the specification examples shown here are only typical ones. For rules on specifying an audit trail file path name specification, see (b) Rules for audit trail file path name specification in (4) Rules.

Example 1:

ADB_AUDITREAD(MULTISET['/audit/adbaud-20170401-123000-159.aud','/audit/adbaud-20170415-123000-952.aud'])

In the preceding example, the path names of two audit trail files are specified. These two audit trail files are used as the input information for the ADB_AUDITREAD function.

Example 2:

ADB_AUDITREAD(MULTISET['/audit/*.aud'])

In the preceding example, the audit trail file path name includes the special character *. In this case, all audit trail files stored in the /audit directory (files with the extension aud) are used as the input information for the ADB_AUDITREAD function. However, the current audit trail file is not used as the input information for the ADB_AUDITREAD function.

Example 3:

ADB_AUDITREAD(MULTISET['/audit1/*.aud','/audit2/*.aud'])

In the preceding example, the audit trail files stored in the /audit1 and audit2 directories are used as the input information for the ADB_AUDITREAD function.

Example 4:

ADB_AUDITREAD(MULTISET['/audit/adbaud-201707*.aud','/audit/adbaud-201708*.aud'])

In the preceding example, of the audit trail files stored in the /audit directory, the audit trail files that were created in July and August, 2017, are used as the input information for the ADB_AUDITREAD function.

Important

For audit-trail-file-path-name-specification, you can also specify archive files in which audit trail files have been compressed by using the OS command gzip.

Example:

ADB_AUDITREAD(MULTISET['/audit/*.gz'])

In the preceding example, the audit trail files that have been compressed in the archive files (with the extension gz) in the /audit directory are used as the input information for the ADB_AUDITREAD function.

■ If audit-trail-file-path-name-specification is omitted

If audit-trail-file-path-name-specification is omitted, the audit trail files in the audit trail directory (the directory specified by the adb_audit_log_path operand in the server definition) are used as the input information for the ADB_AUDITREAD function. However, the following files are not used as the input information for the ADB_AUDITREAD function:

Current audit trail file
Files in the subdirectories of the audit trail directory

Example:

Specification in the server definition

adb_audit_log_path = /audit

Specification of the ADB_AUDITREAD function

ADB_AUDITREAD()

If the server definition and the ADB_AUDITREAD function are specified as shown in the preceding example, the audit trail files to be used as the input information for the ADB_AUDITREAD function are as shown in the following figure:

[Figure]

Note: If audit-trail-file-path-name-specification is omitted, the system assumes that the adb_audit_log_path operand specification in the server definition + /*.aud is specified as a multiset value expression in the multiset-value-constructor-by-enumeration format. Note that * is specified as a special character.

To Page Top

(3) Privileges required at execution

To execute the ADB_AUDITREAD function, the audit viewer privilege is required.

To Page Top

(4) Rules

(a) Rules for the ADB_AUDITREAD function

The ADB_AUDITREAD function can be used if the audit trail facility is enabled.
When the ADB_AUDITREAD function is executed, the audit trails in the audit trail files specified in audit-trail-file-path-name-specification are returned as a dataset in a table format. For details about the names of, data types of, and information in the columns of the table-formatted dataset returned by the ADB_AUDITREAD function, see Column structure of table function derived table when retrieving audit trails in the HADB Setup and Operation Guide.
If a specified audit trail file contains no audit trail records (other than the header information), the result of the table function derived table for that audit trail file will be an empty set. If a specified audit trail file is a 0-byte file, which contains neither audit trail records nor header information, the SQL statement will result in an error.

This rule is checked during execution of the SQL statement, not during preprocessing of the SQL statement.

(b) Rules for audit trail file path name specification

For audit-trail-file-path-name-specification, specify the path names of the audit trail files that are to be used as the input information for the ADB_AUDITREAD function. Absolute paths must be specified for the path names of the audit trail files.
The file names in the path names of audit trail files can include the following special characters:
- * (asterisk)
  
  This denotes any character string consisting of 0 or more characters.
- ? (question mark)
  
  This denotes any single character.
Specification example 1:
```
ADB_AUDITREAD(MULTISET['/audit/*.aud'])
```
In the preceding example, all audit trail files in the /audit directory (files with the extension aud) are used as the input information for the ADB_AUDITREAD function.
Specification example 2:
```
ADB_AUDITREAD(MULTISET['/audit/adbaud-201704*.aud','/audit/adbaud-201705*.aud'])
```
In the preceding example, any audit trail files having such names as the following are used as the input information for the ADB_AUDITREAD function:
- /audit/adbaud-20170401-123000-159.aud
- /audit/adbaud-20170415-123000-952.aud
- /audit/adbaud-20170501-123000-599.aud
Important
The asterisks (*) and question marks (?) included in the names of audit trail files are handled as special characters. The asterisks (*) and question marks (?) included in directory names in the path names of audit trail files are handled as ordinary characters.

Example:
```
ADB_AUDITREAD(MULTISET['/audit*/adbaud-201706*.aud'])
```
In the preceding example, the asterisk in the directory name is handled as an ordinary character. Therefore, /audit* is handled as a directory name. However, the asterisk in the file name is handled as a special character. Therefore, the preceding audit trail file path name specification can denote such files as follows:
- /audit*/adbaud-20170601-123000-159.aud
- /audit*/adbaud-20170602-165522-656.aud
If the audit trail file path name specification including special characters denotes no existing audit trail file that can be used as the input information (audit-trail-file-path-name-specification results in an empty set), the SQL statement results in an error.
If the audit trail file path name specification includes special characters, the following audit trail files are not used as the input information for the ADB_AUDITREAD function:
- Current audit trail file
- Audit trail files in the subdirectories of directories included in the audit trail file path name specification
If the audit trail file path name specification including special characters denotes 65,536 or more files, the SQL statement results in an error. Note that the current audit trail file, which is not used as input information, is excluded from the files that can be denoted by an audit trail file path name specification that includes special characters.
The SQL statement results in an error if the specified files include files other than the following files:
- Audit trail files
- Archive files in which audit trail files are compressed by the OS command gzip
This rule is checked during execution of the SQL statement, not during preprocessing of the SQL statement.
The current audit trail file cannot be specified as an audit trail file path name.
The spaces at the beginning and end of an audit trail file path name are not handled as the part of the path name.

Examples:

'∆∆∆/audit/adbaud-20170420-123030-159.aud' → '/audit/adbaud-20170420-123030-159.aud'

'/audit/adbaud-20170420-123030-159.aud∆∆∆' → '/audit/adbaud-20170420-123030-159.aud'

'∆∆∆/audit/adbaud-20170420-123030-159.aud∆∆∆' → '/audit/adbaud-20170420-123030-159.aud'

'∆∆∆/audit/adbaud-20170420∆-123030-159.aud∆∆∆' → '/audit/adbaud-20170420∆-123030-159.aud'

∆: Space

Important

Do not specify spaces at the beginning and end of an audit trail file path name. If you specify a path name that begins or ends with spaces, the spaces are removed. Therefore, the specified path name might be changed to an unintended path name.
The maximum length of each audit trail file path name is 1,024 bytes. If an audit trail file path name that is 1,025 or more bytes long is specified, the SQL statement results in an error. Note that the path name length check takes place after the HADB server performs the following processing:
- Deleting any spaces at the beginning and end of audit trail file path names
- Replacing any audit trail file path names (denoted by the specification including special characters) with their actual path names of audit trail files that are used as input information
If the ADB_AUDITREAD function is specified in the CREATE VIEW statement, the audit trail file path names are not checked when the CREATE VIEW statement is executed. The audit trail file path names are checked when an SQL statement in which a defined viewed table is specified is executed. If there are path names that violate the rules, the SQL statement results in an error.

To Page Top

(5) Notes

Before HADB administrators can access all directories included in the absolute paths of all audit trail files, set read and execution privileges for those directories. For example, if audit trail files are stored in the /adbmanager/audit directory, set read and execution privileges for the /, /adbmanager, and /adbmanager/audit directories so that HADB administrators can access these directories. Also, set read privilege for the audit trail files so that HADB administrators can access them.
If an SQL statement in which the ADB_AUDITREAD function is specified is executed, the HADB server opens the audit trail files specified by audit-trail-file-path-name-specification to read the audit trails. Therefore, during execution of an SQL statement in which the ADB_AUDITREAD function is specified, do not move or delete any files specified by audit-trail-file-path-name-specification.
If the path name of an audit trail file includes special characters, the path names of search-target audit trail files are extracted during the preprocessing of the SQL statement. If audit trail files that are extracted during the preprocessing of the SQL statement do not exist during execution of the SQL statement, the files are not to be searched (the SQL statement does not result in an error).
Some of the rules for specifying the ADB_AUDITREAD function are checked during execution (rather than the preprocessing) of the SQL statement. The descriptions of the rules to be checked during execution of the SQL statement include the sentence This rule is checked during execution of the SQL statement, not during preprocessing of the SQL statement.

To Page Top

(6) Examples

Example 1

Output a list of HADB users who accessed the HADB server in the term from April 1, 2017 to April 30, 2017. Assume that the audit trail files containing the audit trails that were output in the term from April 1, 2017 to April 30, 2017 are stored in the /audit directory.

SELECT DISTINCT "USER_NAME"
    FROM TABLE(ADB_AUDITREAD(MULTISET['/audit/*.aud'])) "DT"
       WHERE "EXEC_TIME" BETWEEN TIMESTAMP'2017/04/01 00:00:00.000000'
                             AND TIMESTAMP'2017/04/30 23:59:59.999999'

In the preceding example, the underlined portion indicates the specification of the ADB_AUDITREAD function.

USER_NAME stores the authorization identifier of an HADB user. EXEC_TIME stores the time at which the HADB user performed an operation.

Example 2

Output a list of HADB users who accessed the HADB server in the term from April 1, 2017 to April 30, 2017. Assume that the audit trails that were output in the term from April 1, 2017 to April 30, 2017 are stored in the directory specified for the adb_audit_log_path operand in the server definition.

SELECT DISTINCT "USER_NAME"
    FROM TABLE(ADB_AUDITREAD()) "DT"
       WHERE "EXEC_TIME" BETWEEN TIMESTAMP'2017/04/01 00:00:00.000000'
                             AND TIMESTAMP'2017/04/30 23:59:59.999999'

In the preceding example, the underlined portion indicates the specification of the ADB_AUDITREAD function.

To Page Top