Generic SQL Audit Trail

Last modified 18 Sep 2024 22:22 +02:00
Deprecated
This functionality is deprecated since version 4.4. The functionality is still supported and maintained, but it will no longer be extended. The plan is to remove this functionality sooner or later. Users of this functionality are strongly encouraged to stop using this functionality and migrate to a newer equivalent.
Table of Contents
This page documents the audit trail based on the old Generic SQL Repository. For the new Native PostgreSQL audit trail see this page.

Configuration overview

SQL auditing is configured in config.xml inside midPoint home directory, also known as midpoint.home.

See Auditing for more general information about auditing and some other configuration options, not specific for the SQL trail implementation. These options, often realized via SystemConfiguration object customizations, are not covered in this document.

SQL audit trail for the old Generic Repository is enabled in audit configuration element by adding auditService element containing auditServiceFactoryClass element with the value com.evolveum.midpoint.repo.sql.SqlAuditServiceFactory.

For example, the audit section may look like this (the first auditService is not related to the repository):

<audit>
    <auditService>
        <auditServiceFactoryClass>com.evolveum.midpoint.audit.impl.LoggerAuditServiceFactory</auditServiceFactoryClass>
    </auditService>
    <auditService>
        <auditServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlAuditServiceFactory</auditServiceFactoryClass>
    <auditService>
</audit>

Without further configuration, audit uses the same datasource as the main repository. The audit tables (see below) must be part of the repository database - which is the default.

Configuration options

Basic connection options

The following table lists the basic configuration options for the repository element:

Option Description Default

jdbcUrl

URL for JDBC connection. This must be used (unless non-recommended dataSource is present) and can optionally conatain username and password. See Connecting to the Database from PostgreSQL JDBC Driver documentation for more.

jdbc:postgresql://localhost:5432/midpoint

jdbcUsername

Username for JDBC connection. Can be empty, if username is not needed or provided in JDBC URL or otherwise.

Example: midpoint

jdbcPassword

Password for JDBC connection. Can be empty, if password is not needed or provided in JDBC URL or otherwise.

Example: password

database

Ignored by the Native repository and cannot be changed - do not use it.

postgresql

driverClassName

Ignored by the Native repository and cannot be changed - do not use it.

org.postgresql.Driver

dataSource

Uses JNDI DataSource loading, when this option is defined in configuration. This is only relevant for WAR deployment which is not recommended anyway. jdbcUrl, jdbcUsername, jdbcPassword, and driverClassName is ignored and should not be used. Example: <dataSource>java:comp/env/jdbc/midpoint</dataSource>

WARNING: This is obsolete functionality that is no longer supported or maintained. It is relevant only for WAR deployments on Tomcat and even there we recommend using explicit configuration using options above.

Using empty username and password

It is possible to connect to the database without specifying password or username or both. Examples are:

  • Providing the username and password in JDBC URL.

  • Using PostgreSQL trust authentication - though this is definitely not recommended for serious deployments.

Simply skip configuration elements jdbcUsername and jdbcPassword. If everything is configured as expected, connection will be successful, otherwise JDBC driver will throw an exception and midPoint will not start.

Other connection pool options

All these options are optional, but can be used to fine-tune the repository setup.

MidPoint uses HikariCP as a connection pool (version 4.x). Detailed descriptions of connection pool related options can be found in Hikari configuration documentation. Names used below are the same as names used in HikariCP unless stated otherwise in the description.

All time values are in milliseconds (ms).

Option Description Default

minPoolSize

Minimal # of connections in connection pool, if connection pool is not provided through dataSource.

This option is called minimumIdle in HikariCP. It is always set by midPoint, so HikariCP default is never used.

The value cannot be lower than 2 for midPoint, and - if set so, minimum value 2 will be used.

8

maxPoolSize

Maximum # of connections in connection pool, if connection pool is not provided through dataSource. Please, be aware that for the multi-node setup the total number of connections must not go above the max_connections in the Postgres configuration, so this is a good default only up to 2 nodes. See PostgreSQL Configuration for more tips.

When midPoint needs another connection from the pool it will wait for it. The time is determined by the default value for HikariCP connectionTimeout option, which is 30s. Currently, this cannot be changed in midPoint config, but it is a reasonable time. But if the pool is oversized and HikariCP asks for the connection from the DB, and there is no free connection available, the request fails immediately.

It is better to lower the pool size on the nodes to that case. Don’t raise the max_connections in PostgreSQL without testing it first! It has far-reaching consequences and can actually hurt performance badly.

This option is called maximumPoolSize in HikariCP. It is always set by midPoint, so HikariCP default is never used.

This value cannot be lower than minPoolSize - if set so, effective minPoolSize value is used.

40

maxLifetime

Time after which the connection is retired from the pool. This should be lower than any connection time limit used by the DB or the network infrasctructure.

The minimum allowed value is 30000ms (30 seconds).

none, HikariCP sets 1800000 (30 minutes) by default

idleTimeout

Time after which an idle connection may be retired if current number of connections is higher than minPoolSize.

The minimum allowed value is 10000ms (10 seconds).

none, HikariCP sets 600000 (10 minutes) by default

keepaliveTime

Controls the frequency for keepalive check on idle connections. Keepalive ping contacts the DB backend, so it can prevent connection failures if some network infrastructure drops idle connections.

The minimum allowed value is 30000ms (30 seconds), 0 disables this feature.

none, HikariCP sets 0 (disabled)

leakDetectionThreshold

If the connection is out of the pool (used by the application) for longer than the threshold, the message is logged to indicate possible connection leak, including the stacktrace where the connection was obtained.

The minimum allowed value is 2000 (2 seconds), 0 disables this feature.

none, HikariCP sets 0 (disabled)

initializationFailTimeout

Hikari pool initialization failure timeout, in milliseconds. It is there to allow midPoint to wait until the repository is up and running and therefore to avoid failing prematurely.

1

Other repository configuration options

Option Description Default

fullObjectFormat

Property specifies format (language) used to store serialized object representation into m_object.fullObject and other columns storing serialized object or container representation. Supported values are json and xml. This is safe to change any time, objects are read properly regardless of the format they are stored in.

json

iterativeSearchByPagingBatchSize

The size of the "page" for iterative search, that is the maximum number of results returned by a single iteration. This is a rather internal setting and the default value is reasonable balance between query overhead and time to process the results.

It can be raised if the iterative search overhead (executing the select) is too high compared to the time used for processing the page results.

100

There are no options for compression as this is left to PostgreSQL. This also makes the inspection of the values in the columns easier.

Audit tables

Audit logs are stored in multiple tables whose structure is described in code block below. You can find table structures for different DB vendors in out git, or in the distribution packages in the folder doc/config/sql/generic.

  • id column in m_audit_event table is now generated by default (auto increment).

  • Columns delta and fullResult in m_audit_delta table are optionally compressed using GZIP.

Audit tables in Oracle database
CREATE TABLE m_audit_delta (
  checksum          VARCHAR2(32 CHAR) NOT NULL,
  record_id         NUMBER(19, 0)     NOT NULL,
  delta             BLOB,
  deltaOid          VARCHAR2(36 CHAR),
  deltaType         NUMBER(10, 0),
  fullResult        BLOB,
  objectName_norm   VARCHAR2(255 CHAR),
  objectName_orig   VARCHAR2(255 CHAR),
  resourceName_norm VARCHAR2(255 CHAR),
  resourceName_orig VARCHAR2(255 CHAR),
  resourceOid       VARCHAR2(36 CHAR),
  status            NUMBER(10, 0),
  PRIMARY KEY (record_id, checksum)
) INITRANS 30;
CREATE TABLE m_audit_event (
  id                NUMBER(19, 0) GENERATED AS IDENTITY,
  attorneyName      VARCHAR2(255 CHAR),
  attorneyOid       VARCHAR2(36 CHAR),
  channel           VARCHAR2(255 CHAR),
  eventIdentifier   VARCHAR2(255 CHAR),
  eventStage        NUMBER(10, 0),
  eventType         NUMBER(10, 0),
  hostIdentifier    VARCHAR2(255 CHAR),
  initiatorName     VARCHAR2(255 CHAR),
  initiatorOid      VARCHAR2(36 CHAR),
  initiatorType     NUMBER(10, 0),
  message           VARCHAR2(1024 CHAR),
  nodeIdentifier    VARCHAR2(255 CHAR),
  outcome           NUMBER(10, 0),
  parameter         VARCHAR2(255 CHAR),
  remoteHostAddress VARCHAR2(255 CHAR),
  requestIdentifier VARCHAR2(255 CHAR),
  result            VARCHAR2(255 CHAR),
  sessionIdentifier VARCHAR2(255 CHAR),
  targetName        VARCHAR2(255 CHAR),
  targetOid         VARCHAR2(36 CHAR),
  targetOwnerName   VARCHAR2(255 CHAR),
  targetOwnerOid    VARCHAR2(36 CHAR),
  targetOwnerType   NUMBER(10, 0),
  targetType        NUMBER(10, 0),
  taskIdentifier    VARCHAR2(255 CHAR),
  taskOID           VARCHAR2(255 CHAR),
  timestampValue    TIMESTAMP,
  PRIMARY KEY (id)
) INITRANS 30;
CREATE TABLE m_audit_item (
  changedItemPath VARCHAR2(900 CHAR) NOT NULL,
  record_id       NUMBER(19, 0)      NOT NULL,
  PRIMARY KEY (record_id, changedItemPath)
) INITRANS 30;
CREATE TABLE m_audit_prop_value (
  id        NUMBER(19, 0) GENERATED AS IDENTITY,
  name      VARCHAR2(255 CHAR),
  record_id NUMBER(19, 0),
  value     VARCHAR2(1024 CHAR),
  PRIMARY KEY (id)
) INITRANS 30;
CREATE TABLE m_audit_ref_value (
  id              NUMBER(19, 0) GENERATED AS IDENTITY,
  name            VARCHAR2(255 CHAR),
  oid             VARCHAR2(36 CHAR),
  record_id       NUMBER(19, 0),
  targetName_norm VARCHAR2(255 CHAR),
  targetName_orig VARCHAR2(255 CHAR),
  type            VARCHAR2(255 CHAR),
  PRIMARY KEY (id)
) INITRANS 30;
CREATE TABLE m_audit_resource (
  resourceOid     VARCHAR2(255 CHAR) NOT NULL,
  record_id       NUMBER(19, 0)      NOT NULL,
  PRIMARY KEY (record_id, resourceOid)
) INITRANS 30;

Custom column

EXPERIMENTAL

This feature is experimental. It means that it is not intended for production use. The feature is not finished. It is not stable. The implementation may contain bugs, the configuration may change at any moment without any warning and it may not work at all. Use at your own risk. This feature is not covered by midPoint support. In case that you are interested in supporting development of this feature, please consider purchasing midPoint Platform subscription.
MidPoint 4.2 and later

This feature is available only in midPoint 4.2 and later.

When we need some other information in the audit table, we can add custom columns to the table in the database. We can use new column to tag some special audit records for reporting.

Custom column have three parts of configuration:

  1. Creating column of m_audit_event table in database using SQL command.

  2. Adding of configuration for every custom column to config.xml file in Midpoint home directory.

  3. Adding of configuration of details how an audit event record property is created to system configuration file.

1. Creating column of m_audit_event
ALTER TABLE m_audit_event ADD custFoo VARCHAR(255);

CREATE INDEX iAuditEventCustFoo
    ON m_audit_event (custFoo);

When we want search by our column, we add index for it. Please for name of our new column use 'custXXX' where XXX represents your name. It is important in order to we obviate conflict with already exist columns.

2. Adding of configuration for every custom column to config.xml file
<configuration>
    <midpoint>
        ...
        <audit>
            <auditService>
                <auditServiceFactoryClass>com.evolveum.midpoint.audit.impl.LoggerAuditServiceFactory</auditServiceFactoryClass>
            </auditService>
            <auditService>
                <auditServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlAuditServiceFactory</auditServiceFactoryClass>
                <customColumn>
                    <columnName>custFoo</columnName>
                    <eventRecordPropertyName>foo</eventRecordPropertyName>
                </customColumn>
            </auditService>
        </audit>
        ...
    </midpoint>
</configuration>

After adding of this configuration is needed restart of midpoint.

3. Details how an audit event record property is created
<systemConfiguration>
    ...
    <audit>
        <eventRecording>
            <property>
                <name>foo</name>
                <expression>
                    <path>$target/extension/foo</path>
                </expression>
            </property>
        </eventRecording>
    </audit>
    ...
</systemConfiguration>

We can use a script in expression for obtaining some special information. In the script we can use variable target, which represent target object of audit record, and auditRecord, which represent audit record itself (type AuditEventRecord).

An example with custom column is available in the samples.

Configuration examples

Audit to main repository + logs

SQL repository + auditing to PostgreSQL, audit also to logs. If auditService for SqlAuditServiceFactory does not contain any other configuration, main repository configuration is used - in fact, the same data source (connection pool, etc.) is shared.

<repository>
    <repositoryServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlRepositoryFactory</repositoryServiceFactoryClass>
    <embedded>false</embedded>
    <driverClassName>org.postgresql.Driver</driverClassName>
    <hibernateHbm2ddl>update</hibernateHbm2ddl>
    <hibernateDialect>org.hibernate.dialect.PostgreSQLDialect</hibernateDialect>
    <jdbcPassword>midpoint</jdbcPassword>
    <jdbcUsername>midpoint</jdbcUsername>
    <jdbcUrl>jdbc:postgresql://localhost:5432/midpoint</jdbcUrl>
</repository>
<audit>
    <auditService>
        <auditServiceFactoryClass>com.evolveum.midpoint.audit.impl.LoggerAuditServiceFactory</auditServiceFactoryClass>
    </auditService>
    <auditService>
        <auditServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlAuditServiceFactory</auditServiceFactoryClass>
    <auditService>
</audit>

Audit to separate database

Since 4.2
This functionality is available since version 4.2.

Configuration element auditService for SqlAuditServiceFactory can contain any element from repository configuration - see Repository Configuration for more.

  • One of jdbcUrl or dataSource must be used - otherwise any repository configuration in auditService is ignored and only main repository configuration is used.

  • The recommended way is to use jdbcUrl with jdbcUsername, jdbcPassword and driverClassName and database.

  • Preconfigured dataSource for WAR deployment on application server or Tomcat is also possible - but this is not recommended anymore and not officially supported.

  • In both cases the right value for database element should be used, e.g. postgresql.

  • Separate database must be prepared in a similar way to the main database with tables, indexes and foreing keys containing "audit" created. For PostgreSQL example, see PostgreSQL page, section Separate audit database.

Example configuration with default Oracle for main repository and PostgreSQL on localhost for audit:

<repository>
    <repositoryServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlRepositoryFactory</repositoryServiceFactoryClass>
    <database>oracle</database>
    <jdbcUsername>midpoint</jdbcUsername>
    <jdbcPassword>password</jdbcPassword>
    <jdbcUrl>jdbc:oracle:thin:@localhost:1521/xe</jdbcUrl>
</repository>
<audit>
    <auditService>
        <auditServiceFactoryClass>com.evolveum.midpoint.audit.impl.LoggerAuditServiceFactory</auditServiceFactoryClass>
    </auditService>
    <auditService>
        <auditServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlAuditServiceFactory</auditServiceFactoryClass>
        <jdbcUrl>jdbc:postgresql://localhost/midaudit</jdbcUrl>
        <database>postgresql</database>
        <jdbcUsername>midaudit</jdbcUsername>
        <jdbcPassword>password</jdbcPassword>
    </auditService>
</audit>

See Also

Was this page helpful?
YES NO
Thanks for your feedback