Creating Audit DBEnvironments [ ALLBASE/SQL Database Administration Guide ] MPE/iX 5.5 Documentation
ALLBASE/SQL Database Administration Guide
Creating Audit DBEnvironments
Audit functionality is a group of statements and statement parameters
that allows you to generate audit log records. Audit log records contain
partition information that allows you to group log records for analysis
with the Audit Tool. Some types of database operations you might analyze
are INSERT, UPDATE, or DELETE operations, perhaps for security reasons.
Audit log records contain identifiers such as table names in contrast to
non-audit database log records which contain identifiers such as page
references and data. When audit logging is enabled, these audit log
records are generated in addition to non-audit database log records. You
can use the Audit Tool, described in the section "Using the Audit Tool,"
to audit these log records.
Audit DBEnvironments are defined by specifying audit parameters in the
START DBE NEW or START DBE NEWLOG statement.
The six parameters used to make a DBEnvironment an audit DBEnvironment
are listed below. One of the six, the AUDIT LOG parameter, causes the
other five audit parameters to be in effect. None of the five parameters
is in effect unless you specify AUDIT LOG.
AUDIT LOG causes the audit parameters listed below to be
in effect.
AUDIT NAME identifies the DBEnvironment in each
transaction.
DEFAULT PARTITION identifies the default partition number for the
DBEnvironment.
COMMENT PARTITION identifies the comment partition number for the
DBEnvironment.
MAXPARTITIONS specifies the maximum number of partitions in
an audit DBEnvironment.
AUDIT ELEMENTS consists of the following elements:
COMMENT allows use of the LOG
COMMENT statement.
DATA generates audit log records
for user data write
operations (INSERT, DELETE,
and UPDATE).
DEFINITION generates audit log records
for statements that define
data.
STORAGE generates audit log records
for file and storage
statements.
AUTHORIZATION generates audit log records
for authorization
statements.
SECTION generates audit log records
for the creation and
deletion of permanent
sections.
ALL specifies generation of
audit log records for all
audit elements.
Audit elements are prioritized in a simple hierarchy where the following
assumptions exist:
1. DATA is assumed to be specified if AUDIT LOG is in effect. In
other words, if AUDIT LOG is specified in a START DBE NEW or START
DBE NEWLOG statement, without specifying any audit elements, only
DATA is in effect.
2. Audit elements can be explicitly specified as shown below:
DATA STORAGE SECTION AUDIT ELEMENTS;
3. Specifying ALL assumes that all audit elements are requested.
See the syntax for the START DBE NEW and START DBE NEWLOG statements in
the "SQL Statements" chapter of the ALLBASE/SQL Reference Manual for
information on how to specify audit DBEnvironment parameters.
Example of Setting Up an Audit DBEnvironment
The following examples show how to create a DBEnvironment, load it, and
then enable audit logging.
First, create the DBEnvironment with a temporary log named TempLog:
START DBE 'DBE1' MULTI NEW
BUFFER = (240, 120),
TRANSACTION = 50,
DBEFile0 DBEFILE MyDBE1
WITH PAGES = 300,
NAME = 'MyDBE1',
LOG DBEFILE TempLog
WITH PAGES = 5000,
NAME = 'TempLog';
CREATE TABLE MyTable1
.
.
.
CREATE TABLE MyTable2
.
.
.
LOAD FROM INTERNAL LdFile1 TO MyTable1;
COMMIT WORK;
LOAD FROM INTERNAL LdFile2 TO MyTable2;
COMMIT WORK;
.
.
.
COMMIT WORK RELEASE;
Now you can use START DBE NEWLOG to enable audit logging and audit files:
[REV BEG]
START DBE 'DBE1' MULTI NEWLOG
AUDIT LOG,
AUDIT NAME = 'MyDBE1',
DEFAULT PARTITION = 1,
MAXPARTITIONS = 10,
ALL AUDIT ELEMENTS
LOG DBEFILE MyLog1
WITH PAGES = 5000,
NAME = 'MyLog1';
EXIT;
[REV END]
Now use SQLUtil to create the additional log file that is needed for
audit DBEnvironments:
isql=> sqlutil
>> addlog
DBEnvironment Name: DBE1
Maintenance Word:
Enter Log File Name(s) Separated by a Blank? MyLog2
New Log File Size? 5000
Add Log File (y/n)? y
Log file 'MyLog2' Was Added.
Log Identifier Is: 2
>> exit
Log files need to be made slightly larger to account for audit log
records generated in addition to non-audit log records. Audit log
records are generated for all the statement types specified in the AUDIT
ELEMENTS parameter, so log files may fill up more quickly with audit
logging specified.
Defining Additional Audit DBEnvironment Log Files
Audit DBEnvironments require that at least one additional log file be
added. This is performed with the SQLUtil ADDLOG command. It is
recommended that several additional log files be added because log files
will fill up more quickly.
When START DBE NEWLOG is executed for an existing audit DBEnvironment,
most audit-related parameters not specified remain unchanged. The AUDIT
LOG parameter is an exception. If AUDIT LOG is in effect and you execute
a START DBE NEWLOG statement to change parameter values without again
specifying AUDIT LOG, audit logging is then not in effect.
Disabling Audit Logging
You can disable audit logging for a particular session where you are
entering statements that should not generate audit log records. This
allows all other sessions to continue to generate audit log records. The
following statement is used to disable audit logging for a session:
DISABLE AUDIT LOGGING
Audit logging should be enabled again before the session is ended. The
following statement is used to enable audit logging:
ENABLE AUDIT LOGGING
However, since disabling only lasts for the duration of a session, when
the session ends, audit logging is enabled even if you do not explicitly
enable it again.
MPE/iX 5.5 Documentation