CSV File Collection Plug-ins

Learn how to use Oracle DBSecCentral CSV file collection plug-ins to collect audit data from a CSV file type of trail.

CSV file collection plug-ins support collection of audit data from an CSV file type of trail. All these CSV audit files must be present in single directory. You can specify details of the CSV audit data in the mapper file.

Requirements for CSV File Collection Plug-ins

To use CSV collection plug-ins for reading audit trails from CSV files, your data must meet Oracle Database Security Central requirements.

You can use collection plug-ins for reading audit trails from CSV audit record files if the CSV files meet the requirements for collection.

CSV File Audit Record File Requirements for Oracle Database Security Central

The audit trail must be stored in one or more CSV files in a single directory path.
The user must have read permission on the directory containing the CSV audit files.
CSV files in this directory must be valid, well formed CSV documents, with COMMA as the field delimiter.
If a record has its CommandClass field as null, then the record is treated as invalid.
In the CSV file, each audit record must have a timestamp as one of its element values.

The value of the timestamp element must be monotonically increasing, that is, the value of the field increases with every new audit record inserted into the trail. The timestamp value should be strictly Not Null. Timestamp format must be according to SimpleDateFormat Java class.

This field must mapped to the EventTimeUTC core field in the mapper file. If mapping for event time is not specified in the mapper file, then the collection plug-in shuts down.
CSV files in this directory should be of the same locale and encoding as the agent, as described in the examples below:
- Valid: The user has an agent in a Chinese locale (env). CSV files are also generated in a Chinese locale with same encoding (for example, ZHS16GBK). This setup is valid.
- Invalid: The user has an agent in a German locale (env). CSV files are generated/moved from some other computer, which are Chinese encoded. The collectors fail to start because of an encoding mismatch, as well as a locale mismatch, in this case. This setup is invalid.

Example Audit Trail for a CSV File Collection Plug-in

This example audit trail for a CSV file collection plug-in shows the details of a CSV file collection plug-in.

This example file is used in other locations to demonstrate the creation and structure of a sample mapper file for the creation and structure of a sample mapper file for a CSV file collection plug-in, in the Oracle Database Security Central documentation.

The following table lists the audit record structure and mappings to Oracle Audit Vault Server fields for the hypothetical target type, CSVSOURCE, which generates and stores audit data in CSV audit files.

Table 4 Audit Data Fields in CSV Audit Records and Mappings

Target Field	Audit Vault Server Field	Map to Field Type
`EVENT_NAME`	`CommandClass`	core field
`EVENT_TIME`	`EventTimeUTC`	core field
`CLIENT_IP`	`ClientIP`	core field
`USER_ID`	`UserName`	core field
`TARGET_OBJECT`	`TargetObject`	core field
`EVENT_STATUS`	`EventStatus`	core field
`SESSION_ID`	`marker field`	marker field
`ENTRY_ID`	`marker field`	marker field
`COMMAND_TEXT`	`CommandText`	large field
`COMMAND_PARAM`	`CommandParam`	large field
`SESSION_ID`	`extension field`	extension field
`ENTRY_ID`	`extension field`	extension field

Example 6 Sample CSV Audit Record

5678,createUser,2020-10-01T16:11:23.661+0530,127.0.0.1,1234,admin,user1,0,0,not applicable,1234,"insert into foo.bar","foobar",111
5679,dropUser,2020-10-02T16:11:23.661+0530,127.0.0.1,1234,admin,user2,0,0,not applicable,1234,"delete from foo.bar","foobar",222
5680,createCollection,2020-10-03T16:11:23.661+0530,127.0.0.1,1234,admin,collection1,100,18,authentication failed,1234,"insert into foo.bar","foobar",333
5681,dropCollection,2020-10-04T16:11:23.661+0530,127.0.0.1,1234,admin,collection2,200,13,not authorized to perform operation,1234,"delete from foo.bar","foobar",444

Below is the index corresponding to each field:
EVENT_ID field has index 0
EVENT_NAME field has index 1
EVENT_TIME field has index 2
CLIENT_IP field has index 3
CLIENT_PORT field has index 4
USER_ID field has index 5
TARGET_OBJECT field has index 6
EVENT_STATUS field has index 7
ERROR_ID field has index 8
ERROR_MESSAGE field index 9
SESSION_ID field has index 10
COMMAND_TEXT field has index 11
COMMAND_PARAM field has index 12
ENTRY_ID field has index 13

Creating the CSV File Audit Collection Mapper File

To create a CSV file collection plug-in mapper file, you must describe the collection plug-in mappings in this mapper file in accordance with Oracle Database Security Central standards.

You must describe the collection plug-in mappings in this mapper file as follows:

Standards for Collection Plug-in Mappings in Mapper Files for Oracle Database Security Central

Top-Level Element
```
<AVCSVCollectorTemplate securedTargetType="CSVSOURCE"
      maxSecuredTargetVersion="11.0"
      version="1.0">
```
The AVCSVCollectorTemplate is the top level element and has these mandatory attributes: securedTargetType, maxSecuredTargetVersion, and version. The minSecuredTargetVersion attribute is optional.

The accepted format for the minSecuredTargetVersion, maxSecuredTargetVersion, and version attributes uses numbers, separated by dots, such as 12.2,10.3.2, 11.2.3.0.
Header Information
```
<HeaderInfo>
  <StartTag>CSV</StartTag>
</HeaderInfo>
```
HeaderInfo is mandatory. The StartTag must be set to CSV.
Record Information
```
<RecordInfo>
  <StartTag>CSV</StartTag>
</RecordInfo>
```
RecordInfo is mandatory. StartTag must be set to CSV.

Field Mapping Information

<FieldMappingInfo>

FieldMappingInfo provides mapping information from target fields to various Audit Vault fields, contained in these child elements, CoreFields, LargeFields, ExtensionField, and MarkerField.

Field mappings include <Map> elements, which contain <Name> elements that hold target field names, and <MapTo> elements that hold Audit Value field names that targets are mapped to.

In CSV Plugin Mapper file, the Name element must contain the index of the field in the CSV file. In our sample, below are the index corresponding to each field:

EVENT_ID field has index 0
EVENT_NAME field has index 1
EVENT_TIME field has index 2
CLIENT_IP field has index 3
CLIENT_PORT field has index 4
USER_ID field has index 5
TARGET_OBJECT field has index 6
EVENT_STATUS field has index 7
ERROR_ID field has index 8
ERROR_MESSAGE field index 9
SESSION_ID field has index 10
COMMAND_TEXT field has index 11
COMMAND_PARAM field has index 12
ENTRY_ID field has index 13

There should be no many-to-one mappings from target fields to Audit Vault Server fields. For example, the following is invalid:

<!-- Invalid code
<Map>
    <Name>0</Name>
    <MapTo>UserName</MapTo>
</Map>
<Map>
    <Name>1</Name>
    <MapTo>UserName</MapTo>
</Map> -->

Core Fields

<CoreFields>

CoreFields provides mapping from target fields to core fields of Audit Vault Server. Target fields specified in core field mappings must either be a string or a data type that can be converted to string.

The following elements contain core fields.

<Map>
            <Name>2</Name>
            <MapTo>EventTimeUTC</MapTo>
            <TimestampPattern>yyyy-MM-dd'T'HH:mm:ss.SSSZ</TimestampPattern>
         </Map>

EventTimeUTC provides event time mapping information. The value in TimestampPattern specifies the timestamp format for event time. EventTimeUTC and TimestampPattern are mandatory.

When specifying the TimestampPattern, use the supported patterns and characters of the Java SimpleDateFormat class, NOT Oracle Database specific patterns.

For multibyte characters such as Chinese, specific words such as Month should be added into the pattern as characters in SimpleDateFormat. The AM and PM indicators are obtained based on locale, but should be explicitly mentioned in the TimestampPattern that you provide in the mapper file.

<Map>
   <Name>5</Name>
   <MapTo>UserName</MapTo>
</Map>

UserName represents the user who performed the action. If the mapping is not provided, Audit Data Collection still starts successfully, but every audit record is treated as invalid.

 <Map>
  <Name>1</Name>
  <MapTo>CommandClass</MapTo>
</Map>

CommandClass represents the action of the event. If the mapping is not provided, Audit Data Collection still starts successfully, but all audit records are treated as invalid.

<Transformation>
   <ValueTransformation from="createUser" to="CREATE" />
   <ValueTransformation from="createCollection" to="CREATE" />
   <ValueTransformation from="authenticate" to="AUTHENTICATE" />
   <ValueTransformation from="dropCollection" to="DROP" />
   <ValueTransformation from="dropUser" to="DROP" />
</Transformation>

CommandClass contains a Transformation field with ValueTransformation values, from targets to the Audit Vault Server CommandClass field. These transformations are mandatory.

The to attributes are values for the CommandClass field. If you can meaningfully map an event to one of these values, Oracle recommends that you do so. If this is not possible, use a value that appropriately reflects the action that generated the audit event.

<Map>
   <Name>1</Name>
   <MapTo>TargetObject</MapTo>
   <Transformation>
      <FieldTransformation from="createUser" to="6" />
      <FieldTransformation from="createCollection" to="6" />
      <FieldTransformation from="authenticate" to="6" />
      <FieldTransformation from="dropCollection" to="6" />
      <FieldTransformation from="dropUser" to="6" />
   </Transformation>
</Map>
    <Map>
   <Name>1</Name>
   <MapTo>TargetType</MapTo>
   <Transformation>
      <ValueTransformation from="createUser" to="USER" />
      <ValueTransformation from="createCollection" to="COLLECTION" />
      <ValueTransformation from="authenticate" to="USER" />
      <ValueTransformation from="dropCollection" to="COLLECTION" />
      <ValueTransformation from="dropUser" to="USER" />
   </Transformation>
</Map>
<Map>
   <Name>3</Name>
   <MapTo>ClientIP</MapTo>
</Map>

<Map>
   <Name>7</Name>
   <MapTo>EventStatus</MapTo>
   <!-- Specifying value transformation for Status source field value.
   Mandatory: EventStatus value transformation.
   There are three possible values for EventStatus:
   SUCCESS, FAILURE, UNKNOWN -->
   <Transformation>
      <ValueTransformation from="0" to="FAILURE" />
      <ValueTransformation from="100" to="SUCCESS" />
      <ValueTransformation from="200" to="UNKNOWN" />
   </Transformation>
</Map>

EventStatus contains a Transformation field with ValueTransformation values, from targets to Audit Vault EventStatus fields. These transformations are mandatory.

Large Fields Information

<LargeFields>
   <Map>
      <Name>11</Name>
      <MapTo>CommandText</MapTo>
   </Map>
   <Map>
      <Name>12</Name>
      <MapTo>CommandParam</MapTo>
   </Map>
</LargeFields>

LargeFields are target fields mapped to large fields in the Audit Vault Server. The specified target fields must be of type String or convertible to String.

Extension Fields

<ExtensionField>
   <ComplexName>
      <Name>10</Name>
      <DisplayName>sessionid</DisplayName>
   </ComplexName>
   <ComplexName>
      <Name>13</Name>
      <DisplayName>entryid</DisplayName>
   </ComplexName>
</ExtensionField>

ExtensionFields are target field names that must be stored as a name-value pair in the Extension field in Audit Vault Server. Target fields specified must be of type String or convertible to String.

Marker Fields
```
<MarkerField>
   <Name>10</Name>
   <Name>13</Name>
</MarkerField>
```
MarkerField contains a list of target fields that uniquely identify each audit record.

See Also: