12.3.3 Referencing audit trails (when using SELECT statements to reference audit trails)

To reference audit trails, you execute a SELECT statement with the ADB_AUDITREAD function specified.

Important

Only an HADB user with the audit viewer privilege can reference audit trails by executing a SELECT statement that specifies the ADB_AUDITREAD function.

The following are examples of SELECT statements that reference audit trails:

Example 1

This statement references the audit trails in all audit trail files in the audit trail storage directory (/mnt/audittrail/savearea).

Example of SELECT statement execution

SELECT * FROM TABLE(ADB_AUDITREAD(MULTISET['/mnt/audittrail/savearea/*.aud'])) "DT"

Explanation

In the underlined portion, specify the path of the audit trail files that contain the audit trails you want to reference. In this example, because the name of the audit trail file is specified using the special character * (as *.aud), the statement will reference audit trails stored in all audit trail files (files with the extension aud) in the /mnt/audittrail/savearea directory.

Example 2

In this example, the statement references a list of HADB users who accessed the HADB server between April 1st and April 30th, 2017. The audit trail files to be referenced are stored in the audit trail storage directory (/mnt/audittrail/savearea).

Example of SELECT statement execution

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

Explanation

The USER_NAME column stores the authorization identifier of the HADB user. The EXEC_TIME column contains the date and time at which the HADB user performed the operation on the HADB server (the completion date and time of the event). The USER_NAME and EXEC_TIME columns are the column names in the table function derived table created by the ADB_AUDITREAD function. For details about the column names of table function derived tables, see 12.9.2 Column structure of table function derived table when retrieving audit trails.

For further examples of SELECT statements used when referencing audit trails, see 12.7.5 Performing regular auditing.

For details about the specification rules for the ADB_AUDITREAD function, see ADB_AUDITREAD function in the manual HADB SQL Reference.

▪ Notes

• The audit trail files that can serve as input information for the ADB_AUDITREAD function are the audit trail files output by the HADB server, and these same files after compression by the gzip command of the OS. If you are operating multiple HADB servers, the ADB_AUDITREAD function can also use as input information the audit trail files output by other servers.

• In the ADB_AUDITREAD function, you can specify audit trail files that have been compressed by the gzip command. You cannot specify gzip files that were archived using the tar command.

• Make sure that the appropriate read and execution permissions are assigned and the HADB administrator can access the directories included in the audit trail file path specified in the ADB_AUDITREAD function. For example, if the audit trail files are stored in the /mnt/audittrail/savearea directory, make sure that the read and execution permissions assigned to the /, /mnt, /mnt/audittrail, and /mnt/audittrail/savearea directories all allow access by the HADB administrator. Also assign the appropriate read permission for the audit trail files under the /mnt/audittrail/savearea directory so that the HADB administrator can read them. If the correct permissions are not assigned, the SELECT statement with the ADB_AUDITREAD function specified will cause an error.

• We recommend that you do not operate the audit trail facility in a way that involves referencing audit trails in the audit trail files in the audit trail directory. If an audit trail file is deleted while an SQL statement is referencing the audit trails it contains, the SQL statement might yield unexpected results. Audit trail files are deleted at the following times:

  • When the audit trail files in the audit trail directory are moved to the audit trail storage directory or audit trail long-term storage directory

  • When both of the following conditions are met and the current audit trail file is swapped:

    • A maximum number of generations of audit trail file has been specified

    • The number of audit trail files in the audit trail directory has reached the maximum

  The second of these scenarios is operationally unavoidable because the HADB server deletes the audit trail files automatically. For this reason, we recommend that you reference the audit trails in audit trail files stored in the audit trail storage directory or the audit trail long-term storage directory.

  If you need to reference audit trails that are in an audit trail file in the audit trail directory, move the audit trail file to the audit trail storage directory before referencing the trails.

• You cannot reference the audit trails in the current audit trail file. If you want to reference the audit trails in this file, swap the current audit trail file and then reference the file you swapped out. For details about how to swap the current audit trail file, see 12.4.2 Swapping the current audit trail file.

To Page Top