C Plug-In Reference

This appendix contains high-level data for each plug-in that is shipped with Oracle Audit Vault and Database Firewall (Oracle AVDF). It also contains lookup information to complete the procedures for registering targets and configuring audit trails. These procedures link directly to the relevant sections of this appendix.

C.1 About Oracle Audit Vault and Database Firewall Plug-ins

Learn about the plug-ins supported by Oracle Audit Vault and Database Firewall.

Oracle Audit Vault and Database Firewall supports different types of targets by providing a plug-in for each target type. Oracle Audit Vault and Database Firewall ships with a set of plug-ins out-of-the-box. These plug-ins are packaged and deployed with the Audit Vault Server.

You can also develop your own plug-ins, or get new available plug-ins, and add them to your Oracle Audit Vault and Database Firewall installation.

C.2 Plug-ins That are Shipped with Oracle Audit Vault and Database Firewall

Oracle Audit Vault and Database Firewall supports plug-ins for a variety of different platforms, such as Oracle Solaris, Linux, and Microsoft Windows.

C.2.1 About Plug-ins

Oracle Audit Vault and Database Firewall supports plug-ins for many platforms and third-party products.

Oracle Audit Vault and Database Firewall plug-ins support the target versions listed in Table C-1. Click the link for each target to get detailed information.

Table C-1 Out-of-the-Box Plug-ins and Features Supported in Oracle Audit Vault and Database Firewall

Target Version Audit Trail Collection Audit Policy Creation, Entitlement Auditing Stored Procedure Auditing Audit Trail Cleanup Database Firewall Host Monitor Agent Native Network Encrypted Traffic Monitoring / Retrieve Session Information

Oracle Database Plug-in for Oracle Audit Vault and Database Firewall

11.2.0.4

Yes

Yes (except Unified Audit Policies)

Yes

Yes

Yes

Yes

Yes

Oracle Database Plug-in for Oracle Audit Vault and Database Firewall

12.1, 12.2, 18c, 19c

21c (Starting with Oracle AVDF 20.4)

Yes

Yes (including Unified Audit Policies)

Yes

Yes

Yes

Yes

Yes

Microsoft SQL Server Plug-in for Oracle Audit Vault and Database Firewall (Windows)

Enterprise Edition 2012*, 2014, 2016, 2017

Enterprise Edition 2019 (Starting with Oracle AVDF 20.3)

Enterprise Edition 2022 (Starting with Oracle ADVF 20.10)

Standard Edition 2019 (Starting with Oracle AVDF 20.6)

Standard Edition 2022 (Starting with Oracle ADVF 20.10)

Yes

No

Yes (Versions 2000, 2005, 2008, 2008 R2)

Yes

Yes

Yes (on Microsoft Windows 2008 and onwards)

Yes (Microsoft SQL Server 2005, 2008, 2008 R2)

(Retrieving session information only)

Microsoft SQL Server Plug-in for Oracle Audit Vault and Database Firewall* (Windows Clustered)

2012 R2

Yes

No

Yes (Versions 2012 R2)

Yes

No

No

No

PostgreSQL Plug-in for Oracle Audit Vault and Database Firewall

9.6 to 11.8

12, 13 (Starting with Oracle AVDF release 20.8)

14, 15 (Starting with Oracle AVDF release 20.10)

Yes No No No No No No

SAP Sybase ASE Plug-in for Oracle Audit Vault and Database Firewall*

15.7, 16

Yes

No

Yes

No

Yes

Yes

No

IBM DB2 Plug-in for Oracle Audit Vault and Database Firewall for LUW

10.5, 11.1, 11.5

Yes

No

No

Yes

Yes

Versions 9.1 - 10.5

Yes

No

Quick JSON Target Type for Oracle Audit Vault and Database Firewall Yes No No No No No No

MySQL Plug-in for Oracle Audit Vault and Database Firewall

5.6, 5.7, 8.0

Yes

No

No

Yes

Yes

Yes

No

Oracle Solaris Plug-in for Oracle Audit Vault and Database Firewall

11.3, 11.4 on x86-64 platforms*

Yes

No

No

No

No

Yes Versions 11, 11.1, 11.2

No

Oracle Solaris Plug-in for Oracle Audit Vault and Database Firewall

11.3, 11.4 on SPARC64 platforms

Yes

No

No

No

No

Yes Versions 11, 11.1, 11.2

No

Oracle Linux

6.0 to 6.9

7.0 to 7.5

7.6 to 7.8 (Starting with Oracle AVDF 20.2)

7.9 (Starting with Oracle AVDF 20.4)

8 (Starting with Oracle AVDF 20.3)

8.2, 8.3 (Starting with Oracle AVDF 20.4)

9 (Starting with Oracle AVDF 20.9)

Yes

No

No

No

No

Yes

No

Red Hat Enterprise Linux

6.7 to 6.10

7.0 to 7.5

7.6 to 7.8 (Starting with Oracle AVDF 20.2)

7.9 (Starting with Oracle AVDF 20.4)

8 (Starting with Oracle AVDF 20.3)

8.2, 8.3 (Starting with Oracle AVDF 20.4)

9 (Starting with Oracle AVDF 20.9)

Yes

No

No

No

No

Yes

No

IBM AIX Plug-in for Oracle Audit Vault and Database Firewall

on Power Systems (64-bit)

7.1 (TL5)

7.2 (TL2 and above)

7.3 (TL0) (Starting with Oracle AVDF 20.10)

Yes

No

No

No

No

Yes

No

Microsoft Windows Plug-in for Oracle Audit Vault and Database Firewall

Microsoft Windows Server 2012*, 2012 R2, 2016 on x86-64

2019 on x86-64 (Starting with Oracle AVDF 20.2)

Yes

No

No

No

No

No

No

Microsoft Active Directory Plug-in for Oracle Audit Vault and Database Firewall

2012 to 2016 on 64 bit

Yes

No

No

No

No

No

No

Oracle ACFS Plug-in for Oracle Audit Vault and Database Firewall*

12c

Yes

No

No

No

No

No

No

*

C.2.2 Oracle Database Plug-in for Oracle Audit Vault and Database Firewall

Learn about the Oracle Database plug-in for Oracle Audit Vault and Database Firewall.

Table C-2 lists features of the Oracle Database Plug-in.

Table C-2 Oracle Database Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.oracle

Target Versions

Oracle 11.2.0.4

Oracle 12c Release 1 (12.1)

Oracle 12c Release 2 (12.2)

Oracle 18c

Oracle 19c

21 (Starting Oracle AVDF 20.4)

Target Platforms

Linux/x86-64

Solaris /x86-64

Solaris /SPARC64

AIX/Power64

Windows /x86-64

HP-UX Itanium

See Platform Support Matrix in Oracle Audit Vault and Database Firewall Installation Guide for complete details on supported target platforms and versions.

Setup Script(s)

Yes. See "Oracle Database Setup Scripts" for instructions.

Target Location (Connect String)

jdbc:oracle:thin:@//hostname:port/service

Collection Attributes

None.

ORCLCOLL.NLS_LANGUAGE, ORCLCOLL.NLS_TERRITORY and ORCLCOLL.NLS_CHARSET: These will be deprecated in the future.

ORCLCOLL.NLS_CHARSET attribute is replaced by AV.COLLECTOR.DATABASECHARSET.

See Table C-24 for details.

AV.COLLECTOR.TIMEZONEOFFSET

Note: This attribute must be set to timezone offset of Oracle Database. It is mandatory if Transaction Log audit trail is going to be configured for the target.

AVDF Audit Trail Types

TABLE

DIRECTORY

TRANSACTION LOG

SYSLOG (Linux only)

EVENT LOG (Windows only)

NETWORK

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

For TABLE audit trails: SYS.AUD$, SYS.FGA_LOG$, DVSYS.AUDIT_TRAIL$, UNIFIED_AUDIT_TRAIL, CDB_UNIFIED_AUDIT_TRAIL.

For DIRECTORY audit trails: Full path to directory containing AUD or XML files.

For SYSLOG audit trails: Use DEFAULT or the full path to directory containing the syslog file.

For EVENT LOG and NETWORK audit trails: no trail location required.

For TRANSACTION LOG: Full path to directory containing Golden Gate Integrated Extract file.

Note:

Oracle Audit Vault and Database Firewall queries and collects records from Unified Audit trail which fetches unified audit records from operating system spillover audit files. The Database Audit Management manages the clean up of Unified Audit trail and the underlying operating system spillover audit files.

Audit Trail Cleanup Support

Yes. See Oracle Database Audit Trail Cleanup for instructions.

OS user running the Agent

For Oracle Database Directory Audit Trail: Any user who has read permission on audit files, i.e oracle user, or user in DBA group.

For Table Trail: Any database user (preferably not a DBA). See Oracle Database Setup Scripts for instructions.

For Transaction Log trail : Any user who has read permission on Golden Gate Integrated Extract XML files.

For any other directory audit trail: Any user who has read permission on audit files.

Supported Character Sets for DIRECTORY and SYSLOG audit trails

The DIRECTORY and SYSLOG audit trails use Java character set to open audit files based on the database character sets. This ensures the audit files are processed using the right character sets and to avoid data loss.

The database character set is read from the following sources in the same order:

  1. Target attribute AV.COLLECTOR.DATABASECHARSET
  2. Target attribute ORCLCOLL.NLS_CHARSET (deprecated)
  3. The target Oracle database

Note: An exception to the above process is XML audit files with Java character set specified in XML declaration. Refer to the known issues for a list of character sets that are not supported.

Cluster support (Oracle Real Application Clusters)

Yes

When configuring a Oracle RAC as a target for audit collection, enter the port number of the SCAN Listener.

Oracle Active Data Guard

Additional Information for Audit Collection from Oracle Active Data Guard

C.2.3 MySQL Plug-in for Oracle Audit Vault and Database Firewall

Learn how to use the MySQL plug-in for Oracle Audit Vault and Database Firewall.

Table C-3 lists the features of the MySQL plug-in.

Table C-3 MySQL Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.mysql

Target Versions

Enterprise Edition 5.6, 5.7, 8.0

Target Platforms

Linux (x86-64): OL 5.x, 6.x, 7.x and RHEL 6.x, 7.x

Microsoft Windows (x86-64): 8

Microsoft Windows Server (x86-64): 2012, 2012R2, 2016

Target Location (Connect String)

jdbc:av:mysql://hostname:port/mysql

Collection Attribute(s)

av.collector.securedTargetVersion - (Required) Specifies the MySQL version. Default is 8.0.

av.collector.AtcTimeInterval - (Optional) Specifies the audit trail cleanup file update time interval in minutes. Default is 20.

AVDF Audit Trail Types

DIRECTORY

NETWORK

See Table C-22 for descriptions of audit trail types.

Audit Trail Cleanup Support

Yes.

Audit Trail Location

The path to the directory where the converted files are created.

The default audit format for MySQL 5.5 and 5.6 is old. The default audit format for MySQL 5.7 is new. The audit format can be changed by modifying the configuration on MySQL Server.

The Audit Trail Location is as follows:

  1. For old audit format, the path to the directory is where the converted XML files are created when you run the MySQL XML transformation utility.

  2. For new audit format, the path to the directory is where the audit.log files are generated by MySQL Server.

Table C-4 Old Audit Format

Audit Trail Location Value

Input path format before MySQL 5.7.21

<Path of the converted XML location.>

For example: \ConvertedXML

Input path format of MySQL 5.7.21 onwards

<Path of the converted XML location.>

For example: \ConvertedXML

Table C-5 New Audit Format

Audit Trail Location Value

Input path format before MySQL 5.7.21

<Path of the audit.log location.>

For example: \MySQLLog

Input path format for MySQL 5.7.21 onwards

<Path of the audit log file>/<log file name>.*.log

Where * is the time stamp in YYYYMMDDThhmmss format.

For example: MySQLLog/audit*.log

Note:

  • In the old format audit data is collected from converted XML files. In the new format audit data is collected from both active log and rotated logs.
  • Audit collection from MySQL Community Edition is not supported by this plug-in of Oracle AVDF.

Best Practice:

Enable automatic size-based audit log file rotation, by setting audit_log_rotate_on_size property. See Audit Log File Space Management and Name Rotation in MySQL Reference Manual for further details.

C.2.4 Microsoft SQL Server Plug-in for Oracle Audit Vault and Database Firewall

The following table lists the features of the Microsoft SQL Server plug-in for Oracle Audit Vault and Database Firewall (Oracle AVDF).

Microsoft SQL Server 2012 was deprecated in Oracle AVDF 20.12, and it will be desupported in one of the future releases.

Table C-6 Microsoft SQL Server Plug-in

Plug-in Specification Description
Plug-in directory AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql
Target versions

Enterprise Edition 2012, 2014, 2016, 2017, 2019 (Starting with Oracle AVDF 20.3), 2022 (Starting with Oracle ADVF 20.10)

Standard Edition 2019 (starting with Oracle AVDF 20.6), 2022 (Starting with Oracle ADVF 20.10)

Starting with Oracle AVDF 20.10, agentless and remote collection are supported as follows:

  • .sqlaudt audit events are supported for all supported Microsoft SQL Server versions.
  • .xel audit events are supported for Microsoft SQL Server 2017 and later.

Target platforms

Windows/x86-64

See Platform Support Matrix in Oracle Audit Vault and Database Firewall Installation Guide for complete details on supported target platforms and versions.

Setup scripts

Yes. See Microsoft SQL Server Setup Scripts for instructions.

Note:

After upgrading to Oracle AVDF 20.3 or later, rerun the server setup script for all targets to continue with audit collection.
Target location (Connect string for SQL server authentication) jdbc:av:sqlserver://hostname:port
Target location (Connect string for Windows authentication)

jdbc:av:sqlserver://hostname:port;authenticationMethod=ntlmjava

Use Windows user credentials along with the domain. For example:

domain\username and password

Collection attributes None
AVDF audit trail types

DIRECTORY

TRANSACTION LOG (starting with Oracle AVDF 20.9)

EVENT LOG

NETWORK

See Table C-22 for descriptions of the audit trail types.

Audit trail location for DIRECTORY audit trails
  • *.sqlaudit
  • *.trc (trace)
  • *.xel (extended events)
  • #C2_DYNAMIC
  • #TRACE_DYNAMIC

Examples:

  • directory_path\*.sqlaudit
  • directory_path\prefix*.sqlaudit
  • directory_path\prefix*.trc
  • directory_path\*.xel
  • directory_path\prefix*.xel
  • #C2_DYNAMIC
  • #TRACE_DYNAMIC

Note:

  • For prefix, you can use any prefix for the .trc, *.xel, or *.sqlaudit files.
  • Support for extended events (*.xel files) is included for DIRECTORY audit trails starting with Oracle AVDF 20.3.
Audit trail location for EVENT LOG audit trails
  • application
  • security
Audit trail location for TRANSACTION LOG audit trails (Oracle AVDF 20.9 and later) Full path to the directory containing the Oracle GoldenGate CDC Extract file
Audit trail cleanup support

Yes (not supported for agentless or remote collection)

See Microsoft SQL Server Audit Trail Cleanup for instructions.

Cluster support Yes (not supported for agentless or remote collection)
Target platform for clusters Windows 2012 R2 Enterprise Edition for audit collection
Cluster collection attribute

Attribute name: av.collector.clusterEnabled

Attribute value: 1

Support for the AlwaysOn availability group

Yes (starting with Oracle AVDF 20.3)

Note:

  • Register one target in the Audit Vault Server for every Microsoft SQL Server that is part of the AlwaysOn availability group.
  • The Oracle AVDF audit report provides a view of audit records that are generated by individual Microsoft SQL Servers in the availability group. It is not a consolidated view of audit records that are generated by all servers in the availability group.
Collection attributes (optional)

av.collector.validateConnectionOnBorrow

Setting this attribute to False eliminates unnecessary logging of records or events due to test queries in the target database. This attribute is available starting with Oracle AVDF 20.6.

C.2.5 PostgreSQL Plug-in for Oracle Audit Vault and Database Firewall

Learn about using the PostgreSQL plug-in for Oracle Audit Vault and Database Firewall.

Table C-7 specifies the values or details required for the configuration.

Prerequisite: Ensure to enable pgaudit extension. The audit collection is incomplete and operational details are missed out from the reports in case this extension is not enabled.

Table C-7 PostgreSQL

Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.postgresql

Target Versions

9.6 to 11.8

12 and 13 (Starting with Oracle AVDF 20.8)

14 and 15 (Starting with Oracle AVDF 20.10)

Target Platforms

Linux/x86-64

Setup Scripts

None

Target Location (Connect String)

None

Collection Attributes (Required)

av.collector.securedTargetVersion

Specifies the target version. Default is 11.0.

Collection Attributes (Optional)

AV.COLLECTOR.DATABASECHARSET

The NLS character set of the audit trail file. This is available starting Oracle AVDF 20.4.

The PostgreSQL DIRECTORY audit trails use Java character set to open audit files based on the database character sets. This ensures the audit files are processed using the right character sets and avoid data loss.

Audit Trail Types

DIRECTORY

Audit Trail Location

The path to the directory containing CSV audit files.

Audit Trail Cleanup Support

No

C.2.6 IBM DB2 Plug-in for Oracle Audit Vault and Database Firewall

Learn about how to use the IBM DB2 plug-in for Oracle Audit Vault and Database Firewall.

Table C-8 lists the features of the IBM DB2 plug-in.

Table C-8 IBM DB2 Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.db2

Target Versions

10.5, 11.1, 11.5

Target Platforms

Linux (x86-64): OL 5.x, 6.x, 7.x and RHEL 6.x, 7.x

Microsoft Windows (x86-64): 8

Microsoft Windows Server (x86-64): 2012, 2012R2, 2016

IBM AIX on Power Systems (64-bit): 7.1

Setup Script(s)

Yes. See "IBM DB2 for LUW Setup Scripts" for instructions.

Target Location (Connect String)

jdbc:av:db2://hostname:port/dbname

Note:

  • Connect string is not required for Oracle AVDF release 20.
  • Connect string is not required for IBM DB2 cluster.

Collection Attribute(s)

av.collector.databasename (case sensitive) - (Required) Specifies the IBM DB2 for LUW database name.

AVDF Audit Trail Types

DIRECTORY

NETWORK

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

Path to a directory, for example: d:\temp\trace

Audit Trail Cleanup Support

Yes

Cluster Support

Yes

HADR (High Availability and Disaster Recovery)

Target Platform for Cluster

HADR on OL 7.x

DB2 Multiple Instances Support

Yes

Multiple Instances Environment

In case of multiple instances environment, create an Audit Vault Agent user and then the Agent group. Install the Agent as the newly created Agent user belonging to the Agent group. Add all the users of the instance to the Agent group and then add the Agent user to the instance group. This functionality is supported from Oracle AVDF 20.2 (RU2) and later.

Perform the following steps from every instance to extract the audit files:

  1. Navigate to the extraction utility location using $AGENT_HOME/av/plugins/com.oracle.av.plugin.db2/bin.
  2. Set the environment variables agent_home, db2audit_command, and lslk_cmd.
  3. Run the extraction utility using ./DB295ExtractionUtil -archivepath <archive path> -extractionpath <extraction path> -audittrailcleanup <yes/no>.
  4. The extracted files are generated in the directory at the instance level.
  5. Start the audit trail for every instance as the extraction path is different for each instance.

C.2.7 SAP Sybase ASE Plug-in for Oracle Audit Vault and Database Firewall

Learn how to use the SAP Sybase ASE plug-in for Oracle Audit Vault and Database Firewall.

Table C-9 lists the features of the SAP Sybase ASE plug-in.

Table C-9 SAP Sybase ASE Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.sybase

Target Versions

15.7

16.0

Target Platforms

All platforms

Setup Script(s)

Yes. See "Sybase ASE Setup Scripts for Oracle Audit Vault and Database Firewall" for instructions.

Target Location (Connect String)

jdbc:av:sybase://hostname:port

Collection Attribute(s)

None

AVDF Audit Trail Types

TABLE

NETWORK

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

SYSAUDITS

Audit Trail Cleanup Support

No

Cluster support

No

SAP Sybase Password Encryption

In case you are using password encryption on SAP Sybase database, incorporate the following changes on Oracle Audit Vault and Database Firewall:

  1. Use the following connection string in Audit Vault Server console while setting up the audit trail for SAP Sybase database:

    jdbc:sybase:Tds:<host>:<port>/sybsecurity?ENCRYPT_PASSWORD=TRUE&JCE_PROVIDER_CLASS=com.sun.crypto.provider.SunJCE

  2. Copy the jconn4.jar file from /opt/sybase/jConnect-16_0/classes in Sybase server to Agent_Home/av/jlib.

    Note:

    If you are using Sybase 15.7, then fetch the jconn4.jar file from the latest SAP Sybase server version 16.0.

  3. Restart the Audit Vault Agent.

  4. Start the collection.

C.2.8 Quick JSON Target Type for Oracle Audit Vault and Database Firewall

Learn how to configure and use the Quick JSON target type for Oracle Audit Vault and Database Firewall.

Quick JSON target type can be used to collect audit data from targets that store audit records in JSON format, by mapping few collection attributes.

Table C-10 specifies the values or details required for the configuration.

Table C-10 Quick JSON

Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.quickjson

Target Platforms

Linux/x86-64

Windows /x86-64

Setup Scripts

None

Target Location (Connect String)

None

Collection Attributes (Required)

av.collector.securedTargetVersion

Specifies the target version.

Collection Attributes (Optional)

AV.COLLECTOR.DATABASECHARSET

The NLS character set of the audit trail file. This is available starting Oracle AVDF 20.4.

The Quick JSON DIRECTORY audit trails use Java character set to open audit files based on the database character sets. This ensures the audit files are processed using the right character sets and avoid data loss.

Audit Trail Types

DIRECTORY

Audit Trail Location

The path to the directory containing JSON audit files.

Audit Trail Cleanup Support

No

QuickJSON collector relies on collection attributes to map JSON audit data to Oracle AVDF audit record fields. These collection attributes point to data within JSON audit file using JSON Path expressions. Following table lists the QuickJSON collection attributes.

Table C-11 Quick JSON Collection Attributes

Quick JSON Collection Attribute Name Description Attribute Value Type

av.collector.qck.starttag

Should be set to the first key of JSON audit record. This is not JSON Path expression. It is the name of the required key.

Static String

av.collector.qck.eventtime

Time when the event occurred.

JSON Path Expression

av.collector.qck.username

The user of the target who executed the event.

JSON Path Expression

av.collector.qck.os.username

Operating system login name of the target user who executed the event.

JSON Path Expression

av.collector.qck.eventname

Name of the event as recognized by the target.

JSON Path Expression

av.collector.qck.commandclass

Class of command issued by the target user who executed the event.

JSON Path Expression

av.collector.qck.client.ip

IP address of the client host.

JSON Path Expression

av.collector.qck.targetobject

Object affected by the event.

JSON Path Expression

av.collector.qck.targettype

Type of the target object. For example: Package, Type, or Table.

JSON Path Expression

av.collector.qck.eventstatus

Completion status of the event.

JSON Path Expression

av.collector.qck.errorid

Error number in case of event failure.

JSON Path Expression

av.collector.qck.errormessage

Error message in case of event failure.

JSON Path Expression

av.collector.qck.target.entity

Name of target entity.

JSON Path Expression

av.collector.qck.target.user

Name of target user.

JSON Path Expression

av.collector.qck.target.role

Name of target role.

JSON Path Expression

Note:

The attributes av.collector.qck.target.entity, av.collector.qck.target.user, and av.collector.qck.target.role are only applicable if Quick JSON target is used to collect audit data from Mongo DB.

See Also:

C.2.9 QuickCSV Collector for Oracle Audit Vault and Database Firewall

Learn how to configure and use the QuickCSV target type for Oracle Audit Vault and Database Firewall.

A QuickCSV target type can be used to collect audit data from most targets that store audit records in CSV format through a one-to-one mapping of collection attributes to fields in the log file. If a database stores some data across multiple fields, it cannot be captured in a single field.

The Specifications for QuickCSV Collector table below specifies the values or details required for the configuration.

Table C-12 Specifications for QuickCSV Collector

Specification Description
Plug-in directory <agent_home>/av/plugins/com.oracle.av.plugin.quickcsv
Target platforms All supported OS
Setup scripts None
Target Location (Connect String) None
Collection attributes (See table below)
Audit trail types Directory
Audit trail location Path to directory containing the .csv files
Audit trail cleanup support No

The below table describes the attributes which are mapped to fields within the .csv audit file. Against the attribute, the value to be entered is the field number. The field numbers can be entered as 1, 2, 3, etc. or as $1, $2, $3, etc.

Table C-13 Attributes for QuickCSV Collector

Attribute Description
av.collector.map.client.hostname Target hostname
av.collector.map.client.id Target ID
av.collector.map.client.ip Target IP address
av.collector.map.client.program.name Program running on target which executed the event
av.collector.map.command.class Class of command issued by the target user who executed the event
av.collector.map.command.param Parameters given to command while executing the event
av.collector.map.command.text Command statement for the event
av.collector.map.database.name Name of the target database
av.collector.map.error.id Error number in case of event failure
av.collector.map.error.message Error message in case of event failure
av.collector.map.event.name (Required) Name of the event as recognized by the target
av.collector.map.event.status Completion status of the event
av.collector.map.event.time (Required) Time when the event occurred
av.collector.map.instance.name Name of database instance
av.collector.map.os.username Operating system login name of the target user who executed the event
av.collector.map.repository.name Name of the database repository
av.collector.map.target.object Object affected by the event
av.collector.map.target.owner Name of the user who owns the target
av.collector.map.target.type Type of target object
av.collector.map.username The user of the target who executed the event

Other than the predefined attributes mentioned above, you can also add more attributes to specify fields to be collected from. These attributes must begin with the prefix av.collector.map.extension. The data collected from the field specified against the user-defined attribute will be added to the extension field along with the name given to the attribute.

The below table describes the attributes that specify the format of the .csv file.

Table C-14 Format Attributes for QuickCSV Collector

Format Attribute Description Default value
av.collector.format.delimiter Specifies the delimiter string used in the .csv file. For example, comma (,) semicolon (;) etc. , (comma)
av.collector.format.escape Specifies the escape character within a quoted field NA
av.collector.format.quote Specifies the character used to put a field between quotes " (double-quote)
av.collector.pattern.timestamp Specifies the timestamp format string yyyy-MM-dd HH:mm:ss.SSS z
av.collector.timezoneoffset Specifies the timezone offset for the timestamp NA

C.2.10 SAP Sybase SQL Anywhere Plug-in for Oracle Audit Vault and Database Firewall

Learn about using the SAP Sybase SQL Anywhere plug-in for Oracle Audit Vault and Database Firewall.

Note:

SAP Sybase SQL Anywhere was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.

Table C-15 lists the features of the SAP Sybase SQL Anywhere plug-in.

Table C-15 SAP Sybase SQL Anywhere Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.sqlanywhere

Target Versions

10.0.1

Target Platforms

All platforms

Setup Script(s)

Yes. See "Sybase SQL Anywhere Setup Scripts" for instructions.

Target Location (Connect String)

jdbc:av:sybase://hostname:port

Collection Attributes

None

AVDF Audit Trail Types

NETWORK (used for host monitoring only)

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

Not required

Audit Trail Cleanup Support

No

C.2.11 Oracle Solaris Plug-in for Oracle Audit Vault and Database Firewall

Learn to use the Oracle Solaris plug-in for Oracle Audit Vault and Database Firewall.

Table C-16 lists the features of the Oracle Solaris plug-in.

Table C-16 Oracle Solaris Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.solaris

Target Versions

Versions 11.3 and 11.4 on SPARC64 and x86-64 platforms

Target Platforms

Solaris/x86-64

Solaris/SPARC64

Solaris - x86-64 was deprecated in Oracle AVDF 20.9, and it will be desupported in one of the future releases.

Setup Script(s)

No

Target Location (Connect String)

hostname (fully qualified machine name or IP address)

Collection Attribute(s)

None

AVDF Audit Trail Types

DIRECTORY

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

hostname:path_to_trail

The hostname matches the hostname in the audit log names, which look like this:

timestamp1.timestamp2.hostname

Audit Trail Cleanup Support

No

C.2.12 Linux Plug-in for Oracle Audit Vault and Database Firewall

Learn how to benefit from using the Linux plug-in for Oracle Audit Vault and Database Firewall.

Table C-17 lists the features of the Linux plug-in that collects audit data from Oracle Linux (OL) and Red Hat Enterprise Linux (RHEL).

Table C-17 Linux Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.linux

Target Versions

Oracle Linux (OL):

Oracle Linux 6 was deprecated in Oracle AVDF 20.10, and it will be desupported in one of the future releases.

  • OL 6.0 (with auditd package 2.0)
  • OL 6.1 - 6.5 (with auditd package 2.2.2)
  • OL 6.6 - 6.7 (with auditd package 2.3.7)
  • OL 6.8 - 6.9 (with auditd package 2.4.5)
  • OL 7.0 (with auditd package 2.3.3)
  • OL 7.1 - 7.2 (with auditd package 2.4.1)
  • OL 7.3 (with auditd package 2.6.5)
  • OL 7.4 - 7.5 (with auditd package 2.7.6)
  • OL 7.6 (with auditd 2.8) (Oracle AVDF 20.2 and later)
  • OL 7.7 (with auditd 2.8.5) (Oracle AVDF 20.2 and later)
  • OL 7.8 (with auditd 2.8) (Oracle AVDF 20.2 and later)
  • OL 7.9 (with auditd 2.8) (Oracle AVDF 20.4 and later)
  • OL 8 (with auditd 3.0) (Oracle AVDF 20.3 and later)
  • OL 8.2 and 8.3 (with auditd 3.0) (Oracle AVDF 20.4 and later)
  • OL 9 (Oracle AVDF 20.9 and later)

Red Hat Enterprise Linux (RHEL):

  • RHEL 6.7 (with auditd 2.3.7)
  • RHEL 6.8 (with auditd 2.4.5)
  • RHEL 6.9 (with auditd 2.4.5)
  • RHEL 6.10 (with auditd 2.4.5)
  • RHEL 7.0 (with auditd 2.3.3)
  • RHEL 7.1 (with auditd 2.4.1)
  • RHEL 7.2 (with auditd 2.4.1)
  • RHEL 7.3 (with auditd 2.6.5)
  • RHEL 7.4 (with auditd 2.7.6)
  • RHEL 7.5 (with auditd 2.7.6)
  • RHEL 7.6 (with auditd 2.8) (Oracle AVDF 20.2 and later)
  • RHEL 7.7 (with auditd 2.8.5) (Oracle AVDF 20.2 and later)
  • RHEL 7.8 (with auditd 2.8) (Oracle AVDF 20.2 and later)
  • RHEL 7.9 (with auditd 2.8) (Oracle AVDF 20.4 and later)
  • RHEL 8 (with auditd 3.0) (Oracle AVDF 20.3 and later)
  • RHEL 8.2 and 8.3 (with auditd 3.0) (Oracle AVDF 20.4 and later)
  • RHEL 9 (Oracle AVDF 20.9 and later)

Run rpm -q audit to get the audit package version.

Target Platforms

Linux/x86-64

Setup Script(s)

No. However, the following user/group access rights are needed to start a Linux audit trail:

If the agent process is started with root user, no changes to access rights are needed.

If the agent process is started with a user other than root:

  1. Assign the group name of the Agent user (the one who will start the Agent process) to the log_group parameter in the /etc/audit/auditd.conf file.

  2. The Agent user and group must have read and execute permissions on the folder that contains the audit.log file (default folder is /var/log/audit).

  3. Restart the Linux audit service after you make the above changes.

Target Location (Connect String)

hostname (fully qualified machine name or IP address)

Collection Attribute(s)

None

AVDF Audit Trail Types

DIRECTORY

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

Default location of audit.log (/var/log/audit/audit*.log) or any custom location configured in the /etc/audit/auditd.conf file

Audit Trail Cleanup Support

No

C.2.13 IBM AIX Plug-in for Oracle Audit Vault and Database Firewall

Learn about the IBM AIX plug-in for Oracle Audit Vault and Database Firewall.

Table C-18 lists the features of the IBM AIX plug-in.

Table C-18 IBM AIX Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.aixos

Target Versions

AIX versions:

  • 7.3 (TL0) (Starting with Oracle AVDF 20.10)
  • 7.2 (TL2 and above)
  • 7.1 (TL5)

Supported JRE Version

1.8.0_241 (minimum)

Note: JRE version 11 is not supported on AIX platform.

Target Platforms

Power Systems (64-bit)

Setup Script(s)

No. However, the following user and group access rights are needed to start an AIX audit trail:

If the Agent process is started by the root user, then no changes to access rights are needed.

If the Agent process is started with a user other than root, then run the following commands in the AIX system as root to authorize another user:

  1. Create a new role and grant it the aix.security.audit authorization:

    mkrole authorizations= (aix.security.audit) (role_name)

  2. Alter the Agent user to assign the newly created role:

    chuser roles=role_name agent_user_name

  3. Update the kernel table with the newly created role by running the command: setkst

  4. Add the Agent user to the same group as that of the AIX audit files.

  5. Ensure you have set read permission on the /audit directory where the audit trail files are located.

  6. To start the Agent with the Agent user, log in to the AIX terminal with agent_user_name and switch to the role created in this procedure:

    swrole role_name

Target Location (Connect String)

hostname (fully qualified machine name or IP address)

Collection Attribute(s)

None

AVDF Audit Trail Types

DIRECTORY

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

Default location of trail (/audit/trail) or any custom location configured in the /etc/security/audit/config file

Audit Trail Cleanup Support

Yes. The AIX plug-in will create a .atc file at:

AGENT_HOME/av/atc/SecuredTargetName_TrailId.atc

The .atc file contains the following information:

trail_location end_time_of_audit_event_collection

C.2.14 Microsoft Windows Plug-in for Oracle Audit Vault and Database Firewall

Learn about the Microsoft Windows plug-in for Oracle Audit Vault and Database Firewall.

Table C-19 lists the features of the Microsoft Windows plug-in.

Table C-19 Microsoft Windows Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME\av\plugins\com.oracle.av.plugin.winos

Target Versions

Microsoft Windows Server 2012, 2012 R2, 2016

2019 (Starting with Oracle AVDF 20.2)

Target Platforms

Windows/x86-64

Setup Script(s)

No

Target Location (Connect String)

hostname (fully qualified machine name or IP address)

Collection Attribute(s)

None

AVDF Audit Trail Types

EVENT LOG

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

security (case-sensitive)

Audit Trail Cleanup Support

No

C.2.15 Microsoft Active Directory Plug-in for Oracle Audit Vault and Database Firewall

Learn about how to use the Microsoft Active Directory plug-in for Oracle Audit Vault and Database Firewall.

Table C-20 lists the features of the Microsoft Active Directory plug-in.

Table C-20 Microsoft Active Directory Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME\av\plugins\com.oracle.av.plugin.msad

Target Versions

2012 to 2016 on 64 bit

Target Platforms

Windows/x86-64

Setup Script(s)

No

Target Location (Connect String)

hostname (fully qualified machine name or IP address)

Collection Attribute(s)

None

AVDF Audit Trail Types

EVENT LOG

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

directory service or security (case-sensitive)

Audit Trail Cleanup Support

No

C.2.16 Oracle ACFS Plug-in for Oracle Audit Vault and Database Firewall

Use the Oracle ACFS plug-in for Oracle Audit Vault and Database Firewall to implement Oracle ACFS in Oracle AVDF.

Note:

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) or Oracle Advanced Cluster File System was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.

Table C-21 lists the features of the Oracle ACFS plug-in.

Table C-21 Oracle ACFS Plug-in

Plug-in Specification Description

Plug-in directory

AGENT_HOME/av/plugins/com.oracle.av.plugin.acfs

Target Versions

12c Release 1 (12.1)

Target Platforms

Linux/x86-64

Solaris/x86-64

Solaris/SPARC64

Windows 2008, 2008 R2 64-bit

Setup Script(s)

No

Target Location (Connect String)

hostname (fully qualified machine name or IP address)

Collection Attribute(s)

av.collector.securedtargetversion - (Required) Specify the Oracle ACFS version.

AVDF Audit Trail Types

DIRECTORY

See Table C-22 for descriptions of audit trail types.

Audit Trail Location

The path to the directory containing XML audit files. For example, for a file system mounted at $MOUNT_POINT, the audit trail location is:

$MOUNT_POINT/.Security/audit/

Audit Trail Cleanup Support

No

C.2.17 Summary of Data Collected for Each Audit Trail Type

Explore the types of data that Oracle Audit Vault and Database Firewall (Oracle AVDF) collects for each audit trail type.

When you configure an audit trail for a target, you select the type of audit trail in the Audit Trail Type field. The audit trail type depends on your target type. Table C-22 describes the types of audit trails that you can configure for each target type.

Refer to the product documentation for your target type for details on its auditing features and functionality. See the following documentation for Oracle products:

Table C-22 Summary of Audit Trail Types Supported for Each Target Type

Target Type Trail Type Description
Oracle Database

TABLE

Releases supported: 11.2.0.4; 12.1; 12.2; 18c; 19c.

Release 21 (Starting Oracle AVDF 20.4)

Collects from the following audit trails:

  • Oracle Database audit trail, where standard audit events are written to the SYS.AUD$ dictionary table
  • Oracle Database fine-grained audit trail, where audit events are written to the SYS.FGA_LOG$ dictionary table
  • Oracle Database Vault audit trail, where audit events are written to the DVSYS.AUDIT_TRAIL$ dictionary table
  • Oracle database 12.x unified audit trail, where audit events are written to the UNIFIED_AUDIT_TRAIL data dictionary view

Note:

The SYS.AUD$ and SYS.FGA_LOG$ tables have an additional column, RLS$INFO. The unified audit trail table has a RLS_INFO column. This column describes row-level security policies that are configured. This is mapped to the extension field in Oracle AVDF. To populate this column, set the AUDIT_TRAIL parameter of the target to DB EXTENDED.

Oracle Database

DIRECTORY

Releases 11.2.0.4, 12c, 18c; 19c.

Collects data from the following audit trails:

  • On Linux and UNIX platforms: Oracle database audit files that are written to the operating system (AUD and XML) files
  • On Windows platforms: Operating system XML files

Note:

Oracle recommends that you use unified audit table trails because directory trails are deprecated.

Oracle Database

TRANSACTION LOG

11.2.0.4 onwards for TRANSACTION LOG collection

Collects audit data from GoldenGate Integrated Extract files. If you plan to use this audit trail type, you can define the GoldenGate Integrated Extract rules to audit the tables from which GoldenGate Integrated Extract will capture audit information. The GoldenGate Integrated Extract files, in turn, are read by transaction log audit trail.

For versions before 12.2, Oracle GoldenGate Downstream Mining must be configured.

See Oracle Audit Vault and Database Firewall Auditor's Guide for more information.

Oracle Database SYSLOG

Collects Oracle audit records from either syslog or rsyslog audit files on Linux and Unix platforms only.

If the system has both syslog and rsyslog installed, the exact rsyslog audit file location must be specified to collect data from rsyslog files.

The following rsyslog formats are supported:

  • RSYSLOG_TraditionalFileFormat (has low-precision time stamps)
  • RSYSLOG_FileFormat (has high-precision time stamps and time zone information)

Events from both formats appear the same on reports. However, with RSYSLOG_FileFormat, the AVSYS.EVENT_LOG table shows EVENT_TIME with microsecond precision.

See Oracle Audit Vault and Database Firewall Auditor's Guide for details on this table and Audit Vault Server schema documentation.

Oracle Database EVENT LOG Collects Oracle audit records from Microsoft Windows event logs on Windows platforms only.
Oracle Database NETWORK Collects network traffic (all database operations that use a TCP connection). Used for the Host Monitor Agent.
Microsoft SQL Server DIRECTORY

Collects audit data from the following:

  • sqlaudit
  • trace
  • extended events
  • C2_DYNAMIC
  • TRACE_DYNAMIC
Microsoft SQL Server TRANSACTION LOG

In Oracle AVDF 20.9 and later, collects audit data from Oracle GoldenGate CDC Extract files. If you plan to use this audit trail type, you can define the GoldenGate CDC Extract rules to audit the tables from which GoldenGate CDC Extract will capture audit information. The GoldenGate CDC Extract files, in turn, are read by transaction log audit trail.

Microsoft SQL Server EVENT LOG Collects audit data from Windows application and security event logs.
Microsoft SQL Server NETWORK Collects network traffic (all database operations that use a TCP connection). Used for the Host Monitor Agent.

Sybase ASE

TABLE

Collects audit data from system audit tables (sysaudits_01 through sysaudits_08) in the sybsecurity database

Sybase ASE

NETWORK

Collects network traffic (all database operations using a TCP connection). Used for Host Monitor Agent.

Sybase SQL Anywhere

NETWORK

(For host monitoring only) Collects network traffic (all database operations using a TCP connection).

Note: Sybase SQL Anywhere was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.

IBM DB2 for LUW

DIRECTORY

Collects audit data from ASCII text files extracted from the binary audit log (db2audit.log). These files are located in the security subdirectory of the DB2 database instance.

IBM DB2 for LUW

NETWORK

Collects network traffic (all database operations using a TCP connection). Used for Host Monitor Agent.

MySQL

DIRECTORY

Collects XML-based audit data from a specified location

MySQL

NETWORK

Collects network traffic (all database operations using a TCP connection). Used for Host Monitor Agent.

Oracle Solaris

DIRECTORY

Collects Solaris Audit records (version 2) generated by the audit_binfile plug-in of Solaris Audit

Linux

DIRECTORY

Collects audit data from audit.log

Windows OS

EVENT LOG

Collects audit data from Windows Security Event Log

Microsoft Active Directory

EVENT LOG

Collects audit data from Windows Directory Service, and Security Event Logs

Oracle ACFS

DIRECTORY

Collects audit data from ACFS encryption and ACFS security sources.

Note: Oracle Automatic Storage Management Cluster File System (Oracle ACFS) or Oracle Advanced Cluster File System was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.

Oracle Linux

DIRECTORY

Collects audit data from audit.log

IBM AIX DIRECTORY Collects audit data from the binary audit log (/audit/trail). Only BIN auditing mode is supported. Any custom location of the audit log is configured in the /etc/security/audit/config file.

C.3 Scripts for Oracle AVDF Account Privileges on Targets

Oracle Audit Vault and Database Firewall provides scripts for Oracle Database, Sybase, Microsoft, IBM DB2 for LUW, and MySQL plug-ins.

C.3.1 About Scripts for Setting up Oracle Audit Vault and Database Firewall Account Privileges

You can use scripts to set up accounts and privileges for Oracle Audit Vault and Database Firewall.

You must set up a user account with the correct privileges on each target for Oracle Audit Vault and Database Firewall to use to perform functions that are related to monitoring and collecting audit data. Oracle Audit Vault and Database Firewall provides setup scripts for this purpose so that you can configure your database targets. Depending on the type of target, the scripts set up user privileges that enable Oracle Audit Vault and Database Firewall to do the following functions:

  • Audit data collection

  • Audit policy management

  • Stored procedure auditing

  • User entitlement auditing

  • Native Network Encrypted Traffic monitoring

  • Audit trail cleanup (for some targets)

  • Sensitive Data Discovery (for Oracle Database targets only)

When you deploy the Audit Vault Agent on a host computer (usually the same computer as the target), the setup scripts for creating the user permissions for Oracle Audit Vault and Database Firewall are in the following directory (Linux example below):

$AGENT_HOME/av/plugins/com.oracle.av.plugin.secured_target_type/config/

C.3.2 Oracle Database Setup Scripts

Download and use these scripts to set up user account privileges for Oracle Audit Vault and Database Firewall (Oracle AVDF) to audit Oracle Database targets.

Use these scripts to set up or revoke user privileges on Oracle Database so that Oracle AVDF can perform the following functions:

  • Audit data collection
  • Audit policy management
  • Stored procedure auditing (SPA)
  • User entitlement auditing
  • Sensitive Data Discovery

Downloading Oracle Database Setup Scripts

To download the scripts from the Audit Vault Server console:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab.
  3. Click the Target Setup Script button.

Download and run the target setup scripts for auditing Oracle Database targets. The scripts aren't required for Database Firewall monitoring.

You can also access the scripts in the following directory (Linux example):

/opt/avdf/defaultagent/av/plugins/com.oracle.av.plugin.oracle/config/

Setting Up and Revoking User Privileges on Oracle Database Targets

To set up or revoke Oracle Audit Vault and Database Firewall user privileges on an Oracle Database target:

  1. Create a user account for Oracle Audit Vault and Database Firewall on the Oracle Database. For example:

    SQL> CREATE USER username IDENTIFIED BY password

    You will use this user name and password when registering this Oracle Database as a target in the Audit Vault Server.

  2. Connect as the SYS user with the SYSDBA privilege. For example:

    SQL> CONNECT SYS / AS SYSDBA
  3. To set up Oracle Audit Vault and Database Firewall user privileges, run the following setup script and then enter the user name and mode at the prompts:

    SQL> @oracle_user_setup.sql

    Alternatively, you can enter the script, user name, and mode on one line:

    SQL> @oracle_user_setup.sql username mode
    • username: Enter the name of the user you created in Step 1.

    • mode: Enter one of the following:

      • SETUP: To set up privileges for managing the Oracle Database audit policy from Oracle Audit Vault and Database Firewall, and for collecting data from any audit trail type. For example, use this mode for a TABLE audit trail in Oracle Audit Vault and Database Firewall.

      • SPA: To enable stored procedure auditing for this database

      • ENTITLEMENT: To enable user entitlement auditing for this database

      • DBSAT_DISCOVERY: To enable sensitive data discovery for this database

    Note:

    • For audit collection from CDB, create a user in the CDB and run the oracle_user_setup.sql script for this user.
    • For audit collection from individual PDB, first alter the session to switch to the PDB, create the user on the PDB and then run the oracle_user_setup.sql script for this user.
  4. If Database Vault is installed and enabled on the Oracle database, log in as a user who has been granted the DV_OWNER role do the following:

    Grant the Oracle Audit Vault and Database Firewall user the DV_SECANALYST role on this Oracle Database. For example:

    SQL> GRANT DV_SECANALYST TO username;
    

    For username, enter the user name you created in Step 1.

    The DV_SECANALYST role enables Oracle Audit Vault and Database Firewall to monitor and collect audit trail data for Oracle Database Vault, and run Oracle Database Vault reports.

  5. To revoke Oracle Audit Vault and Database Firewall user privileges, follow these steps:

    1. Connect to the database as the SYS user with the SYSDBA privilege.

    2. Run the following script and then enter the user name and mode at the prompts:

      SQL> @oracle_drop_db_permissions.sql

      Alternatively, you can enter the script, user name, and mode on one line:

      SQL> @oracle_drop_db_permissions.sql username mode
      • username: Enter the name of the user you created in Step 1.

      • mode: Enter one of the following:

        • SETUP: To revoke privileges for managing the Oracle Database audit policy from Oracle Audit Vault and Database Firewall, and for collecting data from any audit trail type.

        • SPA: To disable stored procedure auditing for this database

        • ENTITLEMENT: To disable user entitlement auditing for this database

        • DBSAT_DISCOVERY: To disable sensitive data discovery for this database

C.3.3 Sybase ASE Setup Scripts for Oracle Audit Vault and Database Firewall

The Sybase ASE setup scripts configure audit data collection privileges and auditing privileges for Sybase ASE targets.

C.3.3.1 About Sybase ASE Setup Scripts

Learn about Sybase ASE setup scripts for Oracle Audit Vault and Database Firewall.

The following scripts are provided for configuring necessary user privileges for Oracle Audit Vault and Database Firewall in a Sybase ASE target:

  • sybase_auditcoll_user_setup.sql
  • sybase_auditcoll_drop_db_permissions.sql
  • sybase_spa_user_setup.sql
  • sybase_spa_drop_db_permissions.sql

The scripts are located in the following directory (Linux example below):

$AGENT_HOME/av/plugins/com.oracle.av.plugin.sybase/config/

These scripts allow Oracle Audit Vault and Database Firewall to perform the following functions for Sybase ASE:

  • Audit data collection

  • Stored procedure auditing (SPA)

C.3.3.2 Setting Up Audit Data Collection Privileges for Sybase ASE Targets

Set up audit data collection privileges for Sybase ASE targets to enable you to analyze audit data.

To set up or revoke audit data collection privileges on a Sybase ASE target:

  1. Create a user account for Oracle Audit Vault and Database Firewall in Sybase ASE with the user name avdf_sybuser. For example:

    sp_addlogin avdf_sybuser, password

    You will use the user name av_sybuser and password when registering this Sybase ASE database as a target in the Audit Vault Server.

  2. Run the setup sybase_auditcoll_user_setup.sql script as follows:
    isql -S server_name -U sa -i sybase_auditcoll_user_setup.sql
    
    • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S server_name argument.

    • sa: Enter the system administrator user name.

  3. When prompted for a password, enter the system administrator password.
  4. To revoke the Oracle Audit Vault and Database Firewall user privileges, run the sybase_auditcoll_drop_db_permissions.sql script as follows:
    isql -S server_name -U sa -i sybase_auditcoll_drop_db_permissions.sql
    
    • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S server_name argument.

    • sa: Enter the system administrator user name.

    • When prompted for a password, enter the system administrator password.

C.3.3.3 Setting Up Stored Procedure Auditing Privileges for Sybase ASE Targets

You can configure stored procedure auditing privileges for Sybase ASE Targets.

To set up or revoke stored procedure auditing privileges on a Sybase ASE target:

  1. If you have not already done so, then create a user account for Oracle AVDF in Sybase ASE with the user name avdf_sybuser. For example:

    sp_addlogin avdf_sybuser, password

    You will use the user name av_sybuser and password when registering this Sybase ASE database as a target in the Audit Vault Server.

  2. Run the sybase_spa_user_setup.sql script as follows:
    isql -S server_name -U sa -i sybase_spa_user_setup.sql
    
    • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S server_name argument.

    • sa: Enter the system administrator user name.

  3. When prompted for a password, enter the system administrator password.
  4. To revoke the SPA user privileges, run the sybase_spa_drop_db_permissions.sql script as follows:
    isql -S server_name -U sa -i sybase_spa_drop_db_permissions.sql
    
    • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S server_name argument.

    • sa: Enter the system administrator user name.

    • When prompted for a password, enter the system administrator password.

C.3.4 Sybase SQL Anywhere Setup Scripts

Learn how to use the Sybase SQL Anywhere setup scripts.

Note:

Sybase SQL Anywhere was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.

The Oracle AVDF setup scripts for a Sybase SQL Anywhere target, sqlanywhere_spa_user_setup.sql and sqlanywhere_spa_drop_db_permissions.sql, are located in the following directory (Linux example below):

$AGENT_HOME/av/plugins/com.oracle.av.plugin.sqlanywhere/config/

These scripts are used to set up or revoke user privileges on the SQL Anywhere database for Oracle AVDF to do stored procedure auditing (SPA).

To set up or revoke stored procedure auditing for a SQL Anywhere target:

  1. Log in to the database as a user who has privileges to create users and set user permissions.
  2. Run the sqlanywhere_spa_user_setup.sql script as follows:
    isql -S server_name -U sa -i sqlanywhere_spa_user_setup.sql -v username="username" password="password"
    
    • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S server_name argument.

    • sa: Enter the system administrator user name.

    • username: Enter the name of the user you want to create for Oracle AVDF to use for SPA. Enclose this user name in double quotation marks.

    • password: Enter a password for the Oracle AVDF SPA user you are creating. Enclose the password in double quotation marks.

    After running the script, the user is created with privileges for SPA.

  3. When prompted for a password, enter the system administrator password.
  4. To revoke these privileges and remove this user from the database, run the sqlanywhere_spa_drop_db_permissions.sql as follows:
    isql -S server_name -U sa -i sqlanywhere_spa_drop_db_permissions.sql -v username="username"
    
    • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S server_name argument.

    • sa: Enter the system administrator user name.

    • username: Enter the name of the user you want to create for Oracle AVDF to use for SPA. Enclose this user name in double quotation marks.

    • When prompted for a password, enter the system administrator password.

C.3.5 Microsoft SQL Server Setup Scripts

The Microsoft SQL Server setup scripts manage audit data collection and auditing privileges for Microsoft SQL Server targets.

C.3.5.1 About the SQL Server Setup Script

Use the Microsoft SQL Server setup script to set up or revoke user privileges for Oracle AVDF.

Microsoft SQL Server 2012 was deprecated in Oracle AVDF 20.12, and it will be desupported in one of the future releases.

The Oracle AVDF setup and drop scripts for a Microsoft SQL Server target are mssql_user_setup.sql and mssql_drop_db_permissions.sql for SQL Server 2014 and later (or mssql_user_setup_pre2014.sql and mssql_drop_db_permissions_pre2014.sql for releases prior to 2014).

Starting with Oracle AVDF 20.10, to download the scripts from the Audit Vault Server console:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab.
  3. Click the Target Setup Script button.

You can also access the scripts in the following directory:

AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\config\

These scripts set up or revoke user privileges for Oracle AVDF to perform the following functions for SQL Server:

  • Audit data collection
  • Stored procedure auditing (SPA)
C.3.5.2 Setting Up Audit Data Collection Privileges for SQL Server Targets

You can set up audit data collection privileges for Microsoft SQL Server targets.

Prerequisites

Assign the following required privileges to run the commands in this topic:

Version and Usage Command
To assign the required privileges in SQL Server 2014 and later AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\config\mssql_user_setup.sql
To revoke the assigned privileges in SQL Server 2014 and later AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\config\mssql_drop_db_permissions.sql
To assign the required privileges in SQL Server versions prior to 2014 AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\config\mssql_user_setup_pre2014.sql
To revoke the assigned privileges in SQL Server versions prior to 2014 AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\config\mssql_drop_db_permissions_pre2014.sql

To set up or revoke Oracle AVDF user privileges for audit data collection:

  1. Create a user account for Oracle AVDF in SQL Server or use a Windows authenticated user. For example:
    exec sp_executesql N'create login username with password = ''password'', 
    check_policy= off'
    
    exec sp_executesql N'create user username for login username'
    

    Use this user name and password when registering this SQL Server database as a target in the Audit Vault Server.

  2. Run the mssql_user_setup.sql or mssql_user_setup_pre2014.sql script with one of the following commands:

    For SQL Server authentication (SQL Server 2014 and later):

    sqlcmd -S server_name -U sa -i mssql_user_setup.sql -v username="username" mode="AUDIT_COLL" all_databases="NA" database="NA"
    

    For Windows authentication (SQL Server 2014 and later):

    sqlcmd -S localhost -U sa -i mssql_user_setup.sql -v username="[domain_name\username]" mode="AUDIT_COLL" all_databases="NA" database="NA"
    • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you're running the script locally, then omit the -S server_name argument.

    • sa: Enter the system administrator user name.

    • username: Enter the name of the user that you created in step 1.

  3. When prompted for a password, enter the system administrator password.
  4. To revoke audit data collection privileges, run the mssql_drop_db_permissions.sql or mssql_drop_db_permissions_pre2014.sql script with one of the following commands:

    For SQL Server authentication (SQL Server 2014 and later):

    sqlcmd -S server_name -U sa -i mssql_drop_db_permissions.sql -v username="username" mode="AUDIT_COLL" all_databases="NA" database="NA"
    

    For Windows authentication (SQL Server 2014 and later):

    1. sqlcmd -S server_name -U sa -i mssql_drop_db_permissions.sql -v username="[domain_name\username]" mode="AUDIT_COLL" all_databases="NA" database="NA"
      • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you're running the script locally, then omit the -S server_name argument.

      • sa: Enter the system administrator user name.

      • username: Enter the name of the user that you created in step 1.

    2. When prompted for a password, enter the system administrator password.

C.3.5.3 Setting Up Stored Procedure Auditing Privileges for SQL Server Targets

You can set up stored procedure auditing privileges for SQL Server targets.

To set up or revoke Oracle AVDF user privileges for stored procedure auditing:

  1. If you have not already done so, create a user account for Oracle AVDF in SQL Server. For example:
    exec sp_executesql N'create login username with password = ''password'', 
    check_policy= off'
    
    exec sp_executesql N'create user username for login username'
    

    You will use this user name and password when registering this SQL Server database as a target in the Audit Vault Server.

  2. Run the mssql_user_setup.sql script as follows:
    sqlcmd -S server_name -U sa -i mssql_user_setup.sql -v username="username" mode="SPA" all_databases="Y/N" 
    database="NA/database_name"
    • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S server_name argument.

    • sa: Enter the system administrator user name.

    • username: Enter the name of the user you created in Step 1.

    • Y/N: Enter Y if all databases should be audited for stored procedures. Enter N to specify one database name in the database parameter.

    • NA/database_name: If you entered Y for all_databases, enter NA. If you entered N for all_databases, enter the database name that should be audited for stored procedures.

  3. When prompted for a password, enter the system administrator password.
  4. To revoke SPA privileges run the mssql_drop_db_permissions.sql script as follows:
    sqlcmd -S server_name -U sa -i mssql_drop_db_permissions.sql -v username="username" mode="SPA" all_databases="Y/N" 
    database="NA/database_name"
    
    • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S server_name argument.

    • sa: Enter the system administrator user name.

    • sa_password: Enter the system administrator password.

    • Y/N: Enter Y if SPA privileges for all databases should be revoked. Enter N to specify one database name in the database parameter.

    • NA/database_name: If you entered Y for all_databases, enter NA. If you entered N for all_databases, enter the database name for which SPA privileges should be revoked.

    • When prompted for a password, enter the name of the user you created in Step 1.

C.3.6 IBM DB2 for LUW Setup Scripts

The IBM DB2 for LUW setup scripts manage privileges for audit data collection and stored procedure auditing (SPA) privileges for IBM DB2 for LUW targets.

C.3.6.1 About the IBM DB2 for LUW Setup Scripts

Learn how to use the IBM DB2 for LUW setup scripts.

The Oracle Audit Vault and Database Firewall setup scripts for a DB2 target, db2_auditcoll_user_setup.sql and db2_spa_user_setup.sql, are located in the following directory (Linux example below):

$AGENT_HOME/av/plugins/com.oracle.av.plugin.db2/config/

Note:

Connect string is not required from release 12.2.0.11.0 and onwards.

These scripts are used to set up or revoke user privileges on the DB2 database for Oracle AVDF to do the following functions:

  • Audit data collection

  • Stored procedure auditing (SPA)

C.3.6.2 Setting Up Audit Data Collection Privileges for IBM DB2 for LUW

You can configure audit data collection privileges for IBM DB2 for LUW to control access to the audit data.

To set up or revoke Oracle AVDF user privileges for audit data collection:

  1. Create a new user account in DB2 to be used by Oracle AVDF for audit data collection.

    You will use this user name and password when registering this DB2 database as a target in the Audit Vault Server.

  2. In the $AGENT_HOME/av/plugins/com.oracle.av.plugin.db2/config/ directory, locate the db2_auditcoll_user_setup.sql script and open it for editing.

  3. In the script, put the user name of the account from Step 1 in the grant statement, then save the modified script.

  4. Execute the modified script as follows:

    $> db2 -tvf db2_auditcoll_user_setup.sql

  5. To revoke audit collection privileges:

    1. Modify the db2_auditcoll_drop_db_permissions.sql script as in Step 3 above.

    2. Run the script as follows:

      $> db2 -tvf db2_auditcoll_drop_db_permissions.sql

C.4 Audit Collection Consideration

Considerations for audit collection on other target types.

C.4.1 Additional Information for Audit Collection from Oracle Active Data Guard

Learn about additional information required to collect audit data from Oracle Active Data Guard.

Oracle Active Data Guard is a high availability solution which consists of one primary database and multiple standby databases. This section contains some additional information for configuring different audit trails.

Note:

Oracle AVDF release 20.6 and prior:

  • When Traditional Auditing is enabled, Oracle AVDF supports audit collection from both the primary and standby databases of Oracle Active Data Guard. For Oracle Active Data Guard target, Traditional Auditing is recommended for Oracle AVDF release 20.6 and prior.
  • When Unified Auditing is enabled for Oracle Active Dataguard, audit collection is supported only from the primary database and not from the standby database. The audit data generated in the standby database is not collected.

Oracle AVDF release 20.7 and later: When Unified Auditing is enabled, audit collection is supported from both the primary and standby databases of Oracle Active Data Guard. For Oracle Active Data Guard target, Unified Auditing is recommended for Oracle AVDF release 20.7 and later.

Traditional Auditing

Follow these steps for collecting audit data from databases in Oracle Active Data Guard with traditional auditing:

  1. Set AUDIT_TRAIL parameter to DB, EXTENDED on all target databases.
  2. Create a target in Oracle AVDF with a single connection string that contains the connection details of all the databases. This ensures that Oracle AVDF trail can read from sys.aud$ table of the current primary database even when failover or switchover occurs.
  3. For the above mentioned target configure Oracle Database table trail in Oracle AVDF to read the records from sys.aud$.
  4. Create one target in Oracle AVDF for every database in Oracle Active Data Guard with a connection string that contains connection details of only the specific database.
  5. Configure one directory trail in Oracle AVDF for every target to collect data from *.aud log file for the specific target database in Oracle Active Data Guard.

Unified Auditing (Oracle AVDF 20.6 and Earlier)

Audit data can be collected only from the primary database in Oracle Active Data Guard with unified auditing in releases Oracle AVDF 20.6 and prior. Follow these steps:

  1. Create a target in Oracle AVDF with single connection string that contains the connection details of all the databases. This ensures that Oracle AVDF trail can read from unified_audit_trail table of the primary database even when failover or switchover occurs.
  2. Create Oracle Database table trail in Oracle AVDF to read the records from unified_audit_trail of the primary database.

Unified Auditing (Oracle AVDF 20.7 and Later)

Audit data can be collected from both the primary and standby databases in Oracle Active Data Guard with unified auditing. This is applicable starting with Oracle AVDF release 20.7. Follow these steps:

  1. Ensure to apply patch (33568223 and 33420490) on all the databases in the Oracle Active Data Guard setup.
  2. Create a failover connection string which always connects to the current primary database in Oracle Active Data Guard.
  3. Registration of a single target database is required in Oracle AVDF to collect audit data from all the databases in Oracle Active Data Guard.
  4. Select Active Data Guard checkbox during target registration.
  5. In the Failover Connection String text box, enter the failover connection string which always connects to current primary database.
  6. Create an attribute in the Audit Collection Attributes tab for every database in the Oracle Active Data Guard configuration as follows:

    • Each attribute should be in the format av.target.connection.<name> where <name> can be any identifier defined by the user to identify the database.

    • The value corresponding to each attribute should be specified as the connection string of that specific database. For example, if there are three databases in Oracle Active Data Guard configuration, then the user can create these attributes:

  7. For audit collection create one trail for every database in the Oracle Active Data Guard configuration. Create an additional trail that uses the failover connection string. The remaining trails must use the connection string specified in the Audit Collection Attributes.
  8. Click Add to create an audit trail and specify the following. This step has to be performed only once. There will be only one trail which uses the failover connection.

  9. Click the Add button to create the trails and select the following options. This step has to be performed for every database in the Oracle Active Data Guard.

For cleanup of file based audit data on standby database, use DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL with AUDIT_TRAIL_TYPE as DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED_FILES.

For cleanup of file based audit data on primary database, use DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL with AUDIT_TRAIL_TYPE as DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED_FILES.

For cleanup of table based audit data on primary database, use DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL with AUDIT_TRAIL_TYPE as DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED_TABLE. Since the databases are in Active Data Guard configuration, this will also cleanup table based audit data from all the standby databases.

C.4.2 Additional Information for Audit Collection from Oracle Data Guard

Learn about additional information required to collect audit data from Oracle Data Guard.

Oracle Data Guard is a high availability solution which consists of one primary database and multiple standby databases. This section contains some additional information for configuring different audit trails.

Traditional Auditing

Audit data can be collected from the current primary database in Oracle Data Guard with traditional auditing. Follow these steps:

  1. Set AUDIT_TRAIL parameter to DB, EXTENDED, on all target databases.
  2. Create a target in Oracle AVDF with a single connection string that contains the connection details of all the databases. This ensures that Oracle AVDF trail can read from sys.aud$ table of the current primary database after failover or switchover occurs.
  3. Create Oracle Database table trail in Oracle AVDF to read the records from sys.aud$. of the current primary database.

Unified Auditing

Audit data can be collected from the current primary database in Oracle Data Guard with unified auditing. Follow these steps:

  1. Create a target in Oracle AVDF with single connection string that contains the connection details of all the databases. This ensures that Oracle AVDF trail can read from unified_audit_trail table of the current primary database after failover or switchover occurs.
  2. Create Oracle Database table trail in Oracle AVDF to read the records from unified_audit_trail of the current primary database.

Note:

Oracle AVDF supports audit collection from the traditional audit trail and unified audit trail for the current primary database only. In case of switchover or failover, audit collection starts on the new primary database, from the point at which the collection had stopped on the old primary database. Audit collection is not supported from the standby database.

C.5 Audit Trail Cleanup

Some Oracle Audit Vault and Database Firewall plug-ins include audit trail cleanup utilities.

C.5.1 Oracle Database Audit Trail Cleanup

Oracle Database provides the ability to purge audit trails both manually and with scheduled jobs.

C.5.1.1 About Purging the Oracle Database Target Audit Trail

You can use the DBMS_AUDIT_MGMT PL/SQL package to purge the database audit trail.

The DBMS_AUDIT_MGMT package lets you perform audit trail cleanup tasks such as scheduling purge jobs, moving the audit trail to a different tablespace, setting archive timestamps in the audit trail, and so on. The target database user must have the EXECUTE privilege on DBMS_AUDIT_MGMT to use it.

Oracle Database 11g release 2 (11.2) or later includes the DBMS_AUDIT_MGMT package and its associated data dictionary views installed by default. If your target database does not have this package installed, then you can download the package and data dictionary views from My Oracle Support.

Search for Article ID 731908.1.

For details about using the DBMS_AUDIT_MGMT PL/SQL package and views, refer to the following Oracle Database 11g Release 2 (11.2) documentation:

C.5.1.2 Scheduling Automated Purge Jobs

Simplify maintenance by scheduling automated jobs to purge unneeded audit data.

Oracle Audit Vault and Database Firewall is integrated with the DBMS_AUDIT_MGMT package on an Oracle Database. This integration automates the purging of audit records from the UNIFIED_AUDIT_TRAIL, AUD$, and FGA_LOG$ tables, and from the operating system .aud and .xml files after they have been successfully inserted into the Audit Vault Server repository.

After the purge is completed, the Audit Vault Agent automatically sets a timestamp on audit data that has been collected. Therefore, you must set the USE_LAST_ARCH_TIMESTAMP property to TRUE to ensure that the right set of audit records are purged. You do not need to manually set a purge job interval.

To schedule an automated purge job for an Oracle Database target:

  1. Log in to SQL*Plus on the target database as a user who has been granted the EXECUTE privilege for the DBMS_AUDIT_MGMT PL/SQL package.

    For example:

    sqlplus tjones
    Enter password: password
    
  2. Initialize the audit trail cleanup operation.

    In the following example, the DEFAULT_CLEANUP_INTERVAL setting runs the job every two hours:

    BEGIN
     DBMS_AUDIT_MGMT.INIT_CLEANUP(
      AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      DEFAULT_CLEANUP_INTERVAL    => 2 );
    END;
    /
    

    Note:

    • In case you are collecting audit data from CDB, then execute this step every time there is any change in the PDB instance.
    • In case you are using a CDB unified audit trail, then use CONTAINER_ALL parameter in the above command.
  3. Verify that the audit trail is initialized for cleanup.

    For example:

    SET SERVEROUTPUT ON
    BEGIN
     IF
       DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL)
     THEN
       DBMS_OUTPUT.PUT_LINE('Database and OS audit are initialized for cleanup');
     ELSE
       DBMS_OUTPUT.PUT_LINE('Database and OS audit are not initialized for cleanup.');
     END IF;
    END;
    /
    
  4. Use the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB procedure to create and schedule the purge job.

    In this procedure, ensure that you set the USE_LAST_ARCH_TIMESTAMP property to TRUE, so all records older than the timestamp can be deleted.

    The following procedure creates a purge job called CLEANUP_OS_DB_AUDIT_RECORDS that will run every two hours to purge the audit records.

    BEGIN
      DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
       AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
       AUDIT_TRAIL_PURGE_INTERVAL  => 2,
       AUDIT_TRAIL_PURGE_NAME      => 'CLEANUP_OS_DB_AUDIT_RECORDS',
       USE_LAST_ARCH_TIMESTAMP     => TRUE );
    END;
    /
C.5.1.3 How to Prevent Duplication Collection of Audit Trail Data From a Secure Target

Learn how to configure audit trails on Audit Vault Server to collect audit data from registered secure targets while avoiding duplicate collection of data.

AVSYS.CHECKPOINT table stores CHECKPOINT_TIME for each audit trail. It indicates time stamp, up to which, audit records are collected from secure targets audit trail and inserted/committed to AVSYS.EVENT_LOG table.

LAST_ARCHIVE_TS column of DBA_AUDIT_MGMT_LAST_ARCH_TS view is also updated to indicate time stamp, up to which, the audit data has been collected by audit trail. This helps in deciding the purge operation to prevent deleting those records which are yet to be collected by Audit Trails.

However LAST_ARCHIVE_TS column value does not play any role for an Audit trail to decide from where it has to read audit data during next read operation. As Audit Trail will always refer AVSYS.CHECKPOINT table when collector restarts, it will resume collection from CHECKPOINT_TIME. So Audit Trail will not read any record which has a time stamp lesser than CHECKPOINT_TIME.

So it clarifies that Audit Trail is not dependent on value stored in database last archive time stamp at secure target side to decide the point from which it had to collect. Rather it is just an indication for secure target to know that till this time stamp audit data has been collected hence it can be purged.

Note:

As it is evident that LAST_ARCHIVE_TS column can be modified manually whereas CHECKPOINT_TIME column AVSYS.CHECKPOINT table in Audit Vault server is manged automatically and not supposed to be modified manually. Therefore these two columns need not necessarily be in sync with each other.
C.5.1.4 Oracle GoldenGate Extract Cleanup

Learn how to use Oracle GoldenGate extract cleanup and simply maintenance.

Use the Oracle GoldenGate extract cleanup utility to simplify maintenance. This utility is available starting Oracle AVDF 20.4.

To run the Oracle GoldenGate extract cleanup utility:

  1. Navigate to the following directory on the host machine:
    AGENT_HOME\av\plugins\com.oracle.av.plugin.oracle\bin
  2. Run the following command:
    OracleGoldenGateExtractCleanupHandler <target name> <Agent deployed location>

    The above command has the following variables:

    <target name> is the name of the registered target.

    <Agent deployed location> is the full path of the directory where the Agent is deployed.

    Note:

    Ensure to specify the timezone offset when creating the target, using the target attribute av.collector.timezoneoffset. Also ensure the Agent machine and Oracle Database target are in the same timezone.

C.5.2 Microsoft SQL Server Audit Trail Cleanup

Learn about cleaning up your Microsoft SQL Server audit trail.

Microsoft SQL Server 2012 was deprecated in Oracle AVDF 20.12, and it will be desupported in one of the future releases.

If the SQL Server audit trail has collected data from a trace, extended events, or sqlaudit file and that file is inactive, then you can clean up this file. The SQL Server audit trail writes the names of the SQL Server audit text files to a plain text file with the .atc extension. The .atc file resides in the AGENT_HOME\av\atc directory on the computer on which the agent is installed.

To manually clean up files that Oracle AVDF has completed extracting audit records from:

  1. Go to the AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\bin directory of the computer where the Audit Vault Agent is installed.

    Ensure that the AGENT_HOME environment variable is correctly set to the directory path where the agent.jar file is extracted.

  2. Run the following utility:
    SQLServerCleanupHandler secured_target_name
    

    For example:

    SQLServerCleanupHandler mssqldb4
    

    If you do not set the AGENT_HOME environment variable, you can provide the agent home location in the command line using the following syntax:

    SQLServerCleanupHandler -securedtargetname secured_target_name agent_home_location
    

    For example:

    SQLServerCleanupHandler mssqldb4 c:\AV_agent_installation
    

    Important: If the name of the Audit Vault Agent installation directory contains spaces, enclose the name in double quotes, for example "C:\Agent Directory".

To automate the cleanup of SQL Server trace files, you can use the Windows Scheduler.

Note:

If the SQL Server trace definition is redefined or reinitialized, then you must ensure that the file names of the trace files do not overlap with trace files that were created earlier.

For example, suppose you start SQL Server with a trace definition in which the trace files names use the following format:

c:\serversidetraces.trc
c:\serversidetraces_1.trc
c:\serversidetraces_2.trc
...
c:\serversidetraces_259.trc

Then you restart the SQL Server with a new trace definition. This new trace definition must use a different file name from the current trace files (for example, the current one named c:\serversidetraces.trc). If you do not, then when you purge the audit trail, the new trace files that have same names as the old ones will be deleted.

C.5.2.1 Cleaning Up Oracle GoldenGate Extracts

Use the Oracle GoldenGate Extract cleanup utility to simplify maintenance.

Note:

To purge collected audit data from a remote collection, you need to set the rollover file number and size. These values are set on the Microsoft SQL Server.
  1. Navigate to the following directory on the host machine:

    AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\bin

  2. Run the following command:

    SQLServerGoldenGateExtractCleanupHandler.bat <target name> <agent deployed location>

    <target name> is the name of the registered target.

    <agent deployed location> is the full path of the directory where the Audit Vault Agent is deployed.

C.5.3 MySQL Audit Trail Cleanup

Use the MySQL audit trail cleanup utility to simplify maintenance.

To run the MySQL audit trail cleanup utility:

  1. On the host machine, go to the directory AGENT_HOME\av\plugins\com.oracle.av.plugin.mysql\bin
  2. Run the following command:

    MySQLServerCleanupHandler.bat secured_target_name AGENT_HOME

    The above command has the following variables:

    • secured_target_name - the name of the MySQL target

    • AGENT_HOME - the path to the directory where the Audit Vault Agent is deployed.

C.5.3.1 Cleaning Up Oracle GoldenGate Extracts

Use the Oracle GoldenGate Extract cleanup utility to simplify maintenance.

  1. Navigate to the following directory on the host machine: AGENT_HOME\av\plugins\com.oracle.av.plugin.mysql\bin
  2. Run the following command:
    MySQLGoldenGateCleanupHandler <target name> <Agent deployed location>

    <target name> is the name of the registered target.

    <Agent deployed location> is the full path of the directory where the Audit Vault Agent is deployed.

C.5.4 IBM DB2 Audit Trail Cleanup

Learn about using the IBM DB2 scripts to cleanup records.

Refer to Converting Binary Audit Files to ASCII Format for IBM DB2 for information regarding DB2 records cleanup.

C.6 Procedure Look-Ups: Connect Strings, Collection Attributes, Audit Trail Locations

Procedure lookups enable you to fine tune and customize audit records generation.

C.6.1 Target Locations (Connect Strings)

Use connect strings to register target locations in the Audit Vault Server console.

When registering a target in the Audit Vault Server console, you enter a connect string in the Target Location field. Use a connect string format from Table C-23 depending on the target type.

Note:

The connection string is mandatory for audit collection. However, it's not required for Database Firewall monitoring.

Table C-23 Target Connect Strings (for Target Location Field)

Target Type Connect String

Oracle Database

jdbc:oracle:thin:@//hostname:port/service

Sybase ASE

jdbc:av:sybase://hostname:port

Sybase SQL Anywhere

jdbc:av:sybase://hostname:port

Note: Sybase SQL Anywhere was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.

Microsoft SQL Server (SQL Server Authentication)

jdbc:av:sqlserver://hostname:port

When SSL Encryption is used with MSSQL sever and the server certificate validation is required. Ensure that agent TLS level is set to Level 4.

jdbc:av:sqlserver://<MSSQL Host name>:<Port number>;encryptionMethod=SSL;validateServerCertificate=true;CryptoProtocolVersion=TLSv1.2;trustStore=<key store jks path>;trustStorePassword=<keystore password>;extendedOptions=enableCipherSuites=SSL_RSA_WITH_RC4_128_MD5,SSL_RSA_WITH_RC4_128_SHA

When SSL Encryption is used with MSSQL sever and the server certificate validation is not required.

jdbc:av:sqlserver://<MSSQL Host name>:<Port number>;encryptionMethod=SSL;validateServerCertificate=false;CryptoProtocolVersion=TLSv1.2;

Microsoft SQL Server (Windows Authentication)

jdbc:av:sqlserver://<Host Name>:<Port>;authenticationMethod=ntlmjava

(Use Windows user credentials along with domain. For example, <domain name>\<user name > and password.)

OR

jdbc:av:sqlserver://<Host Name>:<Port>;authenticationMethod=ntlmjava;domain=<domain name>

Use Windows user credentials without domain. For example, <user name > and password.

Oracle Solaris

hostname (fully qualified machine name or IP address)

Oracle Linux

hostname (fully qualified machine name or IP address)

Microsoft Windows

hostname (fully qualified machine name or IP address)

Microsoft Active Directory Server

hostname (fully qualified machine name or IP address)

Oracle ACFS

hostname (fully qualified machine name or IP address)

Note: Oracle Automatic Storage Management Cluster File System (Oracle ACFS) or Oracle Advanced Cluster File System was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.

IBM AIX

hostname (fully qualified machine name or IP address)

C.6.2 Audit Collection Attributes

Oracle Audit Vault and Database Firewall (Oracle AVDF) provides audit collection attributes that are specific to the target platform, such as Oracle Database or MySQL.

C.6.2.1 About Audit Collection Attributes

Specify audit collection attributes when configuring targets.

Some types of targets have optional or required audit trail collection attributes. You can specify audit collection attributes when registering or modifying targets in the Audit Collection Attributes fields.

The following target types do not require audit collection attributes:

  • Microsoft SQL Server

  • Sybase ASE

  • Oracle Solaris

  • Windows

  • Linux

  • Microsoft Active Directory Server

C.6.2.2 Oracle Database Audit Collection Attributes

Specify audit collection attributes to control the types of data that Audit Vault collects.

You can specify audit collection attributes for a DIRECTORY audit trail for Oracle Database. Table C-24 describes the audit collection attributes you can use if you select DIRECTORY as the Audit Trail Type when registering an Oracle Database target in Oracle Audit Vault and Database Firewall.

Table C-24 Audit Collection Attributes for DIRECTORY Audit Trail for Oracle Database

Attribute Name and Description Required? Default Comments

ORCLCOLL.NLS_LANGUAGE

The NLS language of the data source

Yes: If the started audit trail cannot establish a connection to the Oracle target (for example, target is not running)

No: If the started audit trail is able to connect to the Oracle target and get these parameter values from the target (for example, the target is running when the trail is started)

NA

The value is not case sensitive.

ORCLCOLL.NLS_TERRITORY

The NLS territory of the data source

Yes: If the started audit trail cannot establish a connection to the Oracle target (for example, target is not running)

No: If the started audit trail is able to connect to the Oracle target and get these parameter values from the target (for example, the target is running when the trail is started)

NA

The value is not case sensitive.

ORCLCOLL.NLS_CHARSET

The NLS character set of the data source

Yes: If the started audit trail cannot establish a connection to the Oracle target (for example, target is not running)

No: If the started audit trail is able to connect to the Oracle target and get these parameter values from the target (for example, the target is running when the trail is started)

NA

The value is not case sensitive.

ORCLCOLL.RAC_INSTANCE_ID

The instance ID in an Oracle RAC environment

No

1

None.

AV.COLLECTOR.DATABASECHARSET

The NLS character set of the data source.

Yes: If the audit trail started cannot establish a connection to the target Oracle Database. For example, the target is not running.

No: If the audit trail started is able to connect to the target Oracle Database and get these parameter values from the target. For example, the target is running when the trail is started.

NA

None.

ORCLCOLL.HEARTBEAT_INTERVAL

The interval, in seconds, to store the metric information

No

60

Cannot be reconfigured at run time.

This interval determines how frequently metric information is updated. If the value is too low it creates overhead for sending metrics to the Audit Vault Server. If the value is too high it will skew the average metric information.

ORCLCOLL.NT_ORACLE_SID

The Oracle SID name on a Microsoft Windows systems

No

No default

The value is not case sensitive. If no value is specified then the audit trail queries the value from the target.

AV.COLLECTOR.TIMEZONEOFFSET

Timezone offset of Oracle Database target

Optional.

Note: For Oracle AVDF release 20.1 only, it is a mandatory target attribute for Transaction Log audit collection.

This attribute is not required from Oracle AVDF release 20.2 and onwards, as the Transaction Log audit trail fetches the time zone offset from the target database.

NA

None.

C.6.2.3 IBM DB2 for LUW Audit Collection Attribute

Learn about the IBM DB2 for LUW audit collection attribute.

Table C-25 describes the audit collection attribute required when you register an IBM DB2 for LUW target in Oracle AVDF.

Table C-25 Audit Collection Attribute for IBM DB2 for LUW Database

Attribute Name and Description Required? Default Comments

av.collector.databasename

The IBM DB2 for LUW database name

Yes

NA

This parameter is case sensitive.

Note: The audit collection attribute is not required from release 12.2.0.11.0 and onwards.

C.6.2.4 MySQL Audit Collection Attributes

Learn about the MySQL audit collection attributes.

Table C-26 describes the required and optional audit collection attributes when you register a MySQL target in Oracle Audit Vault and Database Firewall.

Table C-26 Audit Collection Attributes for MySQL Database

Attribute Name and Description Required? Default Comments

av.collector.securedTargetVersion

The MySQL database version

Yes

8.0

NA

av.collector.AtcTimeInterval

Specifies a time interval, in minutes, at which the audit trail cleanup time is updated

No

20

Example: If this value is 20, the audit trail cleanup time is updated every 20 minutes in the ATC file. Audit log files that have a time stamp older than the audit trail cleanup time will be cleaned from the source folder when you run the audit trail cleanup utility.

C.6.2.5 Oracle ACFS Audit Collection Attribute

Learn about the Oracle ACFS target audit collection attribute.

Note:

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) or Oracle Advanced Cluster File System was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.

Table C-27 describes the audit collection attribute required when you register an Oracle ACFS target in Oracle Audit Vault and Database Firewall.

Table C-27 Audit Collection Attribute for Oracle ACFS

Attribute Name and Description Required? Default Comments

av.collector.securedtargetversion

The version number of Oracle ACFS

Yes

NA

Five integer values separated by dots, for example 12.1.0.0.0.

C.6.3 Audit Trail Locations

When you configure an audit trail for a target in the Audit Vault Server, you specify a trail location. The trail location depends on the type of target.

Note:

Trail locations are case sensitive. To avoid duplicate data collection, Oracle recommends that you provide the entire trail location either in all capital letters or all lowercase letters.

Note:

If you select DIRECTORY for the audit trail type, the trail location must be a directory mask.

Table C-28 Supported Trail Locations for Targets

Target Type Trail Type Supported Trail Locations
Oracle Database Table SYS.AUD$, SYS.FGA_LOG$, DVSYS.AUDIT_TRAIL$, UNIFIED_AUDIT_TRAIL, CDB_UNIFIED_AUDIT_TRAIL
Oracle Database Directory Full path to the directory that contains the AUD or XML files
Oracle Database syslog

Full path to the directory that contains the syslog or rsyslog file

Include the syslog or rsyslog file prefix in the path. For example, if the file names are messages.0, messages.1, and so on, you might use the following path:

/scratch/user1/rsyslogbug/dbrecord/messages

You can also enter Default and the system will search for either the syslog or the rsyslog location. If both are present, entering Default causes the audit trail to collect data from the syslog files.

Oracle Database

Event log

Network

No trail location required
Oracle Database Transaction Log Full path to the directory that contains the Oracle GoldenGate Integrated Extract XML trail file
Microsoft SQL Server Directory

*.sqlaudit files, or *.trc (trace) files

Examples:

directory_path\*.sqlaudit

directory_path\prefix*.sqlaudit

directory_path\prefix*.trc

For prefix, you can use any prefix for the .trc or *.sqlaudit files.

#C2_DYNAMIC and #TRACE_DYNAMIC are only supported for SQL Server 2000, 2005, 2014, and 2016.

Microsoft SQL Server 2012 was deprecated in Oracle AVDF 20.12, and it will be desupported in one of the future releases.

Microsoft SQL Server Event log application or security (SQL Server 2008, 2012, 2014, and 2016)

Microsoft SQL Server 2012 was deprecated in Oracle AVDF 20.12, and it will be desupported in one of the future releases.

Microsoft SQL Server Transaction Log (Oracle AVDF 20.9 and later) Full path to the directory that contains the Oracle GoldenGate CDC Extract XML trail file
IBM DB2 for LUW Directory Path to a directory, for example: d:\temp\trace
Sybase ASE Table SYSAUDITS
PostgreSQL Directory Path to the directory that contains the CSV audit files
MySQL Directory Path to the directory where converted XML files are created when you run the MySQL XML transformation utility
Linux Directory Default location of the audit.log (/var/log/audit/audit*.log) or any custom location that is configured in the /etc/audit/auditd.conf file
Microsoft Windows Event log

security (case-insensitive)

You can use any case combination in the word security. However, after you start collecting a trail with a particular case combination, you must use the same combination in subsequent collections. Otherwise, a new audit trail will start collecting records from the start of the security event log.

Oracle Solaris Directory

hostname:path_to_trail

The hostname matches the host name in the audit log names, which look like this:

timestamp1.timestamp2.hostname

AIX Directory /audit/trail
Oracle ACFS Directory

Path to the directory that contains XML audit files

For example, a file system that is mounted at $MOUNT_POINT has the following audit trail location:

$MOUNT_POINT/.Security/audit/

Note:

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) or Oracle Advanced Cluster File System was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.

Microsoft Active Directory Server Event log

directory service or security (case-insensitive)

You can use any case combination in the words directory service or security. However, after you start collecting a trail with a particular case combination, you must use the same combination in subsequent collections. Otherwise, a new audit trail will start collecting records from the start of the security event log.

C.7 Installing the Audit Vault Agent Under Its Own OS User Account

For environments that require more separation of duties, you can install the Audit Vault Agent under it's own OS user account instead of under the OS user account that owns the Oracle software installation.

You have two options:

  • Traditional Unix permissions
  • POSIX access control lists (ACLs)

Traditional Unix Permissions

This is the simplest option. It involves adding the Audit Vault Agent user avagentosuser to the same primary group (usually oinstall) as the Oracle software owner. Sometimes the database does write out an audit file without group read access. This is easy to maintain with the chmod g:rx command.

POSIX ACLs

POSIX ACLs let you set privileges on files and directories that override traditional UNIX permissions.

Here are some points to consider before choosing this approach:

  • If you're using Oracle Exadata, when a quarterly bundle patch is applied, the file access control list (FACL) packages are removed (or have to be removed to avoid bundle patch conflicts). When FACL packages are removed, the existing FACLs that are set stay in effect.
  • If the DBAs move any directory in the audit_file_dest path, the FACLs break. A simple action like mv audit audit.old; mkdir audit would break the FACL on that directory.
  • The FACL command to setFACL can only be run by root.

    If the FACLs are broken (or FACL binaries or packages are missing after a bundle patch is applied) and the DBA or Audit Vault Server administrator must work with a system administrator with root access to resolve the issue, then audit collection may no longer be in near real time.

  • The /etc/fstab mount point must have acl set so the ACLs will be applied to that file system and remounted.

You can apply FACLs to the directory to allow access for a specific user. Any new file that's created in that directory (like a new audit record) will have the FACL permissions. Any audit file that exists in the directory before you apply the FACL will not have the FACL permissions, so you need to apply the setFACL command to each file individually.

Each directory in the fully qualified path to the audit directory must have the FACL set so that the dedicated user can traverse the path to the audit files.

Example C-1 Applying FACLs

This example uses the root user and an OS user named avagent.

Between running the UNIX commands as root, you can user your OS user account to see the results.

  1. Run the following commands as root:

    mkdir -p /tmp/dir1/dir2/audit
    mkdir -p /tmp/dir1/dir2/audit2
    touch /tmp/dir1/dir2/audit/file1
    touch /tmp/dir1/dir2/audit2/file2
    chmod -R 750 /tmp/dir1
  2. Grant access to the /tmp/dir1/dir2/audit directory only for the avagent OS user. You have to do this for every directory (just like you would with chmod 750, for example).

    setfacl -m u:avagent:rx /tmp/
    setfacl -m u:avagent:rx /tmp/dir1
    setfacl -m u:avagent:rx /tmp/dir1/dir2
    setfacl -m u:avagent:rx /tmp/dir1/dir2/audit

    The avagent OS user can now access the /tmp/dir1/dir2/audit directory but not the /tmp/dir1/dir2/audit2 directory, because no FACL is applied there.

  3. To see whether an FACL is applied on a file or directory, use the following command:

    getfacl <file/directory>
  4. Specify that any new files that are created in the /tmp/dir1/dir2/audit directory will have the rx access for the avagent OS user.

    setfacl -dm u:avagent:rx /tmp/dir1/dir2/audit
  5. To verify that the default information is set up correctly, use the following command:

    getfacl /tmp/dir1/dir2/audit
  6. To test the preceding settings, create a new file in /tmp/dir1/dir2/audit.

    echo "test" > /tmp/dir1/dir2/audit/file3

    The avagent OS user can access file3 but not file1.

  7. Use getfacl to check the differences between the files.

    getfacl /tmp/dir1/dir2/audit/file1
    getfacl /tmp/dir1/dir2/audit/file3
  8. To resolve files that didn't have a FACL applied before setfacl -d [default] was set up to apply to any new file in the directory, apply the FACL to the files.

    setfacl -m u:avagent:rx /tmp/dir1/dir2/audit/file1

    You can also use wildcards. For example:

    setfacl -m u:avagent:rx /tmp/dir1/dir2/audit/*
  9. To test moving files into the /tmp/dir1/dir2/audit directory, run the following commands:

    mv /tmp/dir1/dir2/audit2/file2 /tmp/dir1/dir2/audit/
    getfacl /tmp/dir1/dir2/audit/file2

    The moved file doesn't have the FACL applied because it wasn't created in the directory when the setfacl -d [default] was set up, so you have to apply the FACL to the moved file.

    setfacl -m u:avagent:rx /tmp/dir1/dir2/audit/file2