1. Using Oracle Essbase
  2. Audit Data, Security, Artifact Changes, and LCM Events
  3. Audit Security, Artifact Changes, and LCM Events

Audit Security, Artifact Changes, and LCM Events

Service administrators can enable security auditing to track changes made to the Essbase server.

Based on parameters you specify in an auditing policy file, Essbase gathers information about changes to system-level security, artifacts, LCM events, and executed MaxL statements (including imports). Essbase consolidates the tracked information into an audit log file, or streams it to an external database. Tracked information about each event includes time, client, user, artifacts affected, duration, ID, application and database name, status, and a description.

You enable server-level auditing of these events using the AUDITTRAIL SECURITY Essbase configuration setting.

Workflow to Enable Security Auditing for Essbase Server

This workflow gets you started enabling security auditing on the Essbase server and writing records to a CSV file or streaming them to an external database.

In this workflow, we use Oracle Database as the external database, but you can also use SQL Server, MySQL, or DB2.

To complete the workflow, you must be a system administrator, and you will need to access the <Essbase Config Path> on your Essbase server machine.

This path contains files you will need to edit:
  • The essbase.cfg configuration file
  • A default security auditing policy file

  1. Enable auditing of server events by adding the following configuration to essbase.cfg on the Essbase server machine:

    AUDITTRAIL SECURITY

    After you update the configuration, restart Essbase.

    See Set Server-Level Configuration Properties and Start, Stop, and Check Servers.

  2. A default policy file (XML) is created on the Essbase server. This file, EssbaseSecurityAuditLogPolicy.xml, is in the path you specified during the configuration phase of deployment to store Essbase configuration (the <Essbase Config Path>, which is also where essbase.cfg resides).
    The default policy file created has these contents:
    <?xml version="1.0" encoding="UTF-8"?>
<security-audit-policy>
   <audit_events_to_capture>LOGIN,LOGINAS,LOGIN_FAIL,LOGOUT,SERVICE_ROLE_ASSIGN,SERVICE_ROLE_REVOKE,APPLICATION_ROLE_ASSIGN,APPLICATION_ROLE_REVOKE,ARTIFACT_UPLOADED,ARTIFACT_MODIFIED,ARTIFACT_DELETED,ARTIFACT_CREATE,ARTIFACT_RENAMED,APPLICATION_DELETED,APPLICATION_CREATE,APPLICATION_RENAMED,DATABASE_DELETED,DATABASE_CREATE,DATABASE_RENAMED,LCM_EXPORT_START,LCM_EXPORT_END,LCM_IMPORT_START,LCM_IMPORT_END,LCM_IMPORT_FAIL,DATA_LOAD_MAXL,LOAD_DATA_JOB_START,LOAD_DATA_JOB_END,LOAD_DATA_JOB_FAILED,DELETE_SESSION,EXECUTE_MAXL,APPLICATION_SET_ACTIVE,APPLICATION_START,APPLICATION_STOP,DATABASE_START,DATABASE_STOP</audit_events_to_capture>
   <audit_sinks>
      <audit_sink>
         <audit_sink_type>CSV</audit_sink_type>
         <max-file-size>50000000</max-file-size>
         <roll-nos>100</roll-nos>
      </audit_sink>
   </audit_sinks>
</security-audit-policy>
    CSV is the default audit sink type. If you are using the default (CSV) audit sink type, and you want to test that the audit details are written to the security audit log CSV file,
    1. Perform an action that is an auditable event, such as creating an application. You can select any action listed in the <audit_events_to_capture> section of your policy.
    2. SSH to the Essbase server.
    3. Navigate to <DOMAIN_HOME>/servers/serverName/logs/essbase/. If you do not know where <DOMAIN_HOME> is, see Environment Locations in the Essbase Platform.
    4. Open and review the file, SecurityAuditLog_n.csv.

      Example of a security audit log CSV file:
      Image of a security audit csv file.

  3. If you want the security audit trail streamed to an external database,
    1. Create a connection to the external source.
    2. Edit the policy file to change the audit sink to DATABASE.
    3. Add a <db_connection_name> parameter within the <audit_sink> parameter. The value of the <db_connection_name> parameter should be the exact name of the connection created in a.
    Example of edited audit policy for streaming security audit trail to Oracle Database:
    <?xml version="1.0" encoding="UTF-8"?>
<security-audit-policy>
   <audit_events_to_capture>LOGIN,LOGINAS,LOGIN_FAIL,LOGOUT,SERVICE_ROLE_ASSIGN,SERVICE_ROLE_REVOKE,APPLICATION_ROLE_ASSIGN,APPLICATION_ROLE_REVOKE,ARTIFACT_UPLOADED,ARTIFACT_MODIFIED,ARTIFACT_DELETED,ARTIFACT_CREATE,ARTIFACT_RENAMED,APPLICATION_DELETED,APPLICATION_CREATE,APPLICATION_RENAMED,DATABASE_DELETED,DATABASE_CREATE,DATABASE_RENAMED,LCM_EXPORT_START,LCM_EXPORT_END,LCM_IMPORT_START,LCM_IMPORT_END,LCM_IMPORT_FAIL,DATA_LOAD_MAXL,LOAD_DATA_JOB_START,LOAD_DATA_JOB_END,LOAD_DATA_JOB_FAILED,DELETE_SESSION,EXECUTE_MAXL,APPLICATION_SET_ACTIVE,APPLICATION_START,APPLICATION_STOP,DATABASE_START,DATABASE_STOP</audit_events_to_capture>
   <audit_sinks>
      <audit_sink>
         <audit_sink_type>DATABASE</audit_sink_type>
           <db_connection_name>OraclePDB</db_connection_name>
      </audit_sink>
   </audit_sinks>
</security-audit-policy>
  4. Test that the audit details streamed to the Database.
    1. Perform an action that is an auditable event, such as creating an application. You can select any action listed in the <audit_events_to_capture> section of your policy.

      Essbase should create an audit table named ESSBASE_SECURITY_AUDIT_EVENT_LOG in the external database schema.

    2. Log in to the external RDBMS and run a query to check for the presence of the table. For example, log in to SQL Developer and run
      select * from ESSBASE_SECURITY_AUDIT_EVENT_LOG
  5. Use a data visualization tool to view and analyze the security audit records. You can use Smart View, Oracle Data Desktop (available with an Oracle Technology Network license), open-source visualization tools from open source or your non-Oracle database vendor.

About the Auditing Policy File

The auditing policy is defined in an XML file that you can edit to suit your needs. In this file, you can specify which Essbase server events to track and whether to write the data to a security audit log or stream it to an external database. If you are writing data to an audit log, you can indicate the maximum file size and the number of security audit log files to keep.

Essbase creates EssbaseSecurityAuditLogPolicy.xml when you restart Essbase after enabling security auditing. You can then edit the file as needed to refine the auditing policy. The file is located in the application directory specified during the configuration phase of Essbase deployment. If you do not know where <Application Directory> is in your environment, refer to Environment Locations in the Essbase Platform for an explanation.

To edit the auditing policy file,
  1. Navigate to EssbaseSecurityAuditLogPolicy.xml. The file is located in the application directory specified during the configuration phase of Essbase deployment.
  2. Open it in a text editor.
  3. Add the items that you want.
    1. Optionally add <audit_sink_type>DATABASE</audit_sink_type> if you want to stream data to an external database.
    2. If you indicated an audit sink type of DATABASE in step a, on the following line, add <db_connection_name>ConnectionName</db_connection_name> with the name of the database connection.
    3. If writing data to an audit log file, Optionally change the maximum file size using <max-file-size>n</max-file-size>, where n = the number of bytes . The default is 50000000 bytes.
    4. If writing data to an audit log file, indicate how many security audit log CSV files to save using <roll-nos>n</roll-nos>, where n = the number of files.
    5. Indicate which audit events you want to capture, using <audit_events_to_capture>events_list</audit_events_to_capture>.

The events that you indicate in the auditing policy file are tracked in a security audit log file or streamed to an external database.

You can indicate the following events to capture in the auditing policy file:

Event Description
LOGIN User [x] logged in successfully
LOGIN_AS User [x] logged in as [y]
LOGOUT User [x] logged out
LOGIN_FAIL User [x] login failed
SERVICE_ROLE_ASSIGN Assigned Essbase service role [x] to [y]
SERVICE_ROLE_REVOKE Revoked Essbase service role [x] from [y]
APPLICATION_ROLE_ASSIGN User/Group [x] has been provisioned the role [y] on the application [z]
APPLICATION_ROLE_REVOKE User/Group [x] has been revoked from the role [y] on the application [z]
ARTIFACT_CREATE Artifact [x] of type [y] created
ARTIFACT_UPLOADED Artifact upload request called for application [a] database [b] object name [c] and object type [d]
ARTIFACT_MODIFIED Artifact [x] of type [y] modified
ARTIFACT_DELETED Artifact [x] of type [y] deleted
ARTIFACT_RENAMED Artifact [x] of type [y] renamed to [z]
APPLICATION_DELETED Application [x] deleted
APPLICATION_CREATE Application [x] created
APPLICATION_RENAMED Application [x] renamed to [y]
DATABASE_DELETED Database [x] deleted in application [y]
DATABASE_CREATE Database [x] created in application [y]
DATABASE_RENAMED Database [x] renamed to [y] in application [z]
LCM_EXPORT_START LCM export job started with file name [x]
LCM_EXPORT_END LCM export job completed with file name [x] and job status [y]
LCM_IMPORT_START LCM import started for application [x] with file name [y]
LCM_IMPORT_END LCM import completed for application [x] with file name [y]
LCM_IMPORT_FAIL LCM import failed for application [x] with file name [y]
DATA_LOAD_MAXL The MaxL import data statement executed for application [x] and database [y] by user [z]
EXECUTE_MAXL MaxL statement [x] executed from user [y]
LOAD_DATA_JOB_START Data load job started using data file [x] and rule file [y]
LOAD_DATA_JOB_END Data load job for data file [x] and rule file [y] completed with status [z]
LOAD_DATA_JOB_FAILED Data load job failed due to [x]
DELETE_SESSION Session [x] deleted

Security Auditing Events

Security auditing events are tracked either in a security audit log file, or they are streamed to an external database depending on which you indicate in the auditing policy file.

For instructions on opening the security audit log or the audit table in the in the external database schema, see Workflow to Enable Security Auditing for Essbase Server.

The security audit log and the audit table include the following information (when it is applicable) about each event:
  • Time - when the event occurred
  • Client - client IP address or hostname
  • Username - the user initiating the action
  • Session ID – the Essbase session ID
  • Event Type - the event type
  • Artifact Type - the type of artifact involved in the event
    Artifact type examples:
    • Artifact Type partition_file for Event Type ARTIFACT_UPLOADED
    • Artifact Type Application for Event Type LCM_EXPORT_START
    • Artifact Type User for Event Type APPLICATION_ROLE_ASSIGN
  • Artifact Name – the name of the artifact involved in the event. For example, a file name, username, or application name
  • Additional Info - additional information associated with the event
  • Description - description of the event

    The Description field content is localized.

  • ID - A 128 bit universally unique identifier describing the event.

    Example: 123e4567-e89b-12d3-a456-426614174000

  • Duration - duration of the even in miliseconds
  • Application Name - name of the application
  • Database Name - name of the database
  • Status - success or failure