Was this helpful?
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