Oracle DBMS_SQL_FIREWALL
Version 23c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Creates a PL/SQL admin packages for database firewall.

According to the Beta docs prevents SQL Injection attacks: I am far from confident that the author understands what a SQL Injection attack actually is.
AUTHID CURRENT_USER
Constants
Name Data Type Value
 Log Types
CAPTURE_LOG NUMBER 1
VIOLATION_LOG NUMBER 2
ALL_LOGS NUMBER 3
 Feature Supported in the Exclude/Include API
SCHEDULER_JOB NUMBER 1
 Add/Delete Context Types
OS_PROGRAM NUMBER 1
OS_USERNAME NUMBER 2
IP_ADDRESS NUMBER 3
 Enforce options for Enable Allow List and Update Allow List Enforcement
ENFORCE_CONTEXT NUMBER 1
ENFORCE_SQL NUMBER 2
ENFORCE_ALL NUMBER 3
Dependencies
KZFW$ADM_LIBT    
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORA-47605 SQL Firewall violation
ORA-47626 SQL Firewall capture for user has been started
First Available 23.2
Pragmas None
Security Model Owned by SYS with EXECUTE granted to the SQL_FIREWALL_ADMIN role.
Source {ORACLE_HOME}/rdbms/admin/catfwp.sql
{ORACLE_HOME}/rdbms/admin/prvtfwp.plb
Subprograms
ADD_ALLOWED_CONTEXT ENABLE_ALLOW_LIST
APPEND_ALLOW_LIST EXCLUDE
CREATE_CAPTURE FLUSH_LOGS
DELETE ALLOWED_CONTEXT GENERATE_ALLOW_LIST
DELETE_ALLOWED_SQL INCLUDE
DISABLE PURGE_LOG
DISABLE_ALLOW_LIST START_CAPTURE
DROP_ALLOW_LIST STOP_CAPTURE
DROP_CAPTURE UPDATE_ALLOW_LIST_ENFORCEMENT
ENABLE Demo
 
ADD_ALLOWED_CONTEXT (new 23c)
Add a context to the list of allowed contexts for a user who is configured for SQL Firewall dbms_sql_firewall.add_allowed_context(
username     IN VARCHAR2,
context_type IN NUMBER,
value        IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(add_allowed_context, AUTO_WITH_COMMIT);
TBD
 
APPEND_ALLOW_LIST (new 23c)
Appends additional SQL statements to an existing allow list by using the capture and/or violation logs as the source dbms_sql_firewall.append_allow_list(
username IN VARCHAR2,
source   IN NUMBER);
PRAGMA SUPPLEMENTAL_LOG_DATA(append_allow_list, UNSUPPORTED_WITH_COMMIT);
TBD
 
CREATE_CAPTURE (new 23c)
Creates a user SQL Firewall capture for the specified level dbms_sql_firewall.create_capture(
username       IN VARCHAR2,
top_level_only IN BOOLEAN DEFAULT FALSE,
start_capture  IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_capture, AUTO_WITH_COMMIT);
See demo at page bottom
 
DELETE ALLOWED_CONTEXT (new 23c)
Deletes a user assigned context value dbms_sql_firewall.delete_allowed_context(
username     IN VARCHAR2,
context_type IN NUMBER,
value        IN VARCHAR2 DEFAULT NULL);
PRAGMA SUPPLEMENTAL_LOG_DATA(delete_allowed_context, AUTO_WITH_COMMIT);
TBD
 
DELETE ALLOWED_SQL (new 23c)
Deletes SQL from the allowed list dbms_sql_firewall.delete_allowed_context(
username       IN VARCHAR2,
allowed_sql_id IN NUMBER);
PRAGMA SUPPLEMENTAL_LOG_DATA(delete_allowed_sql, AUTO_WITH_COMMIT);
TBD
 
DISABLE (new 23c)
Disable SQL Firewall dbms_sql_firewall.disable;
PRAGMA SUPPLEMENTAL_LOG_DATA(disable, AUTO_WITH_COMMIT);
exec dbms_sql_firewall.disable;
 
DISABLE_ALLOW_LIST (new 23c)
Disable SQL Firewall allow-list enforcement for a user dbms_sql_firewall.disable_allow_list(username IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(disable_allow_list, AUTO_WITH_COMMIT);
See demo at page bottom
 
DROP_ALLOW_LIST (new 23c)
Drop a SQL Firewall capture dbms_sql_firewall.drop_allow_list(username IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(drop_allow_list, AUTO_WITH_COMMIT);
See demo at page bottom
 
DROP_CAPTURE (new 23c)
Drop a SQL Firewall capture dbms_sql_firewall.drop_capture(username IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(drop_capture, AUTO_WITH_COMMIT);
exec dbms_sql_firewall.drop_capture('C##UWCLASS');
 
ENABLE (new 23c)
Enable SQL Firewall dbms_sql_firewall.enable;
PRAGMA SUPPLEMENTAL_LOG_DATA(enable, AUTO_WITH_COMMIT);
See demo at page bottom
 
ENABLE_ALLOW_LIST (new 23c)
Enable SQL Firewall allow-list enforcement for a user dbms_sql_firewall.enable_allow_list(
username IN VARCHAR2,
enforce  IN NUMBER  DEFAULT sys.dbms_sql_firewall.enforce_all,
block    IN BOOLEAN DEFAULT FALSE);
PRAGMA SUPPLEMENTAL_LOG_DATA(enable_allow_list, AUTO_WITH_COMMIT);
TBD
 
EXCLUDE (new 23c)
Prevent SQL Firewall from capturing/enforcing allow-lists for database connections and SQL executions during Scheduler operations dbms_sql_firewall.exclude(feature IN NUMBER);
PRAGMA SUPPLEMENTAL_LOG_DATA(exclude, AUTO_WITH_COMMIT);
TBD
 
FLUSH_LOGS (new 23c)
Flushes all SQL Firewall logs that reside in memory to disk dbms_sql_firewall.flush_logs;
PRAGMA SUPPLEMENTAL_LOG_DATA(flush_log, NONE);
exec dbms_sql_firewall.flush_logs;
 
GENERATE_ALLOW_LIST (new 23c)
Generates a SQL Firewall allow-list for a user from existing user capture logs dbms_sql_firewall.generate_allow_list(username IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(generate_allow_list, UNSUPPORTED_WITH_COMMIT);
exec dbms_sql_firewall.generate_allow_list('C##UWCLASS');
 
INCLUDE (new 23c)
enables SQL Firewall to capture and enforce allow-lists for database connections and SQL executions during Scheduler operations. dbms_sql_firewall.include(feature IN NUMBER);
PRAGMA SUPPLEMENTAL_LOG_DATA(include, AUTO_WITH_COMMIT);
TBD
 
PURGE_LOG (new 23c)
Purge SQL Firewall logs dbms_sql_firewall.purge_log(
username   IN VARCHAR2                 DEFAULT NULL,
purge_time IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
log_type   IN NUMBER                   DEFAULT dbms_sql_firewall.all_logs);
PRAGMA SUPPLEMENTAL_LOG_DATA(purge_log, AUTO_WITH_COMMIT);
TBD
 
START_CAPTURE (new 23c)
Start SQL Firewall capture for a user dbms_sql_firewall.start_capture(username IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(start_capture, AUTO_WITH_COMMIT);
See demo at page bottom
 
STOP_CAPTURE (new 23c)
Stop SQL Firewall capture for a user dbms_sql_firewall.stop_capture(username IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(stop_capture, AUTO_WITH_COMMIT);
See demo at page bottom
 
UPDATE_ALLOW_LIST_ENFORCEMENT (new 23c)
Update SQL Firewall allow-list enforcement options for a user dbms_sql_firewall.update_allow_list_enforcement(
username IN VARCHAR2,
enforce  IN NUMBER  DEFAULT NULL,
block    IN BOOLEAN DEFAULT NULL);
PRAGMA SUPPLEMENTAL_LOG_DATA(update_allow_list_enforcement, AUTO_WITH_COMMIT);
TBD
 
Demo
This demo takes SQL Firewall from initial setup, to capture, to allow list creation through capturing a violation
conn / as sysdba

CREATE USER c##applic
IDENTIFIED BY applic
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
PROFILE DEFAULT;

CREATE TABLE c##applic.credit_cards (
credit_card_number VARCHAR2(19),
expiration_date DATE,
security_code VARCHAR2(4),
available_credit NUMBER);

INSERT INTO c##applic.credit_cards
VALUES ('4123-5678-9012-3456', SYSDATE+30, '359', 5000);

INSERT INTO c##applic.credit_cards
VALUES ('5890-1234-5678-9123', SYSDATE+45, '892', 10000);

INSERT INTO c##applic.credit_cards
VALUES ('6574-3521-8805-7492', SYSDATE+120, '461', 25000);
COMMIT;

GRANT SELECT ON c##applic.credit_cards TO c##exploiter;
CREATE USER c##exploiter
IDENTIFIED BY exploiter
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
PROFILE DEFAULT;

Grant succeeded.
 
conn / as sysdba

exec dbms_sql_firewall.enable;

PL/SQL procedure successfully completed.

-- required if previously created / nightmare material
exec dbms_sql_firewall.disable_allow_list('C##EXPLOITER');
exec dbms_sql_firewall.drop_allow_list('C##EXPLOITER');

PL/SQL procedure successfully completed.

exec dbms_sql_firewall.create_capture('C##EXPLOITER', FALSE, TRUE);

PL/SQL procedure successfully completed.

exec dbms_sql_firewall.start_capture('c##EXPLOITER');
*
ERROR at line 1:
ORA-47626: SQL Firewall capture for user C##EXPLOITER has been started.
 
-- connect as the user that will attack the system and let the firewall capture SQL
conn c##exploiter/exploiter

desc c##applic.credit_cards

SELECT COUNT(*) FROM c##applic.credit_cards;

SELECT SUBSTR(credit_card_number, 16, 4) AS FINAL_FOUR FROM c##applic.credit_cards;

ALTER SESSION SET query_rewrite_integrity = TRUSTED;

ALTER SESSION SET query_rewrite_integrity = ENFORCED;
 
-- after the SQL is captured stop the capture process and review capture metadata
conn / as sysdba

exec dbms_sql_firewall.stop_capture('C##EXPLOITER');

PL/SQL procedure successfully completed.

SELECT sql_text FROM dba_sql_firewall_capture_logs;

SQL_TEXT
------------------------------------------------------------------------------------
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED
SELECT SUBSTR (CREDIT_CARD_NUMBER,:"SYS_B_0",:"SYS_B_1") AS FINAL_FOUR FROM C##APPLIC.CREDIT_CARDS
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFORCED
DESCRIBE C##APPLIC.CREDIT_CARDS
SELECT COUNT (*) FROM C##APPLIC.CREDIT_CARDS
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL

SELECT username, command_type, accessed_objects, current_user, top_level, ip_address
FROM dba_sql_firewall_capture_logs;

USERNAME     COMMAND_TYPE  ACCESSED_OBJECTS           CURRENT_USER TOP_LEVEL IP_ADDRESS
------------ ------------- -------------------------- ------------ --------- ----------
C##EXPLOITER ALTER SESSION                            C##EXPLOITER Y         Local
C##EXPLOITER SELECT        "C##APPLIC"."CREDIT_CARDS" C##EXPLOITER Y         Local
C##EXPLOITER ALTER SESSION                            C##EXPLOITER Y         Local
C##EXPLOITER DESCRIBE      "C##APPLIC"."CREDIT_CARDS" C##EXPLOITER Y         Local
C##EXPLOITER SELECT        "C##APPLIC"."CREDIT_CARDS" C##EXPLOITER Y         Local
C##EXPLOITER SELECT        "SYS"."DUAL"               C##EXPLOITER Y         Local
 
conn / as sysdba

exec dbms_sql_firewall.generate_allow_list('C##EXPLOITER');

PL/SQL procedure successfully completed.

SELECT sql_text FROM dba_sql_firewall_allowed_sql;

SQL_TEXT
---------------------------------------------------------------------------------------
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED
SELECT SUBSTR (CREDIT_CARD_NUMBER,:"SYS_B_0",:"SYS_B_1") AS FINAL_FOUR FROM C##APPLIC.CREDIT_CARDS
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFORCED
DESCRIBE C##APPLIC.CREDIT_CARDS
SELECT COUNT (*) FROM C##APPLIC.CREDIT_CARDS
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL

SELECT * FROM dba_sql_firewall_allowed_os_prog;

USERNAME OS_PROGRAM
-------------------- ---------------------------------
C##EXPLOITER sqlplus@localhost.localdomain (TNS V1-V3)
 
SELECT view_name FROM dba_views WHERE view_name LIKE 'DBA_SQL_FIREWALL%' ORDER BY 1;

VIEW_NAME
----------------------------------
DBA_SQL_FIREWALL_ALLOWED_IP_ADDR
DBA_SQL_FIREWALL_ALLOWED_OS_PROG
DBA_SQL_FIREWALL_ALLOWED_OS_USER
DBA_SQL_FIREWALL_ALLOWED_SQL
DBA_SQL_FIREWALL_ALLOW_LISTS
DBA_SQL_FIREWALL_CAPTURES
DBA_SQL_FIREWALL_CAPTURE_LOGS
DBA_SQL_FIREWALL_SESSION_LOGS
DBA_SQL_FIREWALL_SQL_LOGS
DBA_SQL_FIREWALL_STATUS
DBA_SQL_FIREWALL_VIOLATIONS
 
conn / as sysdba

BEGIN
  dbms_advanced_rewrite.declare_rewrite_equivalence(
  'DSF',
  'SELECT SUBSTR(credit_card_number, 16, 4) AS FINAL_FOUR FROM c##applic.credit_cards',
  'SELECT credit_card_number || ''|'' || TO_CHAR(expiration_date) || ''|'' ||
   security_code || ''|'' || TO_CHAR(AVAILABLE_CREDIT) FROM c##applic.credit_cards',
  FALSE,
  'TEXT_MATCH');
END;
/

PL/SQL procedure successfully completed

GRANT alter session TO c##exploiter;

exec dbms_sql_firewall.enable_allow_list('C##EXPLOITER', dbms_sql_firewall.enforce_sql, TRUE);
 
conn c##exploiter/exploiter

desc c##applic.credit_cards

SELECT SUBSTR(credit_card_number, 16, 4) AS FINAL_FOUR FROM c##applic.credit_cards;

FINAL_FOUR
----------------
3456
9123
7492

SELECT expiration_date-1 FROM c##applic.credit_cards;
SELECT expiration_date-1 FROM c##applic.credit_cards
*
ERROR at line 1:
ORA-47605: SQL Firewall violation

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_ASSERT
SQLFW$DATAPUMP
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx