14. Database Auditing#

This chapter describes the auditing feature which tracks and writes logs in real time of statements running on the Altibase server. Basic guidelines on how DBAs administer and manage auditing are provided.

The auditing feature described in this chapter differs from replication auditing which is dealt with in the altiAudit.

Introduction#

The auditing feature monitors the execution of certain database operations and writes related information to files. DBAs can specify statements and objects as auditing targets

When auditing a database server, the server tracks every statement being executed and writes logs in real time.

Thus, the server processing speed can be relatively slower than usual. Altibase supports audit logs in binary or syslog format (syslog is only supported in Linux).

Terminology#

The following terminology is related to the auditing feature.

Statement Auditing#

The auditing of certain statements.

Object Auditing#

The auditing of operations being executed on objects existing in the database.

Audit Log#

When one of the auditing target statements is executed, the Altibase server writes logs in binary format to a certain file. These are called audit logs.

Audit Control Statement#

The SQL statements provided for the control of the auditing feature. With these statements, DBAs can start/stop auditing, and apply newly added auditing conditions to the server.

Audit Condition Statement#

The SQL statements provided for the addition of auditing conditions. With these statements, DBAs can specify the SQL statements and objects to be audited.

altiAudit#

The utility which outputs audit logs written by the Altibase server in text format for the legibility of users. Altibase saves binary audit logs in the $ALTIBASE_HOME/bin directory and syslog in /var/log. To save audit logs on syslog, change the AUDIT_OUTPUT_METHOD property value, and then re-start the server.

This section describes the meta tables and properties necessary for DBAs to administer and manage auditing.

The following is a list of meta tables and views used in auditing. With these, DBAs can check the auditing status and the specified auditing conditions

SYS_AUDIT_#

The meta table which stores the auditing status.

SYS_AUDIT_OPTS_#

The view which stores the auditing conditions. User-specified auditing conditions are stored in the meta table SYS_AUDIT_ALL_OPTS_ (the base table of this view); however, DBAs are recommended to use the view SYS_AUDIT_OPTS_ , since it only stores information useful to DBAs.

For more detailed information on the columns of each meta table and more, please refer to Altibase Data Dictionary.

Auditing information collected by the Altibase server is written to a file. The following properties are provided for the management of this file.

AUDIT_FILE_SIZE
AUDIT_LOG_DIR
AUDIT_OUTPUT_METHOD
AUDIT_TAG_NAME_IN_SYSLOG

For more detailed information on each property, please refer to General Reference > Chapter 2. Altibase Properties.

Audit Control Statements#

The following SQL statements are provided for starting/stopping database auditing, and the management of newly added auditing conditions.

For more detailed information on SQL statements, please refer to SQL Reference.

Starting Auditing#

The following statement starts the auditing function:

ALTER SYSTEM START AUDIT;

When auditing starts, the values of the SYS_AUDIT_ meta table are altered as follows:

iSQL> ALTER SYSTEM START AUDIT;
Alter success.

iSQL> SET VERTICAL ON;
iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_;
SYS_AUDIT_.IS_STARTED  : 1 
SYS_AUDIT_.START_TIME  : 
SYS_AUDIT_.STOP_TIME   : 16-MAY-2013 
SYS_AUDIT_.RELOAD_TIME : 16-MAY-2013

1 row selected.

Stopping Auditing#

The following statement stops the current auditing function:

ALTER SYSTEM STOP AUDIT;

When auditing is stopped, the values of the SYS_AUDIT_ meta table are altered as follows:

iSQL> ALTER SYSTEM STOP AUDIT;
Alter success.

iSQL> SET VERTICAL ON;
iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_;
SYS_AUDIT_.IS_STARTED  : 0 
SYS_AUDIT_.START_TIME  : 
SYS_AUDIT_.STOP_TIME   : 16-MAY-2013 
SYS_AUDIT_.RELOAD_TIME : 16-MAY-2013

1 row selected.

Applying Auditing Conditions#

Even if DBAs add new auditing conditions with audit control statements, these new conditions are not immediately applied to server auditing. For the newly added auditing conditions to be applied to a running server, auditing must either start or auditing must be reloaded with the below statement:

ALTER SYSTEM RELOAD AUDIT;

When this statement is executed, the database server purges its auditing-related internal memory, and then updates the memory by retrieving new conditions from the meta tables.

While the database server is performing auditing, DBAs can use the RELOAD statement for the database server to continue auditing with new conditions. If auditing is not being performed on the server, however, new conditions can be applied by simply starting auditing.

Deleting Auditing Conditions#

Auditing conditions that have been enabled to perform auditing within the Altibase server can be deleted with the DELAUDIT statement. Auditing conditions in the following categories can be deleted:

Statement auditing and DDL statement auditing conditions that have been enabled with the AUDIT … BY user_name statement
Statement auditing and DDL statement auditing conditions that have been enabled without the BY user_name clause
Object auditing conditions

Auditing conditions cannot be deleted if auditing has already started. You can terminate auditing with the following statement and then delete them.

ALTER SYSTEM STOP AUDIT;

The following example demonstrates how to delete auditing conditions for the user user1. After executing the DELAUDIT statement, you can check that the auditing conditions for user1 have been deleted from SYS_AUDIT_OPTS_.

iSQL> DELAUDIT BY user1;
Audit success.
iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;
No rows selected.

Cautions#

Before starting auditing, it is recommended to initialize the SQL plan cache with the following statement.

ALTER SYSTEM RESET SQL_PLAN_CACHE;

If the Altibase server uses the SQL plan cache, it is possible that some audit logs may not be written; since related information are not written to audit logs when a SQL execution plan, which is stored in the cache prior to auditing, is reused after auditing.

Audit Condition Statements#

This statement specifies, among the statements being executed on the database server, which of the statements operating on a given object are to be audited.

This section explains how to enable/disable auditing conditions for objects, statements, or DDL statements, with examples.

Object Auditing#

This is the monitoring and the writing of logs of the execution of certain operations on certain objects.

Enablement#

The following statement enables object auditing conditions.

AUDIT operation_comma_list
ON object_name
BY ACCESS | SESSION
WHENEVER [NOT] SUCCESSFUL;

For more detailed information on statements, please refer to SQL Reference.

Examples of Enablement#

<Query 1> Write logs of all INSERT, UPDATE, DELETE statements that fail during execution on the table friends of user user1.

AUDIT INSERT, UPDATE, DELETE ON user1.friends BY ACCESS WHENEVER NOT SUCCESSFUL;

<Query 2> Write logs if the execution of every DDL statement on the table friends of user user1 is successful in the session

AUDIT ALL ON user1.friends BY SESSION WHENEVER SUCCESSFUL;

Once auditing is enabled as above, the enabled conditions can be checked by selecting the SYS_AUDIT_OPTS_ view.

iSQL> SET VERTICAL ON;
iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_ WHERE USER_NAME = 'USER1' AND OBJECT_NAME = 'FRIENDS';

USER_NAME        : USER1
OBJECT_NAME      : FRIENDS
OBJECT_TYPE      : TABLE
SELECT_OP        : S/-
INSERT_OP        : S/A
UPDATE_OP        : S/A
DELETE_OP        : S/A
MOVE_OP          : S/-
MERGE_OP         : S/-
ENQUEUE_OP       : S/-
DEQUEUE_OP       : S/-
LOCK_TABLE_OP    : S/-
EXECUTE_OP       : S/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

<Query 3> Write audit logs if the execution of the stored procedure proc1 is successful; on failure, write logs in the unit of accesses.

AUDIT EXECUTE ON proc1 BY SESSION WHENEVER SUCCESSFUL;

AUDIT EXECUTE ON proc1 BY ACCESS WHENEVER NOT SUCCESSFUL;

Once auditing is enabled as above, the enabled conditions can be checked by selecting the SYS_AUDIT_OPTS_ view.

iSQL> SET VERTICAL ON;
iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_ WHERE OBJECT_NAME = 'PROC1';
USER_NAME        : SYS
OBJECT_NAME      : PROC1
OBJECT_TYPE      : PROCEDURE
SELECT_OP        : -/-
INSERT_OP        : -/-
UPDATE_OP        : -/-
DELETE_OP        : -/-
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : S/A
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

<Query 4> Write audit logs, in the unit of accesses, of calls made to the stored procedure proc1 in the SELECT statement.

AUDIT SELECT ON proc1 BY ACCESS;

Once auditing is enabled as above, the enabled conditions can be checked by selecting the SYS_AUDIT_OPTS_ view.

iSQL> SET VERTICAL ON;
iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_ WHERE OBJECT_NAME = 'PROC1';
USER_NAME        : SYS
OBJECT_NAME      : PROC1
OBJECT_TYPE      : PROCEDURE
SELECT_OP        : A/A
INSERT_OP        : -/-
UPDATE_OP        : -/-
DELETE_OP        : -/-
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : -/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

<Query 5> Write audit logs, in the unit of sessions, of calls made to the sequence seq1 in the INSERT statement.

AUDIT INSERT ON seq1;

Once auditing is enabled as above, the enabled conditions can be checked by selecting the SYS_AUDIT_OPTS_ view.

iSQL> SET VERTICAL ON;
iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_ WHERE OBJECT_NAME = 'SEQ1';
USER_NAME        : SYS
OBJECT_NAME      : SEQ1
OBJECT_TYPE      : SEQUENCE
SELECT_OP        : -/-
INSERT_OP        : S/S
UPDATE_OP        : -/-
DELETE_OP        : -/-
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : -/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

<Query 6> Write audit logs, in the unit of sessions, when the execution of every DML statement calling the sequence seq1 is successful.

AUDIT ALL ON seq1 WHENEVER SUCCESSFUL;

Once auditing is enabled as above, the enabled conditions can be checked by selecting the SYS_AUDIT_OPTS_ view

iSQL> SET VERTICAL ON;
iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_ WHERE OBJECT_NAME = 'SEQ1';
USER_NAME        : SYS
OBJECT_NAME      : SEQ1
OBJECT_TYPE      : SEQUENCE
SELECT_OP        : S/-
INSERT_OP        : S/-
UPDATE_OP        : S/-
DELETE_OP        : S/-
MOVE_OP          : S/-
MERGE_OP         : S/-
ENQUEUE_OP       : S/-
DEQUEUE_OP       : S/-
LOCK_TABLE_OP    : S/-
EXECUTE_OP       : S/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

Disablement#

The following statement disables the enabled auditing conditions.

NOAUDIT  operation_comma_list
ON object_name
WHENEVER [NOT] SUCCESSFUL;

For more detailed information on statements, please refer to SQL Reference.

Examples of Disablement#

<Query 1> Assume the following auditing conditions are enabled for the table friends.

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_ WHERE OBJECT_NAME = 'FRIENDS';
USER_NAME        : SYS
OBJECT_NAME      : FRIENDS
OBJECT_TYPE      : TABLE
SELECT_OP        : S/S
INSERT_OP        : S/S
UPDATE_OP        : S/S
DELETE_OP        : S/S
MOVE_OP          : S/S
MERGE_OP         : S/S
ENQUEUE_OP       : S/S
DEQUEUE_OP       : S/S
LOCK_TABLE_OP    : S/S
EXECUTE_OP       : S/S
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

Among these conditions, disable auditing for SELECT statements that complete successfully.

iSQL> NOAUDIT SELECT ON friends WHENEVER SUCCESSFUL;

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_ WHERE OBJECT_NAME = 'FRIENDS';
USER_NAME        : SYS
OBJECT_NAME      : FRIENDS
OBJECT_TYPE      : TABLE
SELECT_OP        : -/S
INSERT_OP        : S/S
UPDATE_OP        : S/S
DELETE_OP        : S/S
MOVE_OP          : S/S
MERGE_OP         : S/S
ENQUEUE_OP       : S/S
DEQUEUE_OP       : S/S
LOCK_TABLE_OP    : S/S
EXECUTE_OP       : S/S
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

Statement Auditing#

This is the monitoring and the writing of logs of the execution of certain SQL statements on the Altibase server.

Enablement#

The following statement enables statement auditing conditions.

AUDIT operation_comma_list 
BY user_name
BY ACCESS|SESSION
WHENEVER [NOT] SUCCESSFUL;

For more detailed information on statements, please refer to SQL Reference.

Examples of Enablement#

<Query 1> Write logs, in the unit of accesses, of all CONNECT and DISCONNECT statements that fail execution on the Altibase server.

iSQL> AUDIT CONNECT, DISCONNECT BY ACCESS WHENEVER NOT SUCCESSFUL;
Audit success

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;

USER_NAME        : ALL
OBJECT_NAME      : ALL
OBJECT_TYPE      : 
SELECT_OP        : -/-
INSERT_OP        : -/-
UPDATE_OP        : -/-
DELETE_OP        : -/-
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : -/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/A
DISCONNECT_OP    : -/A
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

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

iSQL> AUDIT INSERT;
Audit success.

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;

USER_NAME        : ALL  
OBJECT_NAME      : ALL  
OBJECT_TYPE      :   
SELECT_OP        : -/-  
INSERT_OP        : S/S  
UPDATE_OP        : -/-  
DELETE_OP        : -/-  
MOVE_OP          : -/-  
MERGE_OP         : -/-  
ENQUEUE_OP       : -/-  
DEQUEUE_OP       : -/-  
LOCK_TABLE_OP    : -/-  
EXECUTE_OP       : -/-  
COMMIT_OP        : -/-  
ROLLBACK_OP      : -/-  
SAVEPOINT_OP     : -/-  
CONNECT_OP       : -/-  
DISCONNECT_OP    : -/-  
ALTER_SESSION_OP : -/-  
ALTER_SYSTEM_OP  : -/-  
DDL_OP           : -/- 

1 row selected.

<Query 3> Write logs, in the unit of accesses, of every INSERT, UPDATE, SELECT and DELETE statement that fail execution on the Altibase.

iSQL> AUDIT INSERT, UPDATE, SELECT, DELETE BY ACCESS WHENEVER NOT SUCCESSFUL;
Audit success.

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;

USER_NAME        : ALL  
OBJECT_NAME      : ALL  
OBJECT_TYPE      :   
SELECT_OP        : -/A  
INSERT_OP        : -/A  
UPDATE_OP        : -/A  
DELETE_OP        : -/A  
MOVE_OP          : -/-  
MERGE_OP         : -/-  
ENQUEUE_OP       : -/-  
DEQUEUE_OP       : -/-  
LOCK_TABLE_OP    : -/-  
EXECUTE_OP       : -/-  
COMMIT_OP        : -/-  
ROLLBACK_OP      : -/-  
SAVEPOINT_OP     : -/-  
CONNECT_OP       : -/-  
DISCONNECT_OP    : -/-  
ALTER_SESSION_OP : -/-  
ALTER_SYSTEM_OP  : -/-  
DDL_OP           : -/-  

1 row selected.

Disenablement#

The following statement disables statement auditing conditions.

NOAUDIT operation_comma_list
BY user_name
WHENEVER [NOT] SUCCESSFUL;

For more detailed information on statements, please refer to SQL Reference.

Examples of Disablement#

<Query 1> Assume the following auditing conditions are enabled.

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;

USER_NAME        : ALL
OBJECT_NAME      : ALL
OBJECT_TYPE      : 
SELECT_OP        : -/A
INSERT_OP        : -/A
UPDATE_OP        : -/A
DELETE_OP        : -/A
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : -/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

Among these conditions, disable auditing SELECT statements.

iSQL> NOAUDIT SELECT;
Audit success.

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;

USER_NAME        : ALL
OBJECT_NAME      : ALL
OBJECT_TYPE      : 
SELECT_OP        : -/-
INSERT_OP        : -/A
UPDATE_OP        : -/A
DELETE_OP        : -/A
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : -/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

DDL Statement Auditing#

This is the monitoring and the writing of logs of all DDL statements being executed on the Altibase server.

Enablement#

The following statement enables DDL statement auditing conditions.

AUDIT DDL
BY user_name
WHENEVER [NOT] SUCCESSFUL;

For more detailed information on statements, please refer to SQL Reference.

Examples of Enablement#

<Query 1> Write logs of all DDL statements executed by user user1.

iSQL> AUDIT DDL BY user1;
Audit success.

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;

USER_NAME        : USER1
OBJECT_NAME      : ALL
OBJECT_TYPE      : 
SELECT_OP        : -/-
INSERT_OP        : -/-
UPDATE_OP        : -/-
DELETE_OP        : -/-
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : -/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : T/T

1 row selected.

<Query 2> Write logs of all DDL statements that fail on the database server.

iSQL> AUDIT DDL WHENEVER NOT SUCCESSFUL;
Audit success.

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;

USER_NAME        : ALL
OBJECT_NAME      : ALL
OBJECT_TYPE      : 
SELECT_OP        : -/-
INSERT_OP        : -/-
UPDATE_OP        : -/-
DELETE_OP        : -/-
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : -/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/T

1 row selected.

Disablement#

The following statement disables DDL statement auditing conditions.

NOAUDIT DDL
BY user_name
WHENEVER [NOT] SUCCESSFUL;

For more detailed information on statements, please refer to SQL Reference.

Example#

<Query 1> Assume the following auditing conditions are enabled.

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;

USER_NAME        : ALL
OBJECT_NAME      : ALL
OBJECT_TYPE      : 
SELECT_OP        : -/-
INSERT_OP        : -/-
UPDATE_OP        : -/-
DELETE_OP        : -/-
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : -/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : T/T

1 row selected.

Disable auditing DDL statements.

iSQL> NOAUDIT DDL;
Audit success.

iSQL> SELECT * FROM SYSTEM_.SYS_AUDIT_OPTS_;

USER_NAME        : ALL
OBJECT_NAME      : ALL
OBJECT_TYPE      : 
SELECT_OP        : -/-
INSERT_OP        : -/-
UPDATE_OP        : -/-
DELETE_OP        : -/-
MOVE_OP          : -/-
MERGE_OP         : -/-
ENQUEUE_OP       : -/-
DEQUEUE_OP       : -/-
LOCK_TABLE_OP    : -/-
EXECUTE_OP       : -/-
COMMIT_OP        : -/-
ROLLBACK_OP      : -/-
SAVEPOINT_OP     : -/-
CONNECT_OP       : -/-
DISCONNECT_OP    : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP  : -/-
DDL_OP           : -/-

1 row selected.

Cautions#

Statement auditing is the auditing of the success or failure to EXECUTE a SQL statement; the success or failure to PREPARE a SQL statement is not an auditing target.

Viewing Auditing Results#

When auditing is being performed on the database server, the auditing results are stored on the memory in real-time, and under the following circumstances, they are written to a file on the disk.

Whenever one block (32KB) is filled in the memory buffer (1MB).
When auditing is terminated with the ALTER SYSTEM STOP AUDIT statement.
When the Altibase server is terminated.

Altibase can store audit logs in binary files or syslog, depending on the AUDIT_OUTPUT_METHOD property value. The differences between binary records and syslog records are listed in the table below.

[Table 14-1] Record Storage Method : Binary versus Syslog

	Binary Records	Syslog Records
Storage Format	Binary	Text
Storage Location	AUDIT_LOG_DIR	location specified in slog.conf(or syslog.conf)
Record Length	Unlimited	1024 characters(max) per record
Reading Method	Uses altiAudit	Reads file specified in syslog.conf(e.g.,/var/log/messages)
Output Format	Detailed output Summary output	Summary output
Supported Operating System	All	Linux

For more detailed information on the altiAudit utility, please refer to the Audit User's Manual.

Viewing Binary Audit Logs#

The default file location is the $ALTIBASE_HOME/trc directory, however, it can be changed with the AUDIT_LOG_DIR property. The file name format is as below.

alti-[the_epoch_time_sec]-[sequence].aud

Example:

alti-1366989680-0.aud

Since audit logs are stored in binary format, the user cannot read them. Altibase provides the altiAudit utility for the purpose of printing audit logs saved in binary format as text format.

The following example shows how to do so.

altiAudit $ALTIBASE_HOME/trc/alti-1366989680-0.aud

Viewing Syslog Audit Logs#

If AUDIT_OUTPUT_METHOD is set between 1~9, logs are stored in syslog. However, syslog is only supported on Linux.

The following example shows audit logs output using syslog. The [AUDIT] delimiter is set by AUDIT_TAG_NAME_IN_SYSLOG.

$ tail /var/log/user.log
Oct 27 09:22:19 mmj altibase: [AUDIT]SYS,1,127.0.0.1,CLI-64LE,isql,DDL,1,65537,19905,4,2,1,0,0,0,0,0,0,0,0,0,0,0,"create table t1(i int)"

14. Database Auditing#

Introduction#

Terminology#

Statement Auditing#

Object Auditing#

Audit Log#

Audit Control Statement#

Audit Condition Statement#

altiAudit#

Related Meta Tables and Properties#

Related Meta Tables#

SYS_AUDIT_#

SYS_AUDIT_OPTS_#

Related Properties#

Audit Control Statements#

Starting Auditing#

Stopping Auditing#

Applying Auditing Conditions#

Deleting Auditing Conditions#

Cautions#

Audit Condition Statements#

Object Auditing#

Enablement#

Examples of Enablement#

Disablement#

Examples of Disablement#

Statement Auditing#

Enablement#

Examples of Enablement#

Disenablement#

Examples of Disablement#

DDL Statement Auditing#

Enablement#

Examples of Enablement#

Disablement#

Example#

Cautions#

Viewing Auditing Results#

Viewing Binary Audit Logs#

Viewing Syslog Audit Logs#