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:
- Monitoring (Out-of-Band)
- Monitoring (Host Monitor)
- 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 |
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.
Rule | Description |
Session Context |
Client program session attributes like, client program name, host IP address, OS user, and database user are used to define the action of the Database Firewall in this rule. |
SQL Statement |
SQL statements captured by Database Firewall are clustered into groups of similar statements. This rule defines the action of the Database Firewall on such SQL clusters. |
Database Object |
Database Object rule defines the action of the Database Firewall based on SQL statement types (DML, DDL, etc.) on a list of configured tables or views. |
Login/Logout |
The login or logout rule defines the action of the Database Firewall based on login and logout sessions by client programs on target databases. |
Unknown Traffic |
This rule defines the action of the Database Firewall for SQL statements that are not recognized for the following possible reasons:
Note: Interpretation of Java code is not supported by Database Firewall. |
Default |
In this rule if a SQL statement does not meet any of the previous configured rules, then the Database Firewall acts as per the actions of this rule. |
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.
Policy Name | Description |
Default |
When Database Firewall monitoring point is configured for the target, this Default policy is applied automatically. This is available starting with Oracle AVDF release 20.7. The auditor can also assign this Default policy to existing Database Firewall monitoring points. The Default policy consists of the following three rules:
Additionally the traffic is logged with data masking turned on to avoid accidental logging of sensitive data. This policy is applied by default to all new targets during registration. This includes all the new monitoring points configured later for the specific target database. This Default policy can be copied and customized like any other policy. Note: In case of upgrade from any release prior to Oracle AVDF 20.7, the Database Firewall monitoring points created prior to the upgrade will continue to have the same Database Firewall policy assigned prior to the upgrade. This Default policy can be applied after the upgrade. |
Log all |
Log all statements for offline analysis. All statements are logged in the Audit Vault Server. Note: If this policy is applied, it can use significant amount of storage for the logged data. |
Log all - no mask |
Log all statements for offline analysis without masking the data. Every statement is logged into Audit Vault Server without masking the data. Note: If this policy is applied, then it can use significant amount of storage for the logged data. Sensitive information may be logged if you select this policy. |
Log sample |
Log a sample of statements for offline analysis. The frequency of logging into Audit Vault Server is every tenth statement having the same cluster ID. Note: If this policy is applied, then it stores fewer statements than logging all statements. It can still use significant amount of storage for the logged data. |
Log unique |
Log examples of statements for offline analysis for unique SQL traffic. Unique statements are logged into Audit Vault Server. A SQL statement is considered as unique based on the following parameters:
Note: If this policy is applied, then it stores fewer statements than logging all statements. It can still use significant amount of storage for the logged data. |
Log unique - no mask |
Log examples of statements for offline analysis covering unique SQL traffic without masking data. This is the same as "Log unique" excluding masking of data. Note: If this policy is applied, then it stores fewer statements than logging all statements. It can still use significant amount of storage for the logged data. Sensitive information may be logged if you select this policy. |
Pass all |
Pass all statements. No statements are logged into the Audit Vault Server. |
7.4 Developing a Database Firewall Policy
Learn about developing a Database Firewall policy.
Developing a Database Firewall policy involves the following steps:
- Creating a new Database Firewall policy.
- Configuring the created Database Firewall policy.
- Publishing a Database Firewall policy.
- Deploying Database Firewall Policies to targets.
- 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.6 Configuring the Created Database Firewall Policy
Learn about configuring the Database Firewall policy already created.
Configuring the Database Firewall policy involves:
- Configuration of global policy settings.
- Creation of sets or profiles.
- 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.
7.6.1.1 Configuring Policies for Login and Logout Events
Learn how to configure Database Firewall policy for login and logout events.
To configure the login and logout policies:
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:
-
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.
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:
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:
- Log in to the Audit Vault Server console as an auditor.
- Select the Policies tab.
- Select Database Firewall Policies in the left navigation menu.
- Click the name of the specific policy. The details of the policy are displayed on the page.
- Click Configuration button in the top right corner.
- Click Policy Pattern tab.
-
In this tab, configure the following:
Pattern Type Action Log Pattern
Select the option Strip binary objects and comments from log files checkbox to strip binary objects and comments from SQL statements before logging them into Audit Vault Server.
Action Rule Pattern
- In the Threshold action reset time (minutes) field, enter an integer for the number of minutes. If you have set a threshold in any of your policy rules, and the Threshold Action in your rule is taken, the action is not repeated for the time specified here. This prevents too many block or warn actions for the same rule.
- In the Action without substitution field,
select one of the actions (
No response
orDrop connection
) if any of the Database Firewall policy rules is set toBlock
, and you have not specified a substitute statement in the rule.
Syntax Rule Pattern
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.
For Case sensitive match select whether this policy does case sensitive matching for the following:
- Client program name
- Database username
- Operating system username
- 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:
- IP Address Sets: A list of IP addresses of client programs (IPv4 format).
- Database User Sets: A list of database user names.
- OS User Sets: A list of operating system user names.
- Client Program Sets (Database Client Sets in Oracle AVDF 20.3 and earlier): A list of client programs. For example SQL*Plus.
- Database Object Sets: A list of tables to be evaluated by a policy.
- 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:
- Log in to the Audit Vault Server console as an auditor.
- Select the Policies tab.
- Select Database Firewall Policies in the left navigation menu.
- Click the name of the specific policy. The details of the policy are displayed in the main screen.
- Click Sets/Profiles in the top right corner. This page lists the sets already defined for the specific policy.
-
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
- Click Add to add a new local set.
- 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. - Click Save. The new local set appears in the specific policy page.
- 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:
To add or delete the SQL cluster from a given set:
- Click SQL Cluster Sets sub tab.
- From the report choose the specific SQL cluster set, the details of the cluster set are displayed.
- Choose Add and follow the procedure to add new clusters to an existing set.
- From the same dialog, choose Delete option to delete one or more clusters from the set.
- 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:
- IP Address Set
- DB User Set
- OS User Set
- 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:
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
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:
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:
- Click Evaluation Order button. A dialog appears.
- Change the order of the rules using the up or down arrows on the right.
- 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:
- IP Address Set
- DB User Set
- OS User Set
- Client Program Set (DB Client Program in Oracle AVDF 20.3 and earlier)
To create a session context rule:
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:
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 tablesAVG_COST
,BOOKS
, andBUSINESS_CONTACTS
. A statement that matches this rule must beTRANSACTION 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
andComposite
under Statement Classes. You can also select the tablesAVG_COST
,BOOKS
, andBUSINESS_CONTACTS
. A statement that matches this rule must either beProcedural
orComposite
, and the SQL statement must have all the tables (AVG_COST
,BOOKS
, andBUSINESS_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. SelectData Manipulation Read only
under the Statement Classes field. This option is available only forData 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.
- Log in to the Audit Vault Server console as an auditor.
- Select the Policies tab.
- Click Database Firewall Policies tab in the left navigation menu.
- 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. - Expand the Database Objects section, and then click on an existing database object rule or click Add. The Database Objects dialog is displayed.
- Provide a Rule Name.
- Enter a Description.
- Select a Profile, the default is none as shown by -.
- In the Statement Classes, select one or more types of statement classes that SQL statements must match in order to apply this rule.
- 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) orSELECT
(Oracle AVDF Release 20.4 and later), the field Capture number of rows returned for SELECT queries is able to be toggled toYes
. Upon setting this field toYes
, 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. - 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.- To edit the list of tables to be monitored, click the pencil icon to the right of the text box.
- 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.
- Include fully qualified table names. Note:
The tables
T1
andMyschema.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.
- In the Action to be taken section, select the Action, Logging Level, and Threat Severity for this rule from the appropriate drop down list.
- 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. - Click Save.
- Log in to the Audit Vault Server console as an auditor.
- Select the Policies tab.
- Click Database Firewall Policies tab in the left navigation menu.
- 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. - Expand the Database Objects section, and then click on an existing database object rule or click Add. The Database Objects dialog is displayed.
- Provide a Rule Name.
- Enter a Description.
- Select a Profile, the default is none as shown by -.
- In the Statement Classes, select one or more types of statement classes that SQL statements must match in order to apply this rule.
- 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 toYes
. Upon setting this field toYes
, 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. - 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.- To add Database Object Sets, click the + button to the right of the text box.
- 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 includeT1
andT2
.
- 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.
- 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.
- In the Action to be taken section, select the Action, Logging Level, and Threat Severity for this rule from the appropriate drop down list.
- 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. - Click Save.
- Log in to the Audit Vault Server console as an auditor.
- Select the Policies tab.
- Click Database Firewall Policies tab in the left navigation menu.
- 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. - Expand the Database Objects section, and then click on an existing database object rule or click Add. The Database Objects dialog is displayed.
- Provide a Rule Name.
- Enter a Description.
- Select a Profile, the default is none as shown by -.
- In the Commands section, select the specific commands to add to this rule
- 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 toYes
. Upon setting this field toYes
, 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. - 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.- To add Database Object Sets, click the + button to the right of the text box.
- 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 includeT1
andT2
.
- 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.
- 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.
- In the Action to be taken section, select the Action, Logging Level, and Threat Severity for this rule from the appropriate drop down list.
- 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. - 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:
-
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:
-
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:
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.
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:
- Test to production Audit Vault Server instance.
- 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.2 Importing Database Firewall Policies
Learn how to import one or more Database Firewall policies.
- Log in to the Audit Vault Server console as an auditor.
- Click Policies tab.
- Click Database Firewall Policies tab in the left navigation menu.
- Click the Import button in the top right corner of the page.
-
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. - Enter the Password. It is the same password that was set when the Database Firewall policies were exported earlier.
- 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.
- 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
. - 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.
Follow these steps to complete some prerequisites:
-
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. -
Log in to the Audit Vault Server through SSH.
-
Run the following command to switch user to root:
su root
- Make sure the
oracle
user has read access to the copied Database Firewall policy .zip file. -
Unlock avsys user by following the next steps.
-
Run the following command to switch user to dvaccountmgr:
su dvaccountmgr
-
Run the following command:
sqlplus /
-
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:
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.
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.
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 |
|
2 |
Configuring alert policy based on row count |
|
3 |
Viewing row count in 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.