How to block a Session from a specific IP in Oracle Database Firewall

Environment:

• 1 Audit Vault Server
• 1 Database Firewall (Configured as Proxy in 192.168.56.11:5557)
• 1 Oracle Database  (Listener: 192.168.56.30:1521/orcl)
• 1 Oracle Client (192.168.56.30)

With Oracle Database Firewall I will create an "Exception" that uses a "IP Address Set" to block a Session being created from an Oracle Client with IP 192.168.56.30


Checking that we can connect from the machine with IP 190.168.56.30:

[oracle@db12c ~]$ sqlplus dgomez/dgomez@192.168.56.11:5557/orcl

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 12 04:56:32 2019

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

Last Successful login time: Wed Jun 12 2019 04:56:28 -06:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> select * from dgomez.allowed;

COL1

--------------------

DEIBY

SQL>

Login into Oracle Audit Vault Server Web Console:

Click in "Policy" and then "Create Policy":

Select Database Type, Policy Name, and Description. The Database Type should be according the the Database we are monitoring with Oracle Database Firewall, in my case, of course Oracle Database.

You will see all the sections that we can fill up in order to Block/Allow operations against the database, we can custom this as much as we want. Click in "IP Address Sets" under "Policy Controls":

Click in "Create New Set":

Type the name of the New Set, and also the IP that will be used by the "Exception". In this case, the IP that we want to block.

Verify that the IP Address Set was added correctly:

Now come Back to the "Policy Overview" Page, click in the title:

Click in "Add Exception" under "Exception Rules":

Type the name of the Exception Rule, in the section "Profile Sets" select "include" for "IP Address Set" and select the IP Address Set that was created before, in my case "IP Address set 1".
In the Section "Policy Controls" select "Block" for "Action", you can select any value for "Logging Level" and "Threat Severity".

Click in "Save" button.

Verify that the Exception rule was created successfully:

Come Back to the "Policy Overview" Page, click in the title:

Click in "Save":

Click in "Publish":

Verify that the new Policy was created successfully:

Now it's time to tell the Database Firewall which Policy should use for our Database.
Click in "Secured Targets", the Oracle Database will be there, select it. In my case the Secured Target is "db12c". If you don't know how to create a "Secured Target" check my previous article [How to configure Oracle Database Firweall as Proxy in DPE Mode].

Expand the section "Firewall Policy":

Select the Policy that was created before in this article, in my case "Policy1":

Verify that Secured Target is using the correct Policy:

Now it's time to test the configuration.

From the Oracle Client (192.168.56.30) I will try to connect to the Database through the Oracle Database Firewall which is configured as Proxy.

Check the IP of Oracle Client:

[oracle@db12c ~]$ ifconfig|egrep -A1 enp0

enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500

        inet 192.168.56.30  netmask 255.255.255.0  broadcast 192.168.56.255

Opening a new Session:

[oracle@db12c ~]$ sqlplus dgomez/dgomez@192.168.56.11:5557/orcl

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 12 04:59:40 2019

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

<< The Session just hang here indefinitely >>

If there was a session already Open at the time we set the Policy for this database, then the session will not able to perform any operation as you can see bellow, the session will get the error ORA-00900 for new upcoming operations:

SQL> select * from dgomez.allowed;

select * from dgomez.allowed

*

ERROR at line 1:

ORA-00900: invalid SQL statement

SQL>

Additional, I saw that if in the "Exception Rule" we put a text in "Substitution" field, then a different error is received when a new session is created.

Using "Tests 1" in "Substitution" field:

Error received when "Substitution" is specified:

[oracle@db12c ~]$ sqlplus dgomez/dgomez@192.168.56.11:5557/orcl

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 12 05:13:20 2019

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

ERROR:

ORA-00900: invalid SQL statement

ERROR:

ORA-00900: invalid SQL statement

Error accessing PRODUCT_USER_PROFILE

Warning:  Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

ERROR:

ORA-00900: invalid SQL statement

Error accessing package DBMS_APPLICATION_INFO

SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.