1. Administrator's Guide
  2. Getting Started with Oracle Audit Vault and Database Firewall
  3. Configuring Targets, Audit Trails, and Database Firewall Monitoring Points

7 Configuring Targets, Audit Trails, and Database Firewall Monitoring Points

Learn about configuring targets, audit trails, and Database Firewall monitoring points.

7.1 About Configuring Targets

Learn about configuring targets.

Targets can be supported databases or operating systems that Audit Vault and Database Firewall monitors. You must register all of the targets in the Audit Vault Server, regardless of whether you are deploying the Audit Vault Agent, the Database Firewall, or both.

If you want to collect audit trails from your targets, you must configure an audit trail for each target and start collection manually.

If you want to monitor a target with the Database Firewall, you must create a monitoring point for that target.

For Oracle Database targets that you monitor with the Database Firewall, you can configure Oracle Audit Vault and Database Firewall to monitor the native network encrypted traffic. To do so, you must run scripts on the target computers to configure the necessary privileges.

If you are using the Database Firewall, you can also monitor the target database's responses to incoming SQL traffic. The following sections contain the high-level workflow for configuring the Oracle Audit Vault and Database Firewall system.

See Also:

7.2 Discovering and Registering Targets and Creating Groups

Learn about discovering and registering targets and creating groups.

7.2.1 Discovering Databases for Target Registration

Starting in Oracle AVDF 20.12, super administrators can import XML files resulting from the Nmap scan command of specified IP address and port ranges to determine which Oracle, Microsoft SQL, MySQL, DB2, PostgreSQL, or Sybase databases in your database fleet are yet to be registered with AVDF.

7.2.1.1 About Discovering Databases for Target Registration

Starting in Oracle AVDF 20.12, super administrators can import the XML file resulting from the Nmap scan command of specified IP address and port ranges to identify databases available for registration with AVDF. Discovered databases can then be assigned to administrators for registration with AVDF.

If you have many databases in your fleet, it may be difficult to determine which ones are not yet registered with Oracle AVDF. The Database Discovery feature introduced in Oracle AVDF 20.12 allows you to scan specified IP address and port ranges using Nmap commands to determine which databases haven't been registered with AVDF. The results of the Nmap scan will inform you which Oracle, Microsoft SQL, MySQL, DB2, PostgreSQL or Sybase databases have not been registered as targets.

The XML file that is the output of the Nmap scan command is the list of un-registered databases. This file can be imported to the Audit Vault Server console by a super administrator and each database can be either hidden from future scans or assigned to an administrator for target registration.

7.2.1.2 Executing Nmap Scan Commands

In order to use the Database Discovery feature available in Oracle AVDF starting in 20.12, you need to execute the Nmap scan command to discover un-registered databases in your database fleet. A super administrator will need to upload the results of the Nmap scan command to the Audit Vault Server.

Prerequisites

  1. Contact your network administrator or check your organization's policies before executing Nmap scan command.
  2. Download the Nmap command tool.

    Nmap Download

    Note:

    Only Nmap version 7.92 and 7.94 are supported.

Procedure

  1. While on a Windows or Linux platform, run the following command on a host in your network:
    nmap -sV -n -p T:<p1-p2> <ip range> --host-timeout <time in minutes>  -oX <xml filepath>
    Attributes:
    • sV - Optional, Probes open ports to determine service/version information.
    • n - Optional, No DNS resolution. This option slashes scanning time
    • p T:<p1 to p2> - This option scans TCP protocol for port range p1 to p2.

      If you do not know the port range, it is recommended to scan the complete port range of 0 - 65536.

    • IP Range - IP range that need to scanned. One example is given above, but for more type of ranges refer to the Nmap command documentation
    • host-timeout - Enter a time in minutes after which the scan on the host will stop. Some hosts simply take a long time to scan. This may be due to poorly performing or unreliable networking hardware or software, packet rate limiting, or a restrictive firewall.
    • oX - XML output file location
    For example: 
    nmap -sV -n -p T:1430-6607 10.89.89.0-10.89.89.255 --host-timeout 2m -oX /tmp/tmp.xml
  2. Ensure that the XML file is accessible by a super administrator.

Related Topics

7.2.1.3 Importing the XML File for Database Discovery as a Super Administrator

After successfully running the Nmap scan command, the XML file needs to be imported to the Audit Vault Server Console by a super administrator. Databases can then be assigned to administrators for registration.

  1. Log in to the Audit Vault Server Console as a super administrator.

  2. Click the Targets tab.
  3. In the left menu, click Database Discovery.
  4. Click Import Nmap XML.
  5. Click Choose File.
  6. Navigate to location of the Nmap output file.
  7. Click Save to import the XML file.
  8. After the XML has been successfully imported, delete the XML file from your local system.

A super administrator will be able to see the list of databases discovered in the Nmap scan and their registration status with AVDF in Database Discovery in the Audit Vault Server Console.

7.2.1.4 Assigning Databases for Registration in Database Discovery as a Super Administrator

After importing the XML file, a super administrator can see the list of discovered databases and their registration status with AVDF. A super administrator can then assign the unregistered databases to other administrator users for target registration.

  1. Log in to the Audit Vault Server Console as a super administrator.

  2. Click the Targets tab.
  3. In the left menu, click Database Discovery.
  4. Select the database(s) you want to assign to an administrator for registration.
  5. Click Assign admin.
  6. Select the administrator user to assign the database(s) to.

    Select Select in the drop down of users if you'd like to unassign the database(s).

  7. Click Save.
7.2.1.5 Registering Assigned Databases in Database Discovery

If you are an administrator with assigned databases or a super administrator you can register discovered databases with AVDF directly from Database Discovery.

  1. Log in to the Audit Vault Server Console as a super administrator or an administrator.
  2. Click the Targets tab.
  3. In the left menu, click Database Discovery.
  4. Select a database you want to register with AVDF.

    You can only select one database at a time.

  5. Click Register.
  6. Complete registration of the selected database.

    For more information see Registering or Removing Targets in Audit Vault Server.

7.2.1.6 Managing Discovered Databases as a Super Administrator

Super administrators can manage the table of discovered databases by either hiding, showing, or deleting databases.

Show/Hide a Database

A hidden databases won't be listed when the default filters are applied to the table of discovered databases, but can later be shown again.

  1. Log in to the Audit Vault Server Console as a super administrator.

  2. Click the Targets tab.
  3. In the left menu, click Database Discovery.
  4. Optional, apply or remove filters to the table.
  5. Select the database(s) you want to either show or hide. All selected databases must all be in the same shown or hidden state.

    Note:

    Registered databases can't be hidden.
  6. Click Show/Hide.

    All selected databases will change to the opposite of their current shown or hidden state.

  7. Optional, leave a comment as to why the database(s) is to be shown or hidden.
  8. Click Save.

Delete a Database

A deleted database is removed from the table of discovered databases. However, if an XML file from the Nmap scan is imported again, any removed databases will once again show up in the table.

  1. Log in to the Audit Vault Server Console as a super administrator.

  2. Click the Targets tab.
  3. In the left menu, click Database Discovery.
  4. Optional, apply or remove filters to the table.
  5. Select the database(s) you want to delete.
  6. Click Delete.
7.2.1.7 Viewing the Status of the XML Import Job

Once the XML file has been imported, administrators can view the details and status of the Database Discovery job.

  1. Log in to the Audit Vault Server Console as an administrator.

  2. Click the Settings tab.
  3. Click System in the left navigation menu.
  4. In the Monitoring section, click Jobs.
  5. Find a Database discovery job to view the details.
  6. Click the job details icon to see more details about the job including:
    • The user that performed the import
    • The time it took for the import to complete
    • The number of databases discovered on how many IP addresses

7.2.2 Registering or Removing Targets in Audit Vault Server

Learn about registering and removing targets in Audit Vault Server.

7.2.2.1 About Targets in the Audit Vault Server

Oracle Audit Vault and Database Firewall (Oracle AVDF) super administrators can create targets and grant other administrators access on those targets.

Administrators can also create targets, but the targets that they create are accessible only to the creator and to the super administrator who created the administrator.

The following guidelines apply when creating and accessing targets:

  • Both super administrators and administrators can create targets.
  • Super administrators can grant access on targets or target groups to specific administrators.
  • Super administrators have access to all targets, and administrators have access only to those targets on which they have been granted access.

In Oracle Database 12c, if you are not using a multitenant container database (CDB), then register a target for your database as you would for previous versions of Oracle Database. If you use a CDB, then you must register a target for the CDB, as well as for each pluggable database (PDB).

7.2.2.2 Registering Targets

Before you can begin audit collection and Database Firewall monitoring, you need to register the targets that you want to audit or monitor.

Target Information

  1. Log in to the Audit Vault Server console as an administrator.

  2. Click the Targets tab.

    Targets is selected in the left navigation menu by default. This page contains a list of configured targets. You can sort or filter the list of targets.

  3. Click Register in the top, right corner.

    The following page appears:


    Description of register_targets.png follows
    Description of the illustration register_targets.png

  4. Enter the name and optionally the description for the new target.
  5. Select the target type from the Type drop-down list. For example, Oracle Database.

  6. Starting with Oracle AVDF 20.7, select a policy from the Retention Policy drop-down list.

    This list displays all the pre-configured policies and user-defined policies from the Archiving tab. If the super administrator has set a user-defined policy as the default, then that policy is selected by default. Otherwise the default value is 3 month(s) online, 6 month(s) in archive.

Audit Connection Details Tab

Enter the details to connect to the target.

The fields in this section change, depending on the target type. The following list describes all the possible fields and options that may appear.

  1. Active Data Guard: For Oracle Database targets, select this check box if the target is an Active Data Guard database. For details, see Additional Information for Audit Collection from Oracle Active Data Guard.
  2. Core (previously Basic) or Advanced: For database targets, select Advanced if you know the connection string. Otherwise, select Core.
  3. Host Name / IP Address: You can use a virtual IP address.

    Tip:

    To improve the accuracy of using Database Discovery (available Oracle AVD 20.12 and later) to discover unregistered databases, host name/IP address should be provided. This will prevent a database from being falsely labeled as unregistered.
  4. Port

    Tip:

    To improve the accuracy of using Database Discovery (available Oracle AVD 20.12 and later) to discover unregistered databases, port information should be provided. This will prevent a database from being falsely labeled as unregistered.
  5. Service Name: If the target is an Oracle Database, enter the Oracle Database service name or SID.
  6. Protocol: Select TCP or TCPS.

  7. Connection String (previously Target Location): If you selected the Advanced option for a database target, enter the connection string or connection URL for the database. This connection string is required for the Audit Vault Agent to collect audit data, but it's not required to deploy the Database Firewall only.

    Note:

    • For Oracle Database, the string may look like the following: jdbc:oracle:thin:@//<IP address of the Database server host>:<port number>/hrdb
    • When you configure Oracle Real Application Clusters (Oracle RAC) as a target for Audit Vault Agent data collection, enter the SCAN listener host name.
    • If the target is a Microsoft SQL Server Cluster, you need to set a mandatory collection attribute. See Microsoft SQL Server Plug-in for Oracle Audit Vault and Database Firewall for details.
  8. Database User Name (previously User Name): Enter the name of an existing database user that has access to the audit data that's generated on the target.

    Note:

    Only case insensitive database user names are supported for Oracle Database.
  9. Password: Enter the password for the database user.

  10. Test Connection: Starting with Oracle AVDF 20.10, for Oracle Database and Microsoft SQL Server targets, click this button to test the connection details that you just entered.

    If Oracle AVDF is unable to connect to the host or database, or if there are other issues, an error message displays more details so you can resolve the issue before continuing.

Audit Collection Attributes Tab

Enter audit collection attributes for the target.

  1. Click Add to enter the attribute details in the Name and Value columns.

    The Audit Vault Agent may require collection attributes for some target types. The following table lists the mandatory collection attributes to enter for different target types.

    Target Type Mandatory Collection Attributes
    Microsoft SQL Server Cluster av.collector.clusterEnabled
    PostgreSQL av.collector.securedTargetVersion
    Oracle Database for Transaction Log Audit Collection

    AV.COLLECTOR.TIMEZONEOFFSET

    Note: This is the timezone offset of the Oracle Database.

    Microsoft SQL Server for Transaction Log Audit Collection

    AV.COLLECTOR.TIMEZONEOFFSET

    Note: This is the timezone offset of the SQL Server database.

    MySQL for Transaction Log Audit Collection

    AV.COLLECTOR.TIMEZONEOFFSET

    Note: This is the timezone offset of the MySQL database.

    Note:

    For PostgreSQL, enable the pgaudit extension. If this extension is disabled, the audit collection is incomplete and reports will be missing operational details.

  2. Optionally use the following information to improve the audit collection rate or effectively utilize the resources of the Audit Vault Agent and Audit Vault Server.

    Note:

    This functionality is not applicable to the Host Monitor Agent or network trails.

    • Starting in Oracle AVDF 20.4, you can improve audit collection performance and increase the audit collection rate by setting the av.collfwk.MULTI_THREADED attribute to true.

      This applies to all audit trails belonging to the target. While this configuration improves the audit collection rate, the resource (CPU and memory) requirements on the Audit Vault Agent machine also increase. There may also be an increase in resource utilization on the Audit Vault Server. Oracle recommends that you use this configuration if the target audit record generation rate is between 86 and 172 million records per day (or between 1000 to 2000 records per second).

    • Starting In Oracle AVDF 20.5, the Audit Vault Agents automatically choose the best possible configuration for improving audit collection rate. This dynamic multithreaded collector functionality effectively utilizes the resources of the Audit Vault Server and Audit Vault Agent.

      This functionality is the default behavior and increases the throughput of the audit trail by increasing the number of threads when the target audit generation rate is high. It also reduces the number of threads when the target audit generation rate is low. This functionality improves the audit collection rate and can support targets generating records up to 2000 per second or 172 million per day. When the target audit generation rate is very high, the resource (CPU and memory) requirements on the Audit Vault Agent machine also increase. There may also be an increase in resource utilization on the Audit Vault Server.

      Oracle recommends that you avoid setting the av.collfwk.MULTI_THREADED attribute and rely on the dynamic multithreaded collector functionality.

      If high throughput is not required due to Audit Vault Agent machine resource constraints, then use the single-threaded collector by setting the av.collfwk.MULTI_THREADED attribute to false. This is the default behavior in Oracle AVDF 20.5 and earlier.

      If high throughput is always required due to an audit data generation rate of 86 to 172 million records per day, then use the static multithreaded collector (always uses maximum threads) by setting the av.collfwk.MULTI_THREADED attribute to true.

  3. If you're configuring audit collection, click Save to complete the target registration.

    To configure Database Firewall monitoring, continue with the remaining steps.

Database Firewall Monitoring Tab

Enter Database Firewall monitoring details.

  1. Click Add.
  2. In the Database Firewall Monitor dialog box, enter the following information on the Core tab (previously Basic):
    1. Database Firewall: Select a value from the list.

    2. Mode: Select one of the following deployment modes:

      • Monitoring (Out-of-Band): The Database Firewall can monitor and alert on SQL traffic, but it can't block or substitute SQL statements.

      • Monitoring (Host Monitor): The Database Firewall can monitor and alert on SQL traffic, but it can't block or substitute SQL statements.

      • Monitoring / Blocking (Proxy): The Database Firewall can block or substitute SQL statements.

      Note:

      Ensure that you select the right mode in accordance with the Database Firewall policy defined for the target. If the Database Firewall policy contains SQL blocking rules, but you select a mode for monitoring only, SQL statements are not blocked. Therefore, if you want to block SQL statements according to policy rules, use Monitoring / Blocking (Proxy) mode.

      For more information about deployment modes, see Introduction to Database Firewall Deployment.

    3. Network Interface Card: Select a value from the list.

    4. Proxy Ports: Select a value from the list.

      Note:

      For an Oracle RAC instance, select the network interface card (NIC) and proxy ports if you selected Monitoring / Blocking (Proxy) mode. The proxy port is not mandatory for monitoring-only modes.
  3. If the target is Oracle Real Application Clusters (Oracle RAC), select the RAC Instance/Autonomous DB check box (RAC Instance check box in Oracle AVDF 20.7 and earlier).

    Caution:

    If you set up an Oracle RAC protected database to be a scan listener, you also need to select the RAC Instance/Autonomous DB check box when registering the database as a target. If you don't identify the target as a RAC database, the scan listener could redirect the client to a different IP address, bypassing the Database Firewall entirely.

  4. In the Connection Details section, click Add to add a target.

    Enter the following information for each available connection to the database:

    • Host Name / IP Address
    • Port
    • Service Name (Optional, for Oracle Database only)
      For Monitoring Only (Host-Monitor) and Monitoring Only (Out-Of-Band) mode, you can enter multiple SIDs or service names, each on a separate line. For Monitoring/Blocking (proxy-mode) mode,
      • Oracle AVDF 20.1-20.9: You need to configure a proxy target for each OSN. This is because a single proxy port cannot service multiple OSN's on the same target database. Add more traffic proxy ports as required.
      • Oracle AVDF 20.10 and later: You can use one proxy port and specify multiple OSN's on the target database that are going to be processed. Specify the OSN's in a list delimited by the "|" character. For example, target1|target2|target 3.

      If you provide a service name or SID, Database Firewall applies policies only to the sessions that match that service name or SID. All other traffic is ignored by default. In Monitoring/Blocking(proxy-mode) mode, that traffic is passed to the target database. Starting with Oracle AVDF 20.8, you can block those sessions by selecting the Block Traffic for Unregistered Service Names check box on the Advanced tab.

  5. Click the Advanced tab.
  6. Enter a number for Database Firewall Monitor Threads.

    The minimum and default value is 1. This controls the number of traffic handling threads in the Database Firewall monitoring point. Use due caution before modifying this value.

  7. If the target database is an Oracle Database and Mode is set to Monitoring / Blocking (proxy), optionally select the Block Traffic for Unregistered Service Names check box to have the Database Firewall block sessions that use service names other than the one that is configured in the target Connection Details section.
  8. If the database client and server are communicating over the TLS protocol, enable TLS.
    With this option, the Database Firewall acts as a TLS proxy. It serves as a TLS server for the database client and acts as a TLS client to the database server. The Database Firewall and the Audit Vault Server have access to the decrypted SQL traffic for further analysis. This feature applies only for Database Firewalls that are deployed in Monitoring / Blocking (Proxy) mode.
    1. Select Enable TLS support.

      Note:

      If you select this option, the Decrypt With Native Network Encryption Key check box is hidden.
    2. In Oracle AVDF release 20.8 and later, select the certificate type under Inbound TLS (From client to DBFW).
      The TLS protocol uses the certificate to authenticate the communication participant. You can use the default certificate that is signed by the Database Firewall or a certificate that is signed by an external Certificate Authority (CA).
    3. If you use the default self-signed certificate, then click Download DBFW Certificate.
      You need to install this certificate on the database client to enable Database Firewall authentication.
    4. If you use the external CA signed certificate, then select the certificate from the drop-down list.
    5. Select the cipher suite level.
      Level 4 - strongest, is the default.
    6. If you don't need database client authentication, then deselect Client Authentication.
      This option is available only for the inbound connection. The outbound connection is always authenticated. If you deselect this option, the Client Trusted Certificates button is disabled.
    7. To manage certificates for client authentication, click Client Trusted Certificates.
    8. Click Choose File and select the certificate on the local machine.
    9. Click Open to load the certificate and add it to the Database Firewall.
      The details of the uploaded certificate appear in the dialog box.
    10. Click Cancel to exit the dialog box.
    11. Follow a similar process to select and manage certificates and the cipher suite level under Outbound TLS (From DBFW to Database).

      To manage the certificates for server authentication, click Database Trusted Certificates.

  9. If Oracle Database uses native network encryption, select Decrypt With Native Network Encryption Key to enable the decryption of traffic.

    Note:

    If the Enable TLS support check box is selected, the Decrypt With Native Network Encryption Key check box is hidden.

    For Oracle AVDF release 20.5 and earler, the check box is Decrypt With Network Native Encryption Key.

    This option also supports the retrieval of session information for Oracle Database. Complete the remaining fields as applicable.

    For Oracle Real Application Clusters (Oracle RAC) targets (if the RAC Instance/Autonomous DB check box is selected on the Core tab), enter the SCAN Listener IP address.

    (In Oracle AVDF 20.7 and earlier, it's the RAC Instance check box, and in Oracle AVDF 20.2 and earlier, it's the Basic tab.)

    For Oracle standalone database targets, enter the IP address of the database listener.

    For Sybase SQL Anywhere (Oracle AVDF 20.1-20.6 only) and Microsoft SQL databases, the field is Retrieve session information from target DB. Retrieving session information is not available for any other non-Oracle database types.

    Note:

    Ensure that the Database Firewall is allowed to make a network connection to the database listener.
  10. Optionally select the Capture Database Response check box to have the Database Firewall monitor the SQL response from the database.
  11. Optionally select the Full Error Message check box to capture the database response codes and error codes.
  12. Click Save in the dialog box to save the configuration for the monitoring point.
  13. Click Save on the main page to save the target.

    Note:

    TCPS must be configured for registering Hybrid Cloud Oracle Databases. See Securing the Agent and Oracle Database Target Connection.

Note:

After registration is complete for Oracle Database targets, the security assessment job is automatically submitted. This job collects security assessment data from Oracle Databases for the assessment reports.

To check the status of the security assessment job, see Monitoring Jobs.

To view the assessment reports, see Assessment Reports.

See Also:

7.2.2.3 Modifying Targets

You can modify a target after it's been registered.

  1. Log in to Oracle Audit Vault Server console as an administrator.
  2. Click the Targets tab.

    Targets is selected in the left navigation menu by default. This page contains a list of configured targets. You can sort or filter the list of targets.

  3. Click the name of the target that you want to modify.
  4. You can update the name, decription, and retention policy of the target.
  5. To modify the target's Audit Connection Details or Audit Collection Attributes, click Modify.
  6. Made your changes.
    1. Starting with Oracle AVDF 20.10, if you change audit connection details for an Oracle Database or Microsoft SQL Server target, click the Test Connection button to test the connection details that you just entered.

      If Oracle AVDF is unable to connect to the host or database, or if there are other issues, an error message displays more details so you can resolve the issue before continuing.

  7. Click Save.

Note:

If you change the name of a target, it will have the following affects:
  • The new name won't appear in Oracle Audit Vault and Database Firewall reports until you restart the Audit Vault Agent.
  • There will not be duplicates enteries for the modified target because the secured_target_id will remain the same. Additionally, there will be no impact to the audit trails or retention policies for the target.
  • In the Event log and Alerts table, the target name will not get changed for old events, but new events will get logged with new target name. So it recommended that if you are querying the Event log or Reports for any targets, that you use secured_target_id to get all the entries for the target, instead of the target name.
7.2.2.4 Removing Targets

Learn about removing targets.

If you no longer need to have a target registered with Oracle Audit Vault and Database Firewall, then you can use either the console or the command-line utility to remove the target. After you have removed the target, the audit data pertaining to the target still resides in the data warehouse within its retention period (according to the archiving policy).

After you have removed a target, its identity data remains so that there will be a record of targets that have been dropped. Remove the target only if you no longer want to collect its data or if it has moved to a new host computer.

To remove a target:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab.
  3. The Targets tab in the left navigation menu is selected by default. Select the check boxes against the targets that you want to remove.
  4. Click Delete button in the top right corner of the page.

    See Also:

7.2.3 Creating a Target Group

Learn how to create target groups.

As a super administrator you can create target groups to grant other administrators access to targets as a group rather than individually.

  1. Log in to the Audit Vault Server console as a super administrator.

  2. Click the Targets tab.

  3. In the left navigation menu, click Target Groups.

  4. Click Create button in the top right corner.

  5. In the Create Target Group dialog, do the following:

  6. Click Save.

7.2.4 Modifying a Target Group

You can modify the contents of a target group or change the target group name and description.

  1. Log in to the Audit Vault Server console as a super administrator.
  2. Click the Targets tab.
  3. In the left navigation menu, click Target Groups.
  4. Click the name of the target group that you want to modify.
  5. In the Modify Target Group dialog, perform any of the following modifications:
  6. Click Save.

7.2.5 Controlling Access to Targets and Target Groups

Learn about controlling access to targets and target groups.

Oracle Audit Vault and Database Firewall super administrators can control which administrators have access to targets or target groups. You can control access for an individual user or for an individual target or group.

See Also:

Managing User Access Rights to Targets and Groups

7.2.6 Moving a Target from One Host Machine to Another

Learn how to handle when a target is moved from one host machine to another.

There are a few changes to be made in Audit Vault Server console when a target is moved from one host machine to another. This depends on the type of the Audit Vault Agent.

An Audit Vault Agent can be of two types:

  1. When the Audit Vault Agent is installed on the target host machine, it is called as a local Agent.
  2. When the Audit Vault Agent is not installed on the target host machine and is installed remotely, it is called as a remote Agent.

The following table contains the configuration and the steps to be followed in the Audit Vault Server console when the target is moved from one host machine to another.

Note:

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) or Oracle Advanced Cluster File System was desupported in Oracle AVDF release 20.8

Sybase SQL Anywhere was desupported in Oracle AVDF release 20.8

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

Agent Type Trail Type Target Type Steps in Audit Vault Server Console

Local

TABLE

Oracle Database

Oracle Key Vault

Sybase ASE

Step 1: Update the target Connection Details by following these steps:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab. The Targets tab in the left navigation menu is selected by default.
  3. Select and click the specific target.
  4. In the Database Firewall Monitoring section on the main page, click to modify the connection details. The Database Firewall Monitor dialog is displayed.
  5. Modify and update the Connection Details in the dialog.
  6. Click Save.
  7. Click Save in the main page.

Step 2: Delete existing trail by following these steps:

  1. Click the Targets tab.
  2. Click Audit Trails in the left navigational menu.
  3. Select the specific audit trail and click Stop.
  4. Click Delete.

Step 3: Create a new trail and configure the Audit Vault Agent installed on the new host machine. Refer to Adding Audit Trails with Agent-Based Collection

Local

DIRECTORY

SYSLOG

EVENT LOG

TRANSACTION LOG

Oracle Database

Step 1: Update the target Connection Details.

Step 2: Delete the existing trail.

Step 3: Create a new trail by configuring the Audit Vault Agent installed on the new host machine and using the new trail location of the new host machine.

Local

DIRECTORY

SYSLOG

EVENT LOG

TRANSACTION LOG

MySQL

Microsoft SQL Server

PostgreSQL

IBM DB2

Quick JSON

Oracle Solaris

Linux

IBM AIX

Microsoft Windows

Microsoft Active Directory

Oracle ACFS

Step 1: Delete the existing trail.

Step 2: Create a new trail by configuring the Audit Vault Agent installed on the new host machine and using the new trail location of the new host machine.

Local

NETWORK

Oracle Database

MySQL

Microsoft SQL Server

IBM DB2

Sybase ASE

Sybase SQL Anywhere

Step 1: Delete the existing trail.

Step 2: Create a new trail by configuring the Audit Vault Agent installed on the new host machine.

Remote

TABLE

Oracle Database

Oracle Key Vault

Sybase ASE

Step 1: Update the target Connection Details.

Step 2: There is no need to delete and recreate the trails. Stop the existing trail.

Step 3: Start the trail.

Remote

DIRECTORY

SYSLOG

EVENT LOG

TRANSACTION LOG

Oracle Database

Step 1: Update the target Connection Details.

Step 2: In case the trail location has changed, then delete the existing trail.

Step 3: Create a new trail and specify the new trail location.

Remote

DIRECTORY

SYSLOG

EVENT LOG

TRANSACTION LOG

MySQL

Microsoft SQL Server

PostgreSQL

IBM DB2

Quick JSON

Oracle Solaris

Linux

IBM AIX

Microsoft Windows

Microsoft Active Directory

Oracle ACFS

Step 1: In case the trail location has changed, then delete the existing trail.

Step 2: Create a new trail and specify the new trail location.

Related Topics

7.3 Preparing Targets for Audit Data Collection

Learn about preparing targets for audit data collection.

7.3.1 Using an NTP Service to Set Time on Targets

Learn how to use NTP Service to configure time settings on targets.

Oracle recommends that you use an It is recommended that you also use a Network Time Protocol (NTP) service on both your targets and the Audit Vault server. This will help to avoid confusion on timestamps on the alerts raised by the Audit Vault Server.

See Also:

Specifying the Server Date, Time, and Keyboard Settings for instructions on using an NTP server to set time for the Audit Vault Server.

7.3.2 Ensuring that Auditing is Enabled on the Target

Learn how to enable auditing.

To collect audit data from a target, you must ensure that auditing is enabled on that target and, where applicable, note the type of auditing that the target is using. Check the product documentation for your target type for details.

To check if auditing is enabled on an Oracle Database target:

  1. Log in to the Oracle database as a user with administrative privileges. For example:
    sqlplus trbokuksa
Enter password: password
Connected.
  2. Run the following command: 
    SHOW PARAMETER AUDIT_TRAIL

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
audit_trail                          string      DB
  3. If the output of the SHOW PARAMETER command is NONE or if it is an auditing value that you want to change, then you can change the setting as follows.

    For example, if you want to change to XML, and if you are using a server parameter file, you would enter the following: 

    CONNECT SYS/AS SYSDBA
Enter password: password

ALTER SYSTEM SET AUDIT_TRAIL=XML SCOPE=SPFILE;
System altered.

SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

STARTUP
ORACLE instance started.
  4. Make a note of the audit trail setting.

    You will need this information when you configure the audit trail in Oracle Audit Vault and Database Firewall.

7.3.3 Setting User Account Privileges on Targets

Some target types require credentials for Oracle Audit Vault and Database Firewall (Oracle AVDF) to access them.

If you plan to collect audit data from a target, perform stored procedure auditing (SPA) or entitlements auditing, or monitor native network encrypted traffic for Oracle Database, then you must create a user account on the target with the appropriate privileges to enable Oracle AVDF to access the required data.

For database targets, Oracle AVDF provides scripts to configure user account privileges for database target types. For Oracle Database targets, you can download the setup scripts from the Audit Vault Server console by clicking the Target Setup Script button on the Targets tab.

For non-database targets, create a user that has the appropriate privileges to access the audit trail. For example, for a Windows target, this user must have administrative permissions to read the security log.

Note:

Oracle AVDF does not accept user names with quotation marks. For example, "J'Smith" is not a valid user name for an Oracle AVDF user account on targets.

See Scripts for Oracle AVDF Account Privileges on Targets for information on the scripts to configure user account privileges for database target types.

7.3.4 Scheduling Audit Trail Cleanup

Learn about scheduling audit trail cleanup.

Oracle AVDF supports audit trail cleanup for Oracle Database, Microsoft SQL Server, IBM DB2, and MySQL.

See Also:

Audit Trail Cleanup

7.4 Preparing Targets for Use With Global Sets (Previously Called Data Discovery)

Starting with Oracle AVDF 20.9, you can create global sets of privileged users and sensitive objects on your Oracle Databases as part of Data Discovery (Oracle AVDF 20.9) or Global Sets (Oracle AVDF 20.10 and later).

In order to create these global sets, privileged users and sensitive objects need to be discovered on your Oracle Database by adding privileges to the database user and gathering statistics, respectively.

Related Topics

7.4.1 Prerequisites for Enabling Global Sets or Data Discovery

Complete these prerequisites before enabling Global Sets or Data Discovery in Oracle Audit Vault and Database Firewall.

7.4.2 Managing Privileges for Discovering Privileged Users

Before global Privileged User Sets can be used, download and run the target setup script on the Oracle Database to add privileges to the user as follows.

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 script on the Target Oracle database to add privileges to the user.

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

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

Enabling User Privileges for Oracle Database for Discovering Privileged Users

To add the required privileges, run the setup scripts from the previous steps:

Note:

The downloaded zip file contains SQL scripts for several functions, this workflow is only to enable the discovery of privileged user.

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

    SQL> CONNECT SYS / AS SYSDBA
  2. Run the following script:
    SQL> @oracle_user_setup.sql username DBSAT_DISCOVERY

Revoking User Privileges for Oracle Database for Discovering Privileged Users

To disable discovery of privileged users for the target, revoke the privileges of the user:

  1. Connect to the database as the SYS user with the SYSDBA privilege.
  2. Run the following script:
    SQL> @oracle_drop_db_permissions.sql username DBSAT_DISCOVERY

7.4.3 Managing Statistics Gathering for Discovering Sensitive Objects

Before global Sensitive Object Sets can be used, statistics need to be gathered on the Oracle Database.

  1. Connect as the SYS user with the SYSDBA privilege. For example:
    SQL> CONNECT SYS / AS SYSDBA
  2. Run this command:
    exec DBMS_STATS.GATHER_DATABASE_STATS

    Alternatively, you can run the DBMS_STATS procedure for all objects in a particular schema: 

    exec DBMS_STATS.GATHER_SCHEMA_STATS(schema_name);

    Note:

    To invoke this procedure you must be the owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you must be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.

7.5 Configuring and Managing Audit Trail Collection

Learn about configuring and managing audit trail collection.

7.5.1 Prerequisites for Adding Audit Trails in Oracle Audit Vault Server

Complete these prerequisites before adding audit trails in Oracle Audit Vault Server.

7.5.2 Adding Audit Trails with Agentless Collection

Starting in Oracle AVDF 20.9, you can use agentless collection instead of the Audit Vault Agent for up to 20 Oracle Database table audit trails. Starting in Oracle AVDF 20.10, you can also use agentless collection for Microsoft SQL Server directory audit trails for .sqlaudit and .xel (extended events). The total number of audit trails for agentless collection should not exceed 20.

With agentless collection, you use the agentless collection service that comes with the Audit Vault Server instead of deploying the Audit Vault Agent on the target host machines. The agentless collection service is automatically installed when you install the Audit Vault Server or when you update Oracle AVDF to release 20.9 or later.

Note:

You can use agentless collection only on a standalone, unpaired Audit Vault Server. If the Audit Vault Server is paired for high availability, the agentless collection service stops running. If you unpair the Audit Vault Server, the agentless collection service automatically starts running again.

Prerequisites

  1. Update Oracle AVDF to the latest release update based on the following requirements:

    • For Oracle Database table audit trails, update to Oracle AVDF 20.9 or later.
    • For Microsoft SQL Server directory audit trails for .sqlaudit and .xel (extended events) targets, update to Oracle AVDF 20.10 or later.

    For update instructions, see one of the following chapters:

  2. Ensure that the Audit Vault Server is not paired for high availability. To unpair the Audit Vault server, see Unpair Primary and Standby Audit Vault Servers.
  3. Register the Oracle Database or Microsoft SQL Server target. See Registering Targets.
  4. Prepare the target. See Preparing Targets for Audit Data Collection.

Agentless Collection Support for Microsoft SQL Server

Starting with Oracle AVDF 20.10, ensure that Microsoft SQL Server targets meet the following conditions for agentless collection support:

  • Agentless and remote collection are supported for the following versions of Microsoft SQL Server:

    • .sqlaudit audit events: All supported versions of Microsoft SQL Server.
    • .xel audit events: Microsoft SQL Server 2017 and later.

  • Agentless and remote collection are not supported in Microsoft SQL Server clustered environments.

  • Agentless and remote collection may be slow when there's a large number of files. In this case, Oracle recommends that you use local, agent-based collection.

  • Audit trail cleanup (ATC) is not supported for agentless and remote collection.

    You need to set up the file rollover count properly so that the audit file is purged automatically and doesn't lose audit data. See the Microsoft SQL Server documentation for more information about the file rollover count.

Procedure

  1. Click the Targets tab.
  2. Click the link for the Oracle Database or Microsoft SQL Server target for which you want to add the audit trail.
  3. Under Audit Data Collection, click Add.

  4. For Audit Trail Type, select one of the following values:

    • For Oracle Database, select TABLE.
    • For Microsoft SQL Server, select DIRECTORY.

    For details on these audit trail types, see the plug-in reference:

  5. In the Trail Location field, enter or select the location of the audit trail on the target computer.

    For example:

    • Oracle Database example: UNIFIED_AUDIT_TRAIL
    • Microsoft SQL Server examples: directory_path\*.sqlaudit or directory_path\*.xel
  6. Select Agentless Collection. This option is only visible for Oracle Database TABLE trails and Microsoft SQL Server DIRECTORY trails.
  7. Click Save.

    The agent name for the audit trail appears as Agentless Collection on the Audit Trails and Targets pages.

7.5.3 Adding Audit Trails with Agent-Based Collection

To begin collecting audit data with the Audit Vault Agent, configure an audit trail for each target that's registered on the Audit Vault Server and then start the audit trail collection.

Note:

When using the Audit Vault Agent to collect directory trails, the agent must be installed on the same host that contains the directory.

  1. Create a new target.
  2. Click the Targets tab.
  3. Click the link for the target for which you want to add the audit trail.
  4. Under Audit Data Collection, click Add.
  5. For Audit Trail Type, select one of the following trail types:.
    • CUSTOM
    • DIRECTORY
    • EVENT LOG
    • NETWORK

      For monitoring multiple nodes of an Exadata or RAC database using network trail, create a separate target for each node.

    • SYSLOG

      This trail type can collect from syslog or rsyslog files. If both are present, you must provide the exact trail location in the next step if you want to collect audit data from rsyslog files.

      Note:

      Ensure that records generated by rsyslog have the same time zone information as the Audit Vault Agent that's running on the collection host.
    • TABLE
    • TRANSACTION LOG

    Note:

    For details on which types of audit trails can be collected for each target type, see Table C-22.

    For complete details on all audit trail types, see Plug-ins That are Shipped with Oracle Audit Vault and Database Firewall.

  6. In Trail Location, enter the location of the audit trail on the target computer. The trail location depends on the type of target.

    For example, for Oracle Database, the trail location might be unified_audit_trail.

    For supported trail locations, see Audit Trail Locations.

    Note:

    If you select DIRECTORY or TRANSACTION LOG for Audit Trail Type, then the trail location must be a directory mask.
  7. Select Agent-based Collection if it's visible. If it's not visible, then agent-based collection is used by default for the audit trail.
  8. For Agent Host, select the host computer where the Audit Vault Agent is deployed.

  9. Click Save.

    The audit trail should now appear on the Audit Trails tab. The collection status is stopped (a red circle) initially. The audit trail starts automatically shortly after you add it.

See Also:

About Plug-ins

7.5.4 Stopping, Starting, and Autostart of Audit Trails in Oracle Audit Vault Server

Lean about stopping, starting, and setting up autostart of audit trails in Oracle Audit Vault Server.

An audit trail starts automatically shortly after you add it. To start an audit trail, the Audit Vault Agent must be running on a host computer.

Audit trails that are started will automatically restart if the Audit Vault Agent is restarted, or updated due to an Audit Vault Server update.

An audit trail can go down at times such as when the target goes down temporarily. With Autostart, the system automatically attempts to restart an audit trail if it goes down. Autostart is normally enabled unless you have manually stopped the trail. You can set parameters on when and how many times the system attempts Autostart using the AVCLI utility.

Starting with AVDF 20.10, audit trails are monitored daily. Alerts are generated and email notifications are sent if audit trail is in STOPPED_ERROR state even after 20 retries.

Starting with AVDF 20.10, network trails are monitored hourly. Alerts are generated and email notifications are sent out if network trail is in STOPPED_ERROR state.

To start or stop audit trail collection for a target:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab. The Targets tab in the left navigation menu is selected by default.
  3. Select the specific target by clicking on the name.
  4. Under the Audit Data Collection section, select the targets that have the audit trails that you want to start or stop.
  5. Click Stop or Start accordingly.

    Note:

    • You cannot start an audit trail while the Audit Vault Agent is updating.
    • If your environment has a large number of audit files to collect, for example one million or more, then the audit trail may take a few minutes to start.

    See Also:

7.5.5 Checking the Status of Trail Collection on the Audit Vault Server

Learn about checking the status trail collection in Audit Vault Server.

  1. Log in to the Audit Vault Server console as an administrator.

  2. Click the Targets tab. The Targets tab in the left navigation menu is selected by default.

  3. Click Audit Trails tab in the left navigation menu.

    It lists targets that have audit trails configured. Check the Collection Status column. The status can be one of the following:

    • Idle - Trail is up and running, no new audit data to collect. In this state, the trail is waiting for the target to generate new audit data.

    • Starting - Collection process is starting.

    • Collecting - Trail is currently actively collecting audit data.

    • Stopping - Collection process is stopping.

    • Stopped - Trail is currently stopped.

    • Recovering - Trail is recovering after it has been stopped previously. The trail was stopped before updating the checkpoint for the records collected. In the recovery state, the trail reads records starting from the current checkpoint and filter out the duplicate records which were already read. The recovery state can take a while depending on the server load.

    • Unreachable - A heartbeat timeout has occurred, indicating that a heartbeat message has not been received from the trail in the last 30 minutes. This status is temporary unless the trail has crashed. The Audit Vault Server checks the status of the audit trail. It attempts to check the status 5 times (by default) in Oracle AVDF releases 20.1 to 20.6. In Oracle AVDF release 20.7 and onwards, the Audit Vault Server attempts 20 times (by default) to reach the audit trail before concluding it is Unreachable.

    • Archive data files are required (link) - If you see this link, it means a new audit trail contains expired audit records that must be archived, and that the required archive data files are not available.

    The Trail Autostart Details column indicates whether autostart is enabled for a trail, and whether there have been attempts to restart a failed audit trail (for example, if a target goes down temporarily).

Tip: You can sort and filter the audit trail list.

Note:

  • To view audit trails status for a specific agent host, click the name of the trail.
  • If an audit trail fails to start, then you can get more information by looking at the Error Message column.

See Also:

Handling New Audit Trails with Expired Audit Records

Check the Audit Trail Status with SQL*Plus

To check the audit trail status with SQL*Plus, query avsys.audit_trail_view.

For example:

SQL> SELECT location, host_name, status FROM audit_trail_view
LOCATION                                                        HOST_NAME            STATUS
--------------------------------------------------------------- -------------------- -----------
unified_audit_trail                                             xxxxxxxx             IDLE
sys.unified_audit_trail_DELETED_2016-06-28 11:56:25.203 +00:00  xxxx                 STOPPED
/var/log/audit/audit.log_DELETED_2016-06-29 08:49:04.446 +00:00 xxxx                 STOPPED
/var/log/audit_DELETED_2016-06-29 08:53:00.906 +00:00           xxxx                 STOPPED
dvsys.audit_trail$                                              xxxx                 IDLE

Note:

If the AVSYS account is locked or the password is unknown, see Unlocking and Locking the AVSYS User.

Check the Audit Trail Status with AVCLI

To check the audit trail status with AVCLI, use the LIST TRAIL FOR SECURED TARGET command.

For example:

AVCLI> LIST TRAIL FOR SECURED TARGET TARGET_NAME; 
-----------------------------------------------------------------------------------------------------------------------------------------|
AUDIT_TRAIL_TYPE | HOST | LOCATION | STATUS | REQUEST_STATUS |  AUTO_START_STATUS | AUTOSTART_ATTEMPTS | LAST_START_TIME | ERROR_MESSAGE |
=========================================================================================================================================|
TABLE | xxx.xxx.com | UNIFIED_AUDIT_TRAIL | STARTING | | ENABLED | 3 | 2016-07-28 | 20:06:42.802312 GMT ||
------------------------------------------------------------------------------------------------------------------------------------------

Checking Downtime History of the Trail

Audit Vault Server console displays the current status of the trail. Starting Oracle AVDF 20.6, the Audit Vault Server console maintains record of the trail downtime. It also displays the reason for the downtime. This information is available in the Downtime Report. This report contains downtime information of every trail and a cumulative downtime report of all the trails in the Audit Vault Server. It captures the intervals during which the specific trail may have gone down either due to an error, or if it was manually stopped through the Audit Vault Server console, or it had changed status to one of the following:

Note:

Not all the status information is available in the reports.

To capture downtime report for the trail and to view the history of the trail, follow these steps:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab.
  3. Click Audit Trails in the left navigation menu.
  4. Select the trails for which the downtime report needs to be generated.
  5. Click Downtime button. The downtime report for the selected trails is displayed. Use the filter option, download the report, or click the back button to navigate to the Audit Trails tab.

The downtime of the Audit Vault Agent, the specific time as to when the Agent went down, the duration for which the data has not been captured, and the reason for the Agent going down is also made available in the reports.

Note:

  • This downtime data is available, archived, and purged like any other data managed by Oracle AVDF. By default in release 20.6, the downtime data is available for a period of one month and is purged after that.
  • The history of trails configured prior to upgrade to Oracle AVDF 20.6 is not captured or available.
  • The report for new trails configured after upgrade to Oracle AVDF 20.6 is available.
  • Data for the trails configured after upgrade to Oracle AVDF 20.6 is available from the time the trail was started.

7.5.6 Audit Collection Best Practices

Follow these best practices for audit collection.

  • Periodically purge the records that have already been read by the audit trail.

    For some targets, the Audit Vault Agent contains scripts for cleanup. See Audit Trail Cleanup for more information. If there are remaining targets where the records have already been read by the audit trail, you can manually clean up the audit trail.

    If you don't purge the records, the following issues might occur:

    • There may be too many records (more than a million) in a table audit trail. This can slow down audit data collection and reduce the throughput of the table audit trail.
    • For directory trails, there may be too many files (more than a thousand) with a size of more than 1 GB. This can slow down audit data collection and reduce throughput of the directory trail.
  • Ensure that the directories of transaction log audit trails, directory audit trails, and Oracle GoldenGate are access controlled.

  • For directory and transaction log audit trails, if the agent user does not have read permission on audit files, then provide the agent user with read permission on the audit files by running the following commands.

    Operating System Command
    Linux 
    setfacl -Rm u:<agent user name>:r-x <audit data directory>
    setfacl -Rdm u:<agent user name>:r-- <audit data directory>
    Solaris 
    chmod A+user:<agent user name>:rx:fd:allow <audit data directory>
    chmod A+user:<agent user name>:r:allow <audit data directory>/*
    AIX/HP-UX Add the agent user to the group that has read permission on the audit data.

7.5.7 Handling New Audit Trails with Expired Audit Records

Learn about handling new audit trails with expired audit records.

With established audit trail collection, audit data is retained in Oracle Audit Vault Server for the Months Online period of a retention (or archiving) policy. After this period, the data files are made available for archiving. The data is then kept in archives for the Months Archived period of the retention policy, and is available to retrieve to the Audit Vault Server during that period.

However, when you add a new audit trail to an existing target, the audit data collected may contain records that fall into the Months Archived period in the retention policy assigned to this target. That is, the online period for these audit records has expired and they should be archived according to the retention policy.

In this case, Oracle Audit Vault and Database Firewall attempts to automatically archive these expired records during the new audit trail collection. In some cases, you may need to make the archive data files available in order for the audit trail to complete collection.

When collecting a new audit trail for an existing target, follow these instruction if you see an Archive data files are required link in the Collection Status of the audit trail.

To make archive data files accessible:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab, and then click Audit Trails.
  3. In the Collection Status column, if applicable, click the Archive data files are required link.

    The required archive data files are listed.

  4. Check that required data files are available in the archive location, and that the connection to the location is set up correctly.
  5. After you make the required data files available, restart this audit trail.

See Also:

7.5.8 Deleting an Audit Trail

Learn how to delete an audit trail.

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab.
  3. In the left navigational menu, click Audit Trails.
  4. Select the audit trails that you want to delete and then, if necessary, click Stop to stop the audit trail.
  5. Select the audit trails that you want to delete, and then click Delete.

7.5.9 Converting Audit Record Formats for Collection

You can use special tools to convert audit record formats so that Audit Vault and Database Firewall can collect these records.

7.5.9.1 Prerequisites for Converting Oracle Audit Vault Record MySQL Formats

Learn about the prerequsites for converting Oracle Audit Vault record MySQL formats.

Before you begin the format conversion process, ensure that you have completed the following tasks.

7.5.9.2 Running the XML Transformation Utility for MySQL Audit Formats

Learn how to run the XML transformation utility for MySQL audit formats.

Audit records of some databases are in the format that cannot be read directly by Oracle Audit Vault and Database Firewall collectors. Such audit records are first converted to a readable format and then collected.
For MySQL targets, Oracle Audit Vault and Database Firewall provides a utility to transform the MySQL XML audit format log file into a required format for audit data collection. You must run this utility on the MySQL host machine before adding an audit trail.

Note:

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

To run the XML Transformation Utility:

  1. On the MySQL host computer, go to the directory AGENT_HOME/av/plugins/ com.oracle.av.plugin.mysql/bin/
  2. Run the following command:
    MySQLTransformationUtility.bat inputPath=path_to_log_folder 
outputPath=path_to_converted_xml agentHome=path_to_AGENT_HOME 
interval=interval_in_minutes xslPath=XSL_file_path securedTargetName=registered_secured_target_name

    This command contains the following variables:

    • path_to_log_folder:

      • For MySQL version prior to 5.7.21: The path to the MySQL log folder listed in my.ini

      • For MySQL version 5.7.21 and later: The path to the MySQL log folder listed in my.ini\<audit file name>.*.log

    • path_to_converted_xml - The path to the folder where the converted XML files will reside. You will use this path as the Trail Location when creating the audit trail for this MySQL target in the Audit Vault Server, or when starting audit trail collection using the AVCLI command line.

    • path_to_AGENT_HOME - The path to the installation directory of the Audit Vault Agent

    • interval_in_minutes - (Optional) The waiting time, in minutes, between two transformation operations. If not specified, the default it is 60 minutes. To run the transformation utility once, specify -ve for this argument.

    • XSL_file_path - (Optional) The path to the XSL file to use for the transformation.

    • registered_secured_target_name - The name of the MySQL target registered in the Audit Vault Server.

    Example:

    For MySQL version prior to 5.7.21: MySQLTransformationUtility.bat inputPath=D:\MySQLLog outputPath=D:\ConvertedXML agentHome=E:\MySQLCollector interval=1 securedTargetName=MYSQL_DEV

    For MySQL version 5.7.21 and later: MySQLTransformationUtility.bat inputPath=D:\MySQLLog\audit.*.log outputPath=D:\ConvertedXML agentHome=E:\MySQLCollector interval=1 securedTargetName=MYSQL_DEV

7.5.9.3 Converting Binary Audit Files to ASCII Format for IBM DB2

Learn about converting binary audit files to ASCII format for IBM DB2.

IBM DB2 creates its audit log files in a binary file format that is separate from the DB2 database. For IBM DB2 targets, you must convert the binary file to an ASCII file before each time you collect audit data (start an audit trail) for a DB2 database, using the script instructions in this section.
Ideally, schedule the script to run periodically. If the script finds older text files that have already been collected by the DB2 audit trail, then the script deletes them. It creates a new, timestamped ASCII text file each time you run it. Optionally, you can set the script to purge the output audit files.

Note:

It is recommended that you extract audit log files for each database and each instance in a separate directory. You must configure separate audit trails for each database and each instance in Oracle AVDF.

In case of multiple instances, if the instances are not owned by the same user, it is recommended to extract audit data corresponding to each instance in a separate location. To collect the audit data, use one agent per instance. Ensure that the agent user is same as the instance user.

  1. Identify a user who has privileges to run the db2audit command.

    This user will extract the binary files to the text files.

  2. This user must have execute privileges to run the conversion script from the Oracle AVDF directory. The script name is DB295ExtractionUtil (for Microsoft Windows, this file is called DB295ExtractionUtil.bat.)
  3. This user identified in the initial step, must have read permission for the $AGENT_HOME/av/atc directory and its contents.
  4. In the server where you installed the IBM DB2 database, open a shell as the SYSADM DB2 user.
  5. Set the following variables:

    • AGENT_HOME (this is the Audit Vault Agent installation directory)

    • DB2AUDIT_HOME (this directory points to the main directory that contains the db2audit command)

  6. Ensure that the Oracle AVDF owner of the agent process has read permissions for the audit text files that will be generated by the extraction utility.
  7. Log in as the DB2 user that you identified in IBM DB2 for LUW Setup Scripts.
  8. Run one of the following scripts, depending on the version of DB2 that you have installed:

    • For supported DB2 databases:

      DB295ExtractionUtil -archivepath archive_path -extractionpath extraction_path -audittrailcleanup yes/no -databasename database_name

      In this specification:

      • archive_path: This is DB2 archive path configured using the db2audit utility.

      • extraction_path: This is the directory where the DB2 extraction utility places the converted ASCII text file. This file is created in either the db2audit.instance.log.0.YYYYDDMMHHMMSS.out or db2audit.db.database_name.log.0.20111104015353.out format.

      • audittrailcleanup yes/no: Enter yes or no, to enable or disable the audit trail cleanup. Entering yes deletes the archived IBM DB2 audit files that were collected by the Oracle AVDF DB2 audit trail. If you omit this value, then the default is no.

      • database_name: (Optional) This is the name, or names separated by spaces, of the database(s) that contain the audit records.

        The utility creates a separate ASCII file for each database named in the command. If this parameter is omitted, then the utility converts the instance binary to an ASCII file. This parameter enables you to collect categories of audit records such as object maintenance (objmaint) records, which capture the creation and dropping of tables.

        Important: If you enter more than one database name in this command, be sure to put the ASCII file for each database in a separate directory after you run the command.

      • audittrailcleanup yes/no: Enter yes or no, to enable or disable the audit trail cleanup. Entering yes deletes the archived IBM DB2 audit files that were collected by the Oracle AVDF DB2 audit trail. If you omit this value, then the default is no.

    • Support for IBM DB2 Database Partition Feature

      Starting Oracle AVDF 20.5, IBM DB2 Database Partition Feature is supported on Linux and AIX platforms. This functionality is supported for DB2 version 10.5 and later. The Database Partition functionality is not supported on Windows platform.

      Specify the following parameters in the DB295ExtractionUtil script:

      • databasepartition yes/no: (Optional) Enter yes if current DB2 setup has Database Partition Feature setup, else enter no. If you omit this value, then the default is no.

      • nodes: (Optional) This is the name of the node (or multiple nodes) separated by spaces, of the DB2 Database Partition Feature setup.

    Note:

    • If the archive path and extraction path are on the shared location, that is accessible by all the nodes in the Database Partition Feature (DPF) setup, then you can exclude the nodes input parameter. The script generates the archive data and audit data for all the nodes in the Database Partition Feature setup, in the shared location.

    • If the archive path and extraction path are host machine specific locations, that are accessible only by the nodes on that machine, then it is recommended to run the script on every machine of the Database Partition Feature setup. Include the nodes input parameter with only the nodes present on the specific machine.

      For example: Machine 1 has Node 0 and Node 1. Machine 2 has Node 2 and Node 3. The script must be run on Machine 1 with parameters -databasepartition yes -nodes 0 1. The script must be run on Machine 2 with parameters -databasepartition yes -nodes 2 3.

    Example 1: The following command creates an ASCII file for the TOOLSDB database, places the file in the /home/extract_dir directory, and deletes the archive files after audit data is collected: 

    DB295ExtractionUtil -archivepath /home/archive_dir -extractionpath /home/extract_dir -audittrailcleanup yes -databasename TOOLSDB

    Example 2: The following command creates an ASCII file for the database instance, places the file in the /home/extract_dir directory, and deletes the archive files after audit data is collected: 

    DB295ExtractionUtil -archivepath /home/archive_dir -extractionpath /home/extract_dir -audittrailcleanup yes

    Example 3: The following command creates an ASCII file for all the nodes of the database instance with Database Partition Feature setup, places the file in the /home/extract_dir directory, and deletes the archive files after audit data is collected: 

    DB295ExtractionUtil -archivepath /home/archive_dir -extractionpath /home/extract_dir -audittrailcleanup yes -databasepartition yes

    Example 4: The following command creates an ASCII file for the specified nodes (0, 1, and 2) of the database instance with Database Partition Feature setup, places the file in the /home/extract_dir directory, and deletes the archive files after audit data is collected: 

    DB295ExtractionUtil -archivepath /home/archive_dir -extractionpath /home/extract_dir -audittrailcleanup yes -databasepartition yes -nodes 0 1 2

    Example 5: The following command creates an ASCII file for all the nodes of the TOOLSDB database with Database Partition Feature setup, places the file in the /home/extract_dir directory, and deletes the archive files after audit data is collected: 

    DB295ExtractionUtil -archivepath /home/archive_dir -extractionpath /home/extract_dir -audittrailcleanup yes -databasename TOOLSDB -databasepartition yes

    Example 6: The following command creates an ASCII file for the specified nodes (0, 1, and 2) of the TOOLSDB database with Database Partition Feature setup, places the file in the /home/extract_dir directory, and deletes the archive files after audit data is collected: 

    DB295ExtractionUtil -archivepath /home/archive_dir -extractionpath /home/extract_dir -audittrailcleanup yes -databasename TOOLSDB -databasepartition yes -nodes 0 1 2

To schedule the script to run automatically, follow these guidelines:

  • UNIX: Use the crontab UNIX utility. Provide the same information that you would provide using the parameters described previously when you normally run the script.

  • Microsoft Windows: Use the Windows Scheduler. Provide the archive directory path (for release 9.5 databases only), extraction path, and target database name in the scheduled task.

7.5.10 Configuring Audit Trail Collection for Oracle Real Application Clusters

You can configure audit trail collection for Oracle Real Application Clusters (Oracle RAC).

Configure a SCAN listener for the RAC and use the SCAN listener IP as the single IP during target registration.

To configure Audit Trail collection for Oracle Real Application Clusters (RAC), follow these guidelines.

Audit Trail Type Number of Audit Trails

TABLE

To configure table trail audit data collection from Oracle RAC environment, 1 audit trail is sufficient.

DIRECTORY

To configure directory audit data collection from Oracle RAC environment, separate audit trails are required. The trail location must be different directories in the shared storage of the Oracle RAC environment.

TRANSACTION LOG

To configure Transaction Log audit data collection from Oracle RAC environment, 1 audit trail is sufficient.

See Also:

Adding Audit Trails with Agent-Based Collection to configure an audit trail.

7.5.11 Configuring Audit Trail Collection for CDBs and PDBs

Learn about configuring audit trail collection for CDBs and PDBs.

Oracle Database can work as Container Database (CDB) or Pluggable Databases (PDB). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c are non-CDB.

The PDB and CDB can be registered as targets. Oracle Audit Vault and Database Firewall supports CDB and PDB level audit collection. To collect audit data from multiple PDB instances within a CDB, adopt either one of the following approaches:

Approach 1: Create a separate target for each PDB instance and create audit trail for each PDB target, which collects data from UNIFIED_AUDIT_TRAIL table.

Approach 2: Create one target for the CDB and create audit trail which collects data from CDB_UNIFIED_AUDIT_TRAIL table.

Note:

  • In Oracle AVDF 20 data will be collected from CDB_UNIFIED_AUDIT_TRAIL, only if all the PDBs are up and running.
  • CDB_UNIFIED_AUDIT_TRAIL provides audit records from all PDB instances in a multitenant environment. The performance of audit collection from CDB_UNIFIED_AUDIT_TRAIL is lower than audit collection from UNIFIED_AUDIT_TRAIL of every PDB instance. If the number of audit records generated per day in CDB_UNIFIED_AUDIT_TRAIL is higher than 8 million, then configure audit collection from UNIFIED_AUDIT_TRAIL of every PDB instance.

To configure Audit Trail collection for CDB or PDB, follow these guidelines:

Audit Trail Type Guidelines

TABLE

  • Audit records specific to CDB activities can be collected from UNIFIED_AUDIT_TRAIL table of the CDB target. Audit records corresponding to CDB activities and all PDB activities can be collected from CDB_UNIFIED_AUDIT_TRAIL.

  • Every PDB stores it's own audit data in it's own UNIFIED_AUDIT_TRAIL table which does not contain audit data of other PDBs. Separate audit trails can be configured for the PDB target to collect data corresponding to that specific PDB only.

  • For PDB target, collection from CDB_UNIFIED_AUDIT_TRAIL is not supported.

Note:

Audit collection from CDB_UNIFIED_AUDIT_TRAIL is supported in release 20.1.0.0.0.

DIRECTORY

  • Audit from directory trail can be collected for CDB, by providing directory trail location as <value of AUDIT_FILE_DEST> (database parameter).

  • Audit from directory trail can be collected for each PDB, by providing directory trail location as <value of AUDIT_FILE_DEST>/<GUID of the PDB>.

Note:

If you are using a multitenant container database (CDB) in Oracle Database 12c, then for a CDB you must register a target for the CDB as well as for every PDB.

CDB Trail Enhancement in Oracle AVDF 20.2

In Oracle AVDF 20.2.0.0.0 (or 20 RU2), audit data is collected from CDB_UNIFIED_AUDIT_TRAIL for PDBs that are up and running, even if some of the PDBs are down. When a PDB is down, the data corresponding to the PDB with status down is not visible in CDB_UNIFIED_AUDIT_TRAIL. When the PDB which was earlier down comes up, then the data corresponding to the specific PDB is collected from CDB_UNIFIED_AUDIT_TRAIL.

If any PDB is down, then the last archive timestamp is not set on the CDB_UNIFIED_AUDIT_TRAIL, even if other PDBs are up and running. Hence those records that have already been read by the audit trail are not purged from the CDB_UNIFIED_AUDIT_TRAIL and this can lead to severe performance degradation of the audit trail.

If there are any PDBs that are permanently taken down or taken down for few days, then they must be specified in the AV.COLLECTOR.IGNORE_PDB_IF_DOWN_LIST target attribute. The value of the AV.COLLECTOR.IGNORE_PDB_IF_DOWN_LIST target attribute is a list of PDBs separated by a colon. For example, PDB1:PDB2:PDB5.

If a PDB is down, but is present in the AV.COLLECTOR.IGNORE_PDB_IF_DOWN_LIST, then the audit trail ignores the specific PDB if it is down and sets the last archive timestamp on the CDB_UNIFIED_AUDIT_TRAIL if all the other PDBs are up and running.

Audit data collection from PDBs which are mentioned in the AV.COLLECTOR.IGNORE_PDB_IF_DOWN_LIST is not completely accurate. Some of the audit records for these PDBs may be missed. It is also possible that the data is purged from these PDBs, depending on when the last archive timestamp was set.

If there is a PDB with status down, that was present in the AV.COLLECTOR.IGNORE_PDB_IF_DOWN_LIST, and has to brought up, then first remove it from AV.COLLECTOR.IGNORE_PDB_IF_DOWN_LIST. Wait for 10 minutes so that the audit trail reads and processes the updated AV.COLLECTOR.IGNORE_PDB_IF_DOWN_LIST attribute. After approximately 10 minutes, bring up the PDB. This ensures that all future records are successfully collected from this PDB without any data loss.

See Also:

Adding Audit Trails with Agent-Based Collection to configure an audit trail.

7.5.12 Migrating Audit Trails from Agentless Collection to Agent-Based Collection

In Oracle AVDF 20.9 and 20.10, use this procedure to migrate audit trails from agentless collection to agent-based collection (for example, if you decide to pair the Audit Vault Server for high availability).

  1. Log in to the Audit Vault Console as an administrator.

  2. Stop the audit trail that you need to migrate.

    See Stopping, Starting, and Autostart of Audit Trails in Oracle Audit Vault Server.

  3. On the Audit Trails page, record the time in the Data Collected Until column for the audit trail.

    This indicates the time and date until which audit records have been collected.

  4. On the target database or machine, purge the audit records that have already been collected.

    See Audit Trail Cleanup.

  5. Delete the audit trail that you need to migrate.

    See Deleting an Audit Trail.

  6. Create a new audit trail for the target and select Agent-based Collection when adding the audit trail.

    See Adding Audit Trails with Agent-Based Collection.

Note:

If records that have already been collected by the agentless collection service are not deleted from the target, then the newly created agent-based audit trail will collect duplicate records.

Even after following the preceding steps, there's a possibility that a small set of duplicate data will be collected.

7.5.13 Migrating Audit Trails to Another Audit Vault Agent

Starting in Oracle AVDF 20.11, you can use the UI console or AVCLI commands to migrate an audit trail from one Audit Vault Agent to another. The same process can also be used to migrate trails from agent-based collection to agentless collection and vice versa. This can be beneficial if an Audit Vault Agent is facing CPU or memory shortages due to a large number of audit trails.

  1. Log in to the Audit Vault Server Console as an administrator.

  2. Click the Targets tab.
  3. Click on Audit Trails in the left navigation menu.
  4. Select which audit trail(s) you'd like to move.
  5. Click Move.
  6. Select Agent-based Collection to move the audit trails to another Audit Vault Agent or select Agentless Collection.
  7. If you selected to move the audit trails to another Audit Vault Agent in the previous step, select the Audit Vault Agent.
  8. Click Move.
  1. Login to AVCLI with administrator privileges.

    Logging in to AVCLI

  2. Use the LIST COLLECTION command to see a list of available Audit Vault Agents.

    List Collection for Agent

  3. Use the MOVE COLLECTION FOR SECURED TARGETS command to move the audit trails.

    Move Collection for Secured Targets

Related Topics

7.5.14 Audit Collection Downtime Alerts

Starting in Oracle AVDF 20.10, audit and network trails are monitored frequently and a system alert is generated if the trails are in the STOPPED_ERROR state.

Starting with AVDF 20.10, audit trails are monitored daily. Alerts are generated and email notifications are sent if audit trail is in STOPPED_ERROR state even after 20 retries.

Starting with AVDF 20.10, network trails are monitored hourly. Alerts are generated and email notifications are sent out if network trail is in STOPPED_ERROR state.

Related Topics

7.6 Configuring Database Firewall Monitoring Points

Learn about configuring Database Firewall monitoring points.

Note:

If you are using Transparent Application Failover (TAF), Fast Application Notification (FAN), or the Oracle Notification Service (ONS), then SQL commands are not sent through this channel. There is no need to route them through Oracle Database Firewall. ONS communications bypass the Database Firewall and connect directly to the ONS listener. ONS communications, including destination host and port, are configured in the ons.config properties file located on the ONS server.

7.6.1 About Configuring Database Firewall Monitoring Points for Targets

Learn about configuring Database Firewall monitoring points for the target.

If you are monitoring databases with a Database Firewall, you must configure one monitoring point for every target database that you want to monitor with the firewall. The monitoring point configuration allows you to specify:

  • Firewall monitoring mode
  • Database Firewall used for the monitoring point
  • Identify the target database being monitored
  • Network traffic sources to the database

Oracle Database Firewall can be deployed in the following modes:

  • Monitoring (Out-of-Band) - In this deployment mode, Oracle Database Firewall can monitor and alert on SQL traffic, but cannot block or substitute SQL statements.

  • Monitoring (Host Monitor) - In this deployment mode, Oracle Database Firewall can monitor and alert on SQL traffic, but cannot block or substitute SQL statements.

  • Monitoring / Blocking (Proxy) - In this deployment mode the Database Firewall can monitor, alert, block, and substitute SQL statements.

Before configuring monitoring points, configure network traffic sources as part of database firewall configuration.

See Also:

Configuring the Database Firewall and Its Traffic Sources on Your Network

7.6.2 Creating and Configuring a Database Firewall Monitoring Point

Learn about creating and configuring Database Firewall monitoring points.

Configure Database Firewall monitoring points using the Audit Vault Server console. If you have configured a resilient pair of Audit Vault Servers, configure the monitoring points on the primary server.

Prerequisites

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab.

    The Targets tab in the left navigation menu is selected by default.

  3. Select and click the target you wish to modify.
  4. From the Database Firewall Monitoring section on the main page, click on Add. The Database Firewall Monitor dialog is displayed.
  5. In the Basic tab (for 20.3 or later the name of the tab is Core), enter the name for this Database Firewall instance or select one from the list.
  6. Select a Mode from the following:
    • Monitoring (Out-of-Band)
    • Monitoring (Host Monitor)
    • Monitoring / Blocking (Proxy)
  7. In the Network Interface Card (NIC) field, select from the list of NIC's. You may select a bonded NIC. Select from the list of NIC's based on your monitoring mode:
    • Monitoring (Out-of-Band) - You may select multiple NIC's from the list by holding the Control key on Windows or the Command key on Mac while selecting the NIC's.
    • Monitoring / Blocking (Proxy) - You may select only one NIC from the list.
    • Monitoring (Host Monitor) - See Creating a Monitoring Point for the Host Monitor Agent
  8. Select the Proxy Ports from the list for Monitoring / Blocking (Proxy) mode. This field does not apply to other modes of Database Firewall deployment.
  9. In the Connection Details section, select one or more targets. You can Add the targets from the list.

    Note:

    • Select the RAC Instance/Autonomous DB check box (RAC Instance check box in Oracle AVDF 20.7 and earlier) if the target is Oracle Real Application Clusters. Select this option for Oracle Databases where the monitoring point is in Monitoring / Blocking (Proxy) mode. For Monitoring (Out-of-Band) mode, uncheck this box. Enter the IP address of the individual RAC node in Target Connections field.
    • Select the Network Interface Card and Proxy Ports for Monitoring / Blocking (Proxy) mode. The proxy port is not applicable for monitoring only modes.

    Enter the following information for each network location of the database. Click the Add button to configure the following additional details of the target instance:

    • Host Name / IP Address

      Note:

      For an Oracle RAC target, if the RAC Instance/Autonomous DB check box (RAC Instance check box in Oracle AVDF 20.7 and earlier) is selected, enter the FQDN of the SCAN Listener as the host name.
    • Port
    • Service Name (Optional, for Oracle Database only). SID can be used in this field. To enter multiple service names and/or SIDs, enter a new line for each of them, and then click Add. Multiple entries are allowed for monitoring only mode. For Monitoring / Blocking (Proxy) mode,
      • Oracle AVDF 20.1-20.9: You need to configure a proxy target for each OSN. This is because a single proxy port cannot service multiple OSN's on the same target database. Add more traffic proxy ports as required.
      • Oracle AVDF 20.10 and later: You can use one proxy port and specify multiple OSN's on the target database that are going to be processed. Specify the OSN's in a list delimited by the "|" character. For example, target1|target2|target 3.

    Note:

    Targets are listed here with the policy details. Choose the right deployment mode as per the requirement. Choose Monitoring / Blocking (Proxy) for monitoring, blocking, and alerting. Choose Monitoring (Out-of-Band) or Monitoring (Host Monitor) modes for monitoring and alerting only.
  10. In the Advanced tab, enter the number of Database Firewall Monitor Threads (minimum and default value is 1). This controls the number of traffic handling threads in the Database Firewall monitoring point. Use due caution before modifying the value.
  11. If the target database is an Oracle Database and Mode is selected as Monitoring / Blocking (proxy), the check box for Block Traffic for Unregistered Service Names is available for selection. When this check box is selected, Database Firewall blocks sessions that use service names other than the one that is configured in the target Connection Details section.
  12. If the database client and server are communicating over the TLS protocol, enable TLS.
    With this option, the Database Firewall acts as a TLS proxy. It serves as a TLS server for the database client and acts as a TLS client to the database server. The Database Firewall and the Audit Vault Server have access to the decrypted SQL traffic for further analysis. This feature applies only for Database Firewalls that are deployed in Monitoring / Blocking (Proxy) mode.
    1. Select Enable TLS support.

      Note:

      If you select this option, the Decrypt With Native Network Encryption Key check box is hidden.
    2. In Oracle AVDF release 20.8 and later, select the certificate type under Inbound TLS (From client to DBFW).
      The TLS protocol uses the certificate to authenticate the communication participant. You can use the default certificate that is signed by the Database Firewall or a certificate that is signed by an external Certificate Authority (CA).
    3. If you use the default self-signed certificate, then click Download DBFW Certificate.
      You need to install this certificate on the database client to enable Database Firewall authentication.
    4. If you use the external CA signed certificate, then select the certificate from the drop-down list.
    5. Select the cipher suite level.
      Level 4 - strongest, is the default.
    6. If you don't need database client authentication, then deselect Client Authentication.
      This option is available only for the inbound connection. The outbound connection is always authenticated. If you deselect this option, the Client Trusted Certificates button is disabled.
    7. To manage certificates for client authentication, click Client Trusted Certificates.
    8. Click Choose File and select the certificate on the local machine.
    9. Click Open to load the certificate and add it to the Database Firewall.
      The details of the uploaded certificate appear in the dialog box.
    10. Click Cancel to exit the dialog box.
    11. Follow a similar process to select and manage certificates and the cipher suite level under Outbound TLS (From DBFW to Database).

      To manage the certificates for server authentication, click Database Trusted Certificates.

  13. If Oracle Database uses native network encryption, select Decrypt With Native Network Encryption Key to enable the decryption of traffic.

    Note:

    If the Enable TLS support check box is selected, the Decrypt With Native Network Encryption Key check box is hidden.

    For Oracle AVDF release 20.5 and earler, the check box is Decrypt With Network Native Encryption Key.

    This option also supports the retrieval of session information for Oracle Database. Complete the remaining fields as applicable.

    For Oracle Real Application Clusters (Oracle RAC) targets (if the RAC Instance/Autonomous DB check box is selected on the Core tab), enter the SCAN Listener IP address.

    (In Oracle AVDF 20.7 and earlier, it's the RAC Instance check box, and in Oracle AVDF 20.2 and earlier, it's the Basic tab.)

    For Oracle standalone database targets, enter the IP address of the database listener.

    For Sybase SQL Anywhere (Oracle AVDF 20.1-20.6 only) and Microsoft SQL databases, the field is Retrieve session information from target DB. Retrieving session information is not available for any other non-Oracle database types.

    Note:

    Ensure that the Database Firewall is allowed to make a network connection to the database listener.
  14. Select the check box for Capture Database Response field. If you check this field, the Database Firewall monitors the SQL response from the database. Select Full Error Message check box to capture the database response codes and error codes.
  15. Click Save at the bottom of the dialog to save the configuration of the monitoring point.

    The new monitoring point appears in the list and starts automatically.

    Note:

    Default Database Firewall Policy will be applied for this Database Firewall Monitoring Point. This message is displayed at the bottom of the dialog.
  16. Click Save in the main page.
  17. To stop or restart the monitoring point, select it from the Database Firewall Monitoring section and click Stop or Start.

Note:

When you use the Monitoring / Blocking (Proxy) mode, you must configure any external devices that use IP or MAC address spoofing detection rules such that they ignore database IP or MAC address changes made by the Database Firewall.

See Also:

7.6.3 Modifying a Database Firewall Monitoring Point

After you create a Database Firewall monitoring point, you can modify the settings, enable database response monitoring, monitor native network encrypted traffic for Oracle Database, and host monitoring.

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click Targets tab.
    The Targets tab in the left navigation menu is selected by default.
  3. Select and click on a specific target from the list.
  4. From the Database Firewall Monitoring section on the main page, click the name of the monitoring point you want to modify.
  5. In the Database Firewall Monitor dialog, you can change some of the settings.
  6. Select a different Mode from the following:

    • Monitoring (Out-of-Band) - In this deployment mode, Database Firewall can monitor and alert on SQL traffic, but cannot block or substitute SQL statements.

    • Monitoring / Blocking (Proxy) - In this deployment mode, the Database Firewall can block or substitute SQL statements.

    • Monitoring (Host Monitor) - In this deployment mode, Database Firewall can monitor and alert on SQL traffic, but cannot block or substitute SQL statements.

    Note:

    • For Oracle AVDF 20.2 and earlier, while configuring the Monitoring (Host Monitor) deployment mode, you must select a network interface card that is not used as a Management Interface.
    • For Oracle AVDF release 20.3 and later, while configuring the Monitoring (Host Monitor) deployment mode, you must select a NIC which has an IP address configured. This may be the Management Interface. This is the NIC to which the Host Monitor Agent will connect. When you select Monitoring (Host Monitor) as the deployment type, only those network interface cards which have IP address configured are displayed in the Network Interface Card field.
  7. Select a different traffic source in the field Network Interface Card.
  8. In the Advanced tab, enter the number of Database Firewall Monitor Threads (minimum value is 1). This controls the number of traffic handling threads in the Database Firewall monitoring point. This value should be left at the default (1) unless there are indications that the Database Firewall is unable to cope with the amount of traffic it is receiving.
  9. If the target database is an Oracle Database and Mode is selected as Monitoring / Blocking (proxy), the check box for Block Traffic for Unregistered Service Names is available for selection. When this check box is selected, Database Firewall blocks sessions that use service names other than the one that is configured in the target Connection Details section.
  10. If the database client and server are communicating over the TLS protocol, enable TLS.
    With this option, the Database Firewall acts as a TLS proxy. It serves as a TLS server for the database client and acts as a TLS client to the database server. The Database Firewall and the Audit Vault Server have access to the decrypted SQL traffic for further analysis. This feature applies only for Database Firewalls that are deployed in Monitoring / Blocking (Proxy) mode.
    1. Select Enable TLS support.

      Note:

      If you select this option, the Decrypt With Native Network Encryption Key check box is hidden.
    2. In Oracle AVDF release 20.8 and later, select the certificate type under Inbound TLS (From client to DBFW).
      The TLS protocol uses the certificate to authenticate the communication participant. You can use the default certificate that is signed by the Database Firewall or a certificate that is signed by an external Certificate Authority (CA).
    3. If you use the default self-signed certificate, then click Download DBFW Certificate.
      You need to install this certificate on the database client to enable Database Firewall authentication.
    4. If you use the external CA signed certificate, then select the certificate from the drop-down list.
    5. Select the cipher suite level.
      Level 4 - strongest, is the default.
    6. If you don't need database client authentication, then deselect Client Authentication.
      This option is available only for the inbound connection. The outbound connection is always authenticated. If you deselect this option, the Client Trusted Certificates button is disabled.
    7. To manage certificates for client authentication, click Client Trusted Certificates.
    8. Click Choose File and select the certificate on the local machine.
    9. Click Open to load the certificate and add it to the Database Firewall.
      The details of the uploaded certificate appear in the dialog box.
    10. Click Cancel to exit the dialog box.
    11. Follow a similar process to select and manage certificates and the cipher suite level under Outbound TLS (From DBFW to Database).

      To manage the certificates for server authentication, click Database Trusted Certificates.

  11. If Oracle Database uses native network encryption, select Decrypt With Native Network Encryption Key to enable the decryption of traffic.

    Note:

    If the Enable TLS support check box is selected, the Decrypt With Native Network Encryption Key check box is hidden.

    For Oracle AVDF release 20.5 and earler, the check box is Decrypt With Network Native Encryption Key.

    This option also supports the retrieval of session information for Oracle Database. Complete the remaining fields as applicable.

    For Oracle Real Application Clusters (Oracle RAC) targets (if the RAC Instance/Autonomous DB check box is selected on the Core tab), enter the SCAN Listener IP address.

    (In Oracle AVDF 20.7 and earlier, it's the RAC Instance check box, and in Oracle AVDF 20.2 and earlier, it's the Basic tab.)

    For Oracle standalone database targets, enter the IP address of the database listener.

    For Sybase SQL Anywhere (Oracle AVDF 20.1-20.6 only) and Microsoft SQL databases, the field is Retrieve session information from target DB. Retrieving session information is not available for any other non-Oracle database types.

    Note:

    Ensure that the Database Firewall is allowed to make a network connection to the database listener.
  12. Select the check box for Capture Database Response field. If you check this field, the Database Firewall monitors the SQL response from the database. Select Full Error Message check box to capture the database response codes and error codes.
  13. Click Save at the bottom of the dialog to save the configuration of the monitoring point.
  14. Click Save in the main page.

7.6.4 Starting, Stopping, or Deleting Database Firewall Monitoring Points

Learn about starting, stopping, and deleting Database Firewall monitoring points.

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab.
  3. Click on a specific target. The details of the target are displayed on the main page.
  4. Under Database Firewall Monitoring tab, select a specific Database Firewall monitoring point.
  5. Click one of the following buttons:

    • Start - To start the monitoring point

    • Stop - To stop the monitoring point

    • Delete - To delete the monitoring point

    See Also:

    Using Audit Vault Server Console

7.6.5 Viewing the Status of Database Firewall Monitoring Points

Learn about viewing Database Firewall monitoring point status.

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab.
  3. Select a specific target. The details of the specific target are displayed on the main page.
  4. Under Database Firewall Monitoring section, click on a specific Database Firewall monitoring point.

    A list of monitoring points and their status is displayed. Possible status values are:

    • Up - The monitoring point is up and running, and there are no errors.

    • Suspended - The user has stopped the monitoring point, and there are no errors.

    • Down - The monitoring point is not working, probably due to errors.

    • Unreachable - There are communication errors between the Database Firewall and the Audit Vault Server.

    See Also:

    Using Audit Vault Server Console

7.6.6 Finding the Port Number Used by a Database Firewall Monitoring Point

Learn about finding Database Firewall monitoring point port numbers.

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab.
  3. Select a specific target. The details of the specific target are displayed on the main page.
  4. Under Database Firewall Monitoring section, click on a specific Database Firewall monitoring point.
  5. The port number is displayed in the field Proxy Ports.

7.6.7 Configuring a Database Firewall to Connect to an Oracle Autonomous Database

Learn how to configure a Database Firewall to connect to an Oracle Autonomous Database.

Prerequisite: Log in to the Oracle Cloud Infrastructure (OCI) account and download the wallet credentials ZIP file that is associated with the user account.
  1. Create a TLS-enabled Database Firewall monitoring point for the Oracle Autonomous Database target.
    • On the Core tab, select RAC Instance/Autonomous DB.
    • On the Advanced tab, select Enable TLS support.

    For complete instructions, see Creating and Configuring a Database Firewall Monitoring Point.

  2. Complete the TLS configuration for inbound connections.
  3. Import the wallet ZIP file that is associated with the user account (which you downloaded earlier) to the Database Firewall instance.
    1. Copy the wallet ZIP file to the file system on the Database Firewall (for example, /tmp/Wallet_DBXXXXXXXXXX.zip).
    2. Log in to the Database Firewall through SSH and switch to the root user.
    3. Extract the contents of the wallet ZIP file.
      unzip /tmp/Wallet_DBXXXXXXXXXX.zip -d my_cloud_wallet
    4. Run the following command to deploy the wallet for the appropriate Database Firewall secured target:
      /opt/avdf/bin/deploy-wallet  <PATH-TO-UNZIPPED-CLOUD-WALLET>  <SECURED-TARGET-NAME>

      Note:

      To view the list of all available secured targets, run the following command:

      /opt/avdf/bin/deploy-wallet --list-targets

7.7 Configuring Stored Procedure Auditing (SPA)

Learn about configuring stored procedure auditing (SPA).

Stored procedure auditing (SPA) enables Oracle Audit Vault and Database Firewall auditors to audit changes to stored procedures on target databases. Oracle Audit Vault and Database Firewall connects to the database server at scheduled intervals and discovers any changes or additions that have been made to stored procedures.

To enable SPA, you simply configure the user account privileges necessary for Oracle Audit Vault and Database Firewall to do stored procedure auditing on a target. Oracle Audit Vault and Database Firewall provides scripts for setting up these privileges. Run the scripts specific to the target type.

An Oracle Audit Vault and Database Firewall auditor can view changes to stored procedures in reports if the auditor enables Stored Procedure Auditing in the target configuration.

See Also:

7.8 Configuring Database Firewall for Databases That Use Native Network Encryption

Learn about monitoring native network encrypted traffic for Oracle Database.

You can monitor native network encrypted traffic for Oracle Database to obtain the name of the database user, operating system, and client program that originated a SQL statement, if this information is not available from the network traffic. This information is then made available in the reports.

Note:

In order to fetch the session information successfully, the target database should have configuration to do a reverse DNS lookup under certain cases where client machine is a Windows instance or uses network host names.

To configure monitoring of native network encrypted traffic for Oracle Database, follow the steps in this section.

7.8.1 Step 1: Apply the Specified Patch to the Oracle Database

Learn how to apply the specified patch to Oracle Database.

Note:

This step is not required for Oracle Database versions 11.2.0.4 or later. For all versions prior to 11.2.0.4, apply the patch specified in this section on the Oracle Database that is using Network Encryption.

To apply the patch:

  1. Shut down the Oracle Database.
  2. Get the patch identified by the bug number 13051081.

    The patch file will be in the format: p13051081_OracleVersion_Platform.zip. For example: p13051081_112030_Linux-x86-64.zip

  3. Unzip the patch .zip file in a directory, identified here as Patch_Directory.
  4. Go to the directory Patch_Directory/13051081.
  5. Execute the command:

    $ opatch apply

  6. Start the Oracle Database.

7.8.2 Step 2: Run the Oracle Advance Security Integration Script

Learn how to run the Oracle Advance Security integration script.

To run the Network Encryption integration script:

  1. From the Oracle Audit Vault and Database Firewall utilities file dbfw-utility.zip (downloaded with the software), copy the database directory to a location from which you can connect to the Oracle Database being patched.
  2. In this location, go to the database/ddi directory and uncompress one of the two oracle compressed files (both contain the same content), preferably into a directory called oracle.

    This directory now contains the uncompressed file: advanced_security_integration.sql.

  3. Only perform this step if Database Vault is enabled on the target database.
    If Database Vault is not enabled then the script you run in the following step creates the users.
    1. Connect to the target database as a Database Vault Account Manager
    2. Create a user:
      CREATE USER <username> IDENTIFIED BY <password>

      The username and password created here will be used as <param1> and <param2>, respectively, in the following step when running the advanced_security_integration script.

    3. Create an avsys user:
      • If your database is 18c or later:
        CREATE USER avsys NO AUTHENTICATION ACCOUNT LOCK DEFAULT TABLESPACE SYSAUX;
      • If your database is older than 18c:
        CREATE USER avsys IDENTIFIED BY VALUES 'S:100000000000000000000000000000000000000000700000000000000000' PASSWORD EXPIRE ACCOUNT LOCK DEFAULT TABLESPACE SYSAUX;
  4. Execute the following command as a user with privileges to create users and grant privileges.

    sqlplus / as sysdba @advanced_security_integration <param1> <param2> <param3>

    where <param1> is the schema or username

    <param2> is the password to be set for the username

    <param3> valid values are ASO and SESSION_INFO

    Use ASO if you want to monitor native network encrypted traffic and fetch session information that is not captured from traffic.

    Use SESSION_INFO if the traffic is plain text and you just want to retrieve session information like username, OS username, client program name, and so on.

    Note:

    The third parameter (<param3>) is mandatory. In case it is missed, the system prompts with a help message.

    In case value of the third parameter (<param3>) is incorrect, the following help message is displayed: 

    
Invalid value is provided for <param3>
The valid values are ASO, SESSION_INFO.
ASO retrieves oracle native network encryption key and session information
SESSION_INFO retrieves session information

7.8.3 Step 3: Provide the Database Firewall Public Key to Oracle Database

Learn how to provide Database Firewall public keys to Oracle Database.

In order to decrypt traffic using native network encrypted traffic for Oracle Database, you must provide the Database Firewall public key.

To provide the public key to the Oracle Database:

  1. Log in to the Audit Vault Server console as administrator.
  2. Click Database Firewall tab.
  3. Click the specific Database Firewall instance from the list.
  4. Click Oracle Native Encryption under Configuration section.

  5. Click Copy Key to copy the public key and paste it into a text file. For example, dbfw_public_key.txt.

    Each Database Firewall has its own public key. In a case where you have Database Firewall high availability or monitoring point resiliency, when you have more than one Database Firewall monitoring this target, each Database Firewall public key must be copied and appended to the dbfw_public_key.txt file.

    Note: For security purposes the dbfw_public_key.txt file must have the same access permissions as the sqlnet.ora file on the Oracle Database server.

  6. Modify the sqlnet.ora file in the Oracle Database to include the public key and to require native network traffic encryption:

    1. Put the file you created in the earlier step on the Oracle Database server, preferably in the same directory as the sqlnet.ora file.

    2. Open the sqlnet.ora file and append the following parameters (in this example the public key file is dbfw_public_key.txt): 

      SQLNET.ENCRYPTION_TYPES_SERVER=AES256
SQLNET.DBFW_PUBLIC_KEY="/path_to_file/dbfw_public_key.txt"
SQLNET.ENCRYPTION_SERVER=REQUIRED

      Note:

      • If the sqlnet.ora file contains the optional parameter SQLNET.ENCRYPTION_CLIENT, its value must not be REJECTED. Otherwise, an error will occur.
      • It is not mandatory for the SQLNET.ENCRYPTION_SERVER parameter to be set as REQUIRED. Native Network Encryption (NNE) can be enabled from the client side as well. But if you want to monitor NNE traffic, SQLNET.ENCRYPTION_SERVER must never have the value REJECTED.

    3. Save and close the sqlnet.ora file.

See Also:

Oracle Database Security Guide for more information on network encryption.

7.8.4 Step 4: Enable Native Network Encrypted Traffic Monitoring for Oracle Database

You can enable native network encrypted traffic monitoring for Oracle Database.

Follow the procedure in Monitor Native Network Encrypted Traffic Through Database Firewall for Oracle Databases to complete the configuration for Oracle Databases that use network encryption.

7.9 Configuring Advanced Settings for Database Firewall

Learn about configuring database connection details under advanced options.

7.9.1 About Native Network Encryption for Oracle Databases

Learn about using native network encryption for Oracle Databases.

If you are using the Database Firewall to monitor an Oracle Database target that uses network encryption, then you must use native network encryption monitoring in order to decrypt statements sent to, and responses received from, that database so they can be analyzed.

Limitations on Decryption of Oracle Database Statements

Configuring Audit Vault and Database Firewall to decrypt traffic with Network Encryption has the following limitations:

  • There is no statement substitution in Audit Vault and Database Firewall when Network Encryption checksum is used.
  • There is no support for Network Encryption RC4 cipher.
  • Supported versions of Oracle Database.
  • Database Firewall doesn't support running the Oracle Advance Security Integration Script on root container databases (CDB$ROOT). It also doesn't monitor and apply policies on traffic with native network encryption for root container databases (CDB$ROOT).

Note:

When dealing with encrypted connections from tools like Microsoft OSTRESS, it is advised to use the -T146 flag to prevent the interference of Microsoft's encryption with the Audit Vault Database Firewall's examination of the data traffic. Additionally, it is suggested to use database interrogation to extract information such as the name of the database user, operating system, and client program that initiated a SQL statement from monitored Microsoft SQL Server and Sybase SQL Anywhere databases.

See Also:

Configuring Database Firewall for Databases That Use Native Network Encryption

7.9.2 Monitor Native Network Encrypted Traffic Through Database Firewall for Oracle Databases

Learn how to enable monitoring of native network encrypted traffic through Database Firewall for Oracle Databases.

This functionality enables Database Firewall to monitor native network encrypted traffic for supported Oracle Database targets.

Note:

Native Network Encrypted traffic monitoring was earlier known as Database Interrogation.

Prerequisite

Log in to the Audit Vault Server console as administrator. See Using Audit Vault Server Console for more information.

To enable this functionality for a Database Firewall monitoring point:

  1. Click the Targets tab. The Targets tab in the left navigation menu is selected.
  2. Click on the specific target. The details of the target are displayed on the main page.
  3. Under Database Firewall Monitoring section, select the monitoring point for which native network encrypted traffic monitoring needs to be enabled.
  4. In the Advanced tab, select the check box Decrypt With Native Network Encryption Key, for enabling decryption of traffic if Oracle Database is using Native Network Encryption. Decrypt with native network encryption key option also supports retrieval of session information for Oracle Database. Fill in the remaining fields as applicable.

    For an Oracle RAC target (if the RAC Instance/Autonomous DB check box is selected on the Core tab), enter the SCAN Listener IP address.

    (In Oracle AVDF 20.7 and earlier, it's the RAC Instance check box, and in Oracle AVDF 20.2 and earlier, it's the Basic tab.)

    For Oracle standalone database targets, enter the IP address of the database listener.

    Note:

    Ensure the Database Firewall is allowed to make a network connection to the above mentioned database listener.
  5. Once the above mentioned field is checked, the following fields are populated. Enter the values in the appropriate fields.
    • Host Name / IP Address - Enter the host name or the IP address of the target database. For Oracle standalone Database targets, enter the IP address of the database host machine. For Oracle RAC target, enter the SCAN Listener IP address.

    • Port - Enter the port number of the target database.

    • Service Name - Enter the service name of the database or database instance.

    • User Name - Enter the user name that was set up for this target.

    • Password - Enter the password for the user name.

  6. Click Save.

7.9.3 Disabling Encrypted Traffic Monitoring for Oracle Databases

Learn about disabling encrypted traffic monitoring for Oracle Databases.

You can temporarily disable encrypted traffic monitoring. Oracle AVDF saves the configuration information that you have created for the next time that you want to enable it.

To disable encrypted traffic monitoring:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab. The Targets tab in the left navigation menu is selected.
  3. Click on the specific target. The details of the target are displayed on the main page.
  4. Under Database Firewall Monitoring section, select the monitoring point for which native network encrypted traffic monitoring needs to be disabled.

    Alternatively, navigate to Database Firewalls tab and then click Database Firewall Monitoring tab in the left navigation menu. A list of monitoring points are displayed on the page. The list can be sorted or filtered. Select the monitoring point for which native network encrypted traffic monitoring needs to be disabled.

  5. In the Advanced tab, uncheck the box against Decrypt With Native Network Encryption Key for disabling decryption of traffic if Oracle Database is using Native Network Encryption. Upon deselection the remaining fields disappear.
  6. Click Save.

7.9.4 Retrieve Session Information for Microsoft SQL Server and Sybase SQL Anywhere Databases

Learn how to obtain session information for non Oracle databases.

You can retrieve session information for Sybase SQL Anywhere (Oracle AVDF 20.1 - 20.6 only) and Microsoft SQL Server databases to obtain the name of the database user, operating system, and client program that originated a SQL statement. Enable this functionality only if this information is not available from the network traffic. This information is then made available in the reports.

While configuring this functionality choose the field Retrieve session information from target DB in the Advanced tab.

7.9.4.1 Setting Permissions to Retrieve Session Information in Microsoft SQL Server

Learn about retrieving session information in Microsoft SQL Server.

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

Note:

It is possible for direct database interrogation (DDI) to fail to fetch information for shorter sessions using this method. Follow the alternate steps that involve running a script to avoid this.
  1. Create a user account for Oracle AVDF for querying session information on the database. This database should be registered as a target in the Audit Vault Server console.

    Make a note of the user name and password for this account.

  2. Grant the following permissions to the user account you created in the previous step:

    • VIEW ANY DEFINITION and VIEW SERVER STATE for SQL Server

    • SELECT on the master.dbo.sysdatabases table

  3. Enable retrieving session information for the Database Firewall monitoring point that is associated with this target database, using the credentials created in the earlier step. Ensure the following steps are accurate while registering Microsoft SQL Server as a target.

    1. Log in to the Audit Vault Server Console as an administrator.

    2. Click on the Targets tab.
    3. Select the Microsoft SQL Server database from the list.
    4. Select the monitoring point from the Database Firewall Monitoring section.
    5. Click the Advanced tab.
    6. Select Retrieve session information from target DB.
    7. In the User Name field, enter the user name of the user created in the earlier step.
    8. In the Password field, enter the password of the user.
    9. In the Host Name / IP Address field, enter the IP address of the SQL Server.
    10. In the Port field, enter the port of the SQL server listening port.
    11. In the Database Name field, enter a valid database service name on SQL Server. In case the database service name is not correct, then SQL server DDI requests fail on the SQL Server with invalid request error.
  1. Create a user account for Oracle AVDF for querying session information on the database. This database should be registered as a target in the Audit Vault Server console.

    Make a note of the user name and password for this account.

  2. Download the necessary mssql_ddi_script.sql script from the utilities V<part_number>.zip file available as part of the Oracle AVDF install files from Oracle Software Delivery Cloud.
  3. Execute the following command as a user with privileges to create schemas, logon triggers and jobs, and grant privileges:

    Caution:

    The script will create a logon trigger on the database and a few tables to be used by the Database Firewall. 
    sqlcmd -S tcp:<IP>,<PORT> -U sa -P <Password> -i mssql_ddi_script.sql -v AVDF_DDI_USER="<username>"

    The password is the password for the sa user and the username is the same as from step one.

  4. Enable retrieving session information for the Database Firewall monitoring point that is associated with this target database, using the credentials created in the earlier step. Ensure the following steps are accurate while registering Microsoft SQL Server as a target.

    1. Log in to the Audit Vault Server Console as an administrator.

    2. Click on the Targets tab.
    3. Select the Microsoft SQL Server database from the list.
    4. Select the monitoring point from the Database Firewall Monitoring section.
    5. Click the Advanced tab.
    6. Select Retrieve session information from target DB.
    7. In the User Name field, enter the user name of the user created in the earlier step.
    8. In the Password field, enter the password of the user.
    9. In the Host Name / IP Address field, enter the IP address of the SQL Server.
    10. In the Port field, enter the port of the SQL server listening port.
    11. In the Database Name field, enter a valid database service name on SQL Server. In case the database service name is not correct, then SQL server DDI requests fail on the SQL Server with invalid request error.
7.9.4.2 Disable Retrieving Session Information in Microsoft SQL Server

After using the mssql_ddi_script.sql script which created a logon trigger and a few tables on the database to configure direct database interrogation (DDI), you can use the mssql_ddi_removal.sql script to disable DDI.

  1. Execute the following command as a user with privileges to drop schemas, logon triggers and jobs, and revoke privileges:
    sqlcmd -S tcp:<IP>,<PORT> -U sa -P<Password> -i mssql_ddi_removal.sql -v AVDF_DDI_USER="<username>"

    The password is the password for the sa user and the username is that of the user account on the database for Oracle AVDF.

  2. On the Audit Vault Server console, disable DDI for the Microsoft SQL Server:

    1. Log in to the Audit Vault Server Console as an administrator.

    2. Click on the Targets tab.
    3. Select the Microsoft SQL Server database from the list.
    4. Select the monitoring point from the Database Firewall Monitoring section.
    5. Click the Advanced tab.
    6. Deselect Retrieve session information from target DB.
7.9.4.3 Setting Permissions to Retrieve Session Information in Sybase SQL Anywhere Database

Learn about retrieving session information in Sybase SQL Anywhere databases.

Note:

  • Sybase SQL Anywhere was deprecated in Oracle AVDF release 20.7 and is desupported in 20.8.
  • Before you can use Sybase SQL Anywhere, you must download and install the SQL Anywhere ODBC driver for Linux.
  1. Create a user account Oracle AVDF for querying session information on the database. This database should be registered as a target in the Audit Vault Server console.

    Make a note of the user name and password for this account.

  2. Grant the following permissions to the user account created in the earlier step:

    • CONNECT

    • SELECT on these system tables: 

      sys.sysuser
sys.sysuserauthority
sys.sysremoteuser
sys.sysloginmap
sys.sysgroup
  3. Enable retrieving session information for the Database Firewall monitoring point that is associated with this target database, using the credentials created in the earlier step.

7.10 Monitoring TLS Encrypted SQL Traffic

Learn how to enable monitoring of TLS encrypted SQL traffic between the database clients and Oracle Database.

Note:

  • This functionality does not support database clients using PKI authentication.
  • This functionality is not supported for Oracle Real Application Cluster (RAC) as a target in Oracle AVDF release 20.7.
  • This functionality is supported for Oracle Real Application Cluster (RAC) as a target starting with Oracle AVDF release 20.8.
  • The Database Firewall acts as a proxy and terminates TLS session from the database clients. In all cases, Database Firewall becomes the client for the database server.
  • Native Network Encryption is disabled in case this functionality is enabled.

7.10.1 Using Default Self Signed Certificates Created During Monitoring Point Creation

Learn how to use self signed certificates created by default when creating a Database Firewall monitoring point.

Starting with Oracle AVDF release 20.7, Database Firewall supports monitoring of TLS encrypted SQL traffic between the database client and Oracle Database. Database Firewall acts a TLS proxy terminating the session from the database client and creating a new TLS outbound session to the database server. Different TLS levels can be set for:

  1. Inbound connection from the database client to Database Firewall
  2. Outbound connection from Database Firewall to Oracle Database

TLS Level-4 is the strictest and set by default. Mutual authentication is enabled by default for both inbound and outbound connections.

Database Firewall decrypts the network traffic from the database clients, extracts SQL traffic, and acts on the SQL statements based on the configured policies. It creates a new TLS session to the database server if the traffic needs to be passed on.

Note:

For production instances it is recommended to use third party CA signed certificates than self signed certificates as per your organizational policy.

Follow these steps to enable TLS encrypted traffic monitoring capability for a target database:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab. The Targets tab in the left navigation menu is selected.
  3. Click the specific target. The details of the target are displayed on the main page.
  4. Under Database Firewall Monitoring section, click Add to create a new monitoring point. The Database Firewall Monitor dialog is displayed.
  5. In the Core tab, select the Database Firewall instance from the list.
  6. Select Monitoring / Blocking (Proxy) as the deployment mode from the list.
  7. Enter the remaining details.
  8. In the Advanced tab, select the check the box against Enable TLS Support field. All the necessary self signed certificates for this monitoring point are created. Mutual authentication is also enabled by default for inbound and outbound TLS connections.
  9. Complete the configuration of mutual authentication for the monitoring point.

7.10.2 Configuring Mutual Authentication for Inbound or Outbound TLS Communication

Learn how to configure mutual authentication for inbound or outbound TLS communication between the database clients and Oracle Database.

You can configure mutual authentication for TLS communication between:

  1. Database client to Database Firewall (inbound connection)
  2. Database Firewall to Oracle Database (outbound connection)

The configuration file for the Database Firewall monitoring point is /var/dbfw/va/x/etc/appliance.conf. In this case x is the Database Firewall monitoring point identifier. The database client always authenticates the associated Database Firewall it is connecting to.

Follow these steps:

  1. Log in to the Database Firewall appliance.
  2. Switch user to root.
  3. Configure the mutual authentication of database client and Database Firewall by following these steps:

    1. Import the monitoring point inbound certificate (/usr/local/dbfw/va/xx/pki/in/in.crt) into the key store of the database client as a trusted CA certificate. In this case xx refers to the monitoring point identifier.

      For Oracle Database clients, this involves importing the inbound certificate of the monitoring point into the client's wallet. Refer to the SQLNET Administrator Guide for complete information.

      For other (non Oracle) database clients, refer to respective database documentation.

    2. Copy the database client's trusted CA certificate into the monitoring point's inbound CA directory /usr/local/dbfw/va/xx/pki/in/ca.

      In this case xx refers to the monitoring point identifier. The permissions of the CA certificate for the clients must be 0440:dbfw:dbfw.

  4. Configure the mutual authentication of Database Firewall and database server by following these steps:

    1. Configure mutual authentication for outbound TLS connection. Copy the trusted CA certificate of the target database into the corresponding outbound CA directory of the monitoring point /usr/local/dbfw/va/xx/pki/out/ca.

      In this case xx refers to monitoring point identifier. The permissions of database CA certificate must be 0440:dbfw:dbfw.

    2. Import the outbound certificate of the monitoring point /usr/local/dbfw/va/xx/pki/out/out.crt into the key store of the target database as trusted CA certificate.

      For Oracle Database target this involves importing the outbound CA certificate of the monitoring point into wallet of the target database. Refer to the SQLNET Administrator Guide for complete information.

      For other (non Oracle) database clients, refer to respective database documentation.

  5. Restart the services. Run the following commands to restart the monitoring points which had changes to the configuration:
    systemctl stop monitor
    /usr/local/dbfw/bin/dbfwctl stop xx
    systemctl start monitor

    In this case xx refers to monitoring point identifier.

  6. Test the connections. TLS connection initiated from the database client to the above monitoring point should result in a successful connection.

7.10.3 Using External Certificates Signed by Certificate Authority

Learn how to use certificates signed by an external CA in Database Firewall.

You can use a certificate signed by an external Certification Authority (CA) based on your organization policy. Starting with Oracle AVDF release 20.7, Database Firewall supports external CA signed certificates for inbound and outbound TLS connections. Database Firewall provides a utility (config-pki_identity) to generate a CSR (Certificate Signing Request) which can be signed externally.

Follow these steps to use one pair of externally signed certificates for all Database Firewall monitoring points:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Create Database Firewall monitoring points and enable TLS encrypted SQL traffic monitoring. Select the appropriate TLS levels in the Inbound TLS (From client to DBFW) and Outbound TLS (From DBFW to Database) sections. See Creating and Configuring a Database Firewall Monitoring Point for details.

    Relevant self signed certificates are created for these Database Firewall monitoring points.

  3. Connect to the Database Firewall through SSH as support user.
  4. Switch user to root.
  5. Delete the self signed certificates for above Database Firewall monitoring points using the /opt/avdf/config-utils/bin/config-pki_identity utility.
  6. Create a CSR (Certificate Signing Request) to be signed externally.

    Note:

    Important aspects to be noted while creating a CSR:

    • The alt_* values are optional, depending on the certificate usage requirements.
    • The key_path and cert_path directories must exist.
    • The value of cert_uid/gid/mode must always be dbfw:dbfw:444.
    • The value of key_uid/gid/mode must always be root:arbitercerts:440.
    • Use the add command in /opt/avdf/config-utils/bin/config-pki_identity utility to create a CSR.

    For example: To create a CSR (in.csr) for the key (in.key), then use the following: 

    
/opt/avdf/config-utils/bin/config-pki_identity add \
key_path=/usr/local/dbfw/va/in.key \
cert_path=/usr/local/dbfw/va/in.csr \
cert_uid=dbfw \
cert_gid=dbfw \
cert_mode=444 \
key_uid=root \
key_gid=arbitercerts \
key_mode=440 \
common_name=test.certificate \
country=--- \
email=first.last@example.invalid \
locality=city \
organisation=company \
organisational_unit=group \
state=area \
alt_dns=foobar.example.org,foobar2.example.org \
alt_email=first.last2@example.invalid,first.last3@example.invalid \
alt_ip='192.0.2.0,192.0.2.1' \
alt_uri=https://<exampleuri.1>,https://<exampleuri.2>
  7. Use the example to create a /usr/local/dbfw/va/out.csr.
  8. Get both the CSRs signed externally:
    1. /usr/local/dbfw/va/in.csr
    2. /usr/local/dbfw/va/out.csr
  9. Copy both the externally signed certificates (in.crt and out.crt) to the /usr/local/dbfw/va directory.
  10. Validate and import both the externally signed certificates using the following example command:
    /opt/avdf/config-utils/bin/config-pki_identity set cert_path=/usr/local/dbfw/va/in.crt
  11. Create a symbolic link for the in.crt from every Database Firewall monitoring point inbound directory to /usr/local/dbfw/va/in.crt.

    Note:

    Add all the trusted certificates that constitute the certificate chain in the corresponding pki/in/ca path before adding externally signed certificate into pki/in path of a monitoring point.
  12. Create a symbolic link for the in.key from every Database Firewall monitoring point inbound directory to /usr/local/dbfw/va/in.key.
  13. Create a symbolic link for the out.crt from every Database Firewall monitoring point outbound directory to /usr/local/dbfw/va/out.crt.

    Note:

    Add all the trusted certificates that constitute the certificate chain in the corresponding pki/out/ca path before adding externally signed certificate into pki/out path of a monitoring point.
  14. Create a symbolic link for the out.key from every Database Firewall monitoring point outbound directory to /usr/local/dbfw/va/out.key.

    For example:

    ln -s /var/dbfw/va/in.crt /var/dbfw/va/xx/pki/in/in.crt ; ln -s /var/dbfw/va/out.crt /var/dbfw/va/xx/pki/out/out.crt

    In this case xx refers to the Database Firewall monitoring point identifier.

  15. Configure mutual authentication for the inbound TLS connection. The inbound connection is the connection from the database client to the Database Firewall.
  16. Configure mutual authentication for the outbound TLS connection. The outbound connection is the connection from the Database Firewall to Oracle Database.
  17. Restart all the modified Database Firewall monitoring points.

7.10.4 Disabling Mutual Authentication for Inbound or Outbound TLS Communication

Learn how to disable mutual authentication for inbound or outbound TLS communication between the database clients and Oracle Database.

You can disable mutual authentication for TLS communication between:

  1. Database client to Database Firewall (inbound connection)
  2. Database Firewall to Oracle Database (outbound connection)

Mutual authentication can be optionally disabled for inbound or outbound TLS communication. The configuration file for the Database Firewall monitoring point is /var/dbfw/va/x/etc/appliance.conf. In this case x is the Database Firewall monitoring point identifier. The database client always authenticates the associated Database Firewall it is connecting to.

Follow these steps to disable mutual authentication for inbound TLS communication:

  1. Modify the following value in the configuration file /var/dbfw/va/x/etc/appliance.conf:

    TLS_CLIENT_AUTH="0"

  2. Import the Database Firewall monitoring point inbound certificate (/usr/local/dbfw/va/xx/pki/in/in.crt or /usr/local/dbfw/va/in.crt) into the SQL client's key store as a trusted CA certificate.

    Note: For Oracle SQL clients this involves importing the Database Firewall monitoring point CA certificate into the SQL client's wallet. Refer to the SQLNET Administrator Guide for complete information. For other (non Oracle) SQL clients, refer to the respective database documentation.

Database Firewall authenticates the database it is connecting to. Follow these steps to disable mutual authentication for outbound TLS communication:

  1. Modify the following value in the sqlnet.ora configuration file:

    SSL_CLIENT_AUTHENTICATION = FALSE

  2. Copy the trusted CA certificate of the target database into the corresponding Database Firewall monitoring point's outbound CA directory (/usr/local/dbfw/va/xx/pki/out/ca).

7.10.5 Configuring a TLS Proxy for an Oracle Real Application Clusters Database

Learn about additional steps that are required to configure a TLS proxy for Oracle Real Application Clusters (Oracle RAC).

Oracle AVDF release 20.7 supports monitoring TLS encrypted SQL traffic between the database clients and Oracle Database. Starting with Oracle AVDF release 20.8, this functionality is supported for Oracle RAC.

Prerequisites

  • The database client must have a wallet that is configured with credentials to communicate with the Oracle RAC database instance.
  • The Oracle RAC database instance must have a wallet that is configured to communicate with the client.
  • The client and the Oracle RAC database instance must be able to connect by using TCPS.
  • You must have an externally created Oracle wallet for the Database Firewall to use, and it must be configured with credentials to communicate with the Oracle RAC database instance. See Managing Oracle Wallets with the orapki Utility for creating and managing oracle wallets.
  1. Create a TLS-enabled Database Firewall monitoring point for the Oracle RAC target. Select Oracle RAC and TLS in the Audit Vault Server console. See Creating and Configuring a Database Firewall Monitoring Point.
  2. Complete the TLS configuration for inbound connections. See Modifying a Database Firewall Monitoring Point.
  3. Import the externally created wallet to the Database Firewall instance.

    1. Copy the externally created wallet to the file system in the Database Firewall (for example, /tmp/my_rac_wallet).

    2. Switch to the root user.

    3. Run the following command to deploy the wallet for the appropriate Database Firewall secured target:

      /opt/avdf/bin/deploy-wallet  <PATH-TO-WALLET> 
      <SECURED-TARGET-NAME>

    Note:

    To view a list of all available secured targets, run the following command:

    /opt/avdf/bin/deploy-wallet
    --list-targets

7.10.6 (Optional) Enabling Common Name Verification for the Database Server

In addition to verifying that the target database's certificate is valid, you can verify the database server's common name from the database certificate. This verification matches the server's common name against a set of allowed common names that you configure.

To enable this additional check of the database certificate's common name, follow these steps:

  1. Connect to the Database Firewall through SSH.
  2. Switch to the root user.
  3. Identify the secured target for which you want to enable this feature.
  4. Edit the appliance.conf file for the secured target.

    For example, in the following file path, x represents the monitoring point identifier: /usr/local/dbfw/va/x/etc/appliance.conf

    1. Locate the following keyword in the file: TLS_PROXY_OUTBOUND_ALLOWED_CN_LIST
    2. Provide an allowed list of values in one of the following formats, depending on whether the secured target type is an Oracle Real Application Clusters (Oracle RAC) database.
      Secured Target Type Description of Allowed List of Values Example
      Non-Oracle RAC database Provide a list of allowed common names that the Database Firewall is allowed to connect to 
      TLS_PROXY_OUTBOUND_ALLOWED_CN_LIST = "CN=<db_cn_name>:CN=<other_db_cn_name>"
      Oracle RAC database Provide the distinguished name for the peer RAC database 
      TLS_PROXY_OUTBOUND_ALLOWED_CN_LIST = "CN=<db_cn_name>, O=<db_org_name>, L=<db_location_name>"
  5. Save the edited configuration.
  6. Restart the monitoring point.

7.11 Configuring and Using Database Response Monitoring

Learn about configuring and using database response monitoring.

7.11.1 About Database Response Monitoring

Learn about database response monitoring.

Enabling the Database Response Monitoring feature enables Oracle Database Firewall to record responses that the target database makes to login requests, logout requests and SQL statements sent from database clients, as shown in Figure 7-1. This feature allows you to determine whether the database executed logins, logouts and statements successfully, and can provide useful information for audit and forensic purposes.

Figure 7-1 illustrates the process flow of database response monitoring.

Figure 7-1 Database Response Monitoring

Description of Figure 7-1 follows
Description of "Figure 7-1 Database Response Monitoring"

The Oracle Audit Vault and Database Firewall auditor can view database responses in audit reports.

Database Response Monitoring records database responses for all SQL statements, logins, and logouts that are logged by the Database Firewall policy.

The information recorded includes the response interpreted by Oracle Audit Vault and Database Firewall (such as "statement fail"), the detailed status information from the database, and the database response text (which may be displayed in the database client).

Note:

The Event Status value in the reports is displayed only if Database Response Monitoring is enabled for the respective monitoring point.

7.11.2 Enabling Database Response Monitoring

Learn about enabling database response monitoring.

To enable database response monitoring for a target:

  1. Log in to the Audit Vault Server console as an administrator.
  2. Click the Targets tab. The Targets tab in the left navigation menu is selected.
  3. Click on the specific target. The details of the target are displayed on the main page.
  4. Under Database Firewall Monitoring section, select the monitoring point for which native network encrypted traffic monitoring needs to be enabled.
  5. In the Database Firewall Monitor dialog, click the Advanced tab.
  6. Select the check box against Capture Database Response field.

    After this field is checked, the Full error message check box is displayed. If this field is checked, any detailed error message text generated by the database is logged along with the error code.

  7. Click Save.

    See Also:

7.12 Securing the Agent and Oracle Database Target Connection

Learn how secure the Agent and Oracle Database target connection.

Data security between an Audit Vault Agent and an Oracle Database target is achieved by default, through network encryption over TCP connection. Data security can also be achieved by using a TCPS/SSL connection.

If the target has been setup to accept TCPS/SSL connections, then follow these steps to configure the Agent:

  1. Ensure that in the target's sqlnet.ora file, the following parameters are set:

    • SQLNET.ENCRYPTION_SERVER = REQUESTED, REJECTED, or the default, ACCEPTED.

    • SQLNET.CRYPTO_CHECKSUM_SERVER = REJECTED or the default, ACCEPTED

  2. Log in to the Audit Vault Server console as an administrator.

  3. Click the Targets tab.

  4. In the left navigation menu, select Targets.

  5. Select the name of the target that you want to modify.

  6. In the target page, do the following:

    1. In the Audit Data Collection section, enter the details in Host Name/IP Address, choose TCPS protocol, Server DN, and upload the wallet file.

    2. Or alternately, select the Advanced option, choose TCPS protocol, upload the wallet file, and then in the Target Location field, provide the TCPS connection string.

      For example:

      jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=host_ip)(PORT=port_number))(CONNECT_DATA=(SERVICE_NAME=service_name)(SERVER=DEDICATED))(SECURITY= (SSL_SERVER_CERT_DN="dn")))

    3. Click Save.

See Also:

7.13 Upgrading the Target Database

If you're upgrading the target database, perform the following tasks to ensure that Oracle Audit Vault and Database Firewall (Oracle AVDF) continues to function properly.