Access to the Security Audit Log
Access to the security audit log is established through registering the security audit log file as a virtual table. After it has been successfully registered, the security audit log file can be queried as any other table.
Registering the Security Audit Log File
To access the security audit log file contents with SQL query statements, you must first register the audit log file as a virtual table using the REGISTER TABLE statement with the DBMS=SXA clause.
The following statements, for example, make a subset of the security audit log file sal1.log available through the table sal1:
REGISTER TABLE sal1(
database CHAR(24) NOT NULL,
audittime DATE NOT NULL,
user_name CHAR(24) NOT NULL,
auditstatus CHAR(1) NOT NULL,
auditevent CHAR(24) NOT NULL,
objecttype CHAR(24) NOT NULL,
objectname CHAR(24) NOT NULL,
description CHAR(80) NOT NULL
)
AS IMPORT FROM 'sal1.log'
WITH DBMS = SXA;
The REGISTER TABLE statement, when used to register the security audit log file, requires the auditor privilege.
When the virtual table is no longer needed, a user with the auditor privilege can use the REMOVE TABLE statement, specifying the name of the virtual table created using REGISTER TABLE.
To display information on registered objects, use the HELP REGISTER statement.
For the complete syntax (including specifications for the security log audit file format) for the REGISTER TABLE and REMOVE TABLE statements, see the SQL Reference Guide.
Querying the Registered Virtual Table
After the security audit log is registered, any user with the auditor privilege can perform queries on the registered virtual table to view its contents.
For example, to obtain all events by the user spy against the database securedb, query the table sal1 as follows:
SELECT audittime, auditstatus, auditevent,
objecttype, objectname, description
FROM sal1
WHERE DATABASE = 'securedb' AND user_name = 'spy'
ORDER BY audittime;
The result of the query might be similar to the following:
audittime auditstatus auditevent objecttype objectname descrpt
01‑Jan‑2008 01:00 N SELECT TABLE salaries Attempt to
access a TABLE
01-Jan-2008
Obtain the Current Audit File Name
The dbmsinfo function can be used to find the name of the audit log file. You must have the auditor privilege to use this call.
To obtain the name of the current security audit log file
Issue the following statement:
dbmsinfo('security_audit_log')
The function returns the file name only, not the full file specification.
Alternatively, you can access the current file through the iiaudit system catalog, in the iidbdb system database.
Last modified date: 11/09/2022