1. Developer's Guide
  2. Audit Collection Plug-ins

3 Audit Collection Plug-ins

Learn about Audit Collection Plug-ins, and how to create them for Oracle Audit Vault.

3.1 About Audit Collection Plug-ins

Find out about the different types of collection plug-ins: What they do, how they collect audit trails, and where to find out more about them.

Collection plug-ins can retrieve audit data stored in either database tables or XML file audit trails, without the need for writing code.

Collection plug-ins are template-based generalized collectors. Users must provide a mapper file to collect audit data from a trail.

These collection plug-ins are created by preparing an XML Mapper file that supplies the mapping information for target fields to Audit Vault Server fields and other details for target types and audit trails.

This process does not require any coding. Audit Vault contains all the code necessary to interpret Mapper files and use them to collect the audit data from the audit trail appropriately.

Collection plug-ins support two types of audit trails:

  • Database Table: database table collection plug-ins can collect audit data from an audit table, using the information from the Mapper file.

  • XML File: XML file collection plug-ins can collect audit data from XML audit files present in a single directory, using the information from the Mapper file.

To use the collection plug-in for Database tables or XML file trails, you perform the following steps:

  1. Create an XML Mapper file for a target audit trail. This chapter discusses Mapper files in general and focuses on their creation.

  2. Create a plugin-manifest file for this target type.

  3. Create the collection plug-in by packaging the mapper file and plugin-manifest file.

You can now deploy this collection plug-in at the Audit Vault Server and use it to collect audit data after adding the target and any necessary collection attributes for this target.

See Also:

3.2 Database Table Collection Plug-ins

To use Oracle Audit Vault to collect audit data from the table type of trail, you can use database table collection plug-ins

Database table collection plug-ins support the collection of audit data from the table type of trail. They collect audit data from a single audit table. You can specify details of the audit table in the mapper file. These mapper files must conform to the schema.

Related Topics

3.2.1 Requirements for Database Table Collection Plug-ins

To use database table collection plug-ins for reading audit trails from target database tables, your data must meet Oracle Audit Vault and Database Firewall requirements.

You can use database table collection plug-ins for reading audit trails from target database tables if your data meets the requirements for collection.

Data Requirements for Table Collection Plug-Ins to Oracle Audit Vault and Database Firewall

  • Audit data must be stored in a single database table.

  • The target system has a user with privileges to read the audit data stored in this table.

  • The columns in the audit tables can be mapped to various Audit Vault core fields and large fields.

    Also single or multiple fields can be mapped to extension and marker fields. Fields mapped to Audit Vault core fields, extension fields, and marker fields must be of String data type or convertible to String. They cannot be of large data type, such as a CLOB. Columns having CLOB data type should use large Audit Vault fields, such as CommandText or CommandParam.

  • The audit trail must contain fields which map to the CommandClass Audit Vault core fields.

    The value of the CommandClass core field must not be null. If it is null, then the record is treated as an invalid record, so you must provide the proper mapping.

  • The audit file must have a field that can be mapped to the UserName core field. If a record has its UserName field as null, then the record is treated as invalid.

  • The collection plug-in can collect the text of any command issued, as well as any parameters passed to the command, in large fields. No other fields can be mapped to large fields in Audit Vault and Database Firewall.

  • The audit trail must contain a single column or group of columns that uniquely identify each audit record.

  • The audit trail must contain a field of type Timestamp that is monotonically increasing, that is, the value of the field increases with every new audit record inserted into the trail. This field must mapped to the EventTimeUTC core field in the mapper file. If, for any audit record, this field value becomes null, the collector treats this as an abnormal condition and shuts down.

Related Topics

3.2.2 Example Audit Trail for a Database Table Collection Plug-in

This example audit trail shows the details of audit trail. This example file is used in other locations to demonstrate the creation and structure of a sample mapper file for Oracle Audit Vault and Database Firewall.

The following table lists the structure for the hypothetical target type, DBSOURCE, that generates and stores audit data in a table AUD:

Table 3-1 AUD Audit Table Data Fields and Mappings

Target Field Data Type Audit Vault Server Field Map to Field Type

USER_ID

varchar

UserName

core field

OS_USER_ID

varchar

OSUserName

core field

ACTION

int

CommandClass

core field

STATUS

int

EventStatus

core field

EVENT_TIME

timestamp

EventTimeUTC

core field

OBJ_NAME

varchar

TargetObject

core field

OBJ_CREATOR

varchar

TargetOwner

core field

USER_HOST

varchar

ClientHostName

core field

SQL_TEXT

clob

CommandText

core field

SQL_BIND

clob

CommandParam

core field

TERMINAL

varchar

TerminalName

extension field

DB_ID

varchar

extension field

extension field

INSTANCE

varchar

extension field

extension field

PROCESS

int

extension field

extension field

SESSION_ID

int

marker field

marker field

ENTRY_ID

int

marker field

marker field

Not all of the target fields map to core fields. The target fields that do not map to core fields map to extension fields, or to designated marker fields, which test the uniqueness of an audit record.

3.2.3 Creating a Database Table Mapper File

Learn how to create an Oracle Audit Vault XML mapper file for a database table collection plug-in, and learn about each XML element and attribute used in this type of mapper file.

See Also:

Example of Creating a Mapper File for Database Table Collection Plug-ins

  • Top Level Element

    <AVTableCollectorTemplate securedTargetType="DBSOURCE" minSecuredTargetVersion="10.2.0"
           maxSecuredTargetVersion="11.0" version="1.0" >

    The AVTableCollectorTemplate is the top level element, which marks the start of the mapper file. It 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.

  • Table Name Information

    <TableName>AUD</TableName>

    You must provide the TableName of the audit table. This is a mandatory field.

    The TableName field in this file must match the trail location in the Add Audit Trail screen.

    Note:

    The collector checks if the trail location matches the TableName specified in the mapper file and does not start if they do not match.

    The collector chooses the appropriate mapper file in the templates folder by validating the target collection attribute av.collector.securedtargetversion version and if it is within the range specified in the top level element minSecuredTargetVersion and maxSecuredTargetVersion attributes.

    For example, if the target collection attribute av.collector.securedtargetversion = 11.1.0.0, then the collector picks a mapper file that has a top level element in a range within the specified version: 

    <AVTableCollectorTemplate securedTargetType="Oracle Database" minSecuredTargetVersion="10.2.0" maxSecuredTargetVersion="12.3" version="1.0">

    This enables multiple versions of the same plug in (different mapper files inside the templates folder) to address different target versions.

  • Target Connection Information

    <ConnectionInfo>

   <DataSource>platform.jdbc.dbsource.DBSourceDataSource</DataSource>
</ConnectionInfo>

    You must provide the full name for the datasource class implementing javax.sql.DataSource interface. This is a mandatory field.

  • Field Mapping Information

    <FieldMappingInfo>

    FieldMappingInfo must provide mapping information from target fields to various Audit Vault fields, along with the value transformations if any. This is a mandatory element.

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

    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>USER_ID</Name>
    <MapTo>UserName</MapTo>
</Map>     
<Map>
    <Name>OS_USER_ID</Name>
    <MapTo>UserName</MapTo>
</Map>  -->

About Mappings for Core, Large, Extension, and Marker Fields

  • The following sections explain mappings for core, large, extension, and marker fields:

    • Core Fields

      <CoreFields>

      CoreFields provides mapping from target fields to core fields of Audit Vault Server. The data type of target fields specified must belong to either a SQL string data type or a data type that can convert to a String.

      The following elements contain core fields.

      <Map>
  <Name>EVENT_TIME</Name>
  <MapTo>EventTimeUTC</MapTo>
</Map>

      EventTimeUTC provides event time mapping information. It is a mandatory field.

      EVENT_TIME target fields must be of the SQL data type Timestamp. 

      <Map>
  <Name>USER_ID</Name>
  <MapTo>UserName</MapTo>
</Map>

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

      <Map>
  <Name>OS_USER_ID</Name>
  <MapTo>OSUserName</MapTo>
</Map>  
    
      <Map>
  <Name>ACTION</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="1" to="CREATE"/>
          <ValueTransformation from="2" to="INSERT"/>
          <ValueTransformation from="3" to="SELECT"/>
          <ValueTransformation from="4" to="CREATE"/>
          <ValueTransformation from="15" to="READ"/>
          <ValueTransformation from="30" to="LOGON"/>
          <ValueTransformation from="34" to="LOGOFF"/>
          <ValueTransformation from="35" to="ACQUIRE"/>
        </Transformation>
      </Map>

      CommandClass contains a Transformation field with ValueTransformation values, from targets to the Audit Vault 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, then Oracle recommends that you do so. If this is not possible, then use a value that appropriately reflects the action that generated the audit event. 

      <Map>
  <Name> OBJ_NAME</Name>
  <MapTo>TargetObject</MapTo>
</Map>


<Map>
   <Name>TERMINAL</Name>
   <MapTo>TerminalName</MapTo>
</Map> 

      <Map>
  <Name>USER_HOST</Name>
  <MapTo>ClientHostName</MapTo>
</Map>

      <Map>
  <Name>OBJ_CREATOR</Name>
  <MapTo>TargetOwner</MapTo>
 </Map>

      <Map>
  <Name>STATUS</Name>
  <MapTo>EventStatus</MapTo>
        <Transformation>          
          <ValueTransformation from="0" to="FAILURE"/>
          <ValueTransformation from="1" to="SUCCESS"/>
          <ValueTransformation from="2" to="UNKNOWN"/>
        </Transformation>
 </Map>

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

      </CoreFields>

    • Large Fields Information

      <LargeFields>     
  <Map>
    <Name>SQL_TEXT</Name>
    <MapTo>CommandText</MapTo>
 </Map>
<Map>
   <Name>COMMAND_PARAMETER</Name>
   <MapTo>CommandParam</MapTo>
 </Map>        
</LargeFields>

      LargeFields are target fields mapped to large fields in the Audit Vault Server, such as CommandText or CommandParam. The specified target fields must be of SQL data type CLOB or String, or be convertible to String.

    • Extension Field

      <ExtensionField>      
   <Name>DB_ID</Name>
   <Name>INSTANCE</Name>
   <Name>PROCESS</Name>
</ExtensionField>

      The ExtensionField is a target field name that must be stored as a name-value pair in the Extension field in Audit Vault Server. Target columns specified here should have a String value or a value that can be converted to String without loss of information.

      ComplexName

      
<ExtensionField>
	<ComplexName>
		<Name>column_name</Name>
		<RegExp>exp</RegExp>
	</ComplexName>
</ExtensionField>

      ComplexName is a tag in the ExtensionField.

      The column_name is an audit table column name which is a string. For example: comment$text

      exp is the regular expression from which we get a list of key value pairs from the text after processing. It should contain 2 groups, out of which one is for key and the other one for value. For example: ([^;]+):([^;]+)

    • Marker Field

      
<MarkerField>       
  <Name>SESSION_ID</Name>  
  <Name>ENTRY_ID</Name>
</MarkerField>

      The MarkerField contains a list of target field names that uniquely identify each audit record. The target fields specified must be of SQL data type String or convertible to String. MarkerField is mandatory.

    • End Tags

      The field tags must be properly closed in order for the file to be valid. The following are examples of field end tags:

      </FieldMappingInfo>

</AVTableCollectorTemplate>

See Also:

3.3 XML File Collection Plug-ins

Learn how to use Oracle AVDF XML file collection plug-ins to collect audit data from an XML file type of trail.

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

Related Topics

3.3.1 Requirements for XML File Collection Plug-ins

To use XML collection plug-ins for reading audit trails from XML files, your data must meet Oracle Audit Vault and Database Firewall requirements.

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

XML File Audit Record File Requirements for Oracle Audit Vault and Database Firewall

  • The audit trail must be stored in one or more XML files in a single directory path.

  • The user must have read permission on the directory containing the XML audit files.

  • XML files in this directory must be valid, well-formed XML documents, within the constraints of the XML 1.0 specification.

  • The file and record start elements must be as specified in the mapper file.

  • All the audit record elements should be at the same level in Audit XML files.

  • All the audit record elements in Audit XML files must be the same.

  • Under every audit record element, all the field elements must be at the same level and one level below the audit record start element.

  • The XML audit file must have an element value that can be mapped to the CommandClass core field. If a record has its CommandClass field as null, then the record is treated as invalid.

  • The XML audit file must have an element value that can be mapped to the UserName core field. If a record has its UserName field as null, then the record is treated as invalid.

  • In the XML 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. If the field value for the event time in audit records is found null, then the collection plug-in takes the time of the record last sent from the same XML audit file.

  • The audit trail must contain a single element value or group of element values in the audit record that uniquely identify each audit record in XML Audit files.

  • Common information shared by all audit records in XML file should be present in the beginning of the XML file, under the file start element, at the same level as the audit record elements.

  • If an audit data target produces audit files with multiple XML formats, then the user must provide a separate mapper file for each audit file format having a different start element.

  • XML 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). XML 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). XML 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.

3.3.2 Example Audit Trail for an XML File Collection Plug-in

This example audit trail for an xml file collection plug-in shows the details of an XML 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 an XML file collection plug-in in Oracle Audit Vault and Database Fireewall documentation.

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

Table 3-2 Audit Data Fields in XML Audit Records and Mappings

Target Field Audit Vault Server Field Map to Field Type

USER_ID

UserName

core field

OS_USER_ID

OSUserName

core field

ACTION

CommandClass

core field

STATUS

EventStatus

core field

EVENT_TIME

EventTimeUTC

core field

OBJ_NAME

TargetObject

core field

OBJ_CREATOR

TargetOwner

core field

USER_HOST

ClientHostName

core field

SQL_TEXT

CommandText

core field

SQL_BIND

CommandParam

core field

TERMINAL

TerminalName

extension field

DB_ID

extension field

extension field

INSTANCE

extension field

extension field

PROCESS

extension field

extension field

SESSION_ID

marker field

marker field

ENTRY_ID

marker field

marker field

Example 3-1 Sample XML Audit Record

<?xml version="1.0" encoding="UTF-8"?>
<Audit>
    <AuditRecord>
        <Audit_type>1</Audit_type>
        <User_id>scott</User_id>
        <Os_user_id>usr1</Os_user_id>
        <Action>select</Action>
        <Status>0</Status>
        <Event_time>2010-11-11 12:23:59.166</Event_time>
        <Obj_name>emp</Obj_name>
        <Terminal>t1</Terminal>
        <Db_id>136</Db_id>
        <Session_id>170191</Session_id>
        <Entry_id>1</Entry_id>
    </AuditRecord>
    <AuditRecord>
        <Audit_type>3</Audit_type>
        <User_id>scott</User_id>
        <Os_user_id>usr1</Os_user_id>
        <Action>delete</Action>
        <Status>1</Status>
        <Event_time>2010-11-11 12:33:59.166</Event_time>
        <Obj_name>emp</Obj_name>
        <Terminal>t1</Terminal>
        <Db_id>136</Db_id>
        <Session_id>170191</Session_id>
        <Entry_id>2</Entry_id>
    </AuditRecord>
</Audit>

3.3.3 Creating the XML File Audit Collection Mapper File

To create an XML file collection plug-in mapper file, you must describe the collection plug-in mappings in this mapper file in accordance with Oracle Audit Vault and Database Firewall 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 Audit Vault and Database Firewall

  • Top-Level Element

    <AVXMLCollectorTemplate securedTargetType="XMLSOURCE"
    maxSecuredTargetVersion="11.0" version="1.0">

    The AVXMLCollectorTemplate 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>Audit</StartTag>
</HeaderInfo>

    HeaderInfo is mandatory. It contains one child element, StartTag, which names the top-level element of the audit record file.

  • Record Information

    <RecordInfo>
  <StartTag>AuditRecord</StartTag>    
</RecordInfo>

    RecordInfo provides the starting element of audit records in XML audit files. RecordInfo is mandatory.

    StartTag is the starting element of each audit record in XML audit files.

  • 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.

    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>USER_ID</Name>
    <MapTo>UserName</MapTo>
</Map>     
<Map>
    <Name>OS_USER_ID</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 be of SQL data type, either a string or a data type that can convert to string.

      The following elements contain core fields.

      <Map>
 <Name>EVENT_TIME</Name>
  <MapTo>EventTimeUTC</MapTo>
  <TimestampPattern>yyyy-MM-dd HH:mm:ss.SSS</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>USER_ID</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>OS_USER_ID</Name>
  <MapTo>OSUserName</MapTo>
</Map>  
 <Map>
  <Name>ACTION</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="1" to="CREATE"/>
          <ValueTransformation from="2" to="INSERT"/>
          <ValueTransformation from="3" to="SELECT"/>
          <ValueTransformation from="4" to="CREATE"/>
          <ValueTransformation from="15" to="READ"/>
          <ValueTransformation from="30" to="LOGON"/>
          <ValueTransformation from="34" to="LOGOFF"/>
          <ValueTransformation from="35" to="ACQUIRE"/>
        </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>OBJ_NAME</Name>
  <MapTo>TargetObject</MapTo>
</Map>
<Map>
  <Name>USER_HOST</Name>
  <MapTo>ClientHostName</MapTo>
</Map>

<Map>
   <Name>TERMINAL</Name>
   <MapTo>TerminalName</MapTo>
</Map> 
<Map>
  <Name>OBJ_CREATOR</Name>
  <MapTo>TargetOwner</MapTo>
</Map>
<Map>
  <Name>STATUS</Name>
  <MapTo>EventStatus</MapTo>
        <Transformation>          
          <ValueTransformation from="0" to="FAILURE"/>
          <ValueTransformation from="1" to="SUCCESS"/>
          <ValueTransformation from="2" to="UNKNOWN"/>
        </Transformation>
</Map>

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

      </CoreFields>

    • Large Fields Information

      <LargeFields>     
  <Map>
    <Name>SQL_TEXT</Name>
    <MapTo>CommandText</MapTo>
  </Map>
<Map>
   <Name>COMMAND_PARAMETER</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 SQL data type CLOB or String, or be convertible to String.

    • Extension Fields

      <ExtensionField>      
   <Name>DB_ID</Name>
   <Name>INSTANCE</Name>
   <Name>PROCESS</Name>
</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 SQL data type CLOB or String, or be convertible to String.

    • Marker Fields

      <MarkerField>       
  <Name>SESSION_ID</Name>  
  <Name>ENTRY_ID</Name>
</MarkerField>

      MarkerField contains a list of target fields that uniquely identify each audit record. The target fields specified must be of SQL data type CLOB or String, or be convertible to String. MarkerField is mandatory.

See Also:

3.3.4 XML Transformation for Non-Standard Audit Records

If you have audit records in a non-standard audit data format, you can apply XML transformation using XSL on the XML audit records.

To apply XML transformation on the audit records, you provide an XSL file that can transform the audit data from its original format to the format currently specified for the XML file collection plug-ins. Doing this means that you can enhance file collection plug-ins to support a variety of XML audit data formats.

Related Topics

3.3.4.1 Additional Requirement for XML Transformation Using XSL

To transform non-standard audit records into the current format, your transformer must follow Oracle Audit Vault and Database Firewall standards.

The transformer must write to audit files in an incremental order. That is, the transformer must write to one audit file until its maximum size is reached, and then move over to another file. Therefore, only one file can be active at a time. If the transformer finds more than one incomplete XML audit file, then the XML file collection plug-in stops.

3.3.4.2 Changes Required to Transform Non-Standard Audit Records

To transform non-standard audit records with Oracle Audit Vault and Database Firewall, you must complete this procedure.

You must perform these steps:

  1. Add a section such as this example to the mapper file after <RecordInfo>, specifying the name of XSL file that you want to be used for transformation, and the SourceFileStartTag for the file to be transformed.
    <XslTransformation>
    <XslFile>test_template.xsl</XslFile>
    <SourceFileStartTag>AUDIT</SourceFileStartTag>
</XslTransformation>
  2. Provide the XSL file and place it in the templates folder of the plugin directory.
  3. You can also make calls to Java functions from within the XSL file. To do this, place the jar file created in the jars folder of the plugin directory.
3.3.4.3 Sample Non-Standard XML Audit Data Record

See how to transform an XML data record to the proper XML format required for an XML file collection plug-in.

As you review this example, note that your source system can produce audit records with a different appearance.

Example 3-2 Audit.xml: Sample XML Audit Record 

<?xml version="1.0" encoding="UTF-8"?>
<AUDIT>
 
   <AUDIT_RECORD TIMESTAMP="2013-06-07T08:27:53" NAME="Audit" 
   SERVER_ID="0" VERSION="1" STARTUP_OPTIONS="C:/Program Files/MySQL/MySQL
   Server 5.6/bin\mysqld --defaults-file=C:\ProgramData\MySQL\MySQL Server
   5.6\my.ini" OS_VERSION="x86_64-Win64" MYSQL_VERSION=
   "5.6.11-enterprise-commercial-advanced"/>
 
   <AUDIT_RECORD TIMESTAMP="2013-06-07T08:30:46" NAME="Connect" CONNECTION_ID="1"
   STATUS="0" USER="root" PRIV_USER="root" OS_LOGIN="" PROXY_USER=""  
   HOST="localhost" IP="127.0.0.1" DB=""/>
 
   <AUDIT_RECORD TIMESTAMP="2013-06-07T08:31:21" NAME="Query" CONNECTION_ID="1"
   STATUS="0" SQLTEXT="CREATE USER 'admin'@'localhost' IDENTIFIED BY 
   'welcome_1'"/>
 
</AUDIT>
3.3.4.4 Creating an XSL File for Transformation

To create an XSL transformation file that defines transformation rules you must create a version that can transform the source audit records that your system creates, and place it in the templates folder of the plugin.

The Audit.xml transformed audit record file does not appear in your folder. It is just an example showing the result of transforming the Audit.xml file into the required XML format, using the XSL transformation file in the test_template.xsl example.

Example 3-3 test_template.xsl 

<?xml version="1.0"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes" />
     <xsl:template match="/">
        <ROOT_DEST>
          <xsl:for-each select="AUDIT/AUDIT_RECORD">
            <Record_Dest>
                <USER><xsl:value-of select="@USER"/></USER>
                <PRIV_USER><xsl:value-of select="@PRIV_USER"/></PRIV_USER>
                <OS_LOGIN><xsl:value-of select="@OS_LOGIN"/></OS_LOGIN>
                <PROXY_USER><xsl:value-of select="@PROXY_USER"/></PROXY_USER>
                <HOST><xsl:value-of select="@HOST"/></HOST>
                <IP><xsl:value-of select="@IP"/></IP>
                <DB><xsl:value-of select="@DB"/></DB>
                <SQLTEXT><xsl:value-of select="@SQLTEXT"/></SQLTEXT>
                <CONNECTION_ID><xsl:value-of select= 
                      "@CONNECTION_ID"/></CONNECTION_ID>
                <STATUS><xsl:value-of select="@STATUS"/></STATUS>
                <TIMESTAMP><xsl:value-of select="@TIMESTAMP"/></TIMESTAMP>
                <NAME><xsl:value-of select="@NAME"/></NAME>
                <SERVER_ID><xsl:value-of select="@SERVER_ID"/></SERVER_ID>
                <VERSION><xsl:value-of select="@VERSION" /></VERSION>
   <STARTUP_OPTIONS><xsl:value-of select="@STARTUP_OPTIONS"/> </STARTUP_OPTIONS>
                <OS_VERSION><xsl:value-of select="@OS_VERSION"/></OS_VERSION>
                <MYSQL_VERSION><xsl:value-of select="@MYSQL_VERSION"/>
                    </MYSQL_VERSION>
            </Record_Dest>
          </xsl:for-each>
         </ROOT_DEST>
        </xsl:template>
  </xsl:stylesheet>

Example 3-4 Transformed Audit Record file

<ROOT_DEST>
     <Record_Dest>
         <USER></USER>
         <PRIV_USER></PRIV_USER>
         <OS_LOGIN></OS_LOGIN>
         <PROXY_USER></PROXY_USER>
         <HOST></HOST>
         <IP></IP>
         <DB></DB>
         <SQLTEXT></SQLTEXT>
         <CONNECTION_ID></CONNECTION_ID>
         <STATUS></STATUS>
         <TIMESTAMP>2013-06-07T08:27:53</TIMESTAMP>
         <NAME>Audit</NAME>
         <SERVER_ID>0</SERVER_ID>
         <VERSION>1</VERSION>
         <STARTUP_OPTIONS>C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld
             --defaults-file=C:\ProgramData\MySQL\MySQL Server
             5.6\my.ini</STARTUP_OPTIONS>
         <OS_VERSION>x86_64-Win64</OS_VERSION>
         <MYSQL_VERSION>5.6.11-enterprise-commercial-advanced</MYSQL_VERSION>
     </Record_Dest>
     <Record_Dest>
         <USER>root</USER>
         <PRIV_USER>root</PRIV_USER>
         <OS_LOGIN></OS_LOGIN>
         <PROXY_USER></PROXY_USER>
         <HOST>localhost</HOST>
         <IP>127.0.0.1</IP>
         <DB></DB>
         <SQLTEXT></SQLTEXT>
         <CONNECTION_ID>1</CONNECTION_ID>
         <STATUS>0</STATUS>
         <TIMESTAMP>2013-06-07T08:30:46</TIMESTAMP>
         <NAME>Connect</NAME>
         <SERVER_ID></SERVER_ID>
         <VERSION></VERSION>
         <STARTUP_OPTIONS></STARTUP_OPTIONS>
         <OS_VERSION></OS_VERSION>
         <MYSQL_VERSION></MYSQL_VERSION>
     </Record_Dest>
     <Record_Dest>
         <USER></USER>
         <PRIV_USER></PRIV_USER>
         <OS_LOGIN></OS_LOGIN>
         <PROXY_USER></PROXY_USER>
         <HOST></HOST>
         <IP></IP>
         <DB></DB>
         <SQLTEXT>CREATE USER 'admin'@'localhost' IDENTIFIED BY 
                 'welcome_1'</SQLTEXT>
         <CONNECTION_ID>1</CONNECTION_ID>
         <STATUS>0</STATUS>
         <TIMESTAMP>2013-06-07T08:31:21</TIMESTAMP>
         <NAME>Query</NAME>
         <SERVER_ID></SERVER_ID>
         <VERSION></VERSION>
         <STARTUP_OPTIONS></STARTUP_OPTIONS>
         <OS_VERSION></OS_VERSION>
         <MYSQL_VERSION></MYSQL_VERSION>
     </Record_Dest>
</ROOT_DEST>

3.4 JSON File Collection Plug-ins

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

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

Related Topics

3.4.1 Requirements for JSON File Collection Plug-ins

To use JSON collection plug-ins for reading audit trails from JSON files, your data must meet Oracle Audit Vault and Database Firewall requirements.

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

JSON File Audit Record File Requirements for Oracle Audit Vault and Database Firewall

  • The audit trail must be stored in one or more JSON files in a single directory path.

  • The user must have read permission on the directory containing the JSON audit files.

  • JSON files in this directory must be valid, well-formed JSON documents, within the constraints of the JSON specification.

  • The file and record start elements must be as specified in the mapper file.

  • The JSON audit file must have a field whose JSONPath can be mapped to the CommandClass core field. If a record has its CommandClass field as null, then the record is treated as invalid.

  • In the JSON 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. If the field value for the event time in audit records is found null, then the collection plug-in takes the time of the record last sent from the same JSON audit file.

  • The audit trail must contain a single element value or group of element values in the audit record that uniquely identify each audit record in JSON Audit files.

  • If an audit data target produces audit files with multiple JSON formats, then the user must provide a separate mapper file for each audit file format having a different start element.

  • JSON 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). JSON 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). JSON 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.

3.4.2 Example Audit Trail for a JSON File Collection Plug-in

This example audit trail for a JSON file collection plug-in shows the details of a JSON 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 JSON file collection plug-in, in the Oracle Audit Vault and Database Firewall documentation.

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

Table 3-3 Audit Data Fields in JSON Audit Records and Mappings

Target Field Audit Vault Server Field Map to Field Type

USER_ID

UserName

core field

OS_USER_ID

OSUserName

core field

ACTION

CommandClass

core field

STATUS

EventStatus

core field

EVENT_TIME

EventTimeUTC

core field

OBJ_NAME

TargetObject

core field

OBJ_CREATOR

TargetOwner

core field

USER_HOST

ClientHostName

core field

SQL_TEXT

CommandText

core field

SQL_BIND

CommandParam

core field

TERMINAL

TerminalName

extension field

DB_ID

extension field

extension field

INSTANCE

extension field

extension field

PROCESS

extension field

extension field

SESSION_ID

marker field

marker field

ENTRY_ID

marker field

marker field

Example 3-5 Sample JSON Audit Record


{
   "ITEMS":[
      {
         "SESSION_ID":123,
         "AUDIT_TYPE":1,
         "USER_ID":"scott",
         "OS_USER_ID":"usr1",
         "ACTION":"select",
         "STATUS":0,
         "EVENT_TIME":"2020-11-28 12:23:59.166",
         "OBJ_NAME":"emp",
         "OBJ_CREATOR":"scott",
         "TERMINAL":"t1",
         "DB_ID":136,
         "ENTRY_ID":1
      },
      {
         "SESSION_ID":123,
         "AUDIT_TYPE":1,
         "USER_ID":"scott",
         "OS_USER_ID":"usr1",
         "ACTION":"delete",
         "STATUS":0,
         "EVENT_TIME":"2020-11-28 12:24:22.177",
         "OBJ_NAME":"emp",
         "OBJ_CREATOR":"scott",
         "TERMINAL":"t1",
         "DB_ID":136,
         "ENTRY_ID":2
      }
   ]
}

3.4.3 Creating the JSON File Audit Collection Mapper File

To create a JSON file collection plug-in mapper file, you must describe the collection plug-in mappings in this mapper file in accordance with Oracle Audit Vault and Database Firewall 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 Audit Vault and Database Firewall

  • Top-Level Element

    
<AVJSONCollectorTemplate securedTargetType="JSONSOURCE"
      maxSecuredTargetVersion="11.0"
      version="1.0">

    The AVJSONCollectorTemplate 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>ITEMS</StartTag>
</HeaderInfo>

    HeaderInfo is mandatory. It contains one child element, StartTag, which names the top-level element of the audit record file.

  • Record Information

    
<RecordInfo>
  <StartTag>SESSION_ID</StartTag>
</RecordInfo>

    RecordInfo provides the starting element of audit records in JSON audit files. RecordInfo is mandatory.

    StartTag is the starting element of each audit record in JSON audit files. If the JSON file has one fully formed JSON record per line, then the HeaderInfo and RecordInfo also have the same start tag, which is the first element of the JSON record.

  • 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.

    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>$.USER_ID</Name>
    <MapTo>UserName</MapTo>
</Map>     
<Map>
    <Name>$.OS_USER_ID</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 be of SQL data type, either a string or a data type that can convert to string.

      The following elements contain core fields.

      <Map>
 <Name>$.EVENT_TIME</Name>
  <MapTo>EventTimeUTC</MapTo>
  <TimestampPattern>yyyy-MM-dd HH:mm:ss.SSS</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>$.USER_ID</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>$.OS_USER_ID</Name>
  <MapTo>OSUserName</MapTo>
</Map>  
 <Map>
  <Name>$.ACTION</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="1" to="CREATE"/>
          <ValueTransformation from="2" to="INSERT"/>
          <ValueTransformation from="3" to="SELECT"/>
          <ValueTransformation from="4" to="CREATE"/>
          <ValueTransformation from="15" to="READ"/>
          <ValueTransformation from="30" to="LOGON"/>
          <ValueTransformation from="34" to="LOGOFF"/>
          <ValueTransformation from="35" to="ACQUIRE"/>
        </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>$.OBJ_NAME</Name>
  <MapTo>TargetObject</MapTo>
</Map>
<Map>
  <Name>$.USER_HOST</Name>
  <MapTo>ClientHostName</MapTo>
</Map>

<Map>
   <Name>$.TERMINAL</Name>
   <MapTo>TerminalName</MapTo>
</Map> 
<Map>
  <Name>$.OBJ_CREATOR</Name>
  <MapTo>TargetOwner</MapTo>
</Map>
<Map>
  <Name>$.STATUS</Name>
  <MapTo>EventStatus</MapTo>
        <Transformation>          
          <ValueTransformation from="0" to="FAILURE"/>
          <ValueTransformation from="1" to="SUCCESS"/>
          <ValueTransformation from="2" to="UNKNOWN"/>
        </Transformation>
</Map>

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

      </CoreFields>

    • Large Fields Information

      <LargeFields>     
  <Map>
    <Name>$.SQL_TEXT</Name>
    <MapTo>CommandText</MapTo>
  </Map>
<Map>
   <Name>$.COMMAND_PARAMETER</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 SQL data type CLOB or String, or be convertible to String.

    • Extension Fields

      <ExtensionField>      
   <Name>$.DB_ID</Name>
   <Name>$.INSTANCE</Name>
   <Name>$.PROCESS</Name>
</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 SQL data type CLOB or String, or be convertible to String.

    • Marker Fields

      <MarkerField>       
  <Name>$.SESSION_ID</Name>  
  <Name>$.ENTRY_ID</Name>
</MarkerField>

      MarkerField contains a list of target fields that uniquely identify each audit record. The target fields specified must be of SQL data type CLOB or String, or be convertible to String. MarkerField is mandatory.

See Also:

3.5 CSV File Collection Plug-ins

Learn how to use Oracle AVDF 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.

3.5.1 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 Audit Vault and Database Firewall 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 Audit Vault and Database Firewall

  • 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.

3.5.2 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 Audit Vault and Database Firewall 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 3-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 3-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

3.5.3 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 Audit Vault and Database Firewall 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 Audit Vault and Database Firewall

  • 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:

3.6 JSON REST Collection Plug-ins

Learn how to use Oracle AVDF JSON collection plug-ins to collect audit data from a JSON type of trail.

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

Related Topics

3.6.1 Requirements for JSON REST Collection Plug-ins

To use JSON collection plug-ins for reading audit trails from JSON files, your data must meet Oracle Audit Vault and Database Firewall requirements.

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

JSON Audit Record File Requirements for Oracle Audit Vault and Database Firewall

  • The audit trail must be stored in one or more JSON files in a single directory path.

  • The user must have read permission on the directory containing the JSON audit files.

  • JSON files in this directory must be valid, well-formed JSON documents, within the constraints of the JSON specification.

  • The file and record start elements must be as specified in the mapper file.

  • The JSON audit file must have a field whose JSONPath can be mapped to the CommandClass core field. If a record has its CommandClass field as null, then the record is treated as invalid.

  • In the JSON 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. If the field value for the event time in audit records is found null, then the collection plug-in takes the time of the record last sent from the same JSON audit file.

  • The audit trail must contain a single element value or group of element values in the audit record that uniquely identify each audit record in JSON audit files.

  • If an audit data target produces audit files with multiple JSON formats, then the user must provide a separate mapper file for each audit file format having a different start element.

  • JSON 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). JSON REST 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). JSON REST 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.

3.6.2 Example Audit Trail for a JSON REST Collections Plug-in

This example audit trail for a JSON collection plug-in shows the details of a JSON 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 JSON collection plug-in, in the Oracle Audit Vault and Database Firewall documentation.

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

Table 3-5 Audit Data Fields in JSON Audit Records and Mappings

Target Field Audit Vault Server Field Map to Field Type

USER_ID

UserName

core field

OS_USER_ID

OSUserName

core field

ACTION

CommandClass

core field

STATUS

EventStatus

core field

EVENT_TIME

EventTimeUTC

core field

OBJ_NAME

TargetObject

core field

OBJ_CREATOR

TargetOwner

core field

USER_HOST

ClientHostName

core field

SQL_TEXT

CommandText

core field

SQL_BIND

CommandParam

core field

TERMINAL

TerminalName

extension field

DB_ID

extension field

extension field

INSTANCE

extension field

extension field

PROCESS

extension field

extension field

SESSION_ID

marker field

marker field

ENTRY_ID

marker field

marker field

Example 3-7 Sample JSON Audit Record


{
   "ITEMS":[
      {
         "SESSION_ID":123,
         "AUDIT_TYPE":1,
         "USER_ID":"scott",
         "OS_USER_ID":"usr1",
         "ACTION":"select",
         "STATUS":0,
         "EVENT_TIME":"2020-11-28 12:23:59.166",
         "OBJ_NAME":"emp",
         "OBJ_CREATOR":"scott",
         "TERMINAL":"t1",
         "DB_ID":136,
         "ENTRY_ID":1
      },
      {
         "SESSION_ID":123,
         "AUDIT_TYPE":1,
         "USER_ID":"scott",
         "OS_USER_ID":"usr1",
         "ACTION":"delete",
         "STATUS":0,
         "EVENT_TIME":"2020-11-28 12:24:22.177",
         "OBJ_NAME":"emp",
         "OBJ_CREATOR":"scott",
         "TERMINAL":"t1",
         "DB_ID":136,
         "ENTRY_ID":2
      }
   ]
}

3.6.3 Creating the JSON REST Audit Collection Mapper File

To create a JSON file collection plug-in mapper file, you must describe the collection plug-in mappings in this mapper file in accordance with Oracle Audit Vault and Database Firewall 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 Audit Vault and Database Firewall

  • Top-Level Element

    
<AVJSONCollectorTemplate securedTargetType="JSONSOURCE"
      maxSecuredTargetVersion="11.0"
      version="1.0">

    The AVJSONCollectorTemplate 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>ITEMS</StartTag>
</HeaderInfo>

    HeaderInfo is mandatory. It contains one child element, StartTag, which names the top-level element of the audit record file.

  • Record Information

    
<RecordInfo>
  <StartTag>SESSION_ID</StartTag>
</RecordInfo>

    RecordInfo provides the starting element of audit records in JSON audit files. RecordInfo is mandatory.

    StartTag is the starting element of each audit record in JSON audit files. If the JSON file has one fully formed JSON record per line, then the HeaderInfo and RecordInfo also have the same start tag, which is the first element of the JSON record.

  • Service Details

    <ServiceDetails>

    Example code:

    
 <!-- Query format for providing the start time and end time query parameters -->
      <QueryFormat>{startTime}/{endTime}</QueryFormat>
        <!-- Timestamp format for start time and end time -->
      <TimeFormat>yyyy-MM-dd hh:mm:ss.SSS</TimeFormat>
      <NextLink>
           <!-- Next link start tag -->
         <NextLinkStartTag>next</NextLinkStartTag>
             <!-- Next link pattern -->
         <NextLinkPattern>$.next.$ref</NextLinkPattern>
      </NextLink>
        <!-- Authentication mechanism for REST Service -->
      <RESTAuthentication>
           <!-- Username and password based Basic Authentication -->
         <BasicAuth/>
      </RESTAuthentication>
   </ServiceDetails>

    Here is the explanation of the fields. All the fields are mandatory.

    Service Details provides information about REST service corresponding to the audit trail.

    Query Format describes the format of the REST query for providing the start time and end time query parameters.

    Time Format describes timestamp format for start time and end time.

    Next Link Start Tag provides the next link start tag of the REST URL.

    Next Link Pattern provides JSON path expression of the next link of the REST URL.

    REST Authentication describes the authentication mechanism used to connect to the target.

    BasicAuth indicates the authentication mechanism is Basic Authentication.

  • 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.

    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>$.USER_ID</Name>
    <MapTo>UserName</MapTo>
</Map>     
<Map>
    <Name>$.OS_USER_ID</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 be of SQL data type, either a string or a data type that can convert to string.

      The following elements contain core fields.

      <Map>
 <Name>$.EVENT_TIME</Name>
  <MapTo>EventTimeUTC</MapTo>
  <TimestampPattern>yyyy-MM-dd HH:mm:ss.SSS</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>$.USER_ID</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>$.OS_USER_ID</Name>
  <MapTo>OSUserName</MapTo>
</Map>  
 <Map>
  <Name>$.ACTION</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="1" to="CREATE"/>
          <ValueTransformation from="2" to="INSERT"/>
          <ValueTransformation from="3" to="SELECT"/>
          <ValueTransformation from="4" to="CREATE"/>
          <ValueTransformation from="15" to="READ"/>
          <ValueTransformation from="30" to="LOGON"/>
          <ValueTransformation from="34" to="LOGOFF"/>
          <ValueTransformation from="35" to="ACQUIRE"/>
        </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>$.OBJ_NAME</Name>
  <MapTo>TargetObject</MapTo>
</Map>
<Map>
  <Name>$.USER_HOST</Name>
  <MapTo>ClientHostName</MapTo>
</Map>

<Map>
   <Name>$.TERMINAL</Name>
   <MapTo>TerminalName</MapTo>
</Map> 
<Map>
  <Name>$.OBJ_CREATOR</Name>
  <MapTo>TargetOwner</MapTo>
</Map>
<Map>
  <Name>$.STATUS</Name>
  <MapTo>EventStatus</MapTo>
        <Transformation>          
          <ValueTransformation from="0" to="FAILURE"/>
          <ValueTransformation from="1" to="SUCCESS"/>
          <ValueTransformation from="2" to="UNKNOWN"/>
        </Transformation>
</Map>

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

      </CoreFields>

    • Large Fields Information

      <LargeFields>     
  <Map>
    <Name>$.SQL_TEXT</Name>
    <MapTo>CommandText</MapTo>
  </Map>
<Map>
   <Name>$.COMMAND_PARAMETER</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 SQL data type CLOB or String, or be convertible to String.

    • Extension Fields

      <ExtensionField>      
   <Name>$.DB_ID</Name>
   <Name>$.INSTANCE</Name>
   <Name>$.PROCESS</Name>
</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 SQL data type CLOB or String, or be convertible to String.

    • Marker Fields

      <MarkerField>       
  <Name>$.SESSION_ID</Name>  
  <Name>$.ENTRY_ID</Name>
</MarkerField>

      MarkerField contains a list of target fields that uniquely identify each audit record. The target fields specified must be of SQL data type CLOB or String, or be convertible to String. MarkerField is mandatory.

See Also:

3.7 Target Collection Attributes

You must define collection attributes before you can use Oracle AVDF plug-ins to collect data from the audit trail.

For Database Table plug-in, JSON, CSV, XML file collection plug-ins, and REST plug-in, you need to set the audit collection attributes during target registration. This has to be completed after deploying the collection plug-in in the Audit Vault Server and before starting the audit trail which uses the plug-in.

You define collection attributes using the AVCLI command ALTER SECURED TARGET.

Required target attributes are:

  • av.collector.securedtargetversion (Mandatory): Current version of the target. This version information helps in choosing the correct mapper file for the audit trail if there are multiple mapper files in the templates directory of the collection plug-in.

  • av.collector.atcintervaltime: The collection plug-in writes the time, up to which audit data has been collected from the trail, to a file. This file will be present in the av/atc directory in the agent home. Also, this file contains the time in UTC time zone. This information can help some third party utilities to clean up audit data from a trail. Note that collection plug-in does not perform the audit data clean-up, it just writes this information to a file. atcintervaltime: specifies how frequently the collection plug-in should update the time information in the file. The value of the attribute is in minutes.

  • av.collector.timezoneoffset (Mandatory): Offset of the target event time from UTC time zone. This helps the collector to report event time correctly to the Audit Vault Server by adjusting the time zones. This attribute is not needed for an XML file collection plug-in if the event time itself contains the time zone information. An example of this setting is as follows:

    av.collector.TimeZoneOffset = +5:30

  • av.collector.enableArchivedTime (Optional): This attribute is applicable only for Oracle table trail. It is set to true by default. When enableArchivedTime is set to true and if INIT_CLEANUP procedure is called for the trail, then the last archived timestamp in dbms_audit_mgmt package is updated to the current checkpoint time based on the ATC interval time. When set to false, the last archived timestamp for the trail is not updated in the target database. In that case, the user must ensure that the audit records with timestamps less than the checkpoint time should not be purged from the audit table. The user can view the Audit Vault Server database checkpoint table in AVSYS schema to verify the checkpoint time of the trail until the records have been collected. If you want to change the attribute value, then the trail must be restarted after the attribute has been updated.

3.8 Preprocessing Audit Data

Learn about the requirements to use Oracle AVDF collection plug-ins.

In general, collection plug-ins can only be used to collect audit trails that conform to the requirements presented in this chapter.

For other audit trails, you can use the Audit Vault Java API.

However, there can be other reasons why you cannot collect audit records directly with a collection plug-in, but you can collection them indirectly.

It can be possible to preprocess these audit trails to generate entries in database tables or XML files in a format that allows collection plug-ins to collect them. For example, IBM DB2 on Linux, Unix, and Microsoft Windows all require you to execute the db2audit program to extract audit records from a proprietary binary format into a text file. To extract new records, you must run this program periodically as the user who owns the DB2 software.

While you cannot define a collection plug-in to read the file directly, It is possible that you can write a program that reads the file periodically, extracts new audit records, and writes them to a new XML file in a directory. Each run of this program can create a new XML file that contains only the new records. You can then define a collection plug-in to read these XML files, and collect the audit records into Oracle Audit Vault Server.

Related Topics