1. Home
  2. Knowledge Base
  3. Security
  4. Database Firewall 20.4 – Block and Substitute SQL

Database Firewall 20.4 – Block and Substitute SQL

The Database Firewall analyzes and inspect SQL statements before they reach the database and based on the firewall policy which we configure, we can decide whether to allow, log, alert, substitute, or block the particular SQL statement.

In this example, the sensitive SALARY column is blocked and the database firewall policy rule does the following transformation.

Original SQL Statement

SELECT FIRST_NAME,LAST_NAME,SALARY FROM EMPLOYEES;

Substituted SQL Statement

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;

Configure Database Firewall in Monitoring/Blocking mode on port 15221 for pluggable database PDB1

Create the tnsnames.ora alias PDB1_DFW

Note that the IP address 192.168.56.250 belongs to the Database Firewall server. (192.168.56.100 is the actual IP address of the database server which hosts PDB1).

PDB1_DFW =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.250)(PORT = 15221))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb1)

)

)

Test connection to PDB1 via the Database Firewall

[oracle@db01 admin]$ sqlplus hcm/Oracle_4U@pdb1_dfw

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Mar 19 12:25:09 2021

Version 19.8.0.0.0

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

Last Successful login time: Fri Mar 19 2021 12:25:01 +08:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.8.0.0.0

SQL>

Create a Database Firewall Policy

Click on Create

Click on Sets/Profiles

Select SQL Cluster Sets tab and click on Add

Select the SQL statement ‘SELECT FIRST_NAME,LAST_NAME,SALARY FROM EMPLOYEES’ from the sample SQL

Under the Rules tab, select SQL Statement

The SQL Cluster set which we created in the earlier step is now added to the Rule.

Note: We are transforming the blocked SQL statement as part of the rule and excluding the SALARY column from the SQL statement.

The Database Firewall policy to block and transform the SQL statement has now been added under the User-defined policies.

From the Targets menu select the DB01 database and the Database Firewall Monitoring tab

Click on the edit icon under Database Firewall Policy

Select the User-defined policy DFW_POLICY_1 and click on the ‘tick’ icon.

Ensure that the job to Apply and Publish the policy has completed successfully.

Test the Database Firewall policy

Connect to the PDB1 pluggable database using the TNS alias PDB1_DFW.

Execute the SQL statement SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES.

Note the SALARY column has been excluded from the command output.

[oracle@db02 admin]$ sqlplus hcm/Oracle_4U@pdb1_dfw

SQL*Plus: Release 19.0.0.0.0 – Production on Tue Apr 6 17:23:12 2021

Version 19.8.0.0.0

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

Last Successful login time: Tue Apr 06 2021 16:01:49 +08:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.8.0.0.0

SQL> select first_name,last_name,salary from employees;

FIRST_NAME LAST_NAME

——————– ————————-

Ellen Abel

Sundar Ande

Mozhe Atkinson

David Austin

Hermann Baer

Shelli Baida

Amit Banda

Elizabeth Bates

Sarah Bell

David Bernstein

Laura Bissot

Updated on June 21, 2021

Was this article helpful?

Related Articles

Comments

Leave a Comment