Managing DirX Audit Databases

DirX Audit stores configuration data, audit messages and history entries in a relational database. The configuration data, audit messages and history entries can be stored in the same database or in separate databases provided that the database server type is the same for the data and history databases. Each tenant has its own database(s). If you want to configure more than one tenant, you must prepare and manage as many databases for as many tenants as you want to set up.

DirX Audit supports the following relational database management systems:

  • SQL Server

  • Oracle Database

The next sections describe how to:

  • Set up DirX Audit Database management

  • Manage DirX Audit Database connectivity

  • Manage SQL Server or Oracle Databases

  • Manage audit messages data

  • Manage history entries data

Setting up Database Management

To set up DirX Audit Database management:

  1. Choose a relational database management server.

  2. Choose a connector / driver.

  3. Create the technical account(s).

  4. Create the database(s).

  5. Create the database object(s) - tables, views and indexes.

The next sections describe how to perform these tasks.

Choose a Relational Database Management Server

You can choose from the list of supported relational database management servers. You can make an individual decision for the audit messages database and for the configuration database but the database type for the audit messages database and history entries database must be the same.

Choose a Connector / Driver

A connector / driver for the SQL Server is pre-installed with DirX Audit Manager and DirX Audit Server. We recommend that you do not change these settings.

A connector / driver for Oracle Database must be installed separately. See the section "Oracle Database JDBC Driver Installation" in "Installation Configurations" in the DirX Audit Installation Guide for instructions on how to install it.

Create the Technical Accounts

Create one, two or three technical accounts for connecting to the database(s) by following the instructions in the relational database management server documentation.

Note that they need the access rights to create tables, indexed/materialized views, set indexes and write records. On Oracle Database, the account must also be allowed to create and execute triggers, jobs, procedures and work with full-text structures.

When using Oracle Database, the account should have at least the following privileges:

CREATE_TABLE

CREATE_INDEXTYPE

CREATE_TYPE

CREATE_VIEW

CREATE_MATERIALIZED_VIEW

CREATE_SEQUENCE

CREATE_PROCEDURE

CREATE_JOB

and should have the following roles:

CONNECT

RESOURCE

CTXAPP

Create the Database(s)

Create one, two or three databases to store your audit messages, configuration data and history entries. Make the technical account(s) - an owner of the database(s). Use the relational database management server documentation to perform this task.

Follow the following rules when naming databases:

  • The database names must not contain the dash sign. You can use the underscore sign instead.

  • The database names should be upper case.

Create the Database Objects - Tables and Indexes

The Tenant Configuration Wizard can create Data (for audit messages), Config (for configuration data) and History (for history entries) database objects including tables, views and indexes automatically when configuring the databases and validating their contents. You can run SQL scripts located on the installation media to create the set of tables, views and indexes manually. The provided indexes support DirX Audit Manager Audit analysis filters and predefined reports distributed with the product. Use the following scripts with your database administration tool in the order given here:

  • For SQL Server:
    install_media/Additions/Scripts/MSSQL/create_data_tables.sql
    install_media/Additions/Scripts/MSSQL/create_data_indexes.sql
    install_media/Additions/Scripts/MSSQL/create_config.sql
    install_media/Additions/Scripts/MSSQL/create_history_oltp_tables.sql
    install_media/Additions/Scripts/MSSQL/create_history_oltp_indexes.sql
    install_media/Additions/Scripts/MSSQL/create_history_olap_tables.sql
    install_media/Additions/Scripts/MSSQL/create_history_olap_indexes.sql
    install_media/Additions/Scripts/MSSQL/create_history_olap_views.sql

  • For Oracle Database:
    install_media/Additions/Scripts/Oracle/create_data_tables.sql
    install_media/Additions/Scripts/Oracle/create_data_indexes.sql
    install_media/Additions/Scripts/Oracle/create_config.sql
    install_media/Additions/Scripts/Oracle/create_history_oltp_tables.sql
    install_media/Additions/Scripts/Oracle/create_history_oltp_indexes.sql
    install_media/Additions/Scripts/Oracle/create_history_olap_tables.sql
    install_media/Additions/Scripts/Oracle/create_history_olap_indexes.sql
    install_media/Additions/Scripts/Oracle/create_history_olap_views.sql
    install_media/Additions/Scripts/Oracle/create_hibernate_sequence.sql

If you are upgrading DirX Audit, please follow instructions in the DirX Audit Migration Guide.

Determine whether additional indexes should be created when designing new filters in the DirX Audit Manager Audit analysis or reports. Query processing is highly dependent on the database indexes. Use the relational database management server documentation to perform this task.

If you selected to use the full-text search in the database configuration, you must also create the full-text database structures. Use the script create_data_fulltext.sql (in the correct folder base on the database server type) to create the full-text structures and create_data_jobs.sql to set up a job for optimizing it (Oracle Database only).

For Oracle Database, the full-text search index is synchronized on each commit. This configuration causes fragmentation and degradation of the index over time. Therefore, a job is created to optimize it once a day (see the script create_data_jobs.sql). If you encounter performance or space problems with this configuration, you should change the configuration of the full-text index (for example, synchronize every hour and optimize or rebuild on a weekend). Consult with your database administrator to determine the best approach.

The created materialized views on Oracle Database are refreshed by default once a day (at 5 AM). See the section "Managing Oracle Databases" for details.

Managing DirX Audit Database Connectivity

You can configure database connectivity with the Tenant Configuration Wizard. You can configure the following connections:

  • DirX Audit connectivity to the database that contains the audit messages. See the section "Data DB Configuration" in "Configuring DirX Audit" in the DirX Audit Installation Guide for details.

  • DirX Audit connectivity to the database contains the configuration data. See the section "Config DB Configuration" in "Configuring DirX Audit" in the DirX Audit Installation Guide for details.

  • DirX Audit connectivity to the database contains the history entries. See the section "History DB Configuration" in "Configuring DirX Audit" in the DirX Audit Installation Guide for details.

Managing SQL Server

SQL Server has the following pre-requisites:

  • The database server must have TCP/IP connection enabled.

  • The port must be set explicitly.

  • The Collation option must be set explicitly for databases.

  • The default language for the technical account(s) must be English.

Managing Oracle Databases

When you are using Oracle Database, be aware that the Database Content dialog’s Oracle Text option must be checked when the database is created using dbca.

When you are using Oracle Database, perform following steps before running the software:

  • Create the DXT_LOGINTRG trigger, if it does not already exist: run the script
    install_media\Additions\Scripts\Oracle\create_trigger_dxt_logintrg.sql in your Oracle Database management tool. Be sure to set the user/schema name properly in the script.

  • Create the procedure for refreshing history views: run the script
    install_media\Additions\Scripts\Oracle\create_history_procedures.sql in your Oracle Database management tool.

When you are using Oracle Database, be aware that materialized views for the History Database are refreshed by default at 5 A.M. each day. The views are used for the History view and risk-related dashboard components in the DirX Audit Manager and for reports. If you wish to refresh the materialized views manually, you can call the DXT_history_view_refresh procedure. The creation script for this procedure is available at install_media\Additions\Scripts\Oracle\create_history_procedures.sql. Use the script to create the procedure if it does not already exist.

If you want to run the materialized views refresh on a different schedule, drop the views, modify the install_media\Additions\Scripts\Oracle\create_history_olap_views.sql script and then run it to recreate the views. Alternatively, you can alter the views.

Managing Audit Messages Data

In the first approach, customers tend to audit everything to be sure that nothing is lost for later exploration. The result is a fast growing DirX Audit Database.

We strongly recommend reducing the created audit data as much as possible to the amount that is really needed. There are many strategies on how to achieve this reduction. The number of created messages is highly dependent on the configured source systems, so it makes the most sense to optimize the source systems.

The following sections provide some hints and describe some data management strategies.

DirX Identity Audit Messages Data

This section provides information about typical audit messages originated in DirX Identity and strategies for controlling them.

Typical Number and Size of Audit Messages

Practical experience with a medium customer installation of about 20,000 identities that runs about 1,000 request workflows per day and that uses real-time and event-based workflows intensively provided the following numbers:

  • Audit messages per day: about 50,000 (the size of one message is about 10 kB)

  • Audit messages per month: about 1.5 million

  • Resulting DB size per month: about 15 GB

For three months (90 days), about 45 GB are required. Purging this amount of data for long-time storage (see the section "Purging Parts of the Database") would result in about
2 GB of data.

A rule of thumb is that you need about 100 GB of disk space for about 10,000 identities for the database and about 3-5 GB in purged form. These numbers can vary a lot depending on the customer scenario that is run, the dynamic activity in the system and the audit configuration in DirX Identity.

Strategies for Controlling the Number and Size of Audit Messages

To control the number and size of audit messages, adjust the following settings:

  • Activate audit policies only for object types that are really needed.

  • Reduce the number of identifying attributes in activated audit policies. Use attributes with short values wherever possible. We recommend using unique identifiers like employee numbers or pseudonyms. Note that these attributes are part of every audit message that is produced for the object type.

  • Reduce the number of audited attributes in activated audit policies. Use attributes with short values wherever possible.

  • Decide whether you really need system signature for audit messages. Using it almost doubles the message size.

  • Decide whether you really need client signature for audit messages because this requires additional space, too.

  • Decide whether you really need to store original audit messages into the DirX Audit Database.

DirX Access Audit Messages Data

This section provides information about typical audit messages originated in DirX Access and strategies for controlling them.

Typical Number and Size of Audit Messages

Practical experience with a medium-size customer installation of about 20,000 identities that runs Web single sign-on for several Web applications provided the following numbers:

  • Audit messages per day: about 40,000 (message size is about 20 kB).

  • Audit messages per month: about 1.2 million.

  • Resulting DB size per month: about 24 GB, of which 75% represents the original message delivered from DirX Access to DirX Audit.

For three months (90 days), about 75 GB are required. Purging this amount of data for long-time storage (see the section "Purging Parts of the Database") would result in about
5 GB of data.

A rule of thumb is that you need about 200 GB of disk space for about 10,000 identities for the database and about 5-10 GB in purged form. These numbers can vary a lot depending on the customer scenario that is run, the dynamic activity in the system and the audit configuration in DirX Access.

Strategies for Controlling the Number and Size of Audit Messages

To control the number and size of audit messages, adjust the following settings:

  • Reduce the number of identifying attributes in activated audit policies. Use attributes with short values wherever possible. Note that these attributes are part of every audit message that is produced for this object type.

  • Decide whether you really need to store original audit messages into the DirX Audit Database.

Purging Parts of the Database

If the size of the DirX Audit Database is about to reach the system limits, you should purge a set of messages. Use the DirX Audit Database maintenance tool (DirX Audit DB tool) to perform this task. A regular system task can be scheduled when using the tool with relative time parameters. (See "Using the DirX Audit Tools" in the DirX Audit User Interface Guide for details.)

Data exported with this utility is written in compressed XML files. Compared to the content in the database, this action reduces the size to about 3 to 5 percent.

Purged data is structured in year, month, and daily folders. Thus you can easily restore parts of this data to your standard database or to another one for specific analysis. If the audit messages restored from the archive have never been covered by fact population, you need to run also the fact population tool with a time range that contains the restored messages.

Identifying Duplicated Audit Messages

The Identification - UID (unique identifier) field is required and is constrained to be unique. If the value is missing when the audit message is collected and to be persisted, DirX Audit Server persistent unit generates a new value using the java.util.UUID class and calling its randomUUID() method. The generated Identification - UID value is extended with a configurable prefix. Its default value is dxt_. You can set a different prefix value; see the section "Customizing the Identification - UID Prefix" for details.

The Identification - UID value can be missing accidentally in audit messages collected from DirX Identity or DirX Access products, but this always indicates a failure in the audited system and should be reported to product support along with the original message. On the other hand, when auditing a third-party system, the Identification - UID can be missing intentionally because the audited system cannot produce any unique identifier value for audit messages. In these cases, a new unique identifier value is always generated. Please pay special attention when designing your system to guarantee that the audit message is not collected repeatedly. This action leads to duplication of the audit message in the DirX Audit Database with a different Identification - UID value. Other fields are identical.

You can search for potential duplications by running a SQL query over the DirX Audit Database.

select
 am1.IDENTIFICATION_UID 'AM1_IDENTIFICATION_UID',
 am2.IDENTIFICATION_UID 'AM2_IDENTIFICATION_UID'
from
 DAT_AUDITMESSAGES am1
 join DAT_AUDITMESSAGES am2 on
  am1.IDENTIFICATION_WHEN = am2.IDENTIFICATION_WHEN
   and am1.IDENTIFICATION_SOURCE =
       am2.IDENTIFICATION_SOURCE
   and am1.IDENTIFICATION_UID < am2.IDENTIFICATION_UID
where
 am1.IDENTIFICATION_UID like 'dxt_%'
  and am2.IDENTIFICATION_UID like 'dxt_%'

You can extend the join condition with a test on equality of values in additional columns like:

IDENTIFICATION_CATEGORY,
IDENTIFICATION_CAUSE,
IDENTIFICATION_OP,
IDENTIFICATION_OUTCOME or
IDENTIFICATION_TYPE.

You can then delete duplicated audit messages with a query like the following:

delete from
 DAT_AUDITMESSAGES
where
 IDENTIFICATION_UID = 'PROVIDE_SPECIFIC_VALUE_HERE'

This query cascades deletion to the rows in related tables.

Customizing the Identification - UID Prefix

You can change the Identification - UID prefix by setting the property for it in the install_path/conf/configuration.cfg file. This file is created (and later updated) by the Core Configuration Wizard during the first configuration.

The property key for the UID prefix is uid_prefix in the [general] section. The Core Configuration Wizard does not set this particular property in this file. You need to add it manually.

If the value is not set in the file, the default value dxt_ is used.

Managing History Entries Data

When you are going to use the History Database, plan enough disk space. Here are some numbers that help you to calculate the space needed for your environment. They were observed with SQL Server.

Tests were run with N * 10,000 entries, each entry with 24 small attributes, 28 link attributes and 1 large attribute (the description). The results show that the consumed space increases nearly linearly and that about 180 MB are required per 10,000 entries. Changing 10 attributes (6 small and 4 link attributes) adds another 30 MB per 10,000 entries.

An observation from a project confirms that a history entry-related data can consume several 10s kB in a database.

Calculating Foreign Keys

History database data must be processed to be available in views. During this procedure, foreign keys are calculated. A scheduled job running on the DirX Audit Server updates data only for the last several days. If you want to update older data, you can run the SQL queries delivered in install_media/Additions/Scripts/common/updatehistdb manually. Remove the section that contains a placeholder that restricts the execution for the several last days from the where clause or optionally extend the existing scheduled History DB update job time range parameter (jobdatamap.arg_membership_validfrom).