1. Security Guide
  2. Controlling Access to Data
  3. Using Oracle SQL Firewall

13 Using Oracle SQL Firewall

Included in Oracle Database, Oracle SQL Firewall inspects all incoming database connections and SQL statements, and ensures that only explicitly authorized SQL is allowed to be run in the database.

Parent topic: Controlling Access to Data

13.1 Overview of Oracle SQL Firewall

SQL Firewall is part of the Oracle Database kernel. Learn about Oracle SQL Firewall and its use cases and features from this section.

Parent topic: Using Oracle SQL Firewall

13.1.1 About Oracle SQL Firewall

Oracle 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.

You can use SQL Firewall to control which SQL statements are allowed to be processed by the database. You can restrict connection paths that are associated with database connections and SQL statements. In addition, SQL Firewall can use session context data such as IP address to restrict database connections. Unauthorized SQL can be logged and blocked.

SQL Firewall helps to address the following three use cases:

  • Provide real-time protection by restricting database access to only authorized SQL statements and database connections.
  • Mitigate SQL injection attacks, anomalous access, and credential theft/abuse risks.
  • Enforce trusted database connection paths.

SQL Firewall offers the following benefits:

  • SQL Firewall inspects all incoming database connections and SQL statements, including those from PL/SQL, whether local or over the network, encrypted or clear text. It cannot be bypassed. It only allows explicitly authorized SQL. For all other SQL, it logs the offending statements and raises violations. This statement could have been a SQL injection attack or a new SQL statement that the authorized user has not run before.
  • You can decide whether you want to block unauthorized SQL or only log it. This gives you the flexibility on how to handle attacks.
  • SQL Firewall evaluates the complete SQL and the processing context. By running inside the Oracle database server, the firewall easily handles encoding of the SQL statement, synonyms, dynamically generated object names, and any SQL statements that are dynamically generated in PL/SQL units.
  • SQL Firewall relies on the allow-listing (an allow-list is a set of permitted actions) of the authorized SQL statements and associated trusted database connection paths while blocking the rest. You train the SQL Firewall by simply capturing authorized SQL statements for a database account. Subsequently, the firewall detects and prevents unauthorized SQL and potential SQL injection attacks. A typical use case with allow-listed SQL statements is for application SQL workloads issued by application service account.
  • SQL Firewall can also block connections that do not come from trusted IP addresses, operating system user rnames, or program names. This function is useful when you want to put some protection in place immediately, while you create the allow-list of SQL statements for your applications. This feature ensures that any direct access to your databases is coming exclusively from trusted endpoints. This also helps mitigate the risk of stolen or misused application service account credentials.

SQL Firewall enables you to build an allow-list policy for each database user of SQL statements that a typical database user performs, and then detects, blocks, and logs any unexpected SQL.

SQL Firewall policies work at a database account level, whether of an application service account or a direct database user, such as a reporting user or a database administrator. In other words, you might have one SQL Firewall policy for the database user HR and another for the database user pfitch. This flexibility allows you to gradually build up the protection level of the database, starting from either the database administrators or the application service accounts.

You can use SQL Firewall in both the root and a pluggable database (PDB). SQL Firewall is a simple and easy-to-use firewall solution for all Oracle Database deployments, such as on-premises, cloud, multitenant, Oracle Data Guard, or Oracle Real Application Clusters. SQL Firewall works in conjunction with other Oracle Database security features such as Transparent Data Encryption (TDE), database auditing, and Oracle Database Vault.

SQL Firewall supports (that is, it captures and enforces on) all SQL commands except transaction control commands (SAVEPOINT, COMMIT, and ROLLBACK). Additionally, SQL Firewall supports the SQL*Plus commands PASSWORD and DESCRIBE, and remote procedure calls (RPC) through database links.

The following diagram explains how SQL Firewall operates inline within the Oracle Database kernel.

Figure 13-1 SQL Firewall Process

Description of Figure 13-1 follows
Description of "Figure 13-1 SQL Firewall Process"
  1. A user logs in to the Oracle database through a web application.
  2. The user runs SQL statements, creating inbound traffic to the Oracle database.
  3. SQL Firewall inspects the incoming database connections and SQL statements, and enforces it against the permitted SQL statements and trusted connection paths in the allow-list policy for the user. SQL Firewall’s processing outcome is one of the following options:
    • Allow the SQL for its subsequent execution.
    • Allow the SQL and log it.
    • Log and optionally block unathorized SQL.

Parent topic: Overview of Oracle SQL Firewall

13.1.2 Licensing Oracle SQL Firewall

Oracle SQL Firewall must be licensed for use. There are two paths to its license.

Parent topic: Overview of Oracle SQL Firewall

13.1.3 Getting Started with Oracle SQL Firewall

To get started with Oracle SQL Firewall, you follow three steps: first, enable Oracle SQL Firewall; second, capture the user's normal SQL activities; and third, enable and enforce allow-lists.

  1. Enable SQL Firewall. As an administrator with appropriate privileges, enable SQL Firewall in the Oracle database.
  2. Capture the normal SQL activities. For every database user that you want to protect with SQL Firewall, you must enable SQL Firewall to learn the normal SQL traffic of the database user. It does this by capturing all the authorized SQL statements over trusted database connection paths. You can query SQL Firewall-specific data dictionary views to review this captured data to determine if the collected SQL statements and connection paths is adequate to constitute the allow-lists.

    After you review the captured SQL statements, you can generate a SQL Firewall policy with allow-lists that set the baseline for allowed SQL statements and allowed contexts. Allowed SQL statements constitute the approved SQL statements. At run-time, when the policy is enforced, any incoming SQL queries that have a structure syntactically similar to the SQL signature in the policy allow-list will be passed for execution if the corresponding run-time execution context also meets the set of allowed contexts. Allowed contexts represent trusted database connection paths and consist of three distinct groups—client IP addresses, operating system program names, and operating system user names. When the user connects to the database, SQL Firewall checks the current session context attributes, and ensures that access to the database comes exclusively from trusted endpoints defined in the allow-lists. You can review the allow-list and make modifications by using the DBMS_SQL_FIREWALL procedures any time.

  3. Enable and enforce the allow-lists. Enabling the generated SQL Firewall policy protects the database user. SQL Firewall enforces and checks the allow-lists when the user connects to the database and issues SQL statements. You can let SQL Firewall know if you want to enforce checks on allowed contexts, allowed SQL statements, or both. If the database connection paths and SQL statements in the incoming SQL traffic do not match the entries in the enabled and enforced allow-lists, then a SQL Firewall violation is triggered and this incident is logged in the violation log. You can let SQL Firewall know how to respond to SQL Firewall violation incident: allow the traffic to proceed to the database or block. Blocking raises an ORA-47605: SQL Firewall violation error, which prevents anomalous database access, without disrupting client connections for SQL violations following a mismatch of SQL statements. However, blocking for context violations will disrupt and terminate client connections following a mismatch of contexts.

    SQL Firewall raises and logs violations in real-time for every unmatched scenario of database connection or SQL command execution against the entries in the enabled allow-lists of the SQL Firewall policy. A security administrator can monitor the SQL Firewall violation log DBA_SQL_FIREWALL_VIOLATIONS to detect the presence of these abnormalities. You may want to audit SQL Firewall violations (especially the blocked ones); their occurrence potentially indicates abnormal database access attempts including SQL Injection and credential theft or abuse. Auditing violations places a record of the violation in the database audit trail, where it can be protected from tampering.

Key points to consider are as follows:

  • Oracle Database mandatorily audits all SQL Firewall administrative actions and writes these to the unified audit trail data dictionary view, UNIFIED_AUDIT_TRAIL. You can also create unified audit policies to monitor SQL Firewall violations. Another way to monitor and troubleshoot SQL Firewall is to use the SQL_FIREWALL trace file setting.
  • You can export and import SQL Firewall metadata, including existing allow-lists, by using the Oracle Data Pump EXPDB and IMPDB utilities.
  • Oracle recommends that you periodically monitor and purge violations logs by using the DBMS_SQL_FIREWALL.PURGE_LOG procedure as part of routine SQL Firewall management tasks. In a well trained environment, violation logs are not expected to be voluminous.
  • SQL Firewall captures SQL statements that the user issues directly or from PL/SQL units that the user invokes in sessions of target users.
  • SQL Firewall captures only SQL statements that are executed successfully. That is, if a SQL statement fails to execute due to any error, SQL Firewall does not capture the corresponding statement.
  • SQL Firewall captures SQL statements before any internal query transformation (for example, views or macro expansions, or Oracle Virtual Private Database policy enforcement) is performed.
  • SQL Firewall normalizes captured SQL statements and replaces literal values with special symbols before storing them in the log tables.
  • The session context attributes (client IP address, operating system user name, and operating system program name) are checked only once during session creation.
  • You can append to the existing allow-list anytime by using either the DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST procedure or the DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST_SINGLE_SQL procedure from the following two sources:
    • Violation log: DBA_SQL_FIREWALL_VIOLATIONS data dictionary view
    • Capture log: DBA_SQL_FIREWALL_CAPTURE_LOGS data dictionary view
  • For existing sessions that were created before the allow-list is enabled, SQL Firewall also checks the allowed contexts, but does not terminate existing sessions even if they have unmatched session contexts. In this case, SQL Firewall does not log the violation.

Parent topic: Overview of Oracle SQL Firewall

13.1.4 Privileges for Configuring and Using Oracle SQL Firewall

You must be granted the appropriate role to administer Oracle SQL Firewall or to query the views that are associated with Oracle SQL Firewall.

To administer Oracle SQL Firewall, you must be granted the SQL_FIREWALL_ADMIN role. This role provides the following privileges:

  • The ADMINISTER SQL FIREWALL system privilege, which is required to run the PL/SQL procedures in the DBMS_SQL_FIREWALL package
  • The EXECUTE privilege for the DBMS_SQL_FIREWALL PL/SQL package
  • The READ privilege for the SQL Firewall DBA_SQL_FIREWALL_* data dictionary views

To be able to query the DBA_SQL_FIREWALL_* data dictionary views (but not administer SQL Firewall), users must be granted the SQL_FIREWALL_VIEWER role.

Note:

The SQL Firewall SQL_FIREWALL_ADMIN and SQL_FIREWALL_VIEWER roles are powerful roles. Only grant these roles to trusted users.

Related Topics

Parent topic: Overview of Oracle SQL Firewall

13.1.5 Getting Hands-On Experience with Oracle SQL Firewall

You can use the Oracle LiveLabs workshop for Oracle SQL Firewall to get experience using SQL Firewall.

See the DB Security - SQL Firewall LiveLab.

The following sample demonstration scripts and video of Oracle SQL Firewall in action are also provided for your reference

Parent topic: Overview of Oracle SQL Firewall

13.2 Configuring Oracle SQL Firewall

You can configure Oracle SQL Firewall in either an Oracle database using the DBMS_SQL_FIREWALL package, or you can configure it in Oracle Data Safe.

Parent topic: Using Oracle SQL Firewall

13.2.1 About Configuring Oracle SQL Firewall

Both methods of configuring Oracle SQL Firewall, either with Oracle Data Safe or with the DBMS_SQL_FIREWALL package, have their advantages, depending on how you want to use SQL Firewall.

  • Managing multiple SQL Firewalls centrally: You can use the Data Safe user interface if you want to manage multiple SQL Firewalls centrally. You can use Data Safe REST APIs, software developer kits (SDKs), CLI, and Terraform for further automation and integration. You can also use the more extensive Oracle Cloud Infrastructure (OCI) ecosystem for integrating SQL Firewall violations with its alerts and notifications.
  • Managing SQL Firewall within an individual Oracle Database instance: To manage SQL Firewall within an individual Oracle Database instance, use the PL/SQL procedures in the DBMS_SQL_FIREWALL package.

Parent topic: Configuring Oracle SQL Firewall

13.2.2 Configuring and Managing Oracle SQL Firewall with Oracle Data Safe

With Oracle Data Safe on Oracle Cloud, you can manage multiple SQL Firewalls centrally and get a comprehensive view of SQL Firewall violations across a fleet of Oracle databases.

SQL Firewall administrators can use Data Safe to collect SQL activities of a database user with its associated database connection paths (IP address, OS program, OS user), and monitor the progress of the collection. Data Safe enables you generate and enable the SQL Firewall policy from the collected SQL traffic. Data Safe automatically collects the violation logs, and lets you monitor SQL Firewall violations from the console.

The following image shows the SQL Firewall dashboard in Data Safe.

Figure 13-2 SQL Firewall Dashboard in Data Safe

Description of Figure 13-2 follows
Description of "Figure 13-2 SQL Firewall Dashboard in Data Safe"

The violation summary in the dashboard provides a comprehensive view of SQL Firewall violations from all the targets in the compartment that have SQL Firewall enabled for the chosen period. From here, you can drill down into the violations for detailed analysis.

Related Topics

Parent topic: Configuring Oracle SQL Firewall

13.2.3 Configuring and Managing Oracle SQL Firewall with the DBMS_SQL_FIREWALL Package

After you configure Oracle SQL Firewall for a target user, you can perform maintenance tasks such as modifying the configuration, purging old logs, and troubleshooting errors.

Parent topic: Configuring Oracle SQL Firewall

13.2.3.1 Configuring Oracle SQL Firewall Using the DBMS_SQL_FIREWALL Package

A user who has the SQL_FIREWALL_ADMIN role can use the DBMS_SQL_FIREWALL PL/SQL package to configure Oracle SQL Firewall in the root or a pluggable database (PDB).

  1. Connect to the root or PDB as a user who has been granted the SQL_FIREWALL_ADMIN role.
  2. Enable SQL Firewall.
    EXEC DBMS_SQL_FIREWALL.ENABLE;
  3. For every database user to protect with SQL Firewall in the Oracle database, enable SQL Firewall to learn the normal SQL traffic of the database user by capturing all the authorized SQL statements over trusted database connection paths.
    The examples in this procedure assume the user is a PDB user named APP. For example:
    BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE (
    username         => 'APP',
    top_level_only   => TRUE,
    start_capture    => TRUE
  );
END;
/

    In this specification:

    • username is the name of the application user that SQL Firewall will monitor. You can only create one capture for each user. You cannot create SQL Firewall captures for the SYS, SYSDG, SYSBACKUP, SYSRAC, SYSKM, DVSYS, LBACSYS, or AUDSYS users.
    • top_level_only controls the level of SQL statements that are captured.
      • TRUE generates capture logs only for top-level SQL statements, that is, statements that the user directly runs.
      • FALSE generates capture logs for both top-level SQL statements and SQL commands issued from PL/SQL units. The default is FALSE.
    • start_capture controls when the capture will be effective.
      • TRUE enables SQL Firewall to start capturing the target user's activities right away. The default is TRUE.
      • FALSE creates a capture for the user, but does not start the capture right away. When you want to start the capture later on, you must run the DBMS_SQL_FIREWALL.START_CAPTURE procedure for the user. For example:
        EXEC DBMS_SQL_FIREWALL.START_CAPTURE ('APP');

    As an application service account, run the normal application SQL workload from the trusted database connection paths when the capture is started for the application service account. In the event of a change in application in the SQL workload following application patching, you may want SQL Firewall to unlearn and learn, starting over. You can delete the current capture, and create a new one. Specifically, if you want to restart the capture process, then you must first stop this capture (if it is started), then either purge the capture logs and start this capture again, or, delete this capture and create (and start) the capture again.

  4. Review the capture logs and sessions logs to determine the adequacy of the capture.
    For example:
    SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS WHERE USERNAME = 'APP';
  5. Stop the capture.
    For example:
    EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE ('APP');
  6. Generate the SQL Firewall policy with allow-lists for the user:
    A SQL Firewall policy with allow-lists sets the baseline for allowed SQL statements and allowed contexts. Allowed SQL statements constitute the approved SQL statements. Allowed contexts represent trusted database connection paths. SQL Firewall creates the allow-list based on data collected from existing capture logs for the user.

    For example:

    EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('APP');
  7. To find the permitted and allowed SQL statements that the user can run, query the DBA_SQL_FIREWALL_ALLOWED_* data dictionary views.
    For example: 
    SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE USERNAME = 'APP';

    To find the trusted database connection paths for the user, perform the following query:

    SELECT OS_PROGRAM FROM DBA_SQL_FIREWALL_ALLOWED_OS_PROG WHERE USERNAME = 'APP';
  8. Optionally, add or modify entries in the allowed contexts by running the DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT and DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT procedures.
    You can only add a context after you have generated the allow-list. A context can specify the client IP address, names of operating system users, or the operating system program that can be used for database connections. You can add as many context values as you need. For example, if the user's allowed context list does not contain the IP address 192.0.2.1 but you want to allow the user to connect from this IP after the enablement of the allow-list: 
    BEGIN
  DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT (
    username       => 'APP',
    context_type   => DBMS_SQL_FIREWALL.IP_ADDRESS,
    value          => '192.0.2.1'
   );
END;
/

    To specify all possibilities for a specific context_type, enter the % wildcard.

    The following three types of context_type settings are valid:

    • 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, such as oracle.
    • DBMS_SQL_FIREWALL.OS_PROGRAM accepts any valid operating system program name that the user uses to run SQL statements, such as sqlplus or SQL Developer.

    You can query the following data dictionary views to check the contexts:

    • DBA_SQL_FIREWALL_ALLOWED_IP_ADDR
    • DBA_SQL_FIREWALL_ALLOWED_OS_USER
    • DBA_SQL_FIREWALL_ALLOWED_OS_PROG
  9. Enable the generated SQL Firewall policy to protect the database user.
    The SQL Firewall enforces checks on the allow-lists when the user connects to the database and issues SQL statements.
    This enablement becomes effective immediately, even in the existing sessions of the target user.

    For example:

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

    In this specification:

    • username can be a specific user whose allow-list has been generated, or it can be all users whose allow-list are not currently enabled. To specify all users, use NULL as the value.
    • enforce specifies one of the following enforcement types:
      • 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.
    • block specifies the following:
      • TRUE blocks the user's database connection or the user's SQL execution whenever the user violates the allow-list definition.
      • FALSE allows unmatched user database connections or SQL commands to proceed. This setting is the default.

      SQL Firewall always generates a violation log for any unmatched user database connection or SQL statement regardless of the enforcement option.

      At this stage, if the user attempts to perform a SQL query that violates the allow-list and you have specified SQL Firewall to block this SQL, then an ORA-47605: SQL Firewall violation error appears.

  10. Monitor the violation log for abnormal SQL connection attempts or SQL queries that are reported if they are not in allow-list.
    For example: 
    SELECT SQL_TEXT, FIREWALL_ACTION, IP_ADDRESS, CAUSE, OCCURRED_AT
FROM DBA_SQL_FIREWALL_VIOLATIONS WHERE USERNAME = 'APP';

    Output similar to the following appears: 

    SQL_TEXT                                                  FIREWALL_ACTION  IP_ADDRESS   CAUSE            OCCURRED_AT
–-------------------------------------------------------- –--------------- –----------  –---------------- –----------------------------------

SELECT SALARY FROM HR.EMPLOYEES WHERE SALARY >:"SYS_B_0"  BLOCKED          192.0.2.146  Context violation 12-MAY-23 11.12.39.626053 PM +00:00
13.2.3.2 Modifications to Oracle SQL Firewall Configurations

After you create a Oracle SQL Firewall configuration for a user, you can modify the configuration as necessary.

To find information about Oracle SQL Firewall configurations, you can query the DBA_SQL_FIREWALL_* data dictionary views.

Table 13-1 lists operations that you can perform after you have configured SQL Firewall.

Table 13-1 Oracle SQL Firewall Modification Procedures

Operation Procedure

Enable SQL Firewall

  • To enable SQL Firewall in the database, use DBMS_SQL_FIREWALL.ENABLE.
Manage captures
  • To create a capture, use DBMS_SQL_FIREWALL.CREATE_CAPTURE.
  • To start a capture, use DBMS_SQL_FIREWALL.START_CAPTURE.
  • To modify a capture, delete the current one by using DBMS_SQL_FIREWALL.DROP_CAPTURE, and then create a new one by using DBMS_SQL_FIREWALL.CREATE_CAPTURE.
  • To stop the SQL Firewall capture process for the specified user, use DBMS_SQL_FIREWALL.STOP_CAPTURE.
  • To delete the SQL Firewall capture for a specified user and delete all the existing capture logs for this user:
    1. Use DBMS_SQL_FIREWALL.STOP_CAPTURE to stop the capture process.
    2. Use DBMS_SQL_FIREWALL.DROP_CAPTURE to remove the capture.
Manage allow-lists
  • To generate an allow-list for a given user, use DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST.
  • To enable an allow-list for a given user, use DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST.
  • To update an allow-list enforcement, use DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT.
  • To prevent SQL Firewall from capturing and enforcing allow-lists for database connections and SQL executions in Oracle Scheduler jobs, use DBMS_SQL_FIREWALL.EXCLUDE.
  • To append all the SQL from a capture log or violation log (or from both) to the allow-list, use the DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST procedure. You can run this procedure when the allow-list is either enabled or disabled. The change takes place immediately.
  • To append a single SQL record from a capture log or violation log to the allow-list, use the DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST_SINGLE_SQL procedure as follows:
    1. Query the DBA_SQL_FIREWALL_VIOLATIONS or the DBA_SQL_FIREWALL_CAPTURE_LOGS data dictionary view to find the target SQL record that you want to add to the allow-list.
    2. Enter the obtained USERNAME, SQL_SIGNATURE, CURRENT_USER, and TOP_LEVEL values of that record in the DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST_SINGLE_SQL procedure to add the target SQL record to the allow-list.

    You can run DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST_SINGLE_SQL when the allow-list is either enabled or disabled. The change takes place immediately.

  • To export the allow-list of a given user to JSON format into the specified CLOB, use DBMS_SQL_FIREWALL.EXPORT_ALLOW_LIST.
  • To import the allow-list for a given user into a target database, use DBMS_SQL_FIREWALL.IMPORT_ALLOW_LIST.
  • To disable an allow-list for a given user, use DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST.
  • To add or delete any context values from allowed context lists, use DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT or DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT, respectively.
  • To delete any SQL statement from allowed SQL lists, use DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL.
  • To delete the allow-list for a specified user:
    1. Disable the allow-list by using DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST.
    2. Use DBMS_SQL_FIREWALL.DROP_ALLOW_LIST.
Manage allowed contexts
  • To add a specified value to the allowed contexts of a specified user for the given context type, use DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT.
  • To modify an allowed context, delete the current one by using DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT, and then create a new one by using DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT.
  • To delete the specified value from the allowed contexts of a specified user for the given context type, use DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT.
Manage allowed SQL
  • To delete the specified entry from the allowed SQL of a specified user, use DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL. You can run this procedure when the allow-list is either enabled or disabled, and the change takes place immediately.

Manage SQL Firewall log tables
  • To move the SQL Firewall log tables to a different user-defined tablespace other than the default tablespace, SYSAUX:
    1. Disable SQL Firewall by using DBMS_SQL_FIREWALL.DISABLE.
    2. Use the MOVE clause of the ALTER TABLE statement to perform the move operation.
  • To purge capture logs or violation logs for a user or all users, use DBMS_SQL_FIREWALL.PURGE_LOG.
  • To flush all the SQL Firewall logs that reside in the memory into the log tables, use DBMS_SQL_FIREWALL.FLUSH_LOGS.

Disable SQL Firewall

  • To disable SQL Firewall in the database and stop all the existing captures and allow-lists that are enabled, use DBMS_SQL_FIREWALL.DISABLE.
13.2.3.3 Managing Performance for Capture Logs

Depending on application workloads, Oracle SQL Firewall may generate a large volume of capture logs.

To minimize the adverse impact on database performance, Oracle SQL Firewall relies internally on Fast Ingest for better write performance if sufficient memory is available. To make full use of SQL Firewall, Oracle recommends that you do the following:
  • Allocate at least an additional 2G to the LARGE_POOL_SIZE parameter setting, on top of the existing LARGE_POOL_SIZE requirement.
  • Resize the SGA_TARGET parameter setting to include this additional requirement. Ensure that the final size is 8G or more.
13.2.3.4 Purging Oracle SQL Firewall Logs

Periodically, you should purge the logs that Oracle SQL Firewall generates by using the DBMS_SQL_FIREWALL.PURGE_LOG procedure.

SQL Firewall generates and stores the violation logs in a log table. In an ideal SQL Firewall trained environment, the violation log is not expected to be large. Oracle recommends that you periodically purge these logs. After you verify that the generated allow-list is valid, you should purge unnecessary logs to reclaim the disk space that the logs are using.
  1. Log in to the root or the pluggable database (PDB) where SQL Firewall is configured as a user who has been granted the SQL_FIREWALL_ADMIN role.
  2. Optionally, as a user who has the SELECT ANY DICTIONARY system privilege, query the following data dictionary views to check the logs that you plan to purge:
    • DBA_SQL_FIREWALL_CAPTURE_LOGS
    • DBA_SQL_FIREWALL_VIOLATIONS
  3. Connect to the PDB a user who has been granted the SQL_FIREWALL_ADMIN role.
  4. Run the DBMS_SQL_FIREWALL.PURGE_LOG procedure.
    For example:
    BEGIN
  DBMS_SQL_FIREWALL.PURGE_LOG (
    username    => 'APP',
    purge_time  => '2023-02-01 00:00:00.00 -08:00',
    log_type    => 'DBMS_SQL_FIREWALL.ALL_LOGS'
   );
 END;
/

    In this specification:

    • username is the target user for which this SQL Firewall configuration was created. If you omit this value, then Oracle Database purges all logs that match the purge_time and log_type settings.
    • purge_time is the timestamp (in TIMESTAMP format) that you can specify to purge only logs that were generated before a certain time. If you omit this value, then Oracle Database purges all logs, regardless of the time when they were generated.
    • log_type is the type of the logs to be purged. If you do not specify a value, then the default is DBMS_SQL_FIREWALL.ALL_LOGS. Specify one of the following constants:
      • DBMS_SQL_FIREWALL.CAPTURE_LOG
      • DBMS_SQL_FIREWALL.VIOLATION_LOG
      • DBMS_SQL_FIREWALL.ALL_LOGS (default)
13.2.3.5 Auditing Oracle SQL Firewall Violations by Using Unified Audit Policies

Oracle recommends that you audit SQL Firewall violations as violations indicate the occurrence of potential abnormal database access patterns.

Auditing SQL Firewall violations with unified auditing records the violation in the database audit trail, UNIFIED_AUDIT_TRAIL data dictionary view. It is important that you turn on violation auditing after SQL Firewall is fully trained and the allow-lists of the user is complete, to avoid false positives and reduce unnecessary audit volume.

You can create unified audit policies that are specific to SQL Firewall by specifying the SQL_FIREWALL component when you create the unified audit policy. When you query the UNIFIED_AUDIT_TRAIL, you can query the FW_ACTION_NAME and FW_RETURN_CODE columns.

Note:

Oracle Database mandatorily audits all invocations of the SQL Firewall DBMS_SQL_FIREWALL PL/SQL administrative procedures.
13.2.3.6 Troubleshooting Oracle SQL Firewall by Enabling or Disabling SQL Firewall Trace Files

As a user who has been granted the ALTER SESSION or ALTER SYSTEM system privilege, you can generate trace files within the PDB in which you are using Oracle SQL Firewall.

You can set SQL Firewall trace events in both the CDB and in individual PDBs.

  • To enable tracing for SQL Firewall, use one of the following statements: 
    ALTER SESSION SET EVENTS 'TRACE[SQL_FIREWALL] DISK=trace_level';
ALTER SYSTEM SET EVENTS 'TRACE[SQL_FIREWALL] DISK=trace_level';

    In this specification, replace trace_level with one of the following values:

    • LOW shows the minimum tracing information.
    • HIGH shows more detailed tracing information, plus the information returned by LOW.
    • HIGHEST shows the most detailed tracing information, plus the information returned by HIGH and LOW.
  • To disable tracking for SQL Firewall, use one of the following statements:
    ALTER SESSION SET EVENTS 'TRACE[SQL_FIREWALL] OFF';
ALTER SYSTEM SET EVENTS 'TRACE[SQL_FIREWALL] OFF';

13.3 How Oracle SQL Firewall Works with Other Oracle Features

Learn how Oracle SQL Firewall works in conjunction with other Oracle features.

Parent topic: Using Oracle SQL Firewall

13.3.1 Oracle SQL Firewall and Oracle Data Pump

You can use Oracle Data Pump to export and import Oracle SQL Firewall captures and allow-list metadata.

Parent topic: How Oracle SQL Firewall Works with Other Oracle Features

13.3.1.1 About Oracle Data Pump Export and Import Operations on Oracle SQL Firewall Metadata

Oracle SQL Firewall integrates with Oracle Data Pump to support the export and import of the SQL Firewall metadata, including the metadata for captures and allow-lists.

This is typically required in scenarios where the training can be done once on a non-production database, and then SQL Firewall can be enabled on multiple production databases using the allow-list that was generated during the non-production training stage.

Oracle Database maintains the status of captures and allow-lists during the export and import operations, unless you are merging an allow-list from the source database into an existing allow-list in the target database. For example, if a capture is enabled in the source database at the export time, it will be enabled in the target database after the import operation completes. This is the similar if you are importing an allow-list when there is no allow-list for the same user in the target database before the import operation.

If you are merging an allow-list from the source database into an existing allow-list in the target database, the settings (such as status, top_level_only, enforce, and block) of the allow-list in the target database remain the same as before the import operation. Only the allowed SQL and contexts are merged.

For Oracle Data Pump, Oracle supports the export or import of all the existing SQL Firewall metadata (that is, captures and allow-lists) as a whole. Oracle does not support the export or import of a specific capture or a specific allow-list through Oracle Data Pump.

If you only want to export or import the allow-list for one user, from one specific database to another, then use the DBMS_SQL_FIREWALL.EXPORT_ALLOW_LIST or DBMS_SQL_FIREWALL.IMPORT_ALLOW_LIST procedure. (These two procedures do not rely on Oracle Data Pump and can be used independently.) Oracle does not support the export and import of SQL Firewall logs (that is, capture and violation logs).

13.3.1.2 Cases Where Oracle Data Pump Skips the Import for an Oracle SQL Firewall Capture or Allow-List

During an import operation, Oracle Data Pump will skip a particular Oracle SQL Firewall capture or allow-list and continue to import other captures or allow-lists for certain cases.

These cases are as follows:

  • If the target users do not exist in the target database, then the captures and allow-lists for those non-existing users are not imported.

  • If an allow list refers to one or more current users that do not exist in the target database, then this allow-list is not imported.

  • For an allow-list to be imported, if an allow-list for the same user already exists in the target database and its top_level_only setting is different than the allow-list to be imported, then the allow-list is not imported.

  • For an allow-list to be imported, if a capture for the same user already exists in the target database and its top_level_only setting is different than the allow-list to be imported, then the allow-list is not imported.

  • If an allow-list to be imported is enabled, and in the target database, there is an enabled capture for the same user but there is no disabled allow-list for the same user, then the allow-list is not imported to avoid having an enabled capture and an enabled allow-list for the same user at the same time.

  • If a capture to be imported already exists for the same user in the target database, then the capture is not imported.

  • If a capture to be imported is enabled, and there is an enabled allow-list for the same user in the target database, then the capture is not imported to avoid having an enabled capture and an enabled allow-list for the same user at the same time.

  • For a capture to be imported, if an allow-list for the same user already exists in the target database and its top_level_only setting is different than the capture to be imported, then the capture is not imported.

13.3.1.3 Using Oracle Data Pump with Oracle SQL Firewall

You can use the expdp and impdp commands to export and import Oracle SQL Firewall captures and allow-lists metadata.

  1. Log in to the server where SQL Firewall is used.
  2. At the command line, perform the Oracle Data Pump export or import operation.
    • To export SQL Firewall metadata, use the following syntax: 
      expdp user_name@pdb_name FULL=Y DIRECTORY=dumpfile_dir INCLUDE=SQL_FIREWALL dumpfile=dumpfile_name.dmp LOGFILE=filename.log

      In this specification:

      • FULL=Y, which enables full export mode. SQL Firewall metadata will be exported only with the full export mode.
      • INCLUDE=SQL_FIREWALL can be used in the INCLUDE or EXCLUDE filter. This tag is optional. It enables you to export and import just the SQL Firewall metadata from one database to another.

      For example:

      expdp "hr@hr_pdb" FULL=Y DIRECTORY=sql_fw_dumpfiles INCLUDE=SQL_FIREWALL DUMPFILE=sql_fw_app.dmp LOGFILE=sql_fw_app.log
Enter password: password
    • To import SQL Firewall metadata: 
      impdp user_name@pdb_name FULL=Y DIRECTORY=dumpfile_dir INCLUDE=SQL_FIREWALL dumpfile=dumpfile_name.dmp LOGFILE=filename.log

      For example:

      impdp "hr@hr_pdb" FULL=Y DIRECTORY=dumpfile_dir INCLUDE=SQL_FIREWALL dumpfile=sql_fw_app.dmp LOGFILE=sql_fw_app.log
Enter password: password

13.3.2 Oracle SQL Firewall and Oracle Scheduler Jobs

In most scenarios, you may want to exclude Oracle Scheduler jobs from Oracle SQL Firewall enforcement because these are not typically run by users.

By default the Oracle Scheduler jobs are excluded. You can enable or disable the enforcement of SQL Firewall during Oracle Scheduler operations by setting the FEATURE parameter to the DBMS_SQL_FIREWALL.SCHEDULER_JOB constant, using the following procedures:

  • DBMS_SQL_FIREWALL.INCLUDE permits SQL Firewall to capture any SQL or enforce any allow-lists during Oracle Scheduler operations.
  • DBMS_SQL_FIREWALL_EXCLUDE prevents SQL Firewall from capturing any SQL or enforcing any allow-lists during Oracle Scheduler operations.

For example:

EXEC DBMS_SQL_FIREWALL.EXCLUDE (DBMS_SQL_FIREWALL.SCHEDULER_JOB);

Related Topics

Parent topic: How Oracle SQL Firewall Works with Other Oracle Features

13.3.3 Oracle SQL Firewall and Oracle Database Vault

Oracle Database Vault requires special authorization before you can use Oracle SQL Firewall in a Database Vault environment.

Parent topic: How Oracle SQL Firewall Works with Other Oracle Features

13.3.3.1 Using SQL Firewall in an Oracle Database Vault Environment

Depending on the type of protection that you want to configure, you can use either or both Oracle Database Vault and SQL Firewall.

Database Vault enables you to use realms and command rules to block access to sensitive objects, the execution of critical commands, and SQL connections from untrusted factors such as the time of the day, IP address, host name, program name, or any number of identifiable attributes that are associated with the user. In a Database Vault environment, you can extend this protection by using SQL Firewall to capture an allow-list of SQL commands with an associated trusted database connection paths for a database account. Then you can log (and optionally block) the unseen SQL traffic. SQL Firewall enforcement can distinguish approved SQL statements and connections from the unauthorized SQL traffic, which adds to the protection layer that realms and command rules provide to prevent access to sensitive objects unless they have been explicitly authorized.

The following table shows a comparison of how you can enforce protections using Database Vault realms and command rules, and SQL Firewall.

Table 13-2 Comparison of Oracle Database Vault and SQL Firewall Protections

Use Case Realms Command Rules SQL Firewall

Protect database schemas

Yes, traditional or mandatory realms can limit access to your data.

  • Entire schema or schemas
  • Object types
  • Specific objects by name

Yes, DML or DDL statements against schema objects

No

Protect database roles

Yes, traditional or mandatory realms can protect your roles.

Yes, create a command rule with GRANT or REVOKE statements for specific roles.

No

Protect database objects

Yes, traditional or mandatory realms can limit access to your data.

  • Entire shema or schemas
  • Object types
  • Specific objects by name

Yes, DML or DDL statements against schema objects

  • Entire schema or schemas
  • Object types
  • Specific objects by name

No

Protect individual SQL statements

No

Yes, control statements against schema or individual schema objects.

Yes, block all but explicitly allowed SQL statements.

Allow-list and protect application SQL traffic

No

No

Yes, block all but explicitly allowed SQL statements.

Protect against risks of compromised accounts

Yes, establish trusted path conditions based on any factors that can be checked programmatically.

Yes, protect CONNECT command usage.

Yes, block sessions from untrusted client IP, program and OS user name

Protect database users against SQL Injection risks

No

No

Yes, create an allow-list SQL Firewall policy for each database user and enforce it.
13.3.3.2 Authorization for Using SQL Firewall in an Oracle Database Vault Environment

In an Oracle Database Vault environment, users who want to configure SQL Firewall must have Oracle Database Vault-specific authorization.

When Database Vault is enabled, the management of SQL Firewall (that is, the invocation of the DBMS_SQL_FIREWALL package) requires SQL Firewall administrators to have Database Vault-specific authorization in addition to the ADMINISTER SQL FIREWALL system privilege. This requirement is to ensure that only trusted users will be able to manage SQL Firewall in a Database Vault environment.

You can authorize SQL Firewall administrators to allow or not allow captures on users who have the DV_OWNER, DV_ADMIN, or DV_ACCTMGR roles in a Database Vault environment. When Database Vault operations control is enabled, common users will be blocked from using SQL Firewall (that is, the DBMS_SQL_FIREWALL procedures for managing captures and allow-lists) on local users unless the common users are included in the exception list.

13.3.4 Oracle SQL Firewall and Oracle Real Application Security

You can use Oracle SQL Firewall with Oracle Real Application Security (Oracle RAS) to capture SQL statements that come from an Oracle RAS application for the XS$NULL user.

You can generate and enforce an allow-list for the XS$NULL user after completing a SQL Firewall capture operation. However, SQL Firewall does not perform capture and enforce operations for Oracle RAS end-user identities.

Parent topic: How Oracle SQL Firewall Works with Other Oracle Features

13.3.5 Oracle SQL Firewall and Oracle Database Centrally Managed Users and Enterprise Users

Oracle SQL Firewall will capture a global user’s activities if the SQL Firewall capture is enabled.

However, SQL Firewall does not distinguish enterprise user identities (for example, centrally managed users with Active Directory (CMU-AD) users, Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users, Oracle Internet Directory (OID) users, or Microsoft Azure Active Directory users).

Parent topic: How Oracle SQL Firewall Works with Other Oracle Features

13.3.6 Oracle SQL Firewall and Oracle Virtual Private Database

When Oracle Virtual Private Database policies are run, Oracle SQL Firewall captures SQL commands right after their executions.

However, SQL Firewall does not consider any modification or transformation that is made by the database kernel (for example, views, synonyms, SQL macro expansion, Virtual Private Database enforcement, and so on). You should train SQL Firewall to capture all the expected incoming SQL statements to formulate the allow-list.

Parent topic: How Oracle SQL Firewall Works with Other Oracle Features

13.3.7 Oracle SQL Firewall in a Multitenant Environment

Oracle SQL Firewall is affected at both the CDB root level and the individual PDB level.

You can run the SQL Firewall processes and set SQL Firewall trace events in both the CDB and individual PDBs.

In the CDB root:

  • You can enable SQL Firewall in the CDB root container, and then create SQL Firewall policies, enable or disable SQL Firewall, start or stop captures, and enable or disable allow-lists. These settings apply to the CDB root only.
  • In an Oracle Database Vault operations control environment, there are no restrictions in using SQL Firewall.

In individual PDBs:

  • You can enable SQL Firewall in an individual PDB, and then create SQL Firewall policies, enable or disable SQL Firewall, start or stop captures, and enable or disable allow-lists. These settings apply to the current PDB only.
  • In a Database Vault operations control environment, common users cannot start or stop captures on local users, nor can they enable or disable allow-lists on local users.

Parent topic: How Oracle SQL Firewall Works with Other Oracle Features

13.4 Oracle SQL Firewall Data Dictionary Views and Example Queries

Oracle provides a set of data dictionary views that enable you to find different kinds of information about the Oracle SQL Firewall protections that you have configured.

Parent topic: Using Oracle SQL Firewall

13.4.1 Oracle SQL Firewall Data Dictionary Views

Oracle Database provides a set of data dictionary views that provide information about Oracle SQL Firewall configurations.

Table 13-3 lists these data dictionary views.

Table 13-3 Data Dictionary Views That Display Oracle SQL Firewall Information

View Description

DBA_SQL_FIREWALL_ALLOW_LISTS

Lists the status and generation date of the user's allow-lists

DBA_SQL_FIREWALL_ALLOWED_IP_ADDR

Lists the allowed IP addresses for a user

DBA_SQL_FIREWALL_ALLOWED_OS_PROG

Lists the allowed operating system programs for a user

DBA_SQL_FIREWALL_ALLOWED_OS_USER

Lists the allowed operating system users for a user

DBA_SQL_FIREWALL_ALLOWED_SQL

Lists information about the allowed SQL statements for a user, including the allowed SQL ID and the allow-list version of the allowed SQL

DBA_SQL_FIREWALL_CAPTURE_LOGS

Lists log information for a user's SQL Firewall configuration, such as the database user name, SQL text, accessed objects, and the SQL Firewall session ID

DBA_SQL_FIREWALL_CAPTURES

Lists the status SQL Firewall captures, such as whether they are enabled

DBA_SQL_FIREWALL_SESSION_LOGS

Lists information about the SQL Firewall session, such as the session ID, database user name, and client program

DBA_SQL_FIREWALL_SQL_LOGS

Lists information about the SQL logs, such as the SQL text, the command type, the SQL signature, accessed objects, and the character set

DBA_SQL_FIREWALL_STATUS

Lists the status of an SQL Firewall configuration, such as whether it is enabled and what its timestamp is

DBA_SQL_FIREWALL_VIOLATIONS

Provides a detailed report on SQL Firewall violations, including information such as the objects that were accessed, the user the SQL was run on, and whether the action was blocked or allowed

Related Topics

Parent topic: Oracle SQL Firewall Data Dictionary Views and Example Queries

13.4.2 Query to Find a User's Allowed SQL and Accessed Objects

The DBA_SQL_FIREWALL_ALLOWED_SQL data dictionary view shows the SQL that a user is allowed to use.

For example:

SELECT SQL_TEXT, ACCESSED_OBJECTS FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE USERNAME = 'HR';
 
SQL_TEXT                            ACCESSED_OBJECTS  
----------------------------------- ------------------
SELECT COUNT(*) FROM HR.EMPLOYEES   "HR"."EMPLOYEES'

Related Topics

Parent topic: Oracle SQL Firewall Data Dictionary Views and Example Queries

13.4.3 Query to Find a User's Allowed IP Address

The DBA_SQL_FIREWALL_ALLOWED_IP_ADDR data dictionary view shows the IP address that a user is allowed to use.

For example:

SELECT IP_ADDRESS FROM DBA_SQL_FIREWALL_ALLOWED_IP_ADDR WHERE USERNAME = 'HR';
 
IP_ADDRESS
------------
192.0.2.1

Related Topics

Parent topic: Oracle SQL Firewall Data Dictionary Views and Example Queries

13.4.4 Query to Find a User's Oracle SQL Firewall Violations

The DBA_SQL_FIREWALL_VIOLATIONS data dictionary view shows the Oracle SQL Firewall violations that a user has committed.

For example:

SELECT SQL_TEXT, OCCURRED_AT, FIREWALL_ACTION FROM DBA_SQL_FIREWALL_VIOLATIONS WHERE USERNAME = 'HR';
 
SQL_TEXT                           OCCURRED_AT                         FIREWALL_ACTION
---------------------------------- ----------------------------------  –--------------
SELECT COUNT(*) FROM HR.EMPLOYEES  12-OCT-23 10.30.02.238383 AM +00:00 BLOCKED

Related Topics

Parent topic: Oracle SQL Firewall Data Dictionary Views and Example Queries