Home » Articles » 23c » Here

SQL Firewall in Oracle Database 23c

The SQL Firewall allows us to detect, block and log unexpected SQL and session contexts in an Oracle 23c database, giving us greater control to prevent unusual activity.

• Setup
• Configure SQL Firewall
• Maintenance
• Views
• Considerations

Related articles.

• Service-Level Access Control Lists (ACLs) - Database Service Firewall in Oracle Database 12c Release 2 (12.2)

Setup

We create some test users to help demonstrate the SQL firewall functionality.

• FWADMIN : A user to manage the SQL firewall. This user needs the SQL_FIREWALL_ADMIN role.
• SCHEMA_OWNER: The owner of all the database objects required by the application.
• APP_USER_1 : An application user that needs to access the objects owned by SCHEMA_OWNER.

Here are the user creation commands.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists fwadmin cascade;

create user fwadmin identified by fwadmin;
grant create session to fwadmin;
grant sql_firewall_admin to fwadmin;


drop user if exists schema_owner cascade;

create user schema_owner identified by schema_owner quota unlimited on users;
grant db_developer_role to schema_owner;


drop user if exists app_user_1 cascade;

create user app_user_1 identified by app_user_1;
grant create session to app_user_1;
grant select any table on schema schema_owner to app_user_1;

We create two tables for the application users to interact with.

drop table if exists schema_owner.t1 purge;
create table schema_owner.t1 (id number);
insert into schema_owner.t1 values (1);

drop table if exists schema_owner.t2 purge;
create table schema_owner.t2 (id number);
insert into schema_owner.t2 values (2);

commit;

We could grant the SQL_FIREWALL_VIEWER role to any user that needs to view information about the SQL Firewall, not administer it.

Configure SQL Firewall

We login to our firewall admin user and enable the SQL Firewall using the DBMS_SQL_FIREWALL package.

conn fwadmin/fwadmin@//localhost:1521/freepdb1

exec dbms_sql_firewall.enable;

We check the status of the SQL Firewall using the following query.

select status
from   dba_sql_firewall_status;

STATUS
--------
ENABLED

SQL>

We initiate a capture process to track all interactions with a user. In this case we want to check what is happening to the APP_USER_1 user.

begin
  dbms_sql_firewall.create_capture (
    username       => 'app_user_1',
    top_level_only => true,
    start_capture  => true);
end;
/

In separate sessions we perform some operations when connected to the APP_USER_1 user.

-- From SQL*Plus
conn app_user_1/app_user_1@//localhost:1521/freepdb1

select * from schema_owner.t1;
select * from schema_owner.t2;

-- From SQLcl
conn app_user_1/app_user_1@//localhost:1521/freepdb1

select * from schema_owner.t1;
select * from schema_owner.t2;

Back in our firewall admin user we check the capture logs, and can see some operations have been captured. In addition to the SQL text we can see a number of session attributes have been logged.

conn fwadmin/fwadmin@//localhost:1521/freepdb1

set linesize 150 pagesize 40
column command_type format a12
column current_user format a15
column client_program format a45
column os_user format a10
column ip_address format a10
column sql_text format a30

select command_type,
       current_user,
       client_program,
       os_user,
       ip_address,
       sql_text
from   dba_sql_firewall_capture_logs
where  username = 'APP_USER_1';

COMMAND_TYPE CURRENT_USER    CLIENT_PROGRAM                                OS_USER    IP_ADDRESS SQL_TEXT
------------ --------------- --------------------------------------------- ---------- ---------- ------------------------------
SELECT       APP_USER_1      sqlplus@localhost.localdomain (TNS V1-V3)     oracle     127.0.0.1  SELECT DECODE (USER,:"SYS_B_0"
                                                                                                 ,XS_SYS_CONTEXT (:"SYS_B_1",:"
                                                                                                 SYS_B_2"),USER) FROM SYS.DUAL

SELECT       APP_USER_1      sqlplus@localhost.localdomain (TNS V1-V3)     oracle     127.0.0.1  SELECT * FROM SCHEMA_OWNER.T2
SELECT       APP_USER_1      SQLcl                                         tim_hall   10.0.2.2   SELECT * FROM SCHEMA_OWNER.T2
SELECT       APP_USER_1      sqlplus@localhost.localdomain (TNS V1-V3)     oracle     127.0.0.1  SELECT * FROM SCHEMA_OWNER.T1
SELECT       APP_USER_1      SQLcl                                         tim_hall   10.0.2.2   SELECT * FROM SCHEMA_OWNER.T1

SQL>

We stop the capture.

exec dbms_sql_firewall.stop_capture('app_user_1');

We generate an allow-list for the user based on the capture logs.

exec dbms_sql_firewall.generate_allow_list ('app_user_1');

We have four views that allow us to check the allow-list contents.

column username format a20

select *
from   dba_sql_firewall_allowed_ip_addr
where  username = 'APP_USER_1';

USERNAME             IP_ADDRESS
-------------------- ----------
APP_USER_1           10.0.2.2
APP_USER_1           127.0.0.1

SQL>


column os_program format a50

select *
from   dba_sql_firewall_allowed_os_prog
where  username = 'APP_USER_1';

USERNAME             OS_PROGRAM
-------------------- --------------------------------------------------
APP_USER_1           SQLcl
APP_USER_1           sqlplus@localhost.localdomain (TNS V1-V3)

SQL>


column os_user format a10

select *
from   dba_sql_firewall_allowed_os_user
where  username = 'APP_USER_1';

USERNAME             OS_USER
-------------------- ----------
APP_USER_1           oracle
APP_USER_1           tim_hall

SQL>


column sql_text format A50

select current_user,
       sql_text
from   dba_sql_firewall_allowed_sql
where  username = 'APP_USER_1';

CURRENT_USER    SQL_TEXT
--------------- --------------------------------------------------
APP_USER_1      SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"S
                YS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL

APP_USER_1      SELECT * FROM SCHEMA_OWNER.T2
APP_USER_1      SELECT * FROM SCHEMA_OWNER.T1

SQL>

We can delete allowed SQL using the DELETE_ALLOWED_SQL procedure. We can add or remove items to the context lists using the ADD_ALLOWED_CONTEXT and DELETE_ALLOWED_CONTEXT procedures. We target specific contexts using the IP_ADDRESS, OS_USERNAME and OS_PROGRAM constants. Here is an example of adding and removing an IP address.

-- Add IP address.
begin
  dbms_sql_firewall.add_allowed_context (
    username     => 'app_user_1',
    context_type => dbms_sql_firewall.ip_address,
    value        => '192.168.56.1');
end;
/


-- Check the allow-list.
column ip_address format a12

select *
from   dba_sql_firewall_allowed_ip_addr
where  username = 'APP_USER_1';

USERNAME             IP_ADDRESS
-------------------- ------------
APP_USER_1           10.0.2.2
APP_USER_1           127.0.0.1
APP_USER_1           192.168.56.1

SQL>


-- Delete IP address.
begin
  dbms_sql_firewall.delete_allowed_context (
    username     => 'app_user_1',
    context_type => dbms_sql_firewall.ip_address,
    value        => '192.168.56.1');
end;
/


-- Check the allow-list.
select *
from   dba_sql_firewall_allowed_ip_addr
where  username = 'APP_USER_1';

USERNAME             IP_ADDRESS
-------------------- ------------
APP_USER_1           10.0.2.2
APP_USER_1           127.0.0.1

SQL>

We enforce the allow list using the ENABLE_ALLOW_LIST procedure. The type of enforcement is determined by the constant used.

• ENFORCE_CONTEXT : Only enforces the context (IP Address, OS User and OS Program) allow-list.
• ENFORCE_SQL : Only enforces the SQL allow-list.
• ENFORCE_ALL : Enforces the context and SQL allow-lists.

In this example we enable the both the context and SQL allow-lists. Setting the BLOCK parameter to false means it will log, but not block violations.

begin
  dbms_sql_firewall.enable_allow_list (
    username => 'app_user_1',
    enforce  => dbms_sql_firewall.enforce_all,
    block    => true);
end;
/

We can see the status of the allow-list using the DBA_SQL_FIREWALL_ALLOW_LISTS view.

select username,
       status,
       top_level_only,
       enforce,
       block
from   dba_sql_firewall_allow_lists
where username='APP_USER_1';

USERNAME             STATUS   TOP_LEVEL_ONLY ENFORCE         BLOCK
-------------------- -------- -------------- --------------- --------------
APP_USER_1           ENABLED  Y              ENFORCE_ALL     Y

SQL>

We can check violations of the SQL Firewall using the DBA_SQL_FIREWALL_VIOLATIONS view.

select sql_text,
       firewall_action,
       ip_address,
       cause,
       occurred_at
from   dba_sql_firewall_violations
where  username = 'APP_USER_1';

Using a different session we issue an unknow and a known SQL statement to the database. Notice the unknown statement fails with a "ORA-47605: SQL Firewall violation" error.

conn app_user_1/app_user_1@//localhost:1521/freepdb1

-- Unknown.
select count(*) from schema_owner.t1;

Error starting at line : 1 in command -
select count(*) from schema_owner.t1
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-47605: SQL Firewall violation

SQL>


-- Known.
select * from schema_owner.t1;

  COUNT(*)
----------
         1

SQL>

We go back to our firewall admin user and check for violations.

conn fwadmin/fwadmin@//localhost:1521/freepdb1

column occurred_at format a35

select sql_text,
       firewall_action,
       ip_address,
       cause,
       occurred_at
from   dba_sql_firewall_violations
where  username = 'APP_USER_1'
and    sql_text like '%COUNT%';

SQL_TEXT                                           FIREWAL IP_ADDRESS   CAUSE                OCCURRED_AT
-------------------------------------------------- ------- ------------ -------------------- -----------------------------------
SELECT COUNT (*) FROM SCHEMA_OWNER.T1              Blocked 10.0.2.2     SQL violation        03-JUL-23 09.35.55.318805 PM +00:00

SQL>

If we notice violations that should have been let through, we can add them to the allow list manually, or by pulling them from the logs.

exec dbms_sql_firewall.append_allow_list('app_user_1', dbms_sql_firewall.violation_log);

If we try to run the query that caused the violation again, it works fine this time. We don't even have to start a new session to see this change.

conn app_user_1/app_user_1@//localhost:1521/freepdb1

select count(*) from schema_owner.t1;

  COUNT(*)
----------
         1

SQL>

Maintenance

The DBMS_SQL_FIREWALL package contains a number routines for the maintenance of the SQL Firewall. Some examples are presented below.

We flush any log entries from memory into the log tables.

exec dbms_sql_firewall.flush_logs;

We clear down the capture and violation logs using the PURGE_LOG procedure.

-- Capture log entire contents.
exec dbms_sql_firewall.purge_log('app_user_1', null, dbms_sql_firewall.capture_log);

-- Violation logs older than 30 days.
exec dbms_sql_firewall.purge_log('app_user_1', systimestamp-30, dbms_sql_firewall.violation_log);

-- Capture and violation logs older than 30 days.
exec dbms_sql_firewall.purge_log('app_user_1', systimestamp-30, dbms_sql_firewall.all_logs);

We can alter the allow-list enforcement using the UPDATE_ALLOW_LIST_ENFORCEMENT procedure.

-- Disable context protection.
exec dbms_sql_firewall.update_allow_list_enforcement('app_user_1', dbms_sql_firewall.enforce_context, false);

-- Disable SQL protection.
exec dbms_sql_firewall.update_allow_list_enforcement('app_user_1', dbms_sql_firewall.enforce_sql, false);

-- Disable context and SQL protection.
exec dbms_sql_firewall.update_allow_list_enforcement('app_user_1', dbms_sql_firewall.enforce_all, false);

We exclude scheduled jobs from capture or enforcement.

exec dbms_sql_firewall.exclude (dbms_sql_firewall.scheduler_job);


select status, exclude_jobs
from   dba_sql_firewall_status;

STATUS   EXCLUDE_JOBS
-------- ------------
ENABLED  Y

SQL>

We disable an allow-list entirely.

exec dbms_sql_firewall.disable_allow_list ('app_user_1');

We disable entire SQL Firewall, including running captures and enabled allow-lists.

exec dbms_sql_firewall.disable;

The log tables are common tables in the SYSAUX tablespace of the root container. They can be moved to another tablespace as follows.

-- Connect to the root container.
conn / as sysdba

-- Create a new tablespace.
create tablespace fw_ts datafile '/opt/oracle/oradata/FREE/fw_ts01.dbf' size 1g autoextend on next 1g;

-- Disable the SQL firewall.
exec dbms_sql_firewall.disable;

-- Move the tables.
alter table sys.sql_log$ move tablespace fw_ts;
alter table sys.event_log$ move tablespace fw_ts;
alter table sys.violation_log$ move tablespace fw_ts;

-- Enable the firewall.
exec dbms_sql_firewall.enable;

-- Check the tablespace for the log tables.
column table_name format a20
column tablespace_name format a20

select table_name, tablespace_name
from   dba_tables
where  table_name in ('SQL_LOG$', 'EVENT_LOG$', 'VIOLATION_LOG$')
order by 1;

TABLE_NAME           TABLESPACE_NAME
-------------------- --------------------
EVENT_LOG$           FW_TS
SQL_LOG$             FW_TS
VIOLATION_LOG$       FW_TS

SQL>

They will still appear to be in the SYSAUX tablespace when viewed from the PDB.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

column table_name format a20
column tablespace_name format a20

select table_name, tablespace_name
from   dba_tables
where  table_name in ('SQL_LOG$', 'EVENT_LOG$', 'VIOLATION_LOG$')
order by 1;

TABLE_NAME           TABLESPACE_NAME
-------------------- --------------------
EVENT_LOG$           SYSAUX
SQL_LOG$             SYSAUX
VIOLATION_LOG$       SYSAUX

SQL>

Views

The following views are available to support the SQL Firewall.

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

11 rows selected.

SQL>

These are based on the underlying "$" tables in the SYSAUX tablespace.

select table_name
from   dba_tables
where  table_name like 'FW%$'
or     table_name in ('SQL_LOG$', 'EVENT_LOG$', 'VIOLATION_LOG$')
order by 1;

TABLE_NAME
--------------------------------------------------------------------------------
EVENT_LOG$
FW_ALLOW_LIST$
FW_ALLOW_LIST_CONTEXT$
FW_ALLOW_LIST_SQL$
FW_CAPTURE$
FW_STATUS$
SQL_LOG$
VIOLATION_LOG$

8 rows selected.

SQL>

The log tables are common tables in the SYSAUX tablespace of the root container. They can be moved to another tablespace as follows.

-- Connect to the root container.
conn / as sysdba

-- Create a new tablespace.
create tablespace fw_ts datafile '/opt/oracle/oradata/FREE/fw_ts01.dbf' size 1g autoextend on next 1g;

-- Disable the SQL firewall.
exec dbms_sql_firewall.disable;

-- Move the tables.
alter table sys.sql_log$ move tablespace fw_ts;
alter table sys.event_log$ move tablespace fw_ts;
alter table sys.violation_log$ move tablespace fw_ts;

-- Enable the firewall.
exec dbms_sql_firewall.enable;

-- Check the tablespace for the log tables.
column table_name format a20
column tablespace_name format a20

select table_name, tablespace_name
from   dba_tables
where  table_name in ('SQL_LOG$', 'EVENT_LOG$', 'VIOLATION_LOG$')
order by 1;

TABLE_NAME           TABLESPACE_NAME
-------------------- --------------------
EVENT_LOG$           FW_TS
SQL_LOG$             FW_TS
VIOLATION_LOG$       FW_TS

SQL>

They will still appear to be in the SYSAUX tablespace when viewed from the PDB.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

column table_name format a20
column tablespace_name format a20

select table_name, tablespace_name
from   dba_tables
where  table_name in ('SQL_LOG$', 'EVENT_LOG$', 'VIOLATION_LOG$')
order by 1;

TABLE_NAME           TABLESPACE_NAME
-------------------- --------------------
EVENT_LOG$           SYSAUX
SQL_LOG$             SYSAUX
VIOLATION_LOG$       SYSAUX

SQL>

Considerations

Here are some things to consider when using the SQL Firewall.

• If we are basing our SQL Firewall configuration off a capture, we must run the capture for a sufficiently long time to pick up all the possible variations. Imagine a situation where we run a capture for one week, then enable the SQL firewall based on that capture. We then try to run some quarterly processing and it gets blocked, because that wasn't run during the capture period.
• It's important we validate the captured context and SQL information. If there were a hack during the capture process, we would be adding this to the allow-list. We need the validation step to exclude problem context and SQL information.
• Applications evolve over time, so a new release may include many new SQL statements that will be blocked until the SQL allow-list is amended. The session context allow-lists (IP Address, OS User and OS Program) may also change over time.
• We may decide that blocking unknown SQL is too risky, and only block unknown session contexts.
• The presence of the SQL Firewall is not an excuse to ease up on "least privilege" management, and traditional access control using firewalls. It is an addition to those security layers that should already be in place.
• The SQL firewall could be used to monitor activity, and not block it. Setting the BLOCK parameter in the ENABLE_ALLOW_LIST procedure to false means it will log, but not block violations.
• Firewall violations can be audited using a unified audit policy, as described here.
• The SQL Firewall can be enabled in the root container or a PDB.
• It's worth reading Pete Finnigan's series of posts on the SQL Firewall, but especially part 3 here, as it shows some possible limitations in the initial release.

For more information see:

• Using SQL Firewall
• DBMS_SQL_FIREWALL
• Service-Level Access Control Lists (ACLs) - Database Service Firewall in Oracle Database 12c Release 2 (12.2)
• SQL Firewall Oracle 23c - Part 3

Hope this helps. Regards Tim...

Back to the Top.

Created: 2023-07-03  Updated: 2023-07-07

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer