Oracle Database

ORACLE-WIKI.NET

Introduction

SQL Firewall provides real-time protection against common database attacks by restricting database access to only authorized SQL statements or connections for a designated user.

It mitigates risks from SQL injection attacks, anomalous access, and credential theft or abuse, preventing or detecting potential SQL injection attacks.
The SQL Firewall allows you to set a white list of SQL statements that a user can run.

This means only a set list of SQL statements can be run by the database engine. The SQL Firewall can restrict SQL statements by such things as IP address, connections, program etc. Any statements that fall outside of this context can be blocked and logged.

This powerful tool can stop data theft, unauthorized access and such things as SQL injection

When enabling a the SQL Firewall, you do so at the account level. This allows you to have different policies for different users.

SQL Firewall does not work for SAVEPOINT, COMMIT, and ROLLBACK, but will work for paswsword an describe.

There are 6 steps to enabling the SQL Firewall as listed below

1. Capture the user's SQL activities
2. Review the capture
3. Generate an allow-list
4. Set the context (Optional)
5. Enable the allow-list
6. Monitor violations

Step-By-Step

1. Capture the user's SQL activities

• SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;
• SQL>

BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE (
    username         => 'MYUSER',
    top_level_only   => TRUE,
    start_capture    => TRUE
  );
END;
/

• After a suitable period of time has elapsed allowing you to capture the SQL for the user, stop the capture with the following command
• SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE ('MYUSER');

2. Review the capture

• SQL> SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS WHERE USERNAME = 'MYUSER';

3. Generate an allow-list

• SQL> EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('MYUSER');

4. Optionally set a context

• SQL>

BEGIN
  DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT (
    username       => 'MYUSER',
    context_type   => DBMS_SQL_FIREWALL.IP_ADDRESS,
    value          => '192.168.5.5'
   );
END;
/

4. Enable the allow-list

• SQL>

BEGIN
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
    username       => 'APP',
    enforce        => DBMS_SQL_FIREWALL.ENFORCE_SQL,
    block          => TRUE
   );
END;
/

6. Monitor violations

• SQL> SELECT SQL_TEXT, FIREWALL_ACTION, IP_ADDRESS, CAUSE, OCCURRED_AT FROM DBA_SQL_FIREWALL_VIOLATIONS WHERE USERNAME = 'MYUSER';

Useful Dictionary Views to Manage and Monitor the SQL Firewall

DBA_SQL_FIREWALL_ALLOWED_IP_ADDR
DBA_SQL_FIREWALL_ALLOWED_OS_USER
DBA_SQL_FIREWALL_ALLOWED_OS_PROG
DBA_SQL_FIREWALL_CAPTURE_LOGS

Purging the Capture and/or the Violation Logs

• SQL>

BEGIN
  DBMS_SQL_FIREWALL.PURGE_LOG (
    username    => 'MYUSER',
    purge_time  => '2023-09-11 16:00:00.00 -00:00',
    log_type    => 'DBMS_SQL_FIREWALL.ALL_LOGS'
   );
 END;
/

Import and Export

There is now a new INCLUDE parameter that can be used with impdp or expdp

• INCLUDE=SQL_FIREWALL

NOTES

1. The user that enables and captures the SQL activities will need to be granted SQL_FIREWALL_ADMIN
2. If required, you can monitor DBA_SQL_FIREWALL_CAPTURE_LOGS during the capture process to watch its progression.
3. The following contexts are allowed when optionally adding a context
   • DBMS_SQL_FIREWALL.IP_ADDRESS accepts IPv4 and IPv6 addresses and subnets in the CIDR notation. It accepts the value Local (case sensitive) for local connections when the IP address is not available.
   • DBMS_SQL_FIREWALL.OS_USERNAME accepts any valid operating system user name
   • DBMS_SQL_FIREWALL.OS_PROGRAM accepts any valid operating system program name. e.g Toad
4. When enabling an Allow List, the enforce parameter can be set to one of the following values.
   • DBMS_SQL_FIREWALL.ENFORCE_CONTEXT enforces the allowed contexts that have been configured.
   • DBMS_SQL_FIREWALL.ENFORCE_SQL enforces the allowed SQL that has been configured.
   • DBMS_SQL_FIREWALL.ENFORCE_ALL enforces both allowed contexts and allowed SQL. This setting is the default.
5. The log_type parameter on DBMS_SQL_FIREWALL.PURGE_LOG can be set to one of the 3 values
   • DBMS_SQL_FIREWALL.CAPTURE_LOG
   • DBMS_SQL_FIREWALL.VIOLATION_LOG
   • DBMS_SQL_FIREWALL.ALL_LOGS (default)
6. Dont forget to purge the violation logs at suitable intervals to avoid space issues.

Published 7th Oct 2023