USER MANUALS

Audit Trail In Data Catalog and Scheduler

It is possible to maintain an audit trail of changes and actions performed both in Scheduler and Data Catalog. This way, we want to give an answer to this question: when and who did what on what element and which was the result?

These changes can be stored in a JSON file and/or in a database, depending on your configuration. In both cases, we are levering Log4j support, so enabling this feature involves logging configuration (as explained below).

To keep an audit trail of Virtual DataPort, use the logs generated by the Denodo Monitor.

Audit Information

The audit information contains the following fields:

  • startTime: Start time of the requested action.

  • endTime: Finish time of the requested action.

  • elapsedTime: Duration (in milliseconds) of the action.

  • sessionID: Internal identifier for the user’s session.

  • clientIP: The IP of the client.

  • userName: Login name of the user executing the action.

  • userAuthMethod: Which authentication method used the user to log in: LOCAL, REMOTE, SSO, KERBEROS, NO_AUTHENTICATION and ACCESS_TOKEN.

  • userBatchedMode: (Only in Scheduler) Whether the user executing the action was in batched mode or not.

  • userPermissions: The permissions of the user executing the action.

  • requestType: Type of operation performed by the method:

    • Scheduler considers request types like: CREATE, READ, UPDATE, DELETE, START, STOP, ENABLE, DISABLE, etc.

    • Data Catalog considers request types like: CREATE, READ, UPDATE, DELETE, SYNCHRONIZE, IMPORT, ASSIGN, CANCEL, etc.

  • elementTypes: Element types used in the request:

    • Scheduler considers element types like: Job, DataSource, Project, ServerInfo, etc.

    • Data Catalog considers element types like: DATABASE, VIEW, WEB_SERVICE, TAG, CATEGORY, PROPERTY, PROPERTY_GROUP, ASSOCIATION, FOLDER, etc.

  • affectedElements: Information about the elements (identifier and name) that were affected by the execution of the audited method.

  • endpoint: The action performed.

    • In Scheduler it contains the method signature with its name and parameters.

    • In Data Catalog it contains the path of the requested URI.

  • arguments: Input arguments for the executed method.

  • state: Status of the action: OK, ERROR, WARNING.

  • warnings: (Only in Scheduler) The information about the elements that could not be executed in bulk methods. It only applies when state = WARNING.

  • threadName: Name of the thread that generated the logging event.

  • threadId: Identifier of the thread that generated the logging event.

  • logLevel: Log level the event was generated at.

How to Configure Audit in Scheduler

To enable auditing in Scheduler, you have to follow these steps:

  1. Enable loggers in <DENODO_HOME>/conf/scheduler/log4j2.xml

    1. Configure appenders: you can define appenders (inside <Appenders> section) to a JSON file and/or to a database (you must configure at least one of them):

      • Appender to JSON:

        <RollingFile name="REQUESTOUT-JSON" fileName="<DENODO_HOME>/logs/scheduler/scheduler-requests.json"
      filePattern="<DENODO_HOME>/logs/scheduler/scheduler-requests.json.%i">
      <JsonTemplateLayout eventTemplateUri="classpath:DenodoJsonLayout.json"
         stackTraceEnabled="false" charset="UTF-8" />
      <SizeBasedTriggeringPolicy size="10 MB" />
      <DefaultRolloverStrategy max="7" />
</RollingFile>

        Replace <DENODO_HOME> properly.

      • Appender to database:

        <JDBC name="REQUESTOUT-DB" tableName="sched_audit_logs" ignoreExceptions="false">
      <ConnectionFactory
         class="com.denodo.scheduler.core.server.quartz.connection.SchedulerPoolingConnectionProvider"
         method="getAuditDBConnection" />
      <AuditDBLayout />

      <ColumnMapping name="startTime" type="java.time.ZonedDateTime" />
      <ColumnMapping name="endTime" type="java.time.ZonedDateTime" />
      <ColumnMapping name="elapsedTime" type="java.lang.Long" />
      <ColumnMapping name="sessionID" />
      <ColumnMapping name="clientIP" />
      <ColumnMapping name="userName" />
      <ColumnMapping name="userAuthMethod" />
      <ColumnMapping name="userBatchedMode" type="java.lang.Boolean" />
      <ColumnMapping name="userPermissions" />
      <ColumnMapping name="requestType" />
      <ColumnMapping name="elementTypes" />
      <ColumnMapping name="affectedElements" />
      <ColumnMapping name="endpoint" />
      <ColumnMapping name="arguments" type="java.sql.Clob" />
      <ColumnMapping name="state" />
      <ColumnMapping name="warnings" type="java.sql.Clob" />
      <ColumnMapping name="threadName" />
      <ColumnMapping name="threadId" type="java.lang.Long" />
      <ColumnMapping name="logLevel" />
</JDBC>

    2. Add logger (with appenders to file and/or database, depending on the previous step):

      <Logger name="EventLogger" level="INFO" additivity="false">
      <AppenderRef ref="REQUESTOUT-JSON" />
      <AppenderRef ref="REQUESTOUT-DB" />
</Logger>

      Depending on the log level:

      • INFO: default level. It will mainly record actions that make changes to the metadata, such as creations, updates and deletions (for instance: create a job).

      • DEBUG: in addition to the information registered with INFO, it will include getters (for instance: get jobs).

      • TRACE: in addition to the information registered with DEBUG, it will include internal metadata calls and minor getters (for instance: get meta configuration for VDP jobs).

        • Besides auditing more methods from the API, it also includes more information for each one, as explained in Audit Information

          • UserAuthenticationMethod.

          • UserBatchMode.

          • UserPermissions.

  2. Only when the appender to database is configured, you have to do these additional steps:

    1. Configure the auditing database:

      • You can configure the target database where to store the audit information. Note that by default, the audit will use the same database as the metadata.

      • If you want to use a different database than the one used to store the Scheduler metadata (this is highly recommended), then you have to manually configure its settings in <DENODO_HOME>/conf/scheduler/ConfigurationParameters.properties

        Example of audit database settings for PostgreSQL
        # Audit DataSource
AuditDataSource/driverClassName=org.postgresql.Driver
AuditDataSource/url=jdbc:postgresql://auditdb.denodo.com:5432/audit
AuditDataSource/user=audit_user
AuditDataSource/password.secret=audit_password
AuditDataSource/databaseName=PostgreSQL
AuditDataSource/useKerberos=false
AuditDataSource/classpath=postgresql-12

    2. Create the table (sched_audit_logs) to store the audit information in the audit database set up in the previous step: For this you can execute sql script tables_audit_trail_<db>.sql from <DENODO_HOME>/scripts/scheduler/sql/<db>.

How to Configure Audit in Data Catalog

To enable auditing in Data Catalog, uncomment loggers in <DENODO_HOME>/conf/data-catalog/log4j2.xml following these steps:

  1. Configure appenders: You can uncomment or define appenders (inside <Appenders> section) to a JSON file and/or to a database. You must configure at least one of them:

    • Appender to JSON:

      <RollingFile name="REQUESTOUT-JSON"
      fileName="<DENODO_HOME>/logs/vdp-data-catalog/data-catalog-requests.json"
      filePattern="<DENODO_HOME>/logs/vdp-data-catalog/data-catalog-requests.json.%i">
      <JsonTemplateLayout
         eventTemplateUri="classpath:DenodoJsonLayout.json" stackTraceEnabled="false" charset="UTF-8"/>
      <SizeBasedTriggeringPolicy size="10 MB"/>
      <DefaultRolloverStrategy max="7"/>
   </RollingFile>

    • Appender to database:

      <JDBC name="REQUESTOUT-DB" tableName="data_catalog_audit_logs" ignoreExceptions="false">
      <ConnectionFactory
         class="com.denodo.webapp.datacatalog.business.common.datasource.AuditConnectionProvider"
         method="getAuditDBConnection"/>
      <AuditDBLayout/>

      <ColumnMapping name="startTime" type="java.time.ZonedDateTime"/>
      <ColumnMapping name="endTime" type="java.time.ZonedDateTime"/>
      <ColumnMapping name="elapsedTime" type="java.lang.Long"/>
      <ColumnMapping name="sessionID"/>
      <ColumnMapping name="clientIP"/>
      <ColumnMapping name="userName"/>
      <ColumnMapping name="userAuthMethod"/>
      <ColumnMapping name="userPermissions"/>
      <ColumnMapping name="requestType"/>
      <ColumnMapping name="elementTypes"/>
      <ColumnMapping name="affectedElements"/>
      <ColumnMapping name="endpoint"/>
      <ColumnMapping name="arguments" type="java.sql.Clob"/>
      <ColumnMapping name="state"/>
      <ColumnMapping name="threadName"/>
      <ColumnMapping name="threadId" type="java.lang.Long"/>
      <ColumnMapping name="logLevel"/>
   </JDBC>

  2. Uncomment logger (with appenders to file and/or database, depending on the previous step):

    <Logger name="EventLogger" level="INFO" additivity="false">
      <AppenderRef ref="REQUESTOUT-JSON" />
      <AppenderRef ref="REQUESTOUT-DB" />
</Logger>

    Depending on the log level:

    • INFO: default level. It will record most of the HTTP requests that Data Catalog receives through its public API REST.

    • DEBUG: in addition to the information registered with INFO, it will include those HTTP requests that are recurrently called, like notification/user/unread or Ping.

      • Besides auditing more methods from the API, it also includes more information for each one, as explained in Audit Information.

        • UserAuthenticationMethod.

        • UserPermissions.

  3. Only when the appender to database is configured, you have to do these additional steps:

    1. Configure the auditing database. You can specify the target database where to store the audit information configuring its settings in <DENODO_HOME>/conf/data-catalog/audit-datasource.properties

      Example of audit database settings for PostgreSQL
      # Audit DataSource
# Supported types: derby, mysql, oracle, postgresql, sqlserver
audit.datasource.type=postgresql
audit.datasource.driver-class-name=org.postgresql.Driver
audit.datasource.url=jdbc:postgresql://auditdb.denodo.com:5432/audit
audit.datasource.user=audit_user
audit.datasource.password.secret=audit_password
audit.datasource.maxPoolSize=50
audit.datasource.minimumIdle=50
# Validation query. If using JTDS driver for SQL Server this field is mandatory
audit.datasource.connectionTestQuery=
audit.datasource.connectionTimeout=100000
audit.datasource.kerberos.use-kerberos=
audit.datasource.kerberos.userName=
audit.datasource.kerberos.password.secret=
audit.datasource.kerberos.key-tab-file=
audit.datasource.kerberos.credentials-cache-file=

      Note

      How to use an external database for the Data Catalog.

    2. Create the table (data_catalog_audit_logs) to store the audit information in the audit database set up in the previous step. For this you can execute sql script data_catalog_tables_audit_trail_<db>.sql from <DENODO_HOME>/scripts/data-catalog/sql/audit/<db>.

Masking Sensitive Information

By default, when auditing events, sensitive or environment-dependent information will be masked.

It is possible to control which information should be masked with the following properties:

  • com.denodo.util.audit.config.AuditMaskingConfig.obfuscate.ip: set to false when you do not want to obfuscate the IP address of the users.

  • com.denodo.util.audit.config.AuditMaskingConfig.obfuscate.user: set to false when you do not want to obfuscate the user names.

  • com.denodo.util.audit.config.AuditMaskingConfig.obfuscate.auditMask: set to false when you do not want to obfuscate sensitive properties (mainly, environment dependent properties, such as server uris, host names, ports, etc).

  • com.denodo.util.audit.config.AuditMaskingConfig.obfuscate:

    • true will enable all obfuscation (no matter the values from the other properties).

    • false will disable all obfuscation (no matter the values from the other properties).

    • If not set, the previous properties will control the obfuscation.

Note

Passwords and keytabs will always be masked, regardless of these properties.

To configure how the audit information should be masked, the properties have to be configured in:

  • In Scheduler <DENODO_HOME>/conf/scheduler/ConfigurationParameters.properties.

  • In Data Catalog <DENODO_HOME>/conf/data-catalog/DataCatalogBackend.properties.

Add feedback