Oracle AVDF – Database Firewall Policies

The successful deployment of a Database Firewall depends on an effective policy. Oracle AVDF includes preconfigured firewall policies as described in the Firewall Policy page in the Policy tab of the Audit Vault Server console.

These include policies that log all SQL statements, or log only unique SQL statements. In addition, the Database Firewall policy editor enables you to design your own policies quickly and efficiently.

Policy rules can depend on any combination of the SQL statement type, name of the database user, IP address of the database client, operating system user name, client program name, or any exceptions you specify.

First policy and global concept

1. Log in to the Audit Vault Server console as an auditor, and click on the Policy tab:

2. Under the Policy menu, click Firewall Policy.

3. Click Create Policy.

The Create Policy dialog appears. Select the Database Type from the drop-down list (choice between IBM DB2, Microsoft SQL Server, MySQL, Oracle Database, Sybase ASE, Sybase SQL Anywhere), Enter a Policy Name and Optionally, enter a Description:

3. Click on “Create”. The new policy is created, and the policy’s Overview page appears:

When you create a new policy, or click an existing policy name in the Firewall Policies page, that policy’s Overview page appears. This page shows the policy rules that are being applied to the statement types (clusters) being monitored by the Database Firewall, as well as exceptions and other rules that may apply.

The policy’s Overview page is divided into these sub-sections:

• Exception Rules – Lists exceptions you have created. The rules that you have assigned to SQL statement clusters will not apply to these exceptions. You can move the rules up or down in the list. The rules are evaluated in the order listed.
• Analyzed SQL – Displays the number of SQL statement clusters for which you have defined policy rules, and their policy actions (such as Warn or Block).
• Novelty Policies (Any) – Lists special policies you have created for specific statement classes and/or specific tables in your secured target databases. If you have identified specific tables in a policy in this section, the policy rule applies if it matches Any of the tables.
• Novelty Policies (All) – Lists special policies you have created for specific statement classes and/or specific tables in your secured target databases. If you have identified specific tables in a policy in this section, the policy rule applies if it matches All of the tables.
• Default Rule – Shows the default rule for any statements that are not matched by the rules set for Analyzed SQL clusters, Exceptions, or Novelty Policies.
• Policy Controls – Lets you configure firewall policy settings, create policy profiles, as well as sets of filters to use in defining profiles and Exception rules.

Practical case

Developing a policy is an iterative process that keeps refining and improving the policy with new data. In order to be able to create a policy statements have to be executed on the database. The examples below present some possibilities provided by Firewall Policy.

These examples are based on a very simple context with two schemas/users:

1. PSI
2. GRS

PSI schema contains three tables:

1. PSI.CREDIT_CARD containing Credit Cards numbers
2. PSI.EMP containing employees’ salaries
3. PSI.TEST containing one non sensitive row

SQL> select table_name from dba_tables where owner='PSI';
TABLE_NAME
------------------------------
EMP
CREDIT_CARD
TEST

Novelty Policy

The table CREDIT_CARD contains credit cards numbers and EMP contains Employee salary. These two tables are very sensitive and nobody can have a look on these tables:

The first step in order to create this policy is to create a novelty rule. Novelty policies specify the action, logging level, and threat severity to use for specific types of statements and/or statements that operate on selected tables. Novelty policies can be used to loosen or tighten your normal policy rules if certain statements are encountered. In our context we want to create a novelty policy that will block all access to these tables:

1. In the Audit Vault Server console, select the “Policy” tab.
2. From the Policy menu, click “Firewall Policy”.
3. Click on the newly created Firewall Policy named “MyPolicy”
4. Click Add Novelty Rule in section Novelty Policy (Any):
5. In the Novelty Policy Details dialog, define the following:

a. Novelty Rule: Enter a name for this rule: MyNR

b. Statement Classes: Select one or more types of statements that SQL statements must match in order to apply this rule. In this example we have to select “Data Manipulation Read Only”

c. Policy Controls: Select the Action, Logging Level, and Threat Severity for this rule from the appropriate drop-down list. In this example we have to select “Block” for action and specify in the substitution field, the statement below:

select 'You do not have access to this table' from dual

6. Affected Tables: Select the table(s) to use for matching statements to this policy. In order to have tables in this list, tables have to be accessed first. If there is no activity on the database the list will be empty. In our specific case we select tables: PSI.EMP and PSI.CREDIT_CARD and we click on “Add Tables”:

7. Click on “Create”.
8. Now we can test this policy. For the moment this policy will block access to any user trying to have access to these two tables.In order to apply this policy we have to save the policy by clicking on “save” and then “publish”.
9. Click on “Secured Targets”
10. Click on the target where you want to apply the policy
11. Click on Firewall Policy
12. Select the Policy “MyPolicy”
13. Now you can check that the policy is applied by doing a select on this table.

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ConnectÚ Ó :Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.credit_card;

‘YOUDONOTHAVEACCESSTOTHISTABLE’
------------------------------------
You do not have access to this table
SQL>

We can execute the same query with user GRS, the result will be the same:

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ConnectÚ Ó :Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.credit_card;

‘YOUDONOTHAVEACCESSTOTHISTABLE’
------------------------------------
You do not have access to this table
SQL>

Exception Rule

The table CREDIT_CARD contains credit cards numbers and EMP contains Employee salary. These two tables are still very sensitive but since PSI has been promoted Chief Financial Officer he need access to these tables. Therefore we will create an exception for him:

1. In order to change the policy this one has to be unused. Click on secured targets, select the target, and click on firewall policy and change the policy to “log all”:

2. Now you can make modification to your policy “MyPolicy”.

First of all we need to create a Profile. Within a firewall policy, a profile lets you define a different set of policy rules based on the session data associated with SQL statements. To define the profile, you use the session filters you defined in the Policy Controls section of the firewall policy. These session filters filter SQL statements based on:

• IP addresses
• Database user login names
• Client program names (for example, SQL*Plus)
• Operating system user names

In this example we will create a profile based on Database user login named. This user will be PSI.

3.Click on Policy
4.Click on Firewall Policy
5. Click on MyPolicy
6. Click on Database User Set
7. Create a new set by clicking on “Create New Set”
8. Enter values for field New Set Name and member:

9.Click on “Create Set”
10. Click on “Cancel”
11.Click on “Profiles”
12. Create a new Profile by clicking on “Create New Profile”
13. Enter the Profile Name and select “UsersHavingAccessToMyTable” in the field “DB User Set”:

14. Click on “Create Profile”

Now we have to create an exception based on this profile. An exception determines the action, logging level, and threat severity to use when certain session data is encountered. For example, an exception could specify rules for statements that originate (or do not originate) from selected client IP addresses or database user names. In this example, the exception will be based on database user name.

Exceptions override all other policy rules. For example, you may want to override the normal policy rules if SQL statements originate from an administrator, or if they originate from anywhere other than a specific IP address.

You can define many exceptions and control the order in which they are evaluated. Each Exception has its own Action, Logging, and Threat Severity settings.

15. Click on policy
16. Click on firewall policy
17. Click on your newly created policy “MyPolicy”
18. Click on “Add Exception”
19. Enter the expception rule name : “ExceptionForPSITable”
20. Into DB User Set select “Include” and select “UsersHavingAccessToblMyTable”
21. In Policy Control click on “Pass”
22. Click on “Create”:

23. Click on “Save” and “Publish”
24. Apply this policy to the target

Now, the user PSI can access to all his tables and user GRS have no access to sensitive tables.

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ConnectÚ Ó :Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.emp;

'YOUDONOTHAVEACCESSTOTHISTABLE'
------------------------------------
You do not have access to this table

SQL> select * from psi.credit_card;

‘YOUDONOTHAVEACCESSTOTHISTABLE’
------------------------------------
You do not have access to this table

SQL> select * from psi.test;

NAME
----------
hello

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ConnectÚ Ó :Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.credit_card;

NAME               ID
---------- ----------
Larry     4,8960E+15
John       7,8623E+15

SQL> select * from psi.emp;

NAME             SAL
---------- ----------
Larry         150000
John           80000

SQL> select * from psi.test;

NAME
----------
hello
SQL>

Analyzed SQL

With the exception we granted access to PSI database user to all his table. Since PSI didn’t present good results to shareholders he has been replaced by a new CFO and this one decided that PSI has now only access to credit card number but is not anymore allowed to make select statement on employees’ salaries table:

1. First of all we have to remove the exception we did before by clicking on the exception rule and clicking on “delete”
2. In the policy overview click on “Modify SQL”3.Click on “Change”
3. Select “Secured Target”, select the profile “ProfileForPrivsUsers” and enter in Event Time last 24 hours.
4. Click on “Apply”
5. Click on column header “User Name”
6. Select “psi”
7. A line looking like the one below should appear in the list
8. Select this line
9. Click on “Set Policy”
10. In the Action list click on “Pass”:

11. If you have a look on the list of SQL, the following statement should appear.

12. Create a profile for user GRS named “ProfileForStdUsers” as we did for user PSI. This profile won’t have the possibility to execute statement “Select * from psi.credit_card”.

13. In “Analyze SQL” select profile “ProfileForStdUsers” and filter on GRS user as we did in step

14. Select the following statement:

15. Click on “Set Policy”

16. Select action “Block”

17. In the field Substitution enter the following: “select ‘Your profile does not allow access to this statement’ from dual ”

18. Now your section “Analyzed SQL” should look like the screenshot below:

19. Save and Publish the modification done on this policy’s

20. Apply this policy to your target, click on secured targets, Firewall Policy and select “MyPolicy” in the list.

Now we can test the access:

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ConnectÚ Ó :Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.emp;

‘YOUDONOTHAVEACCESSTOTHISTABLE’
------------------------------------
You do not have access to this table

SQL> select * from psi.credit_card;'

YOURPROFILEDOESNOTALLOWACCESSTOTHISSTATEMENT’
----------------------------------------------------
Your profile does not allow access to this statement

SQL> select * from psi.test;

NAME
----------
hello

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ConnectÚ Ó :Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.emp;

'YOUDONOTHAVEACCESSTOTHISTABLE'
------------------------------------
You do not have access to this table

SQL> select * from psi.credit_card;

NAME               ID
---------- ----------
Larry     4,8960E+15
John       7,8623E+15

SQL> select * from psi.test;

NAME
----------
hello

This blog is not intended to be a comprehensive description of all AVDF policies functionnalities but I do hope that it provided you a good overview of some basic functionnalities.