5 Oracle Audit Vault and Database Firewall Reports and Alerts

Topics

5.1 Introduction to Oracle Audit Vault and Database Firewall Reports

As an Oracle AVDF auditor, you can generate built-in audit reports that capture a wide range of audit data.

Oracle AVDF reports allow you to examine audit data in a consolidated fashion, that is, they show audit data collected from various secured targets, as well as data from Database Firewalls you have deployed. You can use the reports to monitor activities of interest, to meet regulatory requirements, and as a basis for setting up additional alerts to meet your needs.

The built-in reports are organized into various categories, such as activity reports and compliance reports. An alerts report allows you to view and respond to alerts. To meet regulatory requirements, you can produce reports such as 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 (shown in Figure 5-1).

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, grouping, and highlighting data, and selecting the report columns to display. You can save these interactive views to see them online later. You can also send a report to other auditors for attestation.

Figure 5-1 Oracle AVDF Built-in Reports - Compliance Reports Section

Description of "Figure 5-1 Oracle AVDF Built-in Reports - Compliance Reports Section"

5.2 Built-in Reports

Topics

5.2.1 How to Use the Built-in Reports

You can run the built-in report immediately, or you can create 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.

While browsing reports online, you can download them in HTML or CSV format. You can also schedule reports and download them in PDF or XLS format, or send them to other users. When you specify report notifications, you can use your own notification templates to send emails to other users with either a link to a report, or an attached PDF version of the report.

Figure 5-2 shows a few of the built-in audit reports.

Figure 5-2 Browsing, Scheduling, or Viewing Previously Generated Reports

Description of "Figure 5-2 Browsing, Scheduling, or Viewing Previously Generated Reports"

5.2.2 Available Built-in Reports

There are many built-in reports that you can use to monitor your systems with Oracle Audit Vault and Database Firewall.

See Also:

Oracle Audit Vault and Database Firewall Auditor's Guide for a complete list of the built-in reports.

The following table summarizes the different types of reports available.

Table 5-1 Available Types of Built-in Reports in Oracle Audit Vault and Database Firewall

Types of Reports	Description
Activity	A set of reports that track general database access activities such as audited SQL statements, application access activities, and user login activities. Some typical reports are: Activity Overview: Displays information about all monitored and audited events Data Modification: Displays the details of audited data modifications for a specified period of time Data Modification Before-After Values: Displays the details of modified data and lists the values before and after modification. This report can be filtered. Note: The transaction log collector uses Oracle Streams to collect the audit trail. When the transaction log trail is added, it creates the capture process on the secured target. When the capture process begins, it creates a log miner dictionary in an archive log. From then onwards, only the Before and After records from the archive logs are captured. It is not possible to acquire the Before and After values before the creation of the log miner dictionary. So transaction log trails cannot capture the old data. While setting up a REDO collector, no role should be granted to the source user other than `DV_STREAMS_ADMIN`. To set up `DVSYS.AUDIT_TRAIL$` table trail, first set up the REDO collector with the role `DV_STREAMS_ADMIN` role granted to the source user. Once REDO collector is up and running, grant the role `DV_SECANALYST` to the source user. Database Schema Changes: Displays details of audited DDL activity for a specified period of time Login Failures: Displays details of audited failed user logins for a specified period of time
Alert	Alert reports track critical and warning alerts, and also let you respond online to alerts and notify others about them.
Stored Procedure Audit	A set of reports that help you keep track the changes made to the stored procedures, for example: Stored Procedure Modification History: Displays details of audited stored procedure modifications for a specified period of time Created Stored Procedures: Displays information about stored procedures created within a specified period of time Deleted Stored Procedures: Displays information about deleted stored procedures deleted within a specified period of time
Compliance	A set of reports that track possible violations that are defined by the following compliance areas: Payment Card Industry (PCI) Gramm-Leach-Bliley Act (GLBA) Health Insurance Portability and Accountability Act (HIPAA) Sarbanes-Oxley Act (SOX) Data Protection Act (DPA) IRS Publication 1075
Database Firewall	For database secured targets that you are monitoring with the database firewall, this set of reports gives detailed event information about SQL traffic to these databases. Much of the information is dependent on the firewall policy you have assigned to a database. For example, you can see details of statements that had warnings, or were blocked, according to the policy. You can also see general information about SQL traffic to these databases, for example, statement type (data definition, data manipulation, etc.). Some example reports are: Database Traffic Analysis by Client IP: Displays audit details for statements by the protected database and client IP address Database Traffic Analysis by OS User: Displays audit details for statements grouped by protected database and OS user Database Traffic Analysis by User Blocked Statements: Displays audit details for blocked statements grouped by protected database and OS use
User Entitlements	A set of reports that describe user access and privileges for Oracle Database secured targets, for example: User Accounts: Displays information such as the secured target in which the user account was created or the user account name, and whether this account is locked or expired User Privileges: Displays information such as the secured target in which the privilege was created, user name, and privilege Object Privileges: Displays information such as the secured target in which the object was created, users granted the object privilege, and the schema owner Privileged Users: Displays information such as the secured target in which the privileged user account was created, user name, and privileges granted to the user
User Correlation	For Oracle Database secured targets running on Linux, these reports let you correlate events on the database with the original Linux OS user. This is useful in cases where this user runs a shell or executes a command on the database as another user by using `su` or `sudo`.
Database Vault Activity	If your Oracle Database secured targets have Database Vault enabled, the Database Vault Activity report shows Database Vault events, which capture policy or rule violations, unauthorized access attempts, etc.

5.2.3 Customizing Built-in Reports

You can create customized reports based on the built-in reports and then save the new report formats to view them online. Oracle AVDF provides tools to filter, group, and highlight data, and define columns displayed in the reports.

Figure 5-3 shows an example of filters that you can use to customize built-in reports.

Figure 5-3 Interactively Customizing a Built-In Report

Description of "Figure 5-3 Interactively Customizing a Built-In Report"

The next topic shows some examples of customizing built-in reports.

5.2.4 Examples of Customizing Built-in Reports

Topics

5.2.4.1 Login Failures Report

You may want to examine the Login Failures report to see if there are an unusual number of failed attempts to access a database, as well as which users or IP addresses originated those attempts. Figure 5-2 shows the Failed Logins report.

Figure 5-4 Login Failures Report

Description of "Figure 5-4 Login Failures Report"

Suppose you want to see a visual breakdown of failed logins by client IP address. You can use the formatting tools to create a chart type of your choice, as shown in Figure 5-5.

Figure 5-5 Customizing the Failed Logins Report into a Chart Format by Client IP

Description of "Figure 5-5 Customizing the Failed Logins Report into a Chart Format by Client IP"

In the preceding illustration, we have chosen a horizontal bar chart with each bar representing a different client IP address. We have chosen a simple count to show the number of failed login attempts. The resulting chart, shown in Figure 5-6, clearly shows a large number of failed logins from one IP address.

Figure 5-6 Failed Logins Shown in a Bar Chart by Client IP Address

Description of "Figure 5-6 Failed Logins Shown in a Bar Chart by Client IP Address"

Similarly, you may want to see failed logins originated by a particular user, using the formatting tools to filter the report as shown in Figure 5-7.

Figure 5-7 Filter the Failed Logins Report for a Specific User

Description of "Figure 5-7 Filter the Failed Logins Report for a Specific User"

5.2.4.2 Database Schema Changes

The Database Schema Changes report shows audited DDL activities, for example, DROP TABLE or CREATE PROCEDURE. This is useful for finding unauthorized changes and when you want to investigate changes to the database schema. For example, you may want to maintain strict standards for changes to the database, where multiple users may implement these changes. Or, an application may stop working, requiring you to investigate if a change to the database may be the cause. Figure 5-8 shows the Database Schema Changes report.

Figure 5-8 Database Schema Changes Report

Description of "Figure 5-8 Database Schema Changes Report"

Using the same techniques used for the Failed Logins report, you can sort and group the report in various ways to get the information you want. You can add or remove columns, filter by user name, client program name or IP address, and many other fields.

For example, suppose you are interested in investigating SUPER USER DDL commands for the user SYS. You can filter the report data for that event name and that user. Figure 5-9 shows an example of adding the event name filter, and Figure 5-10 shows the resulting report filtered by both this event name and the user name SYS.

Figure 5-9 Filtering a Report by Event Name

Description of "Figure 5-9 Filtering a Report by Event Name"

Figure 5-10 Database Schema Changes Filtered for a Specific Event Name and User

Description of "Figure 5-10 Database Schema Changes Filtered for a Specific Event Name and User"

As another example of customizing this report, in Figure 5-11, we have chosen to show schema changes by user name, displayed in a bar chart.

Figure 5-11 Database Schema Changes Shown in a Bar Chart by User Name

Description of "Figure 5-11 Database Schema Changes Shown in a Bar Chart by User Name"

This chart shows you the breakdown of how many DDL changes were done by each user.

5.3 Custom Reports

Topics

5.3.1 Introduction to Custom Reports

This topic describes how to create customized reports for Oracle Audit Vault and Database Firewall.

There are two ways of creating custom reports with Oracle Audit Vault and Database Firewall. One way is to interactively customize the built-in reports by filtering data, and then save these interactive views so you can view them again online later.

5.3.2 Tools for Creating Your Own Custom Reports for Oracle AVDF

You can upload your own custom reports to Oracle Audit Vault and Database Firewall (Oracle ADVF) by using Oracle BI Publisher, or another report authoring tool from a third party.

For simple changes to the built-in report formats, you can also do some customizations without using a report authoring tool.

Oracle Audit Vault and Database Firewall provides two types of files to help you get started creating custom reports. The first type of file is a report template in rich text format (RTF), which you can open in a tool such as Microsoft Word. The template determines the display of the report, so for example, you can easily add your own custom logo on the report. The second type of file is a report definition in XML format, which you can open in a text or XML editor. The report definition file specifies the data in the report.

You can download report definition and template files corresponding to any of the built-in reports, and then you can use these files as a starting point for creating your own custom report. Oracle AVDF documentation also provides several appendices on event data collected from different types of secured targets that will help you in creating your own reports.

The following illustration shows how an auditor can download various types of report definition and template files with which to start creating a custom report.

Figure 5-12 Downloading Report Template and Definition Files

Description of "Figure 5-12 Downloading Report Template and Definition Files"

5.4 Alerts and Notifications

Oracle Audit Vault and Database Firewall lets you define rule-based alerts on audit records, whether these records come from the Audit Vault Agent or the Database Firewall.

You can also specify notifications for rule-based alerts. For example, you can set up an email to be automatically sent to a user, such as a security officer, or to a distribution list. Alerts can also be forwarded to syslog. This is useful if you want to integrate them with another system.

Alerts you define are independent of audit policies or firewall policies. For example, in firewall policies, you can specify that certain SQL statements types (clusters) should raise an alert when encountered by the Database Firewall. But you may also want to define a special condition that raises an alert based on factors other than those specified in a firewall policy. This can be a quick way to notify a specific person or group when that condition is met. Or you may want to be alerted whenever this condition is met for all secured targets, whereas the firewall policy may have been assigned to only one secured target.

Because alerts are rule-based, if the rule definition is matched (for example, User A fails to log in to Database B after three tries), then an alert is raised. The rule is called a condition in Oracle Audit Vault and Database Firewall. The condition is a Boolean statement, similar to a WHERE clause in a SELECT statement. For example:

Note he WHERE clause in this SELECT statement:

SELECT user_name, event_status, event_name from avsys.event_log 
   WHERE event_status='FAILURE' and upper(event_name)='LOGON';

The WHERE clause can be translated into this alert condition:

:event_status='FAILURE' and upper(:event_name)='LOGON'

Alert conditions are flexible and can include more than one event, and the events can come from different secured targets. For example, an alert can be applied to four Oracle Databases. The alert condition can also be a complex statement. For example: User A failed to log in to secured target X, and User A also failed to log in to secured target Y.

A good way to define an alert condition is to first look at the Oracle Audit Vault and Database Firewall All Activity Report, which displays details of all captured audit events. From this report you can see possible events that may be of interest to you. This can be the starting point for your alert condition.