Oracle 23ai : SQL Firewall - Part 1 : Intergrate SQL-Firewall with Data-Safe

 

Intro 

We live in the data era, where every organization invests significant resources in securing its IT infrastructure. Protecting data is crucial because modern businesses rely heavily on data analysis. Ensuring the security of sensitive data has become the primary responsibility of security engineers and database administrators. Due to the critical nature of this data, hackers often attempt to gain control through :

• SQL injection attacks 
• Compromised accounts. 

Even OCI introduced the Web application firewalls (WAFs) claim to mitigate SQL injection attacks, but attackers can bypass these pattern-matching techniques. Allowlist-based and network-based SQL firewalls provide stronger protection but are ineffective for local and encrypted traffic. Additionally, they need more run-time context to thoroughly analyze SQL queries.

Oracle's latest long-term release 23ai came up with the latest features to integrate SQL firewall into the database. This enables the database administrator to elevate their database protection.

In the cloud era, having a dashboard for SQL firewall management is crucial. One key feature to highlight is the integration with a data-safe dashboard. With a single dashboard, administrators can gain comprehensive visibility into database workloads, enabling them to proactively protect data rather than reacting to threats after they occur.

Many features come with SQL-firewall, I have highlighted a few below.

• SQL Command Whitelisting and Blacklisting: The firewall can whitelist approved SQL commands and blacklist unauthorized ones, providing fine-grained control over what queries can be executed.

• Behavioral Analysis: The AI models analyze normal SQL query patterns and flag anomalous queries that deviate from the norm, indicating potential threats or misuse.

• Automated Threat Detection: Machine learning algorithms detect and respond to SQL injection attacks and other malicious activities in real time.

• Real-time Monitoring and Alerts: Continuous monitoring of SQL traffic with real-time alerts for suspicious activities ensures immediate response to potential security incidents.

• Self-learning Capabilities: The AI models continuously learn from new SQL queries to improve detection accuracy and reduce false positives over time.

• Detailed Audit Logs: Comprehensive logging of SQL activity for auditing purposes, helping to trace and investigate security incidents.

In this article, I will show you how you can integrate SQL-Firewall with data safe. 

To get more understanding you can use this Oracle blog. 

https://blogs.oracle.com/coretec/post/23c-sql-firewall.

Before this activity make sure to create or install an Oracle 23ai instance and enable sql-firewall.

How to enable SQL-Firewall

Login to 23ai instance and execute exec dbms_sql_firewall.enable; commands to enable SQL-Firewall.

Expected output

SQL> exec dbms_sql_firewall.enable;

PL/SQL procedure successfully completed.

SQL>


SQL> select status from   dba_sql_firewall_status;

STATUS
--------
ENABLED

SQL>

First, log in to Oracle Cloud and navigate to the Oracle Database tab. Under External Databases, you will find options for Data Safe and SQL Firewall.

                                       Figure 1: OCI portal: Data Safe and SQL Firewall                                        

Data safe page there is a section for sql-firewall.

                       

                                      Figure 2: OCI portal: Data Safe, SQL Firewall section.

Data Safe supports integration with all Oracle 23c AI features across various platforms, including:

• Autonomous Database
• Oracle Database on Compute
• Oracle On-Premises Database
• Oracle Cloud Databases
• Oracle Cloud@Customer
• Amazon RDS for Oracle

For this testing, I have already created an instance using DBCS.

Note: You must create a private endpoint to enable connectivity between the database and Data safe.

                                     Figure 3:  List of databases that can be registered with data safe.

These figures show the information required to register the database with Data Safe:

For Registration 

• Database Service Name 
• Port number.

Additionally, you need to create the DATASAFE$ADMIN user. Once the user is created, download the grant script and execute it as specified below.

How to create DATASAFE$ADMIN

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 C23AI_PDB                      READ WRITE NO
SQL> CREATE USER DATASAFE$ADMIN identified by WElcome123## default tablespace USERS temporary tablespace TEMP;

User created.

SQL>

Once you create the user, download the script from the portal and copy it to the database server. In this example, I've named the script "DATASAFE." This script will prompt for USERNAME, TYPE, and MODE.

SQL> @DATASAFE.sql
Enter value for USERNAME (case sensitive matching the username from dba_users)
DATASAFE$ADMIN
Setting USERNAME to DATASAFE$ADMIN
Enter value for TYPE (grant/revoke)
grant
Setting TYPE to grant
Enter value for MODE (audit_collection/audit_setting/data_discovery/masking/assessment/sql_firewall/all)
sql_firewall
Setting MODE to sql_firewall
Granting SQL_FIREWALL privileges to "DATASAFE$ADMIN" ...
Disconnected from Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.4.0.24.05
[oracle@dbsdpl23 DATA_SAFE]$

Figure 4: Integrate database - part -01.

                                                  Figure 5: Integrate database - part -02.

Also, create an ingress rule to allow 1521 traffic on a private network.

                                                        Figure 6: ingress rule

Registration 

This will show you the created endpoint for registration.

                                                        Figure 7: Private endpoint.                                     

 Select the respective security list for registration.

Figure 8: Select the security list.

Review Registration information.

Before submitting, review the information one more time

                                               Figure 9: Registration review part 1
                                                           

                                                Figure 10: Registration review part 2

Complete the registration.

Once the registration is complete, the Target database should change to green.

                                             Figure 11: Target database 

Dashboard

Once registered, the dashboard will display as shown below. It will include information on SQL Firewall violations and SQL collections.

                                             Figure 12: SQL-Firewall Dashboard

In Part 2, I will elaborate on how you can interact with the dashboard and how to capture and enable SQL Firewall rules.

Conclusion

In conclusion, the SQL Firewall provides robust security measures by monitoring and controlling SQL statements executed in your database environment. It offers comprehensive insights into SQL activities, detects potential threats, and enforces security policies to protect your data. Utilizing the SQL Firewall, you can ensure a secure and compliant database environment.