Direct Audit Information To MySQL

The sample includes an external CSV file and a mapping between objects in that file and the managed user repository. The reconciliations across this mapping generate the audit records that will be directed to the MySQL database. The connection to the MySQL database is through a ScriptedSQL implementation of the Groovy Connector Toolkit.

About the Configuration Files

The files that demonstrate the functionality of this sample are located under /path/to/openidm/samples/audit-jdbc/, in the conf/ and data/ directories.

The following files play important roles in this sample:

conf/provisioner.openicf-auditdb.json

This file provides the configuration for the Scripted SQL implementation of the Groovy Connector. The file specifies, among other things, the connection details to the MySQL database, the connector version information, and the object types that are supported for this connection. For more information, see "Groovy Connector Toolkit".

conf/provisioner.openicf-csvfile.json

This file provides the configuration for this instance of the CSV connector. It includes, among other things, the location of the CSV file resource.

conf/sync.json

Provides the mapping between managed users and the data set in the CSV file.

conf/audit.json

This file configures the router as the audit event handler, and routes audit logs to a remote system, identified as auditdb.

data/csvConnectorData.csv

This file contains the sample data set that will be reconciled to the managed user repository.

data/sample_audit_db.mysql

This file sets up the schema for the MySQL database that will contain the audit logs.

tools/*.groovy

The Groovy scripts in this directory allow the connector to perform operations on the MySQL database.

Configure the MySQL Database

Before you start this sample, MySQL must be installed and running, and must include the database required for the sample. In addition, IDM must include the connector .jar required to connect to the MySQL database.

The sample assumes the following MySQL configuration:

  • The database is available on the local host.

  • The database listens on the standard MySQL port, 3306.

  • You can connect to the MySQL database, as user root with password password.

  1. Install and configure MySQL.

  2. When MySQL is up and running, import the database schema to set up the database required for the sample: 

    mysql -u root -p < /path/to/openidm/samples/audit-jdbc/data/sample_audit_db.mysql
Enter password: password

    This step sets up an audit database with tables that correspond to the various audit events. You can view the tables in the audit database as follows: 

    mysql -u root -p 
Enter password: password
mysql> use audit
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_audit     |
+---------------------+
| auditaccess         |
| auditactivity       |
| auditauthentication |
| auditconfig         |
| auditrecon          |
| auditsync           |
+---------------------+
6 rows in set (0.00 sec)

  3. Download MySQL Connector/J, version 5.1 or later from the MySQL website. Unpack the delivery, and copy the .jar into the openidm/bundle directory: 

    cp mysql-connector-java-version-bin.jar /path/to/openidm/bundle/

  4. Edit the url property in the SQL connector configuration file (openidm/samples/audit-jdbc/conf/provisioner.openicf-auditdb.json) to match the host and port of your MySQL instance. The default configuration is as follows: 

    "url" : "jdbc:mysql://localhost:3306/audit?serverTimezone=UTC",

    Note

    The default configuration expects SSL, which is strongly advised in a production environment. If you are running this in a test environment, you can bypass the SSL requirement:

    • Add &useSSL=false to the end of the url.

    • If you are running MySQL 8.0.11+, add &allowPublicKeyRetrieval=true to the end of the url.

Run the Sample

In this section, you will start IDM, then run a reconciliation between the CSV file and the managed user repository. After the reconciliation, you should be able to read the audit logs in the audit database on your MySQL instance.

  1. Prepare IDM as described in "Prepare IDM", then start the server with the configuration for this sample: 

    /path/to/openidm/startup.sh -p samples/audit-jdbc

  2. Reconcile the two data sources, either over the command-line or by using the Admin UI.

    To run the reconciliation over REST, use the following command: 

    curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--request POST \
"http://localhost:8080/openidm/recon?_action=recon&mapping=systemCsvfileAccounts_managedUser&waitForCompletion=true"
{
  "_id": "a3664c26-bf82-4100-b411-19edc248c306-7",
  "state": "SUCCESS"
}

    To run the reconciliation from the Admin UI, select Configure > Mappings, select the systemCsvfileAccounts_managedUser mapping, then select Reconcile.

  3. Inspect the tables in the audit database to see how the logs have been routed to that location.

    The following example displays the reconciliation audit logs: 

    mysql -u root -p
Enter password: password
...
mysql> use audit;
...
mysql> show tables;
+---------------------+
+---------------------+
| Tables_in_audit     |
+---------------------+
| auditaccess         |
| auditactivity       |
| auditauthentication |
| auditconfig         |
| auditrecon          |
| auditsync           |
+---------------------+
6 rows in set (0.00 sec)
mysql> select * from auditactivity;

+----+-------------+--------------------------+-------------+----------------+---------------+...+
| id | objectid    | activitydate             | eventname   | transactionid  | userid        |...                                                                                                                                                                                              | provider | context | status  |
+----+-------------------------------------------+----------+----------------+----------------...
|  1 | 9927b8db*** | 2021-01-25T12:53:00.800Z | activity    | 9927b8db***    | openidm-admin |...

    You can inspect the other audit logs in the same way.

  4. By default, the audit configuration in this sample uses the router audit handler for queries, as indicated in the following line from the conf/audit.json file: 

    "handlerForQueries" : "router",

    With this configuration, when you query the audit logs over REST, the audit data is returned from the router handler (in this case the MySQL database). The following example shows how to query the activity audit log: 

    curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--request GET \
"http://localhost:8080/openidm/audit/activity?_queryFilter=true"
{
  "result": [
    {
      "_id": "9927b8db-4537-467f-a077-dbe8cab2a4c8-1187",
      "timestamp": "2021-01-25T12:53:00.800Z",
      "userId": "openidm-admin",
      "operation": "CREATE",
      "changedFields": null,
      "objectId": "managed/user/47527af8-f7d5-4b4b-9d8e-af45169016d4",
      "eventName": "activity",
      "trackingIds": null,
      "transactionId": "9927b8db-4537-467f-a077-dbe8cab2a4c8-1109",
      "runAs": "openidm-admin",
      "passwordChanged": false,
      "message": "create",
      "status": "SUCCESS"
    },
  ],
 ...
}

    You can query the other audit logs in the same way.

Read a different version of :