7 Database Firewall Policies

You can create and manage Database Firewall policies.

7.1 About Database Firewall Policies

Database Firewall policies allow you to configure actions that Database Firewall should take on the SQL traffic it is receiving.

Oracle strongly recommends that you read Network-Based SQL Traffic Monitoring with Database Firewall to understand:

  • the Database Firewall network placement options
  • the different protection modes
  • the concepts of how a Database Firewall policy works

Database Firewall policies can be configured to report SQL operations on database, control client application access, enforce expected database access behavior, prevent SQL injection, and control application bypass, and prevent malicious SQL statements from reaching the database.

Database Firewall allows to create an allow list of SQL statements to pass, or deny list to block or alert.

Database Firewall policies is defined based on the users, the actions they can perform on the data, and the actions that the Database Firewall must initiate when the event occurs.

7.2 About Database Firewall Deployment Modes and Policies

Learn about Database Firewall deployment modes and corresponding policy types.

Database Firewall can be deployed in the following modes:

  1. Monitoring (Out-of-Band)
  2. Monitoring (Host Monitor)
  3. Monitoring / Blocking (Proxy)

Monitoring (Out-of-Band) or Monitoring (Host Monitor) deployment modes can be used for monitoring only and to alert on potential policy violations.

Monitoring / Blocking (Proxy) mode can be deployed to block certain SQL activities, in addition to monitoring and alerting. For example, policy rule can be defined to block SQL statements that violate the policy guidelines.

Some scenarios to define Database Firewall policies are listed below:

Scenario Description

Monitoring privileged users

Configure the Database Firewall policy to monitor and capture all the SQL statements run by privileged users. Create a list of privileged users and use this list in Session Context rule. This provides all SQL statements run by them over the network for a specific target database. Deploy Database Firewall in any of the three modes and use Session Context rule.

Block DBA access to sensitive application database objects

Deploy Database Firewall in Monitoring / Blocking (Proxy) mode. Create a list of DBA (Database Administrator) users, configure Database Object rule to block any SQL statement run by these users on a list of sensitive objects.

Monitoring sensitive data and access over the network

Configure the Database Firewall policy to monitor user access and their operations on sensitive data by using Database Object rule. Provide a list of sensitive objects (table or views) while configuring the Database Object rule. Database Firewall can be deployed in any of the three modes in this scenario.

Blocking unauthorized access

Deploy Database Firewall in Monitoring / Blocking (Proxy) mode and use the Database Object rule. Create a profile of users, configure allow operations on a specific sensitive data (table or views). Block all other access and operations by creating a second Database Object rule. Ensure the blocking rule is the last one in the rule list.

Allow SQL statements from trusted IP addresses and applications

Deploy Database Firewall in Monitoring / Blocking (Proxy) mode. Configure the Session Context rule to allow SQL traffic from an allow list of client applications and their host IP addresses. Any other access must be monitored, alerted, or blocked by the Database Firewall using the Default rule. To enforce this database access pattern, create allow list in Session Context rule and configure the Default rule to alert or block other SQL statements.

Prevent SQL injection threats or zero day exploits

Deploy Database Firewall in Monitoring / Blocking (Proxy) mode. Configure the Database Firewall to capture SQL statements from trusted set of applications and database users. This is the allow list of SQL statements. Database Firewall uses a SQL grammar based engine to parse and group similar SQL statements into clusters. Create a list of such clusters and configure SQL Statement rule to all SQL statements matching the list for a specific target and alert or block SQL statements that have clusters not matching the list.

Detect potential data exfiltration attempts

Configure the Database Firewall policy to identify potential data exfiltration attempts by capturing the number of rows returned by the database in response to SELECT SQL queries using Database Object rule (starting from Oracle AVDF 20.3). For example, raise an alert if the number of returned rows exceeds a specific expected threshold on a specific sensitive table.

7.3 Types of Database Firewall Policies

Learn about the types of Database Firewall policies.

Database Firewall policies are categorized into:

  • User-defined Database Firewall Policies
  • Pre-defined Database Firewall Policies

User-defined Database Firewall Policies

Oracle AVDF allows you to define your own policies quickly and efficiently. There are 6 types of rules that you can create in these policies.

Pre-defined Database Firewall Policies

Oracle AVDF includes pre-defined Database Firewall policies. These define the frequency of logging SQL statements in Audit Vault Server. They only monitor the SQL statements and do not raise alerts or block SQL statements. For alerting or blocking SQL statements, User-defined Database Firewall Policies should be configured.

7.4 Developing a Database Firewall Policy

Learn about developing a Database Firewall policy.

Developing a Database Firewall policy involves the following steps:

  1. Creating a new Database Firewall policy.
  2. Configuring the created Database Firewall policy.
  3. Publishing a Database Firewall policy.
  4. Deploying Database Firewall Policies to targets.
  5. Exporting and Importing Database Firewall Policies

Note:

  • All these operations are performed using the Audit Vault Server console.
  • In Oracle AVDF 20.3 and later, after the Database Firewall policy is created, it is published automatically.

7.5 Creating a New Database Firewall Policy

Learn about creating a Database Firewall policy.

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click Policies tab.
  3. Click Database Firewall Policies tab in the left navigation menu.

    Though this page lists your User-defined Policies and the Oracle Pre-defined Policies, only User-defined Policies can be created.

  4. Click Create button.

    The Create Policy dialog appears.

  5. Select the Target Type from the drop down list.
  6. Enter a Policy Name. The name can't include # or :.
  7. Optionally, enter Description. The description can't include # or :.

  8. After taking the appropriate action for your release, you will be brought to the policy details page. Here you will configure the policy.

7.6 Configuring the Created Database Firewall Policy

Learn about configuring the Database Firewall policy already created.

Configuring the Database Firewall policy involves:

  1. Configuration of global policy settings.
  2. Creation of sets or profiles.
  3. Configuration of user defined rules.

7.6.1 Configuring Database Firewall Global Policy Settings

Learn how an auditor can configure or define an existing Database Firewall policy settings.

Global Policy settings in a Database Firewall represents the configuration settings applied to all the rules for a specific policy.

Prerequisite: Global Database Firewall settings can be configured after the policy is created.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. From the left navigation menu, click Database Firewall Policies.
  4. In this page, click the name of the specific policy.
  5. Click Configuration button in the top right corner. The following policy rules are displayed in different tabs:
    1. Login / Logout
    2. Sensitive Data Masking
    3. Unknown Traffic
    4. Policy Pattern
  6. For example, click Policy Pattern tab. In this tab, configure the following:
    1. Under Log Pattern section, select whether to Strip binary objects and comments from log files.

    2. Under Action Rule Pattern section enter the Threshold action reset time (minutes) field. Enter a number in minutes. If you have set a Threshold in any of your policy rules, and the Threshold Action in your rule is taken, the action will not be repeated for the time you specify here. This prevents too many block/warn actions for the same rule.

    3. In the Action without substitution field, select the action to take (No response or Drop connection) if one of your policy rules is set to Block and you have not specified a substitute statement in the rule.

    4. Under the Syntax Rule Pattern section, select whether to treat Double quoted strings as identifiers. This determines whether double-quoted strings in SQL statements are treated as identifiers or string constants. If you deselect this check box, sensitive data masking (if used) will mask text in double quotes.

    5. For Case sensitive match, select whether this firewall policy does case sensitive matching for Client program name, Database username, and Operating system username.

  7. Click Save.
7.6.1.1 Configuring Policies for Login and Logout Events

Learn how to configure Database Firewall policy for login and logout events.

You can specify login and logout policies for database users. For example, configure to raise alerts or block database users who make a specified number of unsuccessful login attempts.
Prerequisite: In order to use a login or logout policy for a target database, you must activate database response monitoring when configuring the Database Firewall monitoring point for the specific target database.

To configure the login and logout policies:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click Policies tab.
  3. From the left navigation menu, click Database Firewall Policies.
  4. Click the name of the specific policy.
  5. In the Policy section, click Configuration button in the top right corner.
  6. Under the Login/Logout tab and Login section, configure the following:
    1. Action: Specify the action for login sessions.
    2. Threat Severity: Select the severity level for successful or unsuccessful database user logins.
    3. Set Logging: Check this box to enable logging for logins and to view login session information in reports and alerts.
    4. Failed Login: Optionally, select the checkbox Set failed login policy threshold.

    This setting lets you produce an alert, or block a client program, after exceeding a specified number of consecutive unsuccessful logins. You can set a threshold and action.

    If the threshold limit is reached, the login sessions are blocked for the specified Reset Period (in seconds). After this period, the client program login attempts are passed to the target database.

    Note:

    Blocking a client program session after exceeding a specified number of consecutive unsuccessful logins is supported in Monitoring / Blocking (Proxy) deployment mode only.
  7. Under the Logout section, configure the following:
    1. Action: Specify the action on logout sessions (whether to pass or alert).
    2. Threat Severity: Select the severity level for successful or unsuccessful database user logouts.
    3. Set Logging: To view logout session information in reports or alerts.
  8. Click Save in the top right corner.
7.6.1.2 Configuring Policies for Masking Sensitive Data

Learn how to configure Database Firewall policy for masking sensitive data.

Database Firewall obfuscates passwords, string literals, and numerical constants by default for all SQL statements before logging in to the Audit Vault Server. In addition, the rules can be set for masking selective SQL statements. Data masking prevents sensitive and confidential data, such as credit card numbers from appearing in the log files, reports, and alerts. If a logged statement matches the data masking policy, the policy automatically replaces all user data in that statement.

Database Firewall masks the data depending on the data type:

  • Delimited strings are masked as "#".
  • Passwords are masked as XXX.
  • String literals are masked as "#". String literals can be user names.
  • All numerical constants like float, hexadecimal, decimal, integer, and binary constants are masked as "0" (zero). Numerical constants can be user ID.

Note:

After the data is masked by Database Firewall, it cannot be unmasked.

To set rules for data masking:

  1. If you selected to mask based on criteria, enter the details as follows:

    Columns:

    • Choose from the list.
    • Or enter a database column name from the list of options available. Data masking is applied on the statements containing these columns.
    • To remove one or more column names that are selected, click on the cross mark ("x") next to them. Accordingly the SQL statements are masked.

    Procedures:

    • Enter a procedure name and select from the list of options available to add the procedure name to the Procedures list. Data masking is applied on statements containing the specified procedures.
    • To remove one or more procedure names that are selected, click on the cross mark ("x") next to them. Accordingly the SQL statements are masked.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Click Policies tab.
  3. From the left navigation menu, click Database Firewall Policies.
  4. Click the name of a specific policy.
  5. Click Configuration button in the top right corner.
  6. Click Sensitive Data Masking tab.
  7. Select or deselect the Mask logged data check box.
  8. Select one of the following options:
    1. For all statements: This is the default selection.
    2. For all statements matching the following criteria: If you select to mask based on criteria, then enter the details as follows:

    Columns:

    • Use the search dialog, choose from the list, and click the Add button to add to the list. Data masking is applied on the SQL statements listed in these columns.
    • To remove one or more column names, select them and click the Remove button. Accordingly the SQL statements are masked.

    Procedures:

    • If it is left empty, data masking is applied on SQL statements containing any procedure.
    • Use the search dialog, choose from the list, and click the Add button to add to the list. Data masking is applied on the SQL statements listed in these columns.
    • To remove one or more procedure names, select them and click the Remove button. Accordingly the SQL statements are masked.
  9. Click Save.
7.6.1.3 Configuring Policies for Unknown Traffic

Learn how to configure Database Firewall policy for unknown traffic.

Database Firewall policy rules can be configured for SQL statements that are not recognized for the following possible reasons:

  • Database Firewall is unable to parse the SQL statement
  • Semantics of SQL statement is not valid
  • Communication protocol used by the client program and the target database is not supported by Database Firewall

Note:

Interpretation of Java code is not supported by Database Firewall.

To set the policy rules for unknown traffic:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. Select Database Firewall Policies in the left navigation menu.
  4. Click the name of the specific policy. The details of the policy are displayed on the page.
  5. Click Configuration button in the top right corner.
  6. Click Unknown Traffic sub tab in the main page.
  7. Assign the Action, Logging Level, and Threat Severity accordingly.
  8. Optionally select Set threshold for escalating action checkbox to apply relevant action after unknown traffic statements exceed the number of times specified as the threshold limit. Then, enter the following
    1. Threshold: Enter the number of times unknown traffic must be seen before the escalation action is taken.
    2. Threshold Time (in seconds): Set the threshold time.
    3. Threshold Action: Select Alert or Block as the action taken after the threshold is met.
    4. Substitution SQL (Optional): When Block is selected for Threshold Action, enter a SQL statement to substitute for the unknown SQL statement.
7.6.1.4 Configuring Database Firewall Policies for Policy Pattern

Learn how to configure Database Firewall policy rules for patterns in the SQL statements.

To set the policy rules for different patterns:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. Select Database Firewall Policies in the left navigation menu.
  4. Click the name of the specific policy. The details of the policy are displayed on the page.
  5. Click Configuration button in the top right corner.
  6. Click Policy Pattern tab.
  7. In this tab, configure the following:

  8. Click Save.

7.6.2 Creating And Managing Database Firewall Sets and Profiles

Learn how an auditor creates and manages Database Firewall sets and profiles.

When defining the Database Firewall policy rules, you should consider actors like Database Administrators, client programs, actions they can perform, or they cannot perform. Configure Database Firewall policy rules to take relevant actions based on actors and their actions. These actors are nothing but session context attributes retrieved by Database Firewall from the network traffic when a client program establishes a session with the target database.

These sets allow you to create a list of session context attributes such as client host IP addresses, database users, OS users, database objects, and client programs. You can also create sets of SQL clusters. SQL clusters are a group of SQL statements which are similar to each other. A profile is a named combination of sets.

7.6.2.1 Creating Sets

Learn about the types of sets and how to create them.

The following are the types of sets that can be configured and used in the rules:

  1. IP Address Sets: A list of IP addresses of client programs (IPv4 format).
  2. Database User Sets: A list of database user names.
  3. OS User Sets: A list of operating system user names.
  4. Client Program Sets (Database Client Sets in Oracle AVDF 20.3 and earlier): A list of client programs. For example SQL*Plus.
  5. Database Object Sets: A list of tables to be evaluated by a policy.
  6. SQL Cluster Sets: A list of SQL clusters. A SQL cluster is a group of SQL statements created automatically by Database Firewall that are similar, from the network traffic.

To create local sets:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. Select Database Firewall Policies in the left navigation menu.
  4. Click the name of the specific policy. The details of the policy are displayed in the main screen.
  5. Click Sets/Profiles in the top right corner. This page lists the sets already defined for the specific policy.
  6. Click one of the following:

    • IP Address Sets
    • Database User Sets
    • OS User Sets
    • Client Program Sets (Database Client Sets in Oracle AVDF 20.3 and earlier)
    • Database Object Sets
    • SQL Cluster Sets
  7. Click Add to add a new local set.
  8. Use the dialog box to complete adding a new local set.

    Note:

    If you're importing a file, it must be encoded in the UTF-8 format.
  9. Click Save. The new local set appears in the specific policy page.
  10. You can add more local sets by clicking on Add button for the specific set.

Starting with Oracle AVDF 20.9, see Managing Global Sets/Data Discovery to create global sets.

7.6.2.2 Creating and Managing SQL Cluster Sets

Learn how to create and manage SQL cluster sets in Database Firewall policy.

A SQL cluster set is a group of SQL clusters. SQL cluster is a group of SQL statements created automatically by Database Firewall which are similar to each other, from the network traffic.

You can create a new SQL cluster set as well as delete it. This deletes the definition only and does not remove the network data captured by the Database Firewall.

To create a SQL cluster set:

  1. Log in to the Audit Vault Server console as auditor.
  2. Select the Policies tab.
  3. Select the Database Firewall Policies in the left navigation menu.
  4. Click the name of the specific policy. The details of the policy are displayed in the main page.
  5. Click Sets/Profiles in the top right corner. This page lists the existing sets that are already defined for the specific policy.
  6. Click SQL Custer Sets sub tab.
  7. To add a new SQL cluster set, click Add. The Add SQL Cluster Set dialog is displayed.
  8. To get the list of SQL clusters, choose the filter options in the following fields:
    1. Target
    2. Show cluster for
  9. Click Go. It displays all the SQL clusters depending on the selection.
  10. Select the specific SQL clusters. To know the SQL statements associated with the cluster refer to the sample SQL from cluster column values.
  11. Enter the Name and optionally Description.
  12. Click Save. A new SQL cluster set is added and is listed in the Sets/Profiles page. More such sets can be added by following the above steps.

To add or delete the SQL cluster from a given set:

  1. Click SQL Cluster Sets sub tab.
  2. From the report choose the specific SQL cluster set, the details of the cluster set are displayed.
  3. Choose Add and follow the procedure to add new clusters to an existing set.
  4. From the same dialog, choose Delete option to delete one or more clusters from the set.
  5. Click Save.
7.6.2.3 Creating and Managing Profiles

Learn how to create and manage profiles in Database Firewall policy.

A profile is a named combination of one or more of the below sets:

  1. IP Address Set
  2. DB User Set
  3. OS User Set
  4. Client Program Set (DB Client Set in Oracle AVDF 20.3 and earlier)

You can create a user with the profile. For example, you can create a system DBA profile using the DB User Set. This set can contain all the DBA users.

To create a profile:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. Select Database Firewall Policies tab in the left navigation menu.
  4. Click the name of the specific policy.
  5. Click Sets/Profiles button in the top right corner.
  6. Click Profiles sub tab. This page lists the existing profiles. You can click a profile name to edit it.

    Note:

    Create a set first. It is not possible to create a profile without a set already existing.
  7. Click Add to create a new profile.
  8. In the Add profile dialog, enter the following:
    • Name: Enter a name for the profile.
    • Description: Optionally enter the description.
    • IP Address Set: From the list, select one of the available IP address sets, or leave it unselected.
    • DB User Set: From the list, select one of the available database user sets, or leave it unselected.
    • OS User Set: From the list, select one of the available operating system user sets, or leave it unselected.
    • Client Program Set (DB Client Set in Oracle AVDF 20.3 and earlier): From the list, select one of the available client program sets, or leave it unselected.

    Note:

    Client program names and OS user names are provided by the client. Hence, they may not be reliable depending on the environment.
  9. Click Save.

    The profile appears in the Profiles sub tab. You can now select this profile and set policy rules for the SQL Statements. Starting Oracle AVDF 20.4 and later the profile can be selected in a Database Object rule also.

7.6.3 Database Firewall Policy Rules

Learn about Database Firewall policy rules.

7.6.3.1 About Database Firewall Policy Rules

Learn about types of Database Firewall policy rules.

The following are the Database Firewall rule types:

  • Session Context
  • SQL Statement
  • Database Objects
  • Default

Policy Evaluation by Database Firewall

Figure 7-1 Order of Evaluation of Rules

Description of Figure 7-1 follows
Description of "Figure 7-1 Order of Evaluation of Rules"

Database Firewall is a multi stage engine that analyses and inspects SQL traffic to the database, extracts SQL statement from the traffic, and with a high precision determines whether to allow, alert, or block the SQL statement as specified in the policy. The SQL statement goes through different stages of analysis in the Database Firewall. This includes checks for originating IP address, database user name, OS user name, client program name, SQL statement category (DDL, DML, etc.), database tables, or views being accessed. This information can be used to determine whether the SQL statement can be allowed, requires raising an alert, or requires blocking.

Every stage has actions specified and checks carried out. In case there is a match, then the evaluation of the rule stops. Session Context rules are evaluated first. This is followed by SQL Statements and Database Object rules. In the last is the Default rule.

Note:

Profiles for Database Object rule in Database Firewall policy is introduced starting Oracle AVDF 20.4.
7.6.3.2 Evaluation Order of the Rules

Learn about the order in which the Database Firewall rules are evaluated or applied.

There can be multiple rules within a rule type. The table below lists the order in which they are applied or evaluated.

Rule Type Oracle AVDF Release 20.3 and Earlier Oracle AVDF Release 20.4 and Later

Session Context

Session Context rules are applied in the order they are listed in the policy overview page.

Session Context rules are applied in the order they are listed in the policy overview page.

SQL Statement

SQL Statement rules are applied in the order they are listed in the policy overview page.

SQL Statement rules are applied in the order they are listed in the policy overview page.

Database Object

Database Object can be configured with ANY or ALL tables. The matching is applied using either ALL or ANY operator as follows:

  1. If ALL operator is selected, then the SQL statements must contain all the tables in the list.
  2. If ANY operator is selected, then the SQL statements must contain at least one table from the list.

ANY rules are evaluated first, and then ALL rules.

Database Object rules are applied in the order they are listed in the policy overview page.

Default

Can contain one rule only.

Can contain one rule only.

In Monitoring / Blocking (Proxy) mode, by default the Database Firewall blocks all IPv6 traffic regardless of the policies in place.

Starting with Oracle AVDF 20.4, the evaluation order of the rules can be changed. Follow these steps:

  1. Click Evaluation Order button. A dialog appears.
  2. Change the order of the rules using the up or down arrows on the right.
  3. Click Save. A confirmation message is displayed on the screen. The updated order of the rules is refreshed on the main page.
7.6.3.3 Session Context Rule

Learn about the Session Context rule.

A session from a SQL client program trying to connect to the target database, contains information like Host IP address, Database user name, operating system user name and client program name. These are referred as session context attributes. Session Context rule is applied on the session context attributes. For example, allowing SQL statements from a trusted allow list client IP address range. It also allows to block SQL statements originating outside the expected IP address range.

The following sets are used when creating the Session Context rule:

  1. IP Address Set
  2. DB User Set
  3. OS User Set
  4. Client Program Set (DB Client Program in Oracle AVDF 20.3 and earlier)

To create a session context rule:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. Select Database Firewall Policies in the left navigation menu.
  4. Select the name of an existing custom policy, or click Create to create a new one.

    Note:

    Though the page displays both User-defined Database Firewall Policies and Oracle Pre-defined Database Firewall Policies, only User-defined Database Firewall Policies can be created.
  5. In the Database Firewall Policy Rules section, expand the Session Context section.
  6. Click Add. The Session Context dialog is displayed.
  7. Enter a Rule Name and optionally Description.
  8. For the following fields listed under Ruleset section:
    1. IP Address Set: Select to include or exclude, and then select an IP address set from the list.
    2. DB User Set: Select to include or exclude, and then select a database user set.
    3. OS User Set: Select to include or exclude, and then select an OS user set.
    4. Client Program Sets (DB Client Program in Oracle AVDF 20.3 and earlier): Select to include or exclude, and then select a SQL client program.

    Note:

    • There is no limit on the number of items that can be included in these sets.
    • You can use * (asterisk) as a wildcard for all the sets except IP Address Set.
    • There is an option in all the sets to make it case sensitive or otherwise. This can be done by selecting or deselecting the check box in the global policy configuration, and selecting Policy Pattern, and then Case sensitive match.
    • For example, if you select to include an IP Address Set, and exclude a DB User Set, then this Session Context rule will only apply to SQL traffic from the selected IP Address Set. However, it does not apply to the SQL traffic from the database users in the selected DB User Set.
  9. Select the appropriate options available in the following fields under Action section:
    1. Action
    2. Logging Level
    3. Threat Severity
    4. Optionally select Set threshold for escalating action if you want to apply a different escalation action after a threshold. Enter the following:

      1. Threshold: Enter the number of times SQL match must exceed before the escalation action is taken.
      2. Threshold Time (in seconds)
      3. Threshold Action: Select Alert or Block as the action taken after the threshold is met.
      4. Substitution SQL: (Optional) If you selected Block for the Threshold Action, enter a statement to substitute for the SQL statement matching this rule.
  10. Click Save.
7.6.3.4 SQL Statement Rule

Learn about the SQL Statement rule.

Database Firewall extracts and analyzes SQL statements from network traffic. It groups similar SQL statements into clusters. Such clusters can be further grouped to form cluster sets. SQL Statement rule is used to configure actions that the Database Firewall must take on a SQL statement belonging to cluster sets.

Allow list (white list) or deny list based policies can be created using this rule. The allow list of SQL clusters can be created by sending known or expected SQL statements from trusted applications over a period of time. Include allow list of SQL cluster sets and configure to allow SQL statements with clusters belonging to the allow list, when creating the SQL Statement rule.

Make use of profiles to create a deny list based policy. For example, create cluster sets for sensitive data, and then create a profile of database administrators (DBA). Configure a rule to block SQL statements from DBA profile users with clusters matching the created cluster set. In this case the rule works as deny list.

To create a SQL Statement rule:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. Click Database Firewall Policies in the left navigation menu.
  4. Click the name of an existing policy or click Create to create a new one.
  5. Expand the SQL Statement section.
  6. Click Add.
  7. In the SQL Statement dialog, enter a Rule Name.
  8. Optionally enter the Description.
  9. Select the Profile from the list.
  10. In the Cluster Set(s) field, select the cluster in the Available column and move to the Selected column. Refer to the section on how to create cluster sets. Create a cluster set prior to creating a policy rule for SQL Statement.
  11. In the Action section, enter the details for Action, Logging Level, and Threat Severity fields to apply to SQL statements of this cluster type.
  12. Optionally select the checkbox for Set threshold for escalating action field, if an action is required on the SQL statement that matches this cluster threshold times. Upon selecting this checkbox, enter the following:
    1. Threshold: Enter the number of times a SQL statement must match this cluster before the escalation action is taken.
    2. Threshold Time (in seconds): Enter the time in seconds.
    3. Threshold Action: Select Alert or Block as the action taken after the threshold is met.
    4. Substitution SQL: If you selected Block for the Threshold Action, then enter a statement to substitute for the SQL matching this rule.
  13. Click Save.
7.6.3.5 Database Object Rule

Learn about the Database Object rule.

Database Object rule can be used in the following scenarios:

Database Object rules are used to allow, alert or block specific types of SQL statements (DML, DDL, etc.) on specific database objects such as tables and views. These rules are often used for controlling access to sensitive application data.

  • Monitoring specific statement types that are of interest on sensitive tables. Use Any table field, and select TRANSACTION COMPOSITE under Statement Classes. You can also select the tables AVG_COST, BOOKS, and BUSINESS_CONTACTS. A statement that matches this rule must be TRANSACTION COMPOSITE and it can contain any of the tables selected.
  • Monitoring specific statement types that are of interest on sensitive tables, by using All table field. Select Procedural and Composite under Statement Classes. You can also select the tables AVG_COST, BOOKS, and BUSINESS_CONTACTS. A statement that matches this rule must either be Procedural or Composite, and the SQL statement must have all the tables (AVG_COST, BOOKS, and BUSINESS_CONTACTS).
  • Monitoring exfiltration attempts of sensitive data. Use this functionality by creating a Database Object rule to capture the number of rows returned by a SELECT query. Select Data Manipulation Read only under the Statement Classes field. This option is available only for Data Manipulation Read only statement class. All or ANY tables can be selected according to the requirement. This data is further available for selection in the All Activity and Database Firewall Reports. Alerts can be configured if the returned number of rows exceeds a threshold value.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. Click Database Firewall Policies tab in the left navigation menu.
  4. Click the name of an existing user-defined policy . The page specific to that policy appears.

    Note:

    Though the page displays both User-defined Database Firewall Policies and Oracle Pre-defined Database Firewall Policies, only User-defined Database Firewall Policies can be altered.
  5. Expand the Database Objects section, and then click on an existing database object rule or click Add. The Database Objects dialog is displayed.
  6. Provide a Rule Name.
  7. Enter a Description.
  8. Select a Profile, the default is none as shown by -.
  9. In the Statement Classes, select one or more types of statement classes that SQL statements must match in order to apply this rule.
  10. If the target type is an Oracle Database and one of the values for the Statement Classes field is Data Manipulation Readonly (Prior to Oracle AVDF Release 20.4) or SELECT (Oracle AVDF Release 20.4 and later), the field Capture number of rows returned for SELECT queries is able to be toggled to Yes. Upon setting this field to Yes, it captures the number of rows for select queries as per the policy, and displays in the All Activity and Database Firewall Reports under the column Row Count. This column will be available for selection in the reports. Alerts can be configured for the number of rows fetched or queried.
  11. Select the tables to be monitored in the Tables/Views to be monitored section. You will see a preview of the selected tables if you've already chosen which tables to monitor.

    Note:

    All Tables and Views are monitored unless specified.
    1. To edit the list of tables to be monitored, click the pencil icon to the right of the text box.
    2. In the dialog box you can edit the list of tables to be monitored from the three tabs: Enter Values, From File, or From Collected Data.
    • In the Enter Values tab, type the table names in the text box. You will need to enter each table or view on a separate line. Click Save once done.
      1. If left empty, all the tables analyzed by the Database Firewall are considered.
      2. Include fully qualified table names. Note: The tables T1 and Myschema.T1 are considered as different. Hence, include all tables names as appropriate.
    • In the From File tab, click the Choose File field to upload a .txt file containing the names of tables or views, with one name per line. Select your .txt file from the pop-up of your computer's files. Click Save once done.
    • In the From Collected Data tab, select tables from the Available column and move them to the Selected column using the filters in the middle. You can search for tables by typing in the search box in the top left of the dialog. This will narrow down the list of visible tables under the Available column. Click Save once done.
    • (Optional) At the bottom of each tab is a table called Tables/Views to be monitored. This table contains the selected tables or views that will be monitored. An empty list means all tables and views are monitored. To select all tables and views, select the check box to the left of the Tables/views column header. To select any tables and views, select the check box to the left of the Tables/views column for that table or view. Click Delete to remove any selected table(s) or view(s) from the list to be monitored.
  12. In the Action to be taken section, select the Action, Logging Level, and Threat Severity for this rule from the appropriate drop down list.
  13. If you select Block as the Action, then the Substitution SQL field appears. Enter a statement to substitute for the SQL statement that was blocked.
  14. Click Save.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. Click Database Firewall Policies tab in the left navigation menu.
  4. Click the name of an existing user-defined policy . The page specific to that policy appears.

    Note:

    Though the page displays both User-defined Database Firewall Policies and Oracle Pre-defined Database Firewall Policies, only User-defined Database Firewall Policies can be altered.
  5. Expand the Database Objects section, and then click on an existing database object rule or click Add. The Database Objects dialog is displayed.
  6. Provide a Rule Name.
  7. Enter a Description.
  8. Select a Profile, the default is none as shown by -.
  9. In the Statement Classes, select one or more types of statement classes that SQL statements must match in order to apply this rule.
  10. If the target type is an Oracle Database and one of the values for the Statement Classes field is SELECT, the field Capture number of rows returned for SELECT queries is able to be toggled to Yes. Upon setting this field to Yes, it captures the number of rows for select queries as per the policy, and displays in the All Activity and Database Firewall Reports under the column Row Count. This column will be available for selection in the reports. Alerts can be configured for the number of rows fetched or queried.
  11. In the Tables/Views to be monitored section, select one of the global or local sets from the DB Object Set drop down.

    Database object sets can be global or local sets. Global sets can be viewed in and applied to multiple database firewall policies, whereas local sets can only be viewed in and applied to the database firewall policies they were created in. Global sets can be created in Managing Global Sets/Data Discovery.

    Note:

    All Tables and Views are monitored unless specified.
    1. To add Database Object Sets, click the + button to the right of the text box.
    2. In the dialog box you can edit the list of tables to be monitored from the three tabs: Enter Values, From File, or From Collected Data.
      • In the Enter Values tab, type the table names in the text box. You will need to enter each table or view on a separate line. Click Save once done.
        • If left empty, all the tables analyzed by the Database Firewall are considered.
        • Policy evaluation happens on the table irrespective of schema. For example, the policy will evaluate qualified table name, Myschema.T1 and the plain table name, T1 in the same manner.
        • Table names can also include wild card characters by using * in the table name. For example, T* will include T1 and T2.
      • In the From File tab, click the Choose File field to upload a .txt file containing the names of tables or views, with one name per line. Select your .txt file from the pop-up of your computer's files. Click Save once done.
      • In the From Collected Data tab, select tables from the Available column and move them to the Selected column using the filters in the middle. You can search for tables by typing in the search box in the top left of the dialog. This will narrow down the list of visible tables under the Available column. Click Save once done.
      • (Optional) At the bottom of each tab is a table called Tables/Views to be monitored. This table contains the selected tables or views that will be monitored. An empty list means all tables and views are monitored. To select all tables and views, select the check box to the left of the Tables/views column header. To select any tables and views, select the check box to the left of the Tables/views column for that table or view. Click Delete to remove any selected table(s) or view(s) from the list to be monitored.
  12. In the Action to be taken section, select the Action, Logging Level, and Threat Severity for this rule from the appropriate drop down list.
  13. If you select Block as the Action, then the Substitution SQL field appears. Enter a statement to substitute for the SQL statement that was blocked.
  14. Click Save.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. Click Database Firewall Policies tab in the left navigation menu.
  4. Click the name of an existing user-defined policy . The page specific to that policy appears.

    Note:

    Though the page displays both User-defined Database Firewall Policies and Oracle Pre-defined Database Firewall Policies, only User-defined Database Firewall Policies can be altered.
  5. Expand the Database Objects section, and then click on an existing database object rule or click Add. The Database Objects dialog is displayed.
  6. Provide a Rule Name.
  7. Enter a Description.
  8. Select a Profile, the default is none as shown by -.
  9. In the Commands section, select the specific commands to add to this rule
  10. If the target type is an Oracle Database and one of the values for the Commands field is SELECT, the field Capture number of rows returned for SELECT queries is able to be toggled to Yes. Upon setting this field to Yes, it captures the number of rows for select queries as per the policy, and displays in the All Activity and Database Firewall Reports under the column Row Count. This column will be available for selection in the reports. Alerts can be configured for the number of rows fetched or queried.
  11. In the Tables/Views to be monitored section, select one of the global or local sets from the DB Object Set drop down.

    Database object sets can be global or local sets. Global sets can be viewed in and applied to multiple database firewall policies, whereas local sets can only be viewed in and applied to the database firewall policies they were created in. Global sets can be created in Managing Global Sets/Data Discovery.

    Note:

    All Tables and Views are monitored unless specified.
    1. To add Database Object Sets, click the + button to the right of the text box.
    2. In the dialog box you can edit the list of tables to be monitored from the three tabs: Enter Values, From File, or From Collected Data.
      • In the Enter Values tab, type the table names in the text box. You will need to enter each table or view on a separate line. Click Save once done.
        • If left empty, all the tables analyzed by the Database Firewall are considered.
        • Policy evaluation happens on the table irrespective of schema. For example, the policy will evaluate qualified table name, Myschema.T1 and the plain table name, T1 in the same manner.
        • Table names can also include wild card characters by using * in the table name. For example, T* will include T1 and T2.
      • In the From File tab, click the Choose File field to upload a .txt file containing the names of tables or views, with one name per line. Select your .txt file from the pop-up of your computer's files. Click Save once done.
      • In the From Collected Data tab, select tables from the Available column and move them to the Selected column using the filters in the middle. You can search for tables by typing in the search box in the top left of the dialog. This will narrow down the list of visible tables under the Available column. Click Save once done.
      • (Optional) At the bottom of each tab is a table called Tables/Views to be monitored. This table contains the selected tables or views that will be monitored. An empty list means all tables and views are monitored. To select all tables and views, select the check box to the left of the Tables/views column header. To select any tables and views, select the check box to the left of the Tables/views column for that table or view. Click Delete to remove any selected table(s) or view(s) from the list to be monitored.
  12. In the Action to be taken section, select the Action, Logging Level, and Threat Severity for this rule from the appropriate drop down list.
  13. If you select Block as the Action, then the Substitution SQL field appears. Enter a statement to substitute for the SQL statement that was blocked.
  14. Click Save.

Here are some important points to note for capturing return number of rows for SELECT queries feature:

  • This functionality is applicable for Database Objects rule in release Oracle AVDF 20.3.
  • This is applicable when Database Firewall is deployed in Monitoring / Blocking (Proxy) mode.
  • This feature is available only for Oracle Database (version 12c and later).
  • In Oracle AVDF 20.3 it is recommended not to enable Capture Database Response field if you are planning to use this functionality. This limitation has been removed in Oracle AVDF 20.4 and later.
  • This functionality does not support use of cursors and partial data fetch.
  • This functionality is supported on all 64 bit operating systems.
  • This functionality can be used with JDBC driver based clients, SQL*Plus, and other Oracle DB OCI based clients.
  • In case the return row count information does not show in reports, then review the traffic log timer. See Retrieval of Row Count Does Not Work for more information.
  • While configuring a Database Object rule, consider the tables for which the return row count feature needs to be enabled. It is recommended to enable the return row count option on SELECT queries that operate on a single table. Composite queries on multiple tables can raise false positives. Use ANY or ALL selection as per the requirement.
  • A malicious user may use different ways to hide data exfiltration. One of them may be partial fetches of the result set. Database Firewall marks the row count with value -1 for such cases. It is recommended to configure alerts based on this behavior.
7.6.3.5.1 Statement Class to Command Mappings for Database Firewall Policies

Starting in Oracle AVDF 20.11, Database Firewall policies no longer utilize statement classes. Instead, users are able to create policies based on specific commands such as INSERT, UPDATE, or DELETE. This table can help you identify which commands are a part of which statement class.

Statement Class Commands for Oracle Commands for SQL Server Commands for MySQL Commands for DB2 LUW Commands for Sybase ASE
DCL ADMINISTER, ALTER (DCL), ALTER SESSION, ALTER SYSTEM, COMPRESSED, ENCRYPTED, CHANGE PASSWORD, GRANT, INVALID OPERATION, LOGIN, ORADEBUG, REVOKE, SET ROLE, SHUTDOWN ALTER AUTHORIZATION, DBCC, DENY, GRANT, LOGIN, REVOKE, SET, SETUSER, USE BINLOG,  FLUSH, GRANT, INSTALL, KEYCACHE, KILL, PURGE, RESET, REVOKE, SET ROLE, UNINSTALL, USE GRANT, REVOKE, SET, TRANSFER DBCC, GRANT, KILL, LOAD, LOCK, MOUNT, REVOKE, SET, SETUSER, SYSTEM, TRANSFER, USE
DDL ALTER (DDL), ALTER AUDIT POLICY, ALTER DATABASE, ALTER PROFILE, ALTER TABLE, ALTER TABLESPACE, ALTER USER, ANALYZE, ASSOCIATE, AUDIT, COMMENT, CREATE, DISASSOCIATE, DROP, NOAUDIT, RENAME, TRUNCATE ADD, ALTER(DDL), ALTER DATABASE, ALTER TABLE, ALTER USER, CREATE, DISABLE, DROP, ENABLE, RECONFIGURE, TRUNCATE ALTER, CHECK, CHECKSUM, CREATE, DROP, PARTITION, RENAME, REPLACE, TRUNCATE ALLOCATE, ALTER, COMMENT, CREATE, DROP, RENAME, TRUNCATE ALTER, CREATE, DEALLOCATE, DROP, TRUNCATE
DML DELETE, EXECUTE CURSOR, EXPLAIN, FLASHBACK, INSERT, LOB WRITE, MERGE, PURGE, UPDATE BACKUP, DELETE, INSERT, MERGE, RESTORE, UPDATE, UPDATETEXT, WRITETEXT ANALYZE, DELETE, GET, INSERT, LOAD, OPTIMIZE, REPAIR, UPDATE DELETE, EXPLAIN, INSERT, MERGE, REFRESH, UPDATE DELETE, DUMP, EXECUTE CURSOR, INPUT, INSERT, MERGE, QUIESCE, REFRESH, REMOVE, REORG, UNMOUNT, UPDATE, WRITETEXT
Procedural PROCEDURAL PROCEDURAL PROCEDURAL PROCEDURAL PROCEDURAL
Select SELECT SELECT SELECT SELECT SELECT
Transaction TRANSACTION TRANSACTION TRANSACTION TRANSACTION TRANSACTION

Note:

Composite and Composite with Transaction statement classes do not have any equivalent commands, so they will not be displayed. If you had policies with these statement classes prior to upgrading to Oracle AVDF 20.11 or later, no commands will get automatically added to the policy during upgrade.
7.6.3.6 Default Rule

Learn about the Default rule.

The Default rule specifies the action for any SQL statement that does not meet the criteria of any previous policy rules. When the Database Firewall observes such a statement, the Default rule is applied. The default configuration is to allow the SQL statements without logging them into the Audit Vault Server. A different action in the Default rule can applied along with a substitute statement (optional in case Block action is considered).

To configure the Default rule:

  1. Optionally select Set threshold for escalating action field, if you want to apply a different action after statements fall within the default rule a number of times. Then enter the following:

  2. Click Save.

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click on Policies tab.
  3. Click Database Firewall Policies in the left navigation menu.
  4. Click the name of an existing policy or click Create to create a new one.
  5. Expand the Defaultsection on the main page.
  6. Click Default Rule.
  7. In the Default dialog, select the values for Action, Logging Level, and Threat Severity fields.
  8. Optionally select Set threshold for escalating action field. Then, enter the following:
    1. Threshold: Enter the number of times a SQL statement must fall within the Default rule before the escalation action is taken.
    2. Threshold Time (in seconds): Enter the time in seconds.
    3. Threshold Action: Select Alert or Block as the action taken after the threshold is met.
    4. Substitution SQL: (Optional) If Block is selected for the Threshold Action field, then enter a substitute for the SQL statement matching this rule.
  9. Click Save.

7.7 Publishing and Deploying Firewall Policies

Learn how to publish and deploy firewall policies.

7.7.1 About Publishing and Using Database Firewall Policies

You can edit a Database Firewall policy until it is published.

Publishing a policy makes it available to deploy on targets.

After a Database Firewall policy is deployed on a target, it cannot be edited. However, you can copy the policy and edit the same with another name. After completely editing the Database Firewall policy, it can be published and assigned to the targets.

7.7.2 Publishing a Database Firewall Policy

Learn how to publish a Database Firewall policy as an auditor.

Follow these steps to publish a Database Firewall policy in Oracle AVDF release 20.2 and earlier:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Select the Policies tab.
  3. From the left navigation menu, click Database Firewall Policies.
  4. Click the check box against the specific policy.
  5. Click Save and Publish.

    A Database Firewall policy publish job is started. A confirmation message is displayed on the screen.

  6. Check the status of the published job by navigating to the Settings tab, and then clicking the Jobs tab in the left navigation menu.
  7. After the policy is published, it is available in the Database Firewall Policies tab in the left navigation menu.

    Note:

    In Oracle AVDF 20.3 and later, after the Database Firewall policy is saved, it is published. The Publish button does not exist in Oracle AVDF release 20.3 and later. Navigate to the policy details page and click Save and Publish to publish.

7.7.3 Deploying Database Firewall Policies

Learn how to deploy database firewall policies through either the Policies or Targets tab.

Starting with release 20.8, database firewall policies can be deployed from the Policies tab. Database firewall policies can also be deployed from the Targets tab.

7.7.3.1 Deploying Database Firewall Policies from Policies Tab

Learn how to deploy either User-defined or Pre-defined Database Firewall Policies directly from the Policies tab in Oracle Audit Vault and Database Firewall.

Starting with Oracle AVDF release 20.8, Database Firewall policies can be deployed to target databases directly from the Policies tab. The process is the same for User-defined Policiesand Oracle Pre-defined Policies.
  1. In the table of policies, select the policy you wish to deploy by clicking the check box to the left of the Policy Name column.
    Note: You can only deploy one policy at a time so only select one policy.
  2. Click the Deploy button in the top right of either the User-defined Database Firewall Policies or Pre-defined Database Firewall Policies section depending on what type of policy you are deploying.
    In the pop-up box that appears you will see:
    • The name of the policy you selected next to Policy Name
    • The database type of the policy you selected next to Type
    • A table of target databases that are of the selected Type
  3. Optional: If the policy you selected has multiple target database types, you can filter the list of target databases by clicking on the drop down arrow next to Type and selecting a database type.
  4. Optional: You can search for target databases that are of the selected Type by entering the database name into the text field above the table of target database and clicking Go.
  5. Select which target database(s) you would like to deploy the selected policy to by clicking the check boxes to the left of the Target Name column.
    Note: Though you may select multiple target databases to deploy a policy to, they must be of the same database type.
  6. Click Deploy in the bottom right of the pop-up box to finish deploying the selected Database Firewall Policy to your target database(s).
  7. Click Cancel in the bottom right or the X in the top right of the pop-up box to cancel deploying the selected Database Firewall Policy.
7.7.3.2 Deploying Database Firewall Policies from Targets Tab

Learn how to deploy a Database Firewall policy to target databases from the Targets tab.

To deploy a Database Firewall policy:

  1. Log in to the Audit Vault Server console as an auditor.
    Prior to Oracle AVDF 20.7, log in as a super auditor.
  2. Click Targets tab.
  3. Click Database Firewall Monitoring tab in the left navigation menu.
  4. Click on the name of the specific target.
  5. In the target details page, click Database Firewall Monitoring tab.
  6. Scroll down in this section and click the edit icon under the Database Firewall Policy section.
  7. From the drop down list, select the policy.
  8. Click the check mark.

7.8 Exporting and Importing Database Firewall Policies

Learn how to export and import one or more Database Firewall policies.

Starting in Oracle AVDF release 20.7, User-defined Database Firewall Policies in one Audit Vault Server instance can be exported and later imported to another Audit Vault Server instance. This saves time in creating the same policies across multiple Audit Vault Server instances.

For example, the User-defined Database Firewall Policies can be exported and imported between:

  1. Test to production Audit Vault Server instance.
  2. Primary Audit Vault Server instance to DR (Disaster Recovery) Audit Vault Server instance.

When exported the Database Firewall policy can be downloaded and stored into a file in JSON format in Oracle AVDF release 20.7 or in a proprietary encrypted binary format starting in Oracle AVDF release 20.8. This file contains all the data and can be used to import later. This file is protected with a password defined by the user. The same password has to be entered to view the policy details and also during the import process.

Note:

This functionality is not applicable to Pre-defined Database Firewall Policies as they are available on all the Audit Vault Server instances and can easily be selected.

Prerequisites

  • To export or import Database Firewall policies, the user must have auditor privileges assigned.
  • A User-defined Database Firewall Policy must be first published in order to be exported.

7.8.1 Exporting Database Firewall Policies

Learn how to export one or more Database Firewall policies.

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click Policies tab.
  3. Click Database Firewall Policies tab in the left navigation menu.
  4. Select one or more custom policies using the check box against the policies. These policies must be published in order to be exported.

    Note:

    Though the page displays both User-defined Database Firewall Policies and Oracle Pre-defined Database Firewall Policies, only User-defined Database Firewall Policies can be exported.
  5. Click the Export button in the top right corner of the page.
  6. In the Export Policy dialog, enter the password. The password requirement is similar to other passwords in the Audit Vault Server.
  7. Click Save.
  8. Specify a location to save the file when prompted.
  9. In Oracle AVDF release 20.7 the file is saved as a JSON in the specified location as a bundle (.zip file) and is protected with a password. Starting with Oracle AVDF release 20.8, the Database Firewall policy file is in a proprietary encrypted binary format and is saved in the specified location and is password protected.

7.8.2 Importing Database Firewall Policies

Learn how to import one or more Database Firewall policies.

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click Policies tab.
  3. Click Database Firewall Policies tab in the left navigation menu.
  4. Click the Import button in the top right corner of the page.
  5. In the Import Policy dialog, click and navigate to choose the JSON file that contains all the details of the Database Firewall policies. In Oracle AVDF release 20.7, the JSON file that contains the Database Firewall policy details is a password protected bundle (.zip file). Starting with Oracle AVDF release 20.8, the Database Firewall policy is exported in encrypted JSON file format. A policy file with the same format needs to be imported as per Oracle AVDF release deployed.
  6. Enter the Password. It is the same password that was set when the Database Firewall policies were exported earlier.
  7. Starting in Oracle AVDF 20.9, select an Action for conflicting policy. This will determine how imported policies and sets will interact with existing global user, sensitive object, and global sets from Global Sets/Data Discovery. Options include:
    • Create new policy ensuring the global set names are unique: Creates a new policy and any global imported set names will be unique.
    • Create new policy and keep all sets local: Creates a new policy and all imported sets will be local to this new policy.
    • Replace the existing policy and the policy sets: Replace any existing policies and sets with the same name with those that are being imported.

    Sets of database users and database objects can be global or local sets. Global sets can be viewed in and applied to multiple database firewall policies, whereas local sets can only be viewed in and applied to the database firewall policies they were created in. Global sets can be created in Managing Global Sets/Data Discovery.

  8. Click Save.

    A confirmation message of the import process is displayed. The User-defined Database Firewall policies are imported. The policy details are copied to the Audit Vault Server instance and the policies are published. In case there is a name conflict with any of the policies, then a sequence number is added to differentiate. The Audit Vault Server also checks for the file format, validates the JSON file, fields, and values. In case of any issues, an error message is displayed.

    The import process is a background job. The status of the job is displayed in the Jobs dialog. The name of the job is DBFW Policy Import.

  9. The newly imported policy appears in the list. The Imported column specifies whether the policy was imported or not. Make any changes to the policy and save them accordingly. The imported policies are published by default, and can be deployed to the Database Firewall. In case there are any further changes required, they can be published again after modification.

    Note:

    In case the Database Firewall policy has SQL cluster sets and the pertaining SQL statements are not already captured in the Audit Vault Server, then the SQL traffic details are not displayed when the auditor drills down in the cluster for troubleshooting.

7.8.3 Importing Oracle AVDF 20.7 Database Firewall Policies Through CLI

Learn when to use the CLI functionality to import Database Firewall policies.

Oracle AVDF release 20.7 supports export of User-defined Database Firewall policies in a .zip format. Starting with Oracle AVDF release 20.8, Database Firewall policies are exported into a file in JSON format. A policy file with the same format needs to be imported as per Oracle AVDF release deployed. To import the Database Firewall policy files in .zip format, a new CLI utility is introduced.

Prerequisites

Follow these steps to complete some prerequisites:

  1. The Database Firewall policy file exported in Oracle AVDF release 20.7 is in .zip format. Copy this Database Firewall policy .zip file to the Audit Vault Server appliance.

  2. Log in to the Audit Vault Server through SSH.

  3. Run the following command to switch user to root:

    su root
  4. Make sure the oracle user has read access to the copied Database Firewall policy .zip file.
  5. Unlock avsys user by following the next steps.

  6. Run the following command to switch user to dvaccountmgr:

    su dvaccountmgr
  7. Run the following command:

    sqlplus /
  8. Run the following command:

    alter user avsys identified by <pwd> profile default account unlock;

Note:

The CLI utility must be run as oracle user.

Follow these steps to import the Database Firewall policies using the CLI functionality:

  1. Log in to the Audit Vault Server console through SSH.
  2. Run the following command to switch user to root:
    su root
  3. Run the following command to switch user to oracle:
    su oracle
  4. Run the following command:
    /opt/avdf/bin/import_dbfw_policy_zip <policy_zipfile_path>
  5. Enter the password of the policy .zip file when prompted.
  6. Enter the password of avsys user when prompted. Additionally, to seek help while using the CLI, run the following command:
    opt/avdf/bin/import_dbfw_policy_zip -h
  7. The following message is displayed if the Database Firewall policy file is successfully imported:
    Request to import DBFW Policy submitted successfully

    Note:

    In case of any error, an appropriate error message is displayed on the screen.
  8. The import process is a background job. The status of the import job can be checked in the Audit Vault Server console.

    Click Settings tab. The status of the job is displayed in the Jobs dialog. The Job Type is DBFW Policy Import.

  9. After completing the import, run the following to lock the avsys user
    1. Run the following command to switch back to root user
      exit
    2. Run the following command to switch user to dvaccountmgr
      su dvaccountmgr
    3. Run the following command
      sqlplus /
    4. Run the following command
      alter user avsys account lock;
    5. Run the following command
      exit

7.9 Copying a Database Firewall Policy

Learn how to copy an existing Database Firewall policy, and edit the same to create a new policy.

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click the Policies tab.
  3. Click Database Firewall Policies tab in the left navigation menu. This page lists the policies you have created, as well as the pre-defined policies.
  4. Select the check box against the policy you want to copy.
  5. Click Copy.

    The Copy Policy dialog appears. The Target Type field is already filled in.

  6. Enter a Policy Name.
  7. Optionally enter the Description.
  8. Click Copy.

    Policy created successfully message is displayed.

  9. Click on the name of the policy created to make any further changes.

7.10 Editing a Database Firewall Policy

Learn how to edit a Database Firewall policy.

You can edit Database Firewall policies that you have created or copied, and those policies that are not deployed. After a Database Firewall policy is deployed on the target, it cannot be edited. However, you can copy the policy, redefine with a new name, publish it, and then assign the same to the targets.

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click the Policies tab.
  3. Click on Database Firewall Policies tab in the left navigation menu.
  4. Click the name of the specific policy. The details of the policy are displayed in the main page.
  5. Edit the policy as required.
  6. The rules are categorized under Session Context, SQL Statement, Database Objects, and Default. You can make changes to these rules.
  7. You can add, edit, or delete a rule. The Default rule can only be edited.
  8. Starting with Oracle AVDF 20.4, the evaluation order of the rules can be changed (except for Default). Follow these steps:
    1. Click Evaluation Order button. A dialog appears.
    2. Change the order of the rules using the up or down arrows on the right.
    3. Click Save. A confirmation message is displayed on the screen. The updated order of the rules is refreshed on the main page.
  9. After making all the changes to the Database Firewall policy, click Save in the policy overview page.

7.11 Database Firewall Policy for Capturing Return Row Count

Learn to capture return number of rows for SELECT queries.

Starting Oracle AVDF 20.3, if a Database Firewall is deployed in a Monitoring/Blocking (Proxy) or Monitoring (Host Monitor) deployment mode, it can capture the returned number of rows for SELECT SQL statements. If deployed in Monitoring/Blocking (Proxy) mode this requires no additional configuration, but if deployed in Monitoring (Host Monitor) mode, you need to enable it to capture database responses. See Enabling Database Response Monitoring in the Oracle AVDF Administrator's Guide for more information. The details for the returned number of rows are displayed in All Activity and Database Firewall Reports under the column Row Count. This field can be used in Alert policies to detect attempts to retrieve more than expected amount of data.

The field Capture number of rows returned for SELECT queries is available in the Audit Vault Server console when defining the Database Object rule of the Database Firewall policy. It is available when the target type is an Oracle Database and when Data Manipulation Readonly is selected in the Statement Classes field. Data Manipulation Readonly field is replaced with Select in Oracle AVDF 20.4 and later. When this option is enabled, the Database Firewall captures the returned number of rows for SELECT queries.

Step Process Reference

1

Configuring Database Firewall policy to capture return row count

Database Object Rule

2

Configuring alert policy based on row count

Writing an Alert Condition

3

Viewing row count in reports

All Activity Reports

Database Firewall Reports

7.12 Configuring Firewall Policy for SQL Statements

Learn about policies that can be used for SQL traffic to the database.

Database Firewall is a multistage firewall that monitors SQL traffic going into the database and determines with high precision whether to allow, log, alert, substitute, or block the SQL statements. The SQL traffic goes through multiple stages that checks for the IP address, database or OS user, program name, SQL statement category, such as DDL and DML, and database tables being accessed. It blocks and alerts block listed SQL and SQL that is not in the allowed list or SQL statements. This prevents SQL injection attacks by providing an option to configure policy rules based on allowed list of SQL requests from trusted application paths.

Successful SQL statement monitoring using Database Firewall depends on deciding an effective firewall policy and selecting the appropriate firewall deployment.

7.13 Blocking SQL and Creating Substitute Statements

Learn how to block a SQL statement and provide a sample SQL statement as a substitute.

Database Firewall can block SQL statements when deployed in Monitoring / Blocking (Proxy) mode. Database Firewall can block a SQL statement and you can provide a sample SQL statement as a substitute. A substitute statement may be necessary to ensure that the database client is presented with an appropriate message when a statement is blocked. This substitute statement may also be helpful in misleading a malicious user with the sample SQL statement provided. It can also be configured with a threshold value for blocking or alerting.

Substitute statements cannot be applied on the following SQL commands:

  • LOGIN USERNAME
  • EXECUTE CURSOR
  • ENCRYPTED
  • SHUTDOWN
  • DESCRIBE
  • ORADEBUG
  • TRANSACTION
  • LOB
  • INVALID OPERATION
  • COMMENT
  • COMPRESSED

When creating substitute statements, ensure the results can be handled by the client applications. The following is an example of a good substitute statement for Oracle Database target:

SELECT 100 FROM DUAL

You can block or warn when the SQL statements occur a specified number of times. You can choose to block the SQL statement or produce a warning if a statement repeats a specified number of times (or threshold value) in the selected cluster. You should always enable logging for blocked statements.

7.14 SQL Statement Encrypted with Oracle Native Network Encryption

Learn how Database Firewall can be used to monitor Oracle Database target that uses native network encryption.

When this functionality is enabled, network traffic between the client and database server is encrypted. In order for the Database Firewall to decrypt and apply policy on SQL statements, Oracle AVDF administrator must configure the Database Firewall to decrypt this traffic.