AUDIT

AUDIT

Syntax

audit ::=

audit_operation_clause ::=

by_clause ::=

audit_object_clause ::=

ddl_clause ::=

Prerequisites

Only the SYS user can configure auditing conditions with this statement.

Description

Auditing tracks specific statements or all statements being executed on the Altibase server in real time and records their information. Auditing conditions can be configured with this statement.

audit_operation_clause

This specifies that auditing is to be performed on the execution of certain SQL statements on the Altibase server.

The auditing target operation is specified in sql_statement_type of this clause. Using the comma(,), multiple operations can be specified together. The following operations can be specified for auditing:

• SELECT
• INSERT
• UPDATE
• DELETE
• MOVE
• MERGE
• ENQUEUE
• DEQUEUE
• LOCK
• EXEC or EXECUTE
• COMMIT
• ROLLBACK
• SAVEPOINT
• CONNECT
• DISCONNECT
• ALTER SESSION
• ALTER SYSTEM

ALL specifies auditing on all of the statements listed above.

by_clause

This specifies that auditing is to be performed on the statements that are executed by a certain user.

audit_object_clause

This specifies that auditing is to be performed on certain operations being executed on certain objects on the Altibase server.

Using the comma(,), multiple operations can be specified together. The following table shows the types of objects specifiable for auditing target operations and their auditing descriptions.

Operation	Object Type	Auditing Description
SELECT	TABLE	The SELECT statement being operated on the target table object.
INSERT	TABLE	The INSERT statement being operated on the target table object.
UPDATE	TABLE	The UPDATE statement being operated on the target table object.
DELETE	TABLE	The DELETE statement being operated on the target table object.
MOVE	TABLE	The MOVE statement being operated on the target table object.
MERGE	TABLE	The MERGE statement being operated on the target table object.
ENQUEUE	QUEUE	The ENQUEUE statement being operated on the target queue object.
DEQUEUE	QUEUE	The DEQUEUE statement being operated on the target queue object.
LOCK	TABLE	The LOCK statement being operated on the target table object.
EXEC/EXECUTE	PROCEDURE	The statement which is executing the target procedure object.

ALL specifies auditing of all SQL statements executable on object types; however, only the above statements are valid.

object_name

This specifies the name of the object which is to be an auditing target. Object types available for specification are tables, views, queues, sequences, stored procedures and stored functions.

ddl_clause

This specifies auditing the execution of DDL statements on the Altibase server.

BY ACCESS | SESSION

If BY ACCESS is specified, an audit log is written for every statement or operation that meets the condition. For example, if auditing is performed with BY ACCESS specified and the same SQL statement is executed ten times in the same session, then ten audit logs are written.

If BY SESSION is specified, only one audit log is recorded in 1 prepare - n execute structure to prevent leaving duplicated logs. Also the statistics log of the most recently executed statement will be recorded at the time the statement ends.

When neither is specified, BY SESSION is the default value.

BY ACCESS|SESSION is not supported if the audit target is CONNECT, DISCONNECT, or DDL.

WHENEVER [NOT] SUCCESSFUL

If WHENEVER SUCCESSFUL is specified, only successful SQL statements and operations are audited.

If WHENEVER NOT SUCCESSFUL is specified, only unsuccessful SQL statements and operations are audited.

On omission of this clause, Altibase performs auditing, regardless of success or failure.

If 'BY SESSION WHENEVER SUCCESSFUL' is specified, the same SQL statements executed in a session must all succeed for an audit log of that statement to be written.

If 'BY SESSION WHENEVER NOT SUCCESSFUL' is specified, the same SQL statements executed in a session must fail, at least once, for an audit log of that statement to be written.

Precautions

Even if auditing conditions are configured with the AUDIT statement, new conditions are not immediately applied to database auditing. For new auditing conditions to be applied to the running server, auditing must be restarted or RELOAD must be performed for the auditing conditions with the following statements.

ALTER SYSTEM STOP AUDIT;
ALTER SYSTEM START AUDIT;
ALTER SYSTEM RELOAD AUDIT;

Examples

<Query 1> Write logs of all occasions where the execution of INSERT, UPDATE and DELETE statements fail for the table friends of user user1.

iSQL> AUDIT insert, update, delete ON user1.friends BY ACCESS WHENEVER NOT
SUCCESSFUL;

<Query 2> Write audit logs, in the unit of sessions, of all occasions where the execution of DDL statements succeed for the table friends of user user1.

iSQL> AUDIT all ON user1.friends BY SESSION WHENEVER SUCCESSFUL;

<Query 3> Write logs, in the unit of accesses, of all unsuccessful execution of CONNECT, DISCONNECT statements on the Altibase server.

iSQL> AUDIT connect, disconnect WHENEVER NOT SUCCESSFUL;
Audit success.

<Query 4> Write logs, in the unit of sessions, of all executions of INSERT statements on the Altibase server.

iSQL> AUDIT insert;
Audit success.

<Query 5> Write logs of all executions of DDL statements by user user1.

iSQL> AUDIT DDL BY user1;
Audit success.