7 Reports

Oracle Audit Vault and Database Firewall provides a set of reports that you can generate and customize.

7.1 About the Reports in Audit Vault and Database Firewall

The Oracle Audit Vault and Database Firewall reports are automatically generated reports on audit data from targets and from Database Firewall data.

You can save or schedule reports in either PDF or Excel format. You can also view reports online and interactively adjust the online report view by filtering data. You can save these interactive views to see them online later.

The reports are organized into various categories, such as access reports and management reports. An alerts report allows you to view and respond to alerts. You can also create user-defined reports that focus on specific audit events or firewall data.

You can also produce Sarbanes-Oxley (SOX), Payment Card Industry (PCI), Data Protection Act (DPA), Gramm-Leach-Bliley Act (GLBA), and Health Insurance Portability and Accountability Act (HIPAA) reports. To specify which of these reports are required for a target database, you can add the target to the appropriate group (such as the SOX group) from the Targets tab.

Auditors can view data and modify reports for targets to which they have been granted access by a super auditor. However, an auditor can also send a report to other auditors for attestation regardless of the access rights of the other auditors.

You can specify email recipients for scheduled reports once they are generated, as well as create email templates for report notifications.

7.2 Related Event Data Appendices

For audit data, reports track audit events from a variety of sources, such as Oracle Database audit events, Sybase ASE audit events, and so on.

See the following appendices for more information:

7.3 Built-In Reports

You can browse a set of built-in reports and schedule their generation.

The Built-in Reports can be accessed from the Reports tab, and then by clicking on the PDF/XLS Reports tab (Report Templates tab starting with Oracle AVDF release 20.8) in the left navigation menu.

In the Built-in Reports section, you can browse report data online, schedule reports, and link to previously scheduled and generated reports. The report displays only records that are collected by the collector before the report execution starts.

Note:

Reports run faster if the audit data is in memory on the Audit Vault Server. If your Oracle Audit Vault and Database Firewall administrator has enabled Oracle Database In-Memory, you will see a date range in the top right corner of the Home tab. Reports for the specified date range run faster. See how to enable in memory at Enabling Oracle Database In-Memory for the Audit Vault Server.

To generate or browse the built-in reports:

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

  2. Click the Reports tab, and then on the PDF/XLS Reports tab (Report Templates tab starting with Oracle AVDF release 20.8) in the left navigation menu.

    The Built-in Reports section is listed in the second part of the page.

  3. From the Built-in Reports section, you can download the report template or the report definition.

    Navigate to the Reports tab, expand the specific report, and then perform one of the following actions:

    • Click the report name to view and browse the report data online. The timestamps in reports, when you browse them online, are displayed in your local browser time.

    • Click on the icon under the Schedule column to schedule the report in PDF or XLS format. The timestamps in a PDF or XLS report are written in the Audit Vault Server time (based on the Timezone Offset setting specified by an administrator).

    • Click on the icon under Generated Reports column to view a previously scheduled and generated report.

  4. When browsing a report, click the Details icon in the left most column of a row (an audit event) to view detailed information for that event.

Note:

If your Oracle Audit Vault and Database Firewall administrator changes the name of a target, the new name does not appear on reports until the administrator restarts the Audit Vault Agent.

7.4 Downloading a Report in HTML or CSV Format

You can download reports in .csv (for use in an Excel spreadsheet) or html format.

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click the Reports tab.
  3. All the report categories are listed in the left navigation menu. Select the specific report category. For example, click Activity Reports sub tab.
  4. Select the specific report.
  5. Use the filter options using the search field or by clicking on the column names. From the Actions menu, select Download.
  6. Select CSV or HTML in the dialog.
  7. In the opening dialog box, select Save File and then click OK.
  8. Select a location and enter a name for the file.
  9. Click Save.

7.5 Customizing the Built-in Reports

You can filter and control the display of data in a built-in report.

7.5.1 About Customizing Built-in Reports

You can create customized reports based on the built-in reports and then save the new report formats.

Oracle Audit Vault and Database Firewall provides tools to filter, group, and highlight data, and define columns displayed in the reports. You can also create a categories for your saved reports. Customized and saved reports are listed on the Saved Reports tab.

While you can schedule the default built-in reports to be generated in PDF format, saved custom reports cannot be scheduled or printed in PDF format, and therefore must be viewed online.

7.5.2 Filtering and Controlling the Display of Data in a Report

Learn about filtering and modifying the view in reports.

7.5.2.1 About Filtering and Display Settings in Reports

Learn about filtering and changing display settings in reports.

You can control the display of data in a report to focus on a particular set of data. Oracle Audit Vault and Database Firewall automatically saves the report settings so that if you leave the page, the report settings are still in place when you return. Optionally, you can save the report as a custom report.

7.5.2.2 Filtering Data in a Report

Learn about filtering data in reports.

7.5.2.2.1 About Filtering Data in Reports

Learn about filtering data viewed in reports.

You can filter the report to show all rows based on a particular column, or a subset of rows, using an expression.

You can create multiple filters as needed. For example, if you want to filter all SYS users who are being audited for the SUPER USER LOGON event, you would create one filter to catch all SYS users, and then a second filter to catch all SUPER USER LOGON events. If two or more of the filters for a report are enabled, then the report uses both or all of them (as in an AND operation). You can toggle specific filters on or off, depending on the results that you want.

7.5.2.2.2 Filtering Column and Row Data Using the Search Bar

Learn about filtering rows and columns using the search bar.

You can use the Search bar to search for row data in one or all columns in the report (for example, all rows that contain the letters SYS, such as SYS and SYSTEM, in all columns).

To search for row data in one or all columns:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab, and then access the report that you want.
  3. If you want to focus the search on a specific column, in the Search bar, use the Search icon to select from the drop-down list of available columns.

    By default, the search applies to all columns.

  4. In the Search bar text area, enter all or part of the row text you want to search for.
  5. Click Go.
7.5.2.2.3 Filtering All Rows Based on Data from a Selected Column

Learn about filtering all the rows based on selected data in a column.

This filtering method lets you filter data in all rows based on a selected column (for example, all rows that contain SYS in the User column).

To filter all rows based on data from a selected column:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab, and then access the report that you want.
  3. Click the Actions menu, and select Filter.

    The Filter dialog box appears. The existing filter definitions for the current user session are shown below the Filter dialog box.

  4. For Filter Type, select Column.
  5. In the Column drop-down list, select the column on which you want to base the filter.

    You can select from columns that are displayed in the report or other columns.

  6. Select the Operator and Expression that you want to use, to further filter the data.
  7. Click Apply.

    The existing filter definitions for the current user session are shown above the report columns.

  8. To enable or disable the display of the filtered data, select its corresponding check box. To remove a filter, click its Remove Filter icon.
7.5.2.2.4 Filtering Row Data Using an Expression

Learn about filtering data in rows using an expression.

This method lets you select all rows that meet a WHERE condition, such as all users who are not user SYS. You can create the expression for all columns, even those that are not shown in the current report.

To filter row data using an expression:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab, and then access the report that you want.
  3. From the Actions menu, select Filter.

    The Filter dialog box appears. The existing filter definitions for the current user session are shown below the Filter dialog box.

  4. For Filter Type, select Row.
  5. Enter a Name for the filter.
  6. Use the Columns, Function/Operators, and Filter Expression fields to build your filter expression:
    • Columns: Select the name(s) of the column(s) from the list to use them in the expression. When you select a column, its abbreviation appears in the Filter Expression field.

    • Functions/Operators: Select function(s) and/or operator(s) from the list to build your expression.

    • Filter Expression: If you have built an expression from the available columns, functions and operators, enter any parameters needed to complete your expression. If you type the expression, remember that it is case-sensitive. In most cases, use uppercase letters.

      For example: To view login failure events in the report, use the following filter condition:

      event_name IN (‘LOGIN’,’LOGON’) and event_Status = ‘FAILURE’

      As you build the expression, the Filter Expression field is populated with the expression.

  7. Click Apply.

    Oracle Audit Vault and Database Firewall filters the display of row data based on the expression you created, and adds the filter definition above the report columns.

  8. To enable or disable the display of the filtered data, select its corresponding check box. To remove a filter, click its Remove Filter icon.
7.5.2.3 Hiding or Showing Columns in a Report

Learn to hide or show columns in reports.

When you hide or show columns in a report, you still can perform operations on hidden columns, such as filtering data based on a column that you have hidden.

To hide or show columns in a report:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab, and then access the report that you want.
  3. From the Actions menu, click Select Columns.

    The Select Columns dialog field appears.

  4. Move column names under the Do Not Display or Display in Report boxes:
    • Select the column names to move and then click the left or right arrow between the column name boxes.

    • Move all columns left or right by using the >> and << buttons.

    • Use the top button (the arrows in a circle) to reset the columns to their original locations in the two boxes.

  5. To set the order of displayed columns, in the Display in Report box, select the column name, then click the up or down arrow on the right side of the box to reorder the column's position in the list.
  6. Click Apply.
7.5.2.4 Formatting Data in a Report

Learn how to format data viewed in a report.

7.5.2.4.1 Sorting Row Data for All Columns

Learn to sort data in rows for all the columns.

To sort row data for all columns:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab, and then access the report that you want.
  3. From the Actions menu, select Format, then select Sort.

    The Sort dialog box appears.

  4. Enter the following information:
    • Column: For up to six columns, select the columns to sort. By default, the first sort column is Event Time, which is sorted in descending order.

    • Direction: Select either Ascending or Descending.

    • Null Sorting: Select the Null sorting rule for each column (Default, Nulls Always Last, or Nulls Always First). The default is to not sort nulls.

  5. Click Apply.
7.5.2.4.2 Highlighting Rows in a Report

Learn how to highlight rows in a report.

You can highlight specific rows in a report by assigning them colors. This enables anyone viewing the report to quickly find areas that are of particular interest.

To highlight rows in the report:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab, and then access the report that you want.
  3. From the Actions menu, select Format, then Highlight.

    The Highlight dialog box appears.

  4. Enter the following information:
    • Name: Optionally enter a name for this highlight instance.

    • Sequence: Enter a sequence number to determine the order in which the highlight filter rules are to be applied when two or more highlight filter rules are in effect. The default value is 10.

    • Enabled: Select Yes to enable the highlight or select No to disable it.

    • Highlight Type: Select Row to highlight a row or select Cell to highlight a cell.

    • Background Color: Select a background color for the row or cell. Click a color to display color options, or click the colored icon to the right of the color selection field to display a color selection box from which to choose a different color. Alternatively, you can manually enter the HTML code for a color.

    • Text Color: Select a text color for the row or cell using the same method you used for the background color. (Optional)

    • Highlight Condition: Edit the highlight filter rule expression by identifying the column, the operator, and the expression for each of the three fields in the highlight condition.

      • Column: Select any column name, including hidden columns.

      • Operator: Select an operator from a list of standard Oracle Database operators, such as =, !=, NOT IN, and BETWEEN.

      • Expression: Enter the comparison expression (without quotation marks) based on a known value for that column name to complete the filter expression.

        For example, entering the filter expression EVENT=SUPER USER LOGON filters for all values in the Event column that contain the value SUPER USER LOGON.

  5. Click Apply.
7.5.2.4.3 Charting Data in a Report

Learn how to chart data in a report.

You can select from four chart styles to chart data in a report. After you create the chart, you can access it whenever you access the report.

To chart data in a report:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab, and then access the report that you want.
  3. From the Actions menu, select Format, then Chart.

    The Chart dialog box appears.

  4. Enter the following information:
    • Chart Type: Select from one of the four chart styles: Bar, Line with Area, Pie, and Line.

    • Label: Select from the list of columns for this report. You can include hidden columns as well as displayed columns.

    • Value: Select from the list of columns for this report, including hidden columns. If you select Count from the Function list, then you do not need to select a value.

    • Function: Select an aggregate function (Sum, Average, Minimum, Maximum, or Count) on which to aggregate the data values.

    • Sort: Select ascending or descending sorting for values and labels.

    • Axis Title for Label: Enter a name for the axis title.

    • Axis Title for Value: Enter a name for the axis value.

    • Orientation: Choose Landscape or Portrait.
  5. Click Apply: Select vertical or horizontal.

    The chart appears, with the Edit Chart and View Report links under the search bar.

7.5.2.4.4 Adding Control Breaks to a Report

Learn to add control breaks to selected columns in a report.

You can create a break group based on selected columns. This pulls the column out of the report as a main record and groups all rows with the same value for the selected column under that main record. This is useful for filtering by multiple column values.

For example, you may have an Activity Overview report that displays several columns of data. If you want to see that data broken up by the Client IP Address and Target Name columns, you would add control breaks for those columns. The resulting report would have data broken up into smaller tables for each unique combination of Client IP Address and Target Name.

To add a control break in a column:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click on Reports tab, and then access the report that you want.
  3. From the Actions menu, select Format, then Control Break.
  4. Select the columns to which you want to add a control break.

    You can select up to six columns in the order that you want the data to be broken up. Selecting Enabled adds a control break; selecting Disabled removes the control break.

  5. Click Apply.
7.5.2.4.5 Using the Group By Function to Format a Report

Learn to format a report using the Group By option.

The Group By dialog lets you group data by up to three columns in a report, and specify up to three functions to perform on any column, and display the resulting values as additional columns in the custom report.

For example, suppose you want to create a custom report to show the number of events of a certain status (for example SUCCESS or FAILURE) for each target and client IP address combination. Using Group By, you can create a custom report to group unique targets together in the first column, client IP addresses for each target together in the second column, and display Event Status in the third column. You then specify a function to count distinct values in the Event Status column for each target and client IP address combination.

The resulting custom report will contain four columns: Target, Client IP, Event Status, and the final column will show the results of the function, for example, the number of events with SUCCESS status for that target and IP address.

To use the Group By feature:

  1. Log in to the Audit Vault Server as an auditor.

  2. Click the Reports tab, and then access the report that you want.

  3. From the Actions menu, select Format, then Group By.

    The Group By dialog is displayed.

  4. In the Group By Column field, from the first drop-down list, select a data column for grouping data in column 1 of your custom report.

    For example, if you select Target Name, column 1 of your report will have targets grouped together. Optionally, select data groupings for columns 2 and 3 of your report.

  5. Optionally, in the Functions field, specify up to three functions to operate on specific data columns. For example, Count Distinct.

  6. Under Column, select any data column in the default report.

  7. Optionally, under Label enter a column heading for the new column created by the result of this function.

  8. Optionally, under Format Mask select the format of the data in the new column created by the result of this function.

  9. Optionally, select the Sum check box if you want to add a Sum row to the bottom of your custom report to add the values in the new column.

  10. Click Apply.

7.5.2.5 Resetting the Report Display Values to Their Default Settings

Learn how to reset display of values in reports.

You can reset the report display values to their original default settings.

To reset the display settings to their defaults:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab, and then access the report that you want.
  3. From the Actions menu, select Reset.

    The Reset dialog appears with the following message:

    Restore report to the default settings.

  4. Click Apply.

7.5.3 Saving your Customized Reports

Learn how to save customized reports.

When you customize a built-in report with your specified filters and display settings, you can save this customized report. Such reports are listed in the Saved Reports tab in the Reports tab. The saved reports cannot be printed in PDF format, and therefore must be viewed online.

When you save a custom report, you can save it under a specific category that you select or create as you save the report. You can also make the custom report private or share it with other users as a public report.

To create and save a custom report starting from a built-in report:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab, and then access the report that you want.
  3. Filter and design the display as needed.
  4. From the Actions menu, select Save Report.
  5. Enter the following information in the Save Report dialog box:
    • Name: Enter a name for the report.

    • Description: Enter a brief description of the report.

    • Public: Select this check box to make the report accessible to all users.

  6. Click Apply.

7.5.4 Accessing Your Saved Custom Reports

Learn how to access saved custom reports.

To access a saved custom report:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab.
  3. Click Saved Reports tab in the left navigation menu.

    The Saved Reports page appears.

  4. In the Name column, select the link for the specific report.

    The report page is displayed. From here, you can:

    • Click the saved report name above the filter to edit it.

    • Click a filter to modify it. Alternately, you can click on Actions menu, and then click on Filter.

    • Remove a filter by clicking the Remove Filter icon (an "X")

    • Enable or disable a control break by selecting or unselecting its check box

    • Remove a control break by clicking the Remove Breaks icon (an "x")

    See Also:

7.5.5 Scheduling a Customized Report

Learn how to schedule a customized report.

A customized report has to be scheduled in the Audit Vault Server console. It is not available by default. Follow these steps to schedule a saved customized report:

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab.
  3. Click PDF/XLS Reports tab in the left navigation menu.
  4. In the Built-in Reports section, click the icons under Download Report Template and Download Report Definition columns.
  5. Save the files to your local machine.
  6. Modify the template and definition similar to any custom report.
  7. Click Upload button.
  8. Choose the template file and definition file in the dialog.
  9. Optionally enter the Description.
  10. Click Save.
  11. The newly uploaded report template and definition is visible in the PDF/XLS Reports section in the main page.
  12. Click the icon under Schedule Report column.
  13. Configure the report schedule details.
  14. After the report is generated, it is accessible in the Generated Reports tab.
  15. The report can be downloaded.

7.6 Scheduling and Generating PDF or XLS Reports

Auditors can schedule and generate PDF or XLS reports.

7.6.1 About Scheduling and Creating PDF or XLS Reports

Auditors can schedule reports to be sent to other users in PDF or XLS format.

You can run the report immediately, or you can create or select a schedule to run the report at a later time. You can specify a list of users who receive notifications of the report, or who need to attest to the report.

Note:

To schedule a saved interactive report, refer to Scheduling a Customized Report.

See Also:

The timestamp shown in scheduled reports is based on the Timezone Offset setting specified by the administrator in the Audit Vault Server. See Oracle Audit Vault and Database Firewall Administrator's Guide for more information.

7.6.2 Creating a Report Schedule

When you create a report schedule, you can add filters such as a limit to the number of rows generated.

  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab.
  3. Find the report you want to schedule, and click on the icon under Schedule.
  4. In the page displayed, under the Schedule Report section, select the Report Format. You can choose PDF or XLS.

    You can optionally change the Report Name. The Category Name field is already filled in and cannot be changed.

  5. In the Report Filters section, enter or select:
    • Target - This appears if applicable to the report.

    • Row Limit - The maximum limit for this parameter (ROW_LIMIT) is 999999.

    • Event Time

  6. In the Schedule section, select how you want to schedule the report:
    • Immediately - Run the report immediately

    • Specify Schedule - Select the Run Time, Run Date, and Repeat frequency.

    • Select Schedule - Select an existing schedule for the report by selecting a Schema where the schedule is stored, and the name of the Schedule from the drop-down lists.

      Note:

      These options only appears if a database administrator creates these schedules in the embedded Oracle Database using the DBMS_SCHEDULER PL/SQL package. The Schema list displays schemas that contain DBMS_SCHEDULER schedules. The Schedule list displays all the DBMS_SCHEDULER schedules in that schema. By default, the Schema drop-down list contains the SYS schema, which owns the DBMS_SCHEDULER package.
  7. In the Retention Policy section, if necessary, click on the edit icon to change the default archiving policy, and then click on the check mark.

    The archiving (or retention) policy is created by an Oracle Audit Vault and Database Firewall administrator, and determines how long the generated PDF or XLS report is retained in the Audit Vault Server before it is archived. If you do not select one, the default retention policy will be used (12 months retention online and 12 months in archives before purging).

  8. In the Notification section, optionally select users to notify about this report:
    • For the Send field, select either URL to send an email with a link to the report, or Attachment to send an email with the report attached as an XLS or PDF file.

    • From the Template drop-down list, select a report notification template.

    • From the Distribution List drop-down list, if applicable, select a distribution list.

    • If you want to send the report to additional recipients, enter their email addresses in the To e-mail and Cc fields. Enter full email addresses separated by commas.

    • Click Add to List.

  9. Under Attestation section, select one or more auditors who should attest to the report.

    Optionally, you can set the order in which the auditors are listed in the Attestation area.

  10. Click on the Schedule button at the top right corner of the page.

    The PDF or XLS is stored in the database, and the report appears in the Report Schedules tab under the main Reports tab.

    You can check the Jobs tab under Settings tab to see the status of report generation.

    Note:

    Avoid triggering or scheduling concurrent long running reports at the same time, as they may be left in a hung state forever. The reports must be scheduled with staggered intervals in between. For example, a gap of 5, 10, or 20 minutes.

7.6.3 Viewing or Modifying Report Schedules

An auditor can view and modify scheduled reports.

To view or modify report schedules, navigate to the Reports tab, and then click on Report Schedules on the left navigation.

See Also:

Creating a Report Schedule for details on report schedule fields.

7.6.4 Downloading Generated Reports in PDF or XLS Format

When scheduled reports are generated you can download them to your computer in PDF or XLS format.

The format in which you can download the report depends on the format you selected in your report schedule. You can also notify other users by sending a link to the report, or attaching the report in an email. You can download an unscheduled report in HTML or CSV format, while browsing it online.
  1. Log in to the Audit Vault Server as an auditor.
  2. Click on the Reports tab.
  3. Click on Generated Reports tab in the left navigation menu.

    A list of generated reports appear.

  4. From here, you can do the following:
    • To see a list of pending reports, click Show Pending Reports.

    • To save the report to your computer, click on the report name, and then save the file.

    • To notify another user of the report, select the check box against the specific report, and then click Notify.

    • To attest and annotate the report, click the Details icon in the second column.

7.6.5 Notifying Users About Generated PDF or XML Reports

You can send notifications to other users or distribution lists about a scheduled and generated report.

  1. Log in to the Audit Vault Server as an auditor.
  2. Click on the Reports tab.
  3. Click on the Generated Reports tab in the left navigation menu.

    A list of generated reports appear.

  4. Select the check box for the specific report and then click the Notify button.
  5. In the Notification section, perform the following:
    • For the Send field, select either URL to send an email with a link to the report, or Attachment to send an email with the report attached as an XLS or PDF file.

    • From the Template drop-down list, select a report notification template.

    • From the Distribution List drop-down list, if applicable, select a distribution list.

    • If you want to send the report to additional recipients, enter their email addresses in the To e-mail and Cc fields. Enter full email addresses separated by commas.

  6. Click Notify.

7.7 Annotating and Attesting Reports

After a report has been generated, auditors can annotate and attest to the report.

This enables you to create a record of all notes and attestations for the report in one place, with the most recent note and attestation listed first. If you delete the report, its associated annotation and attestations are removed as well.
  1. Log in to the Audit Vault Server as an auditor.
  2. Click the Reports tab.
  3. Click the Generated Reports tab in the left navigation menu.
  4. Click the Details icon of the specific report.
  5. Scroll down to the Attestations section.
  6. In the Notes field, enter a note for the report.
  7. Perform one of the following actions:
    • To save the note only, click the Save button. The note appears in the Previous Notes area.

    • To save the note and attest to the report, click the Save & Attest button. The note appears in the Previous Notes area and the Attestation area is updated with your user name and the time that you attested to the report.

    • To download the report, click the Download Report button.

  8. To return to the Generated Reports page, click the Cancel button.

    The Generated Reports page appears.

7.8 Creating Non-Interactive Report Templates

You can create, modify, and use existing PDF or XLS report templates.

Prerequisites

  • BI Publisher Desktop is installed on Microsoft Windows host.

  • User is able to log in to Audit Vault Server through console.

  • Information pertaining to the AVSYS schema holding audit data is available.

7.8.1 Creating Non-Interactive Report Template

You can create a new non-interactive or PDF/ XLS report, using an existing RTF or an XML report.

  1. Log in to the Audit Vault Server console as auditor.
  2. Click Reports tab and then click on PDF/XLS Reports tab (Report Templates tab starting with Oracle AVDF release 20.8) in the left navigation menu.
    Result:

    The page displays all the configured reports in two sections PDF/XLS Reports (Report Templates starting with Oracle AVDF release 20.8) and Built-in Reports.

  3. Click on the icon against any of the existing reports under the Download Report Template column.
  4. Save the report to your local drive with a new name.
  5. To preview changes in the RTF file requires sample data. Write a new Report SQL Query referring to the existing SQL in sample report XML file.
  6. The above SQL Query output is generated from SQL Developer and is exported into XML format. It is not compatible with RTF files. To generate data in RTF required XML format, use the DBMS_XMLGEN.GETXML () function. This is a built in function of Oracle Database.
  7. To generate XML data, use the SQL query string as a parameter to dbms_xmlgen.getxml() function.
    Result:

    It returns XML data as output.

    The below SQL example is for reference only.

    SELECT DBMS_XMLGEN.GETXML (‘YOUR REPORT SQL QUERY WITH PARAMETERS’) xml_data FROM dual;

    Example:

    SELECT DBMS_XMLGEN.GETXML('SELECT TO_CHAR(event_time, ''DS TS'') AS event_time, 
    event_name, 
    target_object, 
    event_status, 
    user_name,
    client_ip,
    client_program,
    secured_target_name, 
    COUNT(*) OVER () AS totalrowcount,
    COUNT(secured_target_name) OVER(PARTITION BY secured_target_name) AS securerowcount 
    FROM avsys.event_log elog 
    WHERE ROWNUM <= 3000
    AND ( event_time BETWEEN ''19-DEC-13 09.35.02.570000000 AM'' AND ''20-DEC-13 09.35.02.570000000 AM'' )
    AND secured_target_id IN(SELECT secured_target_id FROM avsys.secured_target   
                            WHERE ( 
                                    (secured_target_name_vc=UPPER(''MSSQLKVM5'') 
                                      OR 
                                      secured_target_name_vc LIKE UPPER(''MSSQLKVM5''||''_DELETED%'')
                                    ) 
                                      OR
                                      UPPER(''MSSQLKVM5'')=''ALL''
                                  )
                                )
    ORDER BY secured_target_name, elog.event_time') xml 
    from dual;
    

    Note:

    To generate SQL query string, use additional single quote inside this function for character identifier as escape character.

    For example:

    1. For DS TS date and timestamp formatting, apply single quote (') as escape character.

    2. For event_time timestamp parameter provide value as ''19-DEC-13 09.35.02.570000000 AM''.

      Note:

      Insert two single quotation marks for defining parameters.

    3. For database_name parameter provide value as ''MSSQL_ST''.

    4. Numeric values can be provided as is. Provide value for ROW_LIMIT parameter as 3000 or 20000 (any numerical value). Similarly make changes to other strings and parameters in the SQL query using single quotes.

  8. Copy the query output from SQL Developer tool (or any other tool).
  9. Paste it into notepad and save this file as XML.
  10. There is another option to use SPOOL command to generate XML file. See Generating XML Data File Using SPOOL Command for complete information. Load the generated XML file.
  11. Open the RTF template or sample report downloaded earlier using Microsoft Word.
  12. Click on BI Publisher tab on the top right corner.
  13. Click on Load XML and navigate to the generated XML and load it.
    Result:

    The following message is displayed:

    Data loaded successfully.

  14. Make the necessary changes to the report.
  15. If the file is in RTF format, then continue with the next step. Else, skip the remaining steps as they are relevant only for RTF files. Use Microsoft Word to edit the RTF file.
  16. Change the existing report name.
  17. Change report parameters like REPORT PERIOD, RUN BY, and REPORT RECORD LIMIT if required.
  18. Change the report parameter label if required.

    For example:

    Change the label RUN BY, you can change it directly to RUN BY USER.

  19. Change the report parameter value if required. This is the SQL query column name.

    For example:

    To change the TIME_FROM value double click on TIME_FROM. Or right click on it to access BI Publisher, then select Properties, and Advanced tab. To change <?TIME_FROM?> to data XML column name and the XML tag name for this column is TIME1, so your tag will be <?TIME1?>.

  20. To change existing chart double click on it and change VALUES, AGGREEGATION, LABELS, TYPE, and STYLE parameters. In case the chart is not required, then delete it.
  21. Change data table labels in the report if required. If the data table columns are different, the change the label and values as mentioned in earlier steps. To add additional columns, right click on the table, select Insert, and then select Insert columns to the Right. Similarly the columns can be merged and deleted.
  22. Change report header name if required.
  23. Choose to display target level count and level count.
  24. Retain the Time Zone and Date in footer section as they are common to all the reports.
  25. Click on the PDF or Excel icon in the tab to verify the changes.
  26. In case all the changes meet the requirements, then save the RTF file.

    Note:

    In the generated PDF report, data for parameters is not be displayed in header section. The parameters data is sourced from application runtime.

  27. This RTF report file can be uploaded along with XML report file for verification.
  28. Create the XML file.

    The following are the different tags in XML report file:

    1. Parameter: Add or change input report parameters in this tag if new report parameters are different.

    2. DATA: Contains the following tags or headers:

      • Column 1: Data Tag

      • Column 2: Description

      • AUDIT_SUBREPORT: Displays parameter values on RTF files in the header section. These change as per the new report parameters.

      • Time zone: Displays time zone information and is common for all the reports. This need not be changed.

      • TLQR: Contains report SQL and column mappings which should map with RTF column values. In this section, you need to paste your new report SQL query and column alias name mapping in XML column and values tags.

  29. This XML report file can be uploaded along with the RTF file generated earlier.

    Note:

    RTF and XML file names must be same.

  30. Navigate to the uploaded reports section in Audit Vault Server console and click Upload.
  31. Provide updated RTF and unchanged report definition taken from earlier steps.
  32. Verify the report in the Generated Report section under the Reports tab of the Audit Vault application.
  33. In case the report is not generated, then check the status of the report in Settings tab, and then select Jobs in the left navigation menu.

7.8.2 Modifying Non-Interactive Report Template

You can modify or make cosmetic changes to Audit Vault reports.

  1. Log in to the Audit Vault Server console as auditor.
  2. Click Reports tab.
    Result:

    The page displays the following reports in multiple sections:

    • Activity Reports
      • Summary
      • Data Access & Modification
      • Login & Logout Events
      • Database Settings
    • Entitlement Reports
    • OS Correlation Reports
    • Database Firewall Reports
    • Stored Procedure Changes
    • DB Vault Activity
  3. Click PDF/XLS sub tab in the left navigation menu.
  4. Click the download icon under Download Report Template and Download Report Definition file for the specific report.
  5. Previewing changes in the RTF file format, requires sample data. Copy the query data from the XML file which is similar to the following. Select the text mentioned below:
    to_char(event_time, 'DS TS') as event_time
    	client_ip,
    	user_name,
    	osuser_name,
    	client_program,
    	secured_target_name,
    	error_code,
    	error_message,
    	decode
    	{
    		audit_trail_id,
    		null, 'Network',
    		'Audit Trail'
    	}	as event_source
    from
    	avsys.event.log
    
  6. The query output generated from SQL Developer and exported into XML format is not compatible with RTF files.
  7. To generate XML data, use the dbms_xmlgen.getxml() function. This is a built in function of Oracle Database.
  8. Pass SQL query string as a parameter to dbms_xmlgen.getxml() function.
    Result:

    It returns XML data with sample output mentioned below.

    SELECT DBMS_XMLGEN.GETXML('SELECT TO_CHAR(event_time, ''DS TS'') AS event_time, 
    event_name, 
    target_object, 
    event_status, 
    user_name,
    client_ip,
    client_program,
    secured_target_name, 
    COUNT(*) OVER () AS totalrowcount,
    COUNT(secured_target_name) OVER(PARTITION BY secured_target_name) AS securerowcount 
    FROM avsys.event_log elog 
    WHERE ROWNUM <= 3000
    AND ( event_time BETWEEN ''19-DEC-13 09.35.02.570000000 AM'' AND ''20-DEC-13 09.35.02.570000000 AM'' )
    AND secured_target_id IN(SELECT secured_target_id FROM avsys.secured_target   
                            WHERE ( 
                                    (secured_target_name_vc=UPPER(''MSSQLKVM5'') 
                                      OR 
                                      secured_target_name_vc LIKE UPPER(''MSSQLKVM5''||''_DELETED%'')
                                    ) 
                                      OR
    
                                      UPPER(''MSSQLKVM5'')=''ALL''
                                  )
                                )
    ORDER BY secured_target_name, elog.event_time') xml 
    from dual;
    

    Note:

    To generate SQL query string, use additional single quote inside this function for character identifier as escape character.

    For example:

    1. For ''DS TS'' date and timestamp formatting, apply single quote (') as escape character.

    2. For event_time timestamp parameter provide value as ''19-DEC-13 09.35.02.570000000 AM''.

      Note:

      Insert two single quotation marks for defining parameters.

    3. For database_name parameter provide value as ''MSSQL_ST''.

  9. The above SQL query generates data in XML format, which can be uploaded in BI publisher template (RTF).
  10. Copy the query output from SQL Developer tool (or any other tool).
  11. Paste it into notepad and save this file as XML.

    Note:

    There is another option to use SPOOL command to generate XML file. See Generating XML Data File Using SPOOL Command for complete information. Load the generated XML file.

7.8.3 Generating XML Data File Using SPOOL Command

You generate XML from SQL*Plus using the SPOOL command.

  1. Take the SQL query used to generate data in XML format.

    For example:

    SELECT DBMS_XMLGEN.GETXML('SELECT TO_CHAR(event_time, ''DS TS'') AS event_time, 
    event_name, 
    target_object, 
    event_status, 
    user_name,
    client_ip,
    client_program,
    secured_target_name, 
    COUNT(*) OVER () AS totalrowcount,
    COUNT(secured_target_name) OVER(PARTITION BY secured_target_name) AS securerowcount 
    FROM avsys.event_log elog 
    WHERE ROWNUM <= 3000
    AND ( event_time BETWEEN ''19-DEC-13 09.35.02.570000000 AM'' AND ''20-DEC-13 09.35.02.570000000 AM'' )
    AND secured_target_id IN(SELECT secured_target_id FROM avsys.secured_target   
                            WHERE ( 
                                    (secured_target_name_vc=UPPER(''MSSQLKVM5'') 
                                      OR 
                                      secured_target_name_vc LIKE UPPER(''MSSQLKVM5''||''_DELETED%'')
                                    ) 
                                      OR
    
                                      UPPER(''MSSQLKVM5'')=''ALL''
                                  )
                                )
    ORDER BY secured_target_name, elog.event_time') xml 
    from dual;
  2. Connect to the Audit Vault Server Database as avsys user.
  3. Execute the command:

    spool <path of the xml file>/<name of the xml file>.xml

  4. Run the SQL query from the earlier step.
  5. Execute the following command to turn off generating the XML data file further:

    spool off

  6. Check the XML file generated in the location defined earlier. Remove unwanted strings and retain only the data.
  7. Save it.
  8. Open the RTF template downloaded earlier.
  9. Click on BI Publisher tab on the top right corner.
  10. Click on Load XML.
  11. Navigate to the location of the generated XML file.
  12. Load it.
    Result:

    The following message is displayed:

    Data loaded successfully.

  13. Make the necessary changes.
  14. To verify the change, click on the PDF or Excel icon in the tab.
  15. If all the changes are complete as expected, save the RTF file.

    Note:

    In the generated PDF report, data for parameters is not displayed in the Header. These parameters and data is captured during application runtime.

  16. Navigate to the uploaded reports section in Audit Vault Server console and click Upload.

    Note:

    RTF and XML file names must be same.

  17. Provide updated RTF and unchanged report definition taken from earlier steps.
  18. Verify the report on the server.

7.8.4 Generating Reports Using RTF And XML Sample Templates

You can generate reports using RTF and XML sample templates.

  1. Use the existing XML and RTF report files.
  2. Save them with a new report name.
  3. To preview changes to the RTF file, sample data is required. Write a new Report SQL Query.
  4. The above SQL Query output is generated from SQL Developer and is exported into XML format. It is not compatible with RTF files. To generate data in required RTF XML format, use the DBMS_XMLGEN.GETXML () function. This is a built in function of Oracle Database.
  5. Provide SQL query string as a parameter to dbms_xmlgen.getxml() function. Execute:

    SELECT DBMS_XMLGEN.GETXML (‘YOUR REPORT SQL QUERY WITH PARAMETERS’) xml_data

    FROM dual;

    Result:

    It returns the following example XML data as output:

    SELECT DBMS_XMLGEN.GETXML('SELECT TO_CHAR(event_time, ''DS TS'') AS event_time, 
    event_name, 
    target_object, 
    event_status, 
    user_name,
    client_ip,
    client_program,
    secured_target_name, 
    COUNT(*) OVER () AS totalrowcount,
    COUNT(secured_target_name) OVER(PARTITION BY secured_target_name) AS securerowcount 
    FROM avsys.event_log elog 
    WHERE ROWNUM <= 3000
    AND ( event_time BETWEEN ''19-DEC-13 09.35.02.570000000 AM'' AND ''20-DEC-13 09.35.02.570000000 AM'' )
    AND secured_target_id IN(SELECT secured_target_id FROM avsys.secured_target   
                            WHERE ( 
                                    (secured_target_name_vc=UPPER(''MSSQLKVM5'') 
                                      OR 
                                      secured_target_name_vc LIKE UPPER(''MSSQLKVM5''||''_DELETED%'')
                                    ) 
                                      OR
                                      UPPER(''MSSQLKVM5'')=''ALL''
    
                                  )
                                )
    ORDER BY secured_target_name, elog.event_time') xml 
    from dual;
    

    Note:

    To generate SQL query string, use additional single quote inside this function for character identifier as escape character.

    For example:

    1. For ''DS TS'' date and timestamp formatting, apply single quote (') as escape character.

    2. For event_time timestamp parameter provide value as ''19-DEC-13 09.35.02.570000000 AM''.

      Note:

      Insert two single quotation marks for defining parameters.

    3. For database_name parameter provide value as ''MSSQL_ST''.

    4. Numeric values can be provided as is. Provide value for ROW_LIMIT parameter as 3000 or 20000 (any numerical value).

    5. Apply additional single quote (') for string and date parameters, if they are present in SQL query.

  6. Copy the query output from SQL Developer tool (or any other tool).
  7. Paste it into notepad and save this file as XML.
  8. There is another option to use SPOOL command to generate XML file. See Generating XML Data File Using SPOOL Command for complete information. Load the XML data file.
    Result:

    The following message is displayed:

    Data loaded successfully.

  9. Make the changes to the RTF file as required. Change the report header name.
  10. Change the report parameters like Label and Values if required.

    For example:

    To change the label use option like RUN BY.

  11. To change the TIME value double click on one of the TIME fields. Or right click on it to access BI Publisher, then select Properties, and then Advanced tab. In the Advanced tab, add column reference value in <?ColumnName?> format. This column name is a reference of SQL Query output column name.
  12. To change the Report Chart go to BI Publisher tab, and click on CHART. Add chart as per your requirement by providing uploaded XML data as parameters.
  13. In the Report Data Table, go to BI Publisher tab, and click on TABLE WIZARD. Select the columns to be displayed in the table.
  14. Change the report header of the second page.
  15. In the target group level and total, choose aggregation at target level and total count at report level. Execute:

    count(*) over () as totalrowcount,

    count(secured_target_name) over(partition by secured_target_name) as securerowcount

  16. Keep same columns alias so that they can be referred in the report.
  17. Retain the Time Zone and Date in footer section as they are common to all the reports.
  18. Click on the PDF or Excel icon in the tab to verify the changes.
  19. In case all the changes meet the requirements, then save the RTF file.

    Note:

    In the generated PDF report, data for parameters is not be displayed in header section. The parameters data is sourced from application runtime.

  20. This RTF report file can be uploaded along with XML report file for verification.
  21. Create report XML using an existing template. Follow and use the comments existing in the template and modify accordingly. This is the report XML which is used to upload along with RTF file generated earlier.
  22. Log in to the Audit Vault Server console as auditor.
  23. Click Reports tab.
  24. Click PDF/XLS Reports tab (Report Templates tab starting with Oracle AVDF release 20.8) in the left navigation menu.
  25. Click Upload button in the top right corner.

    The Upload Custom Report dialog is displayed. Click the fields to choose the file saved in your local machine.

    Note:

    The RTF and XML file names must be same.

  26. Optionally enter the Description and click Save.
  27. Verify the report in the Generated Reports tab.
  28. In case the report is not generated, then check the status in the Settings tab, and then select the Jobs tab in the left navigation menu.

7.9 Creating and Uploading Your Own Custom Reports

You can add your own custom reports by using Oracle BI Publisher, or another report authoring tool from a third party.

You will need a report definition file (XML format) and a report template (RTF format), which you can download from Oracle Audit Vault and Database Firewall. This section describes how to download these files from an existing Oracle Audit Vault and Database Firewall report and use them for your own report. The audit event appendices in this guide contain data that may help you in creating your own reports.
  1. Log in to the Audit Vault Server console as an auditor.

  2. Click on Reports tab.

  3. Click PDF/XLS Reports tab (Report Templates tab starting with Oracle AVDF release 20.8) in the left navigation menu.

    The PDF/XLS Reports (or Report Templates) page is displayed. It lists previously uploaded custom reports. The built-in reports are listed under the Built-in Reports section.

  4. Find a built-in report to use as a starting point for your new custom report.

  5. Click the Download Report Template icon and save the RTF file.

  6. Click the Download Report Definition icon and save the XML file.

  7. Customize the report definition and template files using either Oracle BI Publisher or another tool, as necessary.

  8. Click Upload button located in the top right corner.

    The Upload Custom Report dialog is displayed.

  9. In the Report Template file field, enter the name or browse for your customized report template (RTF) file.

  10. In the Report Definition file field, enter the name or browse for your customized report definition (XML) file.

  11. Optionally enter the Description.
  12. Click Save.

    The new report is listed under PDF/XLS Reports tab (Report Templates tab starting with Oracle AVDF release 20.8).

7.10 Activity Reports

Activity reports capture information such as alerts or changes to stored procedures.

7.10.1 About the Activity Reports

Activity reports cover entitlement data, operating system correlation data, alerts, and other information.

You can access Activity Reports from the Reports tab by clicking Activity Reports. There are six groups of Activity Reports:

  • Activity Reports

  • Entitlement Reports

  • OS Correlation Reports

  • Database Firewall Reports

  • Stored Procedure Audit Reports

  • DB Vault Activity

7.10.2 Activity Reports

Learn about activity reports.

Note:

The maximum limit for the ROW_LIMIT parameter is 20000. Use additional filters available to view specific audit events in the report.
7.10.2.1 About the Activity Reports

Any auditor can access the activity reports.

You can access Activity Reports from the Reports tab, and then by clicking Activity Reports tab in the left navigation menu. In the main page expand the Activity Reports section.

The default activity reports track general database access activities such as all activity, data access activities, user login/logout activities, and entitlements.

Note:

  • The Client Host (host name of the client) value is displayed in the reports only if the DNS is configured for the Audit Vault Server.
  • The Event Status value in the reports is displayed only if Database Response Monitoring is enabled for the respective monitoring point.
7.10.2.2 Activity Overview Report

The Activity Overview Report captures information about all monitored and audited events.

The Activity Overview can be viewed from Saved Reports tab in the left navigation menu. The Activity Overview page provides a summary of all audited and monitored events.

Events appear based on their audit event time in descending order (newest record first). This report can be very large, but you can create a user defined version that filters specific audit data. By default, 15 audit records are displayed on each page.

If you suspect that the Oracle Audit Vault and Database Firewall data warehouse is not being refreshed with the latest audit data, then check the Activity Overview Report. If you find that the audit data that you want is not listed in this report, then ask your Oracle Audit Vault and Database Firewall administrator to check the server-side log files (alert and trace logs) for errors. If there are errors, then contact Oracle Support.

Note:

Apply filters based on date and time. Access the audit interactive reports. For example, Activity Overview report. Click on Actions, and then select Filter. Choose Row as the Filter Type.

In the Filter Expression field, enter the query as follows:

<event_time> BETWEEN ‘MM/DD/YYYY HH:MM:SS PM/AM’ and ‘MM/DD/YYYY HH:MM:SS PM/AM’

For example:

BZ BETWEEN ‘8/20/2018 2:30:50 PM’ and ‘8/20/2018 2:40:50 PM’

7.10.2.3 All Activity Report

The All Activity report displays details of all captured audit events for a specified period of time.

7.10.2.4 All Activity by Privileged Users

The All Activity by Privileged Users report displays details of observed activity of privileged users targeting audit policy for a specified period of time.

7.10.2.5 Data Access Report

The Data Access report displays details of read access events.

For example: SELECT, read access to data events.

See Also:

Related Event Data Appendices for related data access audit events in a specific target type

7.10.2.6 Audit Policy Activity Report

The Audit Settings report (known as Audit Policy Activity report in Oracle AVDF 20.4 and later) is available under the Database Settings category.

It displays details of observed user activity targeting audit settings for a specified period of time.

7.10.2.7 Data Modification Report

The Data Modification report displays events that lead to data modification.

For example: DML activities (insert, update, and delete).

7.10.2.8 Data Modification Before-After Values Report

The Data Modification Before-After Values report displays before and after values of modified data in Oracle Database.

Data for this report comes from the TRANSACTION LOG audit trails written by databases. An administrator must configure and start a TRANSACTION LOG audit trail for the target to be monitored. The location for the TRANSACTION LOG audit trail should be the Golden Gate Integrated Extract directory. See Transaction Log Audit Data Collection Reference for more information.

The user can filter the Data Modification Before-After Values report. To apply the filter on a Column Name, Before Value, and After Value, select Like as the Operator.

Note:

  • The Transaction Log collector is capable of reading the Golden Gate Integrated Extract files which contain the Before and After records. Golden Gate must be configured to generate the Integrated Extract in the XML_AUDIT_VAULT XML format.
  • To check the change in column value of a particular table, add filter on Target Object. The filter can be something like, Target Object Equal to (=) table name and Column Name in the Column field. For example, if the Address column of employee table is changed, the filter should be Target Object = EMPLOYEE and Column Name like %ADDRESS%.
7.10.2.9 Database Schema Activity Report

The Database Schema report (known as Database Schema Activity report in Oracle AVDF 20.4 and later) is available under Database Settings category.

It displays information about changes in the database schema. For example: DDL commands (alter session).

7.10.2.10 Entitlement Activity Report

The Entitlements Report (known as Entitlement Activity report in Oracle AVDF 20.4 and later) is available under Entitlement Reports category.

It displays information about changes in grants of database privileges and roles. For example: DCL commands (GRANT access, revoke).

7.10.2.11 Failed Login Events Report

The Failed Login Events report displays information about failed authentication attempts.

7.10.2.12 Login and Logout Report

The Login and Logout report displays information about all successful login and logout events.

7.10.2.13 Startup and Shutdown Report

The Startup and Shutdown report displays details of observed targets startup and shutdown events for a specified period of time.

7.10.3 Entitlement Reports

The Entitlement Reports capture information such as a user's roles, object privileges, system privileges, and so on.

7.10.4 OS Correlation Reports

The OS Correlation Reports provide useful information about operating system related activities that users perform.

The Linux SU SUDO Transition report provides details of database events that are correlated with the Linux operating system user before su or sudo transition. It is specific to Oracle Database targets running on Linux. This report uses the OS and Database audit trails to correlate su and sudo activity on the Linux OS with Oracle Database audit events. This lets auditors see the original OS user in cases where this user runs a shell or executes a command as another user by using su or sudo.

For example, suppose the Linux OS user, user_01, logs in to a Linux terminal, and then performs su or sudo activity to another Linux user, user_02. Then user_01 connects as the Oracle Database user user_db locally and then remotely, and performs some database activities.The Linux SU SUDO Transition report displays the Oracle Database audit events with the additional columns OS User Transition, Transition Type, and Database Connection Type. These columns provide information about the correlation that occurred before the Oracle Database operations. For example:

Column Name Data

OS User Transition

user_01 > user_02

Transition Type

su (for a sudo operation, it would list sudo)

Database Connection Type

Local (for a remote database connection, it would be remote)

Database User Name

user_db

Similarly, the Linux SU SUDO Transition Report displays data for local and remote database connections and for SYS and non-SYS users.

In order to generate information for this report, you must have audit trails configured and running for both the Oracle Database and for the Linux OS on which the database runs. The Linux OS audit trail must be registered with a host name, and not an IP address. See Oracle Audit Vault and Database Firewall Administrator's Guide for instructions on how to configure audit trails in Oracle AVDF.

Be aware that if there is a slippage in Linux events, then the report does not show the correct correlation data.

Table 7-1 shows the currently available correlation reports.

Table 7-1 su/sudo Correlation Reports

Report Description

Linux SU SUDO Transition

Details of database events correlated with the Linux operating system user before su or sudo transition

7.10.5 Database Firewall Reports

The Database Firewall Reports contain data that is collected if a target is monitored by the Database Firewall (using a firewall policy).

Data displayed as part of the reports include:

  • Database Firewall action and threat level

  • Database user name

  • OS user name

  • Statement type (data definition, procedural, data manipulation, etc.)

  • Client application name and IP address

  • SQL request ID

  • Database Firewall cluster ID

  • Policy Name

  • Rule Type

  • Rule Name

Note:

  • The Client Host (host name of the client) value is displayed in the reports only if the DNS is configured for the Audit Vault Server.
  • The Event Status value in the reports is displayed only if Database Response Monitoring is enabled for the respective monitoring point.

Table 7-2 lists the Database Firewall reports.

Table 7-2 Database Firewall Reports

Report Name in Oracle AVDF 20.4 and Earlier Report Name in Oracle AVDF 20.5 and Later Description

Database Firewall Monitored Activity

Monitored Activity

SQL activity monitored by Database Firewall.

Blocked Statements

Blocked Activity

SQL activity blocked by Database Firewall.

Database Traffic Analysis by OS User

Monitored Activity by OS User

SQL activity monitored by Database Firewall grouped by OS user.

Invalid Statements

Invalid Activity

SQL activity not recognized by Database Firewall.

Warned Statements

Alert Activity

SQL activity marked as warning by Database Firewall.

7.10.6 Stored Procedure Changes

The Stored Procedure Changes auditing reports allow you to audit changes to stored procedures on target databases.

You can access Stored Procedure Changes reports from the Reports tab, and then by clicking Activity Reports in the left navigation menu. The Stored Procedure Changes reports is displayed in the list of reports on the main page.

Oracle Audit Vault and Database Firewall connects to the target database at scheduled intervals and discovers any changes or additions that have been made to stored procedures.

Table 7-3 lists the Stored Procedure Auditing reports.

Table 7-3 Stored Procedure Changes Report

Report Description

Created Stored Procedures

Creation history of stored procedures

Stored Procedure Modification History

Modifications of stored procedures

Deleted Stored Procedures

Deletion history of stored procedures

7.10.7 DB Vault Activity

If your Oracle Database targets have Database Vault enabled, then the Database Vault Activity report shows the details of Oracle Database Vault activity.

This report captures activities such as Database Vault events that capture policy or rule violations, unauthorized access attempts, and so on.

Oracle Database Vault may be enabled in an Oracle Database target to provide greater security by restricting access to sensitive areas of the database. For example, you can restrict administrative access to employee salaries, customer medical records, or other sensitive information.

You can check if Oracle Database Vault is enabled in a target by running the following SQL query in SQL*Plus:

SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

Remember that the PARAMETER column value is case sensitive.

If Oracle Database Vault is enabled, the following output appears:

PARAMETER                     VALUE
----------------------------- -----------------------
Oracle Database Vault         TRUE

7.10.8 Alert Reports

The alert reports track critical and warning alerts.

Alert reports can be accessed from the Alerts tab, and then the Alerts tab in the left navigation menu.

Note:

Starting with 20.1 the alerts reports are accessed from the Alerts tab instead of from the Reports tab.

Alternatively, you can view the All Alerts report from the Reports tab and the Generated Reports tab on the left navigation menu. Enable the filter for the All Alerts report name above the table. Click the All Alerts name to download the report.

An alert is raised when data in audit records matches a predefined alert rule condition. Alerts are grouped by associated target, by event category, and by the severity level of the alert (either warning or critical).

There are two alert severities:

  • Critical Alerts - This report shows critical alerts that were raised by Audit Vault Server.
  • Warning Alerts - This report shows warning alerts that were raised by Audit Vault Server.

See Also:

7.11 Summary Reports

Learn about Summary Reports.

7.11.1 Trend Charts Report

The Trend Charts Report shows the event trends (total events) in the last n days.

Table 7-4 shows the available event trend reports.

Table 7-4 Trend Charts

Report Description

Event Trend

Trend of all events

Event Trend By Target

Trend of events by target

Event Trend By Client IP

Trend of events by client IP

Event Trend By OS User

Trend of events by OS user

7.11.2 Anomaly Reports

The Anomaly Reports Report shows new and dormant user and client IP anomalies (total anomalies) in the last n days.

Table 7-5 shows the available anomaly reports.

Table 7-5 Anomaly Reports

Report Description

New or Dormant User Activity

Activity by newly created or dormant users

New or Dormant Client IP Activity

Activity from newly seen or dormant client IPs

7.11.3 All Activity Reports

The All Activity Reports report shows summaries of client and operating system user activities, DDL and DML activities, and failed logins.

Table 7-6 shows the available summary reports.

Table 7-6 All Activity Reports

Report Description

Activity Summary by Client IP and OS User

Events grouped by user and client IP

Activity Summary by Target

Events grouped by target

DDL Activity Summary by Target

Schema changes grouped by target

DML Activity Summary by Target

Data modifications grouped by target

Failed Logins Summary by Target

Failed authentication attempts grouped by target

7.12 Compliance Reports

The Compliance Reports capture information to ensure that your system is meeting regulatory compliance standards.

7.12.1 About the Compliance Reports

The compliance reports help you meet regulations associated with credit card, financial, data protection, and health care related data.

They track activities that are typically required to meet standard compliance regulations, such as changes to the database structure or its objects, failed logins, administrator activities, system events, and user logins or logoffs.

The following compliance report categories are available:

  • Data Privacy Reports (GDPR)

  • Payment Card Industry (PCI)

  • UK Data Protection Act (DPA)

  • USA Gramm-Leach-Bliley Act (GLBA)

  • USA Health Insurance Portability and Accountability Act (HIPAA)

  • USA Reports based on IRS Publication 1075

  • USA Sarbanes-Oxley Act (SOX)

To access the compliance reports, click the Reports tab, and then click on Compliance Reports tab in the left navigation menu.

7.12.2 Data Privacy Reports

Learn about the Data Privacy Reports.

7.12.2.1 About Data Privacy Reports

Data privacy is also known as information privacy or data protection.

It is concerned with the relationship between collection and dissemination of data and technology, the public perception, expectation of privacy, the legal regulation, and political issues surrounding that data. The details and implementation of data protection vary depending on the region, the context, the methods, and the extent to which it is regulated.

GDPR (General Data Protection Regulation) is a regulation in European Union (EU) law on data protection and privacy for all individuals within the European Union. It addresses the export of personal data outside the EU. GDPR is an overhaul of the existing European Commission data protection legislation. It harmonizes data privacy laws, aims to strengthen, and unify these laws for EU citizens. GDPR is about individuals having autonomy and control over their data. It primarily aims to give control back to citizens and residents over their personal data and to simplify the regulatory environment for international business by unifying the regulation within the EU. It is important for organizations to protect information they possess about individuals to prevent others from accessing or misusing their personal information.

GDPR is applicable in case the following are based in the European Union:

  • Data controller

  • Data processor

  • Data subject or the person

  • Data recipient

  • Authority supervising and auditing data

  • An organization that collects data from EU residents

  • An organization that processes data on behalf of data controller like the service providers

  • An organization based outside the EU that collects or processes personal data of individuals located inside the EU

According to the European Commission, personal data is any information relating to an individual. This information can be private, professional, or public life of the individual. It includes, but is not limited to, a name, a home address, a photo, an email address, bank details, posts on social networking websites, medical information, or an IP address.

In order to comply with GDPR, the data controller must implement measures, which meet the principles of data protection by design, and data protection by default. It is the responsibility and the liability of the data controller to implement effective measures and to demonstrate the compliance of processing activities. This includes if the processing is performed by an external data processor on behalf of the controller.

GDPR considers encryption as one of the components in the security strategy, and mandates that organizations need to consider assessment, preventive, and detective controls based upon the sensitivity of the personal data in their possession.

Articles 30 and 33 of GDPR, mandate that organizations must maintain a record of its processing activities. This can only be achieved by constantly monitoring and auditing activities on personal data. This data can be used to timely notify authorities in case of a breach. In addition to mandating auditing and timely alerts, GDPR also requires that organizations must keep the audit records under their control. A centralized control of audit records prevents attackers or malicious users to cover the tracks of their suspicious activity by deleting the local audit records. There are four reports under Data Protection. They primarily focus on access to sensitive data by regular or privileged users and also privilege settings on objects.

7.12.2.2 Importing Sensitive Data Into Repository

Information about sensitive data is imported and stored in the Oracle AVDF repository.

You can import a data file in .csv and .xml format. These data files are sourced from Oracle Enterprise Manager and Oracle Database Security Assessment Tool by running data discovery job to search for sensitive data in specific Oracle Database targets.

Oracle Database Security Assessment Tool generates the file in .csv format and Oracle Enterprise Manager generates the file in .xml format. The data file extracted contains a list of sensitive columns that is imported into the repository. It is viewed in the Audit Vault Server GUI using Data Privacy Reports.

Note:

Oracle AVDF 20 supports Oracle Enterprise Manager Cloud Control versions 13.4 and later.

See Also:

  1. Ensure you have the sensitive data report in .csv or .xml format by running data discovery job through Oracle Database Security Assessment Tool or Oracle Enterprise Manager respectively.
  2. Save the file in your local drive.
  3. Log in to the Audit Vault Server terminal as root user.
  4. Switch to oracle user, by running the following command:

    su - oracle

  5. Run the following commands to grant (or revoke) av_sensitive role to the admin user, or list of admin users.

    For Oracle AVDF 20.5 and Later

    Action Command

    To grant av_sensitive role to the admin user.

    /usr/local/dbfw/bin/av_sensitive_role.py grant <admin1> <admin2>

    To revoke av_sensitive role from the admin user.

    /usr/local/dbfw/bin/av_sensitive_role.py revoke <admin1> <admin2>

    For Oracle AVDF 20.4 and Earlier

    Action Command

    To grant av_sensitive role to the admin user.

    /usr/local/dbfw/bin/av_sensitive_role grant <admin1> <admin2>

    To revoke av_sensitive role from the admin user.

    /usr/local/dbfw/bin/av_sensitive_role revoke <admin1> <admin2>
  6. Log in to the Audit Vault Server console as admin user.
  7. Click on Targets tab. The Targets sub tab in the left navigation menu is selected by default. The main page lists the configured targets to which you have access. You can sort or filter the list of targets.
  8. Click the name of a specific target.

    The specific target page is displayed.

  9. Click Sensitive Objects button in the top right corner.
  10. Click Browse against the Import From (.xml / .csv) field. Choose the sensitive data file saved in your local drive.
  11. Click Upload.

    Result:

    A pop up message File loaded successfully is displayed on the screen. The recent target file upload information is displayed on the GUI. The previous one is overwritten.

  12. Click Save.

    Note:

    • In case the user does not have the required role to import the sensitive data, or if the uploaded file is in incorrect format, then appropriate error message is displayed.

    • The report contains sensitive data generated from the recent .csv or .xml file uploaded. The earlier imported sensitive data is overwritten and the history is not maintained.

7.12.2.3 Accessing Data Privacy Reports

The sensitive data file is imported into the repository.

Once the sensitive data definitions are imported into the repository, the Audit Vault Server GUI is used to view related Data Privacy Reports. This section contains information on how to access the reports that contain sensitive data.
  1. Ensure the appropriate entitlement data is available for the target.
  2. Log in to the Audit Vault Server GUI as auditor.
  3. Click on Reports tab.
  4. Click on Compliance Reports sub tab in the left navigation menu.

    Data Privacy Reports is displayed on the main page.

  5. Click on the Go button against the field To associate Target(s) with this Compliance Category, click on the Go button.
  6. Check the box against the specific target displayed in the list. In case the specific target is not listed, then use the search option to find the target and then select it.
  7. Click Add. This associates the specific target with the compliance group.
  8. Click Save.
  9. Navigate back to the Compliance Reports.

    The page lists several sensitive reports:

    Report Description

    Sensitive Data

    Displays details of sensitive data like the Schema Name, Target Object, Column Name, Sensitive Type, and Target Type.

    Access Rights to Sensitive Data

    Displays details of user's access rights to sensitive data.

    Activity on Sensitive Data

    Displays details of activity on sensitive data by all users.

    Activity on Sensitive Data by Privileged Users

    Displays details of activity on sensitive data by privileged users.

    Note:

    • To view the privileges granted to users on sensitive data, see Access Rights to Sensitive Data report. See the below example report for reference. The user may have one or more of the privileges listed in the Privilege column for the respective sensitive data.

    • The user can get these privileges assigned directly or through roles granted.

    • Privileges granted to sensitive data that is assigned to a role is displayed only when the role is assigned to any user.

    • Privileges on sensitive data may be granted to the user group PUBLIC. In such a case the privilege is granted to all users. This privilege granted to PUBLIC is not visible in the report.

    • The report contains only privileges on sensitive data granted as an object privilege. System privileges are not displayed in the report.

    Target Name : <Target Name 1>

    Sensitive Object User Name Privileges

    Table_1

    User X

    DELETE,INSERT,SELECT,UPDATE

    Table_2

    User Y

    DELETE,INSERT,SELECT,UPDATE

    Table_3

    User Z

    DELETE,INSERT,SELECT,UPDATE

  10. Click on the specific report to access it.
7.12.2.4 Implementation In Oracle Audit Vault And Database Firewall

Oracle Audit Vault and Database Firewall complies with data protection directives and regulations by offering special services.

These services include centralized auditing, monitoring, reporting, and alerting of anomalous activity on the database. It reports any access to sensitive data stored in the database.

The report relates to sensitive data, as identified and received from the sensitive data discovery processes. It contains information regarding activity on sensitive data by all users including privileged users.

Oracle Audit Vault and Database Firewall complies with data protection at source by centralizing control and administration. It stores and manages the data for processing in a centralized location. It monitors and sends timely alerts of suspicious behavior. It can centrally manage millions of audit records, or different types of security policies, by simplifying the administration related tasks. This is managed using Oracle Enterprise Manager that has a unified web based GUI.

Oracle Audit Vault and Database Firewall centrally collects and manages audit records. It monitors, alerts, reports, and blocks suspicious behavior.

Note:

Oracle Audit Vault and Database Firewall helps in complying with data privacy regulations such as GDPR.