Using solidDB® Audit info (AuditInfoLogEnabled)

Administrator Guide : Security : Using solidDB® audit features : Using solidDB® Audit info (AuditInfoLogEnabled)

Using solidDB^® Audit info (AuditInfoLogEnabled)

solidDB^® Audit Info is based on generating a set of human readable files in the file system. The files will contain a full set of records on all SQL executions, data model operations, user logins, Hot Standby state changes and other monitorable operations.

Analysis of operations having taken place inside the database based on contents of the Audit Info files will require work or a separate analysis program and understanding on the structure of the file(s). This document will describe the structure of the files. solidDB^® product package, however, will not contain additional tools for the analysis.

Due to the files being in human readable format it is strongly recommended to guard the access to the files very carefully. The access rights to the directory of the files should be strictly limited.

Audited operations

To set up audition policies properly, it is essential to understand what kind of information the audit feature collects and what it leaves out. Basically, solidDB^® Audit Info can collect information about.

▪All SQL Operations (DML, DDL and ADMIN COMMANDS) coming through ODBC and JDBC interfaces

▪All SQL Operations executed inside solidDB^® Stored Procedure Language inside Stored Procedures and Triggers

▪A number of operational events necessary to ensure audition information’s completeness such as startups, shutdows, connects, disconnects, transaction commits and rollbacks and HotStandby role changes

▪SQL operations executed by solidDB^® tools that use ODBC to execute SQL (solloado)

Some database-related operations are not passing through audition layers and will not be audited. These operations are:

▪Data operations coming through SA interface. This will also include SA-based solidDB^® tools (solload).

▪Operations where data is coming into the server from another server using solidDB^®’s own replication technologies HotStandby and Advanced Replication. See separate sections about these technologies for more details.

▪Trigger modifying the column value inside trigger code.

Operating Audit Info feature

solidDB^® Audit Info feature is controlled by [Srv]AuditInfoLogEnabled setting in solid.ini file. The setting defines whether audit info is being collected or not. There is a handful of other setttings to control file sizes, numbering patterns and other details. The settings are described in table X.

The file will be generated in a specific directory defined by setting [Srv]AuditInfoLogDir. Since the Audit Info file(s) are likely to contain sensitive data in readably format, it is recommended to restrict the access to this directory to users who require the access to monitor and manage audition feature. Using solidDB^® working directory is not necessarily practical choice for audit directory since DBAs typically require access to files residing in this directory.

Name	Type	Description	Factory value
AuditInfoLogDir	Directory name	The setting contains the directory path for storing the audit info files. The directory has to exist prior to starting the server. If the directory becomes unreachable or full while Audit is in progress, audit will fail with appropriate error code.
AuditInfoLogEnabled	Yes/No	Enable audit info logging into solaudit.out file.	No
AuditInfoLogFileSize	Number, bytes	Max file size for audit info file logging, used if AuditInfoLogEnabled=Yes. Audit info logging uses multiple files with each of size AuditInfoLogFileSize. Latest file is solaudit.out and older files are renamed using a pattern solaudit.<number>.out.	100000000 (about 100 MB)
AuditInfoLogMaxSize	Number, bytes	Max total size for audit info log files, used if AuditInfoLogEnabled=Yes. When max size is reached then oldest audit file is deleted.	1000000000 (about 1 GB)
AuditInfoLogFileNum	Number	Internal parameter to keep the latest audit log file number.
AuditInfoMemQueueLength	Number, number of records	Maximum memory queue length used to buffer audit info records. If audit info records are not processed when the queue gets full then applications need wait for new space in the queue.	10000
AuditInfoReadMaxWait	Number, seconds	Max time to wait audit read operation before records are discarded.	5
AuditInfoFilter	String	See separate table for details.	None

Solmsg.out file will contain information about audition being started at the startup of the server. If for some reason, solidDB^® server process is not able to write to the directory there will be an error message in solmsg.out file. The reason could be lacking rights to directory, directory not being there, disk being full or other problem.

It is possible to filter only certain kinds of database operations to be collected. The configuration is done in the server-side solid.ini file with setting AuditInfoFilter. The details are described in the following table.

Name	Description
params	Parameter values for monitored statements are included.
allsql	All SQL statements are monitored, also those not directly initiated by the user. When this filter is set for example SQL statements executed in stored procedures are included in the monitor output.
read	SQL statements that read table data are included in the monitor output. This means all SELECT statements are monitored.
write	SQL statements that write table data are included in the monitor output. This means that all INSERT, DELETE and UPDATE statements are monitored.
call	Stored procedure calls are included in the monitor output. Since it is not possible to classify procedures as read or write operations a separate filter is used for them.
ddl	SQL DDL statements are included in the monitor output.
dml	SQL DML statements are included in the monitor output.

Example parameter value that monitors SQL write and procedure call operations. Also parameter values are included.

AuditInfoFilter=write, call, params

Audit Info can not be started or stopped by ADMIN COMMANDs. It’s state, however, can be monitored by command ADMIN COMMAND ‘AUDIT STATUS’. The result set can return one of the following:

Return value	Description
Audit is active	Audit is active and collection of audit files to audit directory is in progress. Since the feature can not be dynamically turned off, Audit has been active since the startup of the server. To turn off the feature, modify the solid.ini and restart the server.
Audit not active	Audit is not active and has not been active since last startup.
Audit error, errno #	Audit was initially activated but has failed at some point. The error number is error caused by file system when writing audit entries failed.
Audit log file open failed, errno	Audit feature’s first write operation failed. The error number is error caused by file system when writing audit entries failed.

Validating Audit Info completeness

Ensuring that Audit Trail covers the entire scope being audited is essential. With solidDB^® Audit Info, the Audit Trail will consist of potentially large number of large files. To validate the completeness of the trail we recommend running a check that validates the numbering of audit log entries. The entries should contain all numbers from 0 (or start of audit period) to end of audit period. The number is the field msg_id in the audit trail file.

Note that when using solidDB^® in HotStandby configuration, Audit Info files are collected per server not per logical database. Hence, it is necessary to merge Audit Info from two database servers to one to create a consistent view of the audit trail of logical database server. See Using Audit Info with solidDB^® HotStandby for more details.