Enhancing Database Security with Oracle 23ai SQL Firewall
Data security is a critical requirement for any organization in order to safeguard sensitive information from unauthorized access, breaches, and cyberattacks. Compromised accounts and SQL injection attacks are the most commonly used techniques to get full access of data stored in the database.
As Web Application Firewall (WAF) provides protection at the application layer by filtering HTTP/HTTPS traffic and blocking web-based attacks like cross-site scripting (XSS)and SQL injection, it may not offer comprehensive security for databases. Attackers can bypass WAF using advanced SQL injection techniques or by leveraging encrypted traffic to deliver malicious payloads directly to the database.
In Oracle database 23ai, SQL firewall is directly integrated into the database kernel. This enhances the data protection at the core level by directly monitoring and controlling SQL queries that reach the database.
It detects and blocks unauthorized or suspicious queries in real-time, providing an additional layer of security against sophisticated attacks, insider threats, and misconfigurations.
Let’s test a scenario that demonstrates how SQL firewall helps to protect the database with a SQL injection attack.
Here we will deploy SQL firewall in a 23ai base DB and initiate a SQL injection query and check how the database responds.
Components:
To test this use case, we have deployed following components in oracle cloud tenancy:
- 23ai Base DB
- Data Safe
Step1: Register the database with data safe cloud service in OCI.
We need to create a DATASAFE_ADMIN user in PDB and provide the necessary privileges by downloading the privilege script from data safe console.
CREATE USER DATASAFE_ADMIN identified by ********
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT CONNECT, RESOURCE TO DATASAFE_ADMIN;
@datasafe_privileges.sql DATASAFE_ADMIN GRANT SQL_FIREWALL -VERBOSE
Step 2: Enable the sql firewall from data safe console
Step 3: Start the sql collection for a database user
Step 3: Create a few tables with sensitive data and access it from SOE user to monitor the sql collection
CREATE TABLE demo.users (
user_id NUMBER PRIMARY KEY,
username VARCHAR2(50) NOT NULL,
password VARCHAR2(100) NOT NULL, -- Sensitive data
email VARCHAR2(100),
phone_number VARCHAR2(20),
social_security_number VARCHAR2(11), -- Sensitive data
last_login TIMESTAMP
);
CREATE TABLE demo.customers (
customer_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
phone_number VARCHAR2(20),
address VARCHAR2(200),
city VARCHAR2(50),
state VARCHAR2(50),
zip_code VARCHAR2(10),
customer_status VARCHAR2(20) -- e.g., 'active', 'inactive'
);
CREATE TABLE demo.financial_records (
record_id NUMBER PRIMARY KEY,
account_id NUMBER,
transaction_date DATE,
transaction_amount NUMBER(15,2),
transaction_type VARCHAR2(20), -- e.g., 'debit', 'credit'
description VARCHAR2(255),
customer_id NUMBER, -- Foreign key from the customers table
FOREIGN KEY (customer_id) REFERENCES demo.customers(customer_id)
);
CREATE TABLE demo.orders (
order_id NUMBER PRIMARY KEY,
order_date DATE,
customer_id NUMBER,
product_id NUMBER,
quantity NUMBER,
total_amount NUMBER(15,2),
credit_card VARCHAR2(16), -- Sensitive data
FOREIGN KEY (customer_id) REFERENCES demo.customers(customer_id)
);
-- Insert sample data into users table
INSERT INTO demo.users (user_id, username, password, email, phone_number, social_security_number, last_login)
VALUES (1, 'admin', 'admin_pass123', 'admin@example.com', '123-456-7890', '123-45-6789', SYSDATE);
INSERT INTO demo.users (user_id, username, password, email, phone_number, social_security_number, last_login)
VALUES (2, 'john_doe', 'johndoe_pass', 'john.doe@example.com', '987-654-3210', '987-65-4321', SYSDATE);
-- Insert sample data into customers table
INSERT INTO demo.customers (customer_id, first_name, last_name, email, phone_number, address, city, state, zip_code, customer_status)
VALUES (1, 'Jane', 'Doe', 'jane.doe@example.com', '555-123-4567', '123 Elm St', 'Springfield', 'IL', '62701', 'active');
INSERT INTO demo.customers (customer_id, first_name, last_name, email, phone_number, address, city, state, zip_code, customer_status)
VALUES (2, 'John', 'Smith', 'john.smith@example.com', '555-987-6543', '456 Oak St', 'Chicago', 'IL', '60601', 'inactive');
-- Insert sample data into financial_records table
INSERT INTO demo.financial_records (record_id, account_id, transaction_date, transaction_amount, transaction_type, description, customer_id)
VALUES (1, 1001, SYSDATE, 250.75, 'debit', 'Purchase at Store A', 1);
INSERT INTO demo.financial_records (record_id, account_id, transaction_date, transaction_amount, transaction_type, description, customer_id)
VALUES (2, 1002, SYSDATE, 500.00, 'credit', 'Salary Deposit', 2);
-- Insert sample data into orders table
INSERT INTO demo.orders (order_id, order_date, customer_id, product_id, quantity, total_amount, credit_card)
VALUES (1, SYSDATE, 1, 101, 2, 49.99, '1234123412341234');
INSERT INTO demo.orders (order_id, order_date, customer_id, product_id, quantity, total_amount, credit_card)
VALUES (2, SYSDATE, 2, 102, 1, 19.99, '4321432143214321');
We can find the information about unique statements executed from SOE users including the session context.
Step 4: Lets stop the collection and generate a sql firewall policy from these statement patterns.
Step 5: Deploy and enforce the sql firewall policy
Step 6: Test the scenario
a. Run a legitimate query from SOE user
b. Execute a SQL Injection Query
c. Try to execute an unauthorize access attempt to sensitive data
Step 7: Check the violation report from data safe console
Conclusion:
Oracle Database 23ai SQL Firewall represents a significant advancement in database security, combining the power of AI with Oracle Database. It provides a robust defense against SQL injection attacks and unauthorized access in the database layer.
