Syntax

DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT (
   username       IN  VARCHAR2,
   context_type   IN  NUMBER,
   value          IN  VARCHAR2);

Parameters

Usage Notes

• You can find the user's current context type settings by querying the following data dictionary views:

  • DBA_SQL_FIREWALL_ALLOWED_IP_ADDR
  • DBA_SQL_FIREWALL_ALLOWED_OS_PROG
  • DBA_SQL_FIREWALL_ALLOWED_OS_USER
• Before you can add any contexts for the user, the user's allow-list must be created (using the DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST procedure).
• This procedure can be run when the allow-list is enabled or disabled, and it takes effects immediately.

Example

BEGIN
  DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT (
    username       => 'PFITCH',
    context_type   => DBMS_SQL_FIREWALL.OS_PROGRAM,
    value          => 'SQL Developer'
   );
END;
/

Syntax

DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST (
   username       IN  VARCHAR2,
   source         IN  NUMBER);

Parameters

Usage Notes

• DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST processes the specified source logs and identifies contents to be appended to the allow-list. Then it populates the SQL Firewall metadata tables for the allowed SQL and allowed contexts, which will be used during the allow-list enforcement.
• You can run this procedure when the allow-list is either enabled or disabled.
• The change takes effect immediately.
• A new allow-list version number will be associated with all the allowed SQL entries added by the same DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST execution. This new version number will be 1 plus the current maximum allow-list version of the specified user.

Example

BEGIN
  DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST (
    username       => 'PFITCH',
    source         => DBMS_SQL_FIREWALL.CAPTURE_LOG
   );
END;
/

This procedure is useful for when you want to individually append SQL commands from the violations log or the capture log to an existing allow-list.

Syntax

DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST_SINGLE_SQL (
   username       IN  VARCHAR2,
   sql_signature  IN  VARCHAR2,
   current_user   IN  VARCHAR2,
   top_level      IN  VARCHAR2,
   source         IN  NUMBER DEFAULT);

Parameters

Usage Notes

• DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST_SINGLE_SQL processes the specified source log and identifies the target SQL record to be appended to the allow-list. Then it populates the SQL Firewall metadata tables for the allowed SQL, which will be used during the allow-list enforcement.
• You can run this procedure when the allow-list is either enabled or disabled.
• The change takes effect immediately.
• A new allow-list version number will be associated with the newly added allowed SQL entry.

Example

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.

   Obtain the values for the USERNAME, SQL_SIGNATURE, CURRENT_USER, and TOP_LEVEL columns for the target SQL record.

2. Enter these values in the DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST_SINGLE_SQL SQL procedure to add the target SQL record to the allow-list.

   For example:

   BEGIN
     DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST_SINGLE_SQL (
       username       => 'PFITCH',
       sql_signature  => '7D33A84D0A1B56E382B9A92D01BCD19933969CB16E2AB4934A2258563F5ADB44',
       current_user   => 'PSMITH',
       top_level      => 'N',
       source         => DBMS_SQL_FIREWALL.CAPTURE_LOG
      );
   END;
   /

Syntax

DBMS_SQL_FIREWALL.CREATE_CAPTURE (
   username       IN VARCHAR2,
   top_level_only IN BOOLEAN,
   start_capture  IN BOOLEAN);

Parameters

Usage Notes

To find the status of existing SQL Firewall captures, including users who have already been configured for SQL Firewall captures, query the DBA_SQL_FIREWALL_CAPTURES data dictionary view.

Example

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

Syntax

DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT (
   username       IN  VARCHAR2,
   context_type   IN  NUMBER,
   value          IN  VARCHAR2);

Parameters

Usage Notes

• You can find the user's current context type settings by querying the following data dictionary views:
  • DBA_SQL_FIREWALL_ALLOWED_IP_ADDR
  • DBA_SQL_FIREWALL_ALLOWED_OS_PROG
  • DBA_SQL_FIREWALL_ALLOWED_OS_USER
• This procedure can be run when the allow-list is enabled or disabled, and it takes effects immediately.

Example

BEGIN
  DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT, (
    username       => 'PFITCH',
    context_type   => DBMS_SQL_FIREWALL.OS_PROGRAM,
    value          => 'SQL Developer'
   );
END;
/

Syntax

DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL (
   username       IN  VARCHAR2,
   allowed_sql_id IN  NUMBER);

Parameters

Usage Notes

• You can run this procedure when the allow-list is either enabled or disabled.
• The change takes effect immediately.

Example

BEGIN
  DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL (
    username         => 'PFITCH',
    allowed_sql_id   => 1
   );
END;
/

Syntax

DBMS_SQL_FIREWALL.DISABLE;

Parameters

None

Usage Notes

You can find the current status of SQL Firewall by querying the DBA_SQL_FIREWALL_STATUS data dictionary view.

Example

EXEC DBMS_SQL_FIREWALL.DISABLE;

Syntax

DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST (
   username       IN  VARCHAR2);

Parameters

Usage Notes

To find the status of users' allow-lists, query the DBA_SQL_FIREWALL_ALLOW_LISTS data dictionary view.

Example

EXEC DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST ('PFITCH');

Syntax

DBMS_SQL_FIREWALL.DROP_ALLOW_LIST (
   username       IN  VARCHAR2);

Parameters

Usage Notes

• To find the status of users' allow-lists, query the DBA_SQL_FIREWALL_ALLOW_LISTS data dictionary view.

• You cannot drop an allow-list that is currently enabled. To disable an allow-list, run the DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST procedure.

Example

EXEC DBMS_SQL_FIREWALL.DROP_ALLOW_LIST ('PFITCH');

Syntax

DBMS_SQL_FIREWALL.DROP_CAPTURE (
   username       IN VARCHAR2);

Parameters

Usage Notes

• To find the status of existing SQL Firewall captures, query the DBA_SQL_FIREWALL_CAPTURES data dictionary view.

• You cannot drop a capture that is currently running. To stop the capture, run the DBMS_SQL_FIREWALL.STOP_CAPTURE procedure.

• Dropping a capture for a user does not affect the user's allow-list, which can continue to run even if the capture has been dropped. Captures and allow-lists are separate entities.

Example

EXEC DBMS_SQL_FIREWALL.DROP_CAPTURE ('C##HR_ADMIN');

Syntax

DBMS_SQL_FIREWALL.ENABLE;

Parameters

None

Usage Notes

You can find the current status of SQL Firewall by querying the DBA_SQL_FIREWALL_STATUS data dictionary view.

Example

EXEC DBMS_SQL_FIREWALL.ENABLE;

Syntax

DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
   username       IN  VARCHAR2,
   enforce        IN  NUMBER,
   block          IN  BOOLEAN;

Parameters

Usage Notes

• To find the status of users' allow-lists, query the DBA_SQL_FIREWALL_ALLOW_LISTS data dictionary view.

• SQL Firewall always generates a violation log for any unmatched database connection or SQL statement regardless of the block option setting.

Example

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

Oracle Scheduler jobs are often used in databases for various maintenance purposes. Accidentally interrupting critical jobs can cause undesirable consequences. You can configure SQL Firewall to not capture any SQL statements nor enforce any allow-lists that are run during an Oracle Scheduler job session. This procedure applies to all users that have been configured for SQL Firewall captures and allow-lists. By default, Oracle Scheduler jobs are excluded from SQL Firewall operations.

Syntax

DBMS_SQL_FIREWALL.EXCLUDE (
   FEATURE        IN NUMBER);

Parameters

Usage Notes

• To find the status of whether SQL Firewall is enforced during Oracle Scheduler operations, query the EXCLUDE_JOBS column of the DBA_SQL_FIREWALL_STATUS data dictionary view. If the output is Y, then Oracle Scheduler jobs are excluded from SQL Firewall operations.

• To enable Oracle Firewall to run during Oracle Scheduler operations, run the DBMS_SQL_FIREWALL.INCLUDE procedure.

Example

EXEC DBMS_SQL_FIREWALL.EXCLUDE (DBMS_SQL_FIREWALL.SCHEDULER_JOB);

Syntax

DBMS_SQL_FIREWALL.EXPORT_ALLOW_LIST (
   username       IN      VARCHAR2,
   allow_list     IN/OUT  CLOB;

Parameters

Usage Notes

• Before you run this procedure, you must create the CLOB and then pass it to the API (for example, by DBMS_LOB.CREATETEMPORARY for the PL/SQL client, or by OracleConnection.createClob() for JDBC Java client).

• The export operation includes the allow-list's settings (status, enforce, block, top_level_only, generated_on, and status_updated_on timestamp), allowed SQL, and allowed contexts. In addition, the export operation includes all the referenced SQL logs (by the allowed SQL).

• DBMS_SQL_FIREWALL.EXPORT_ALLOW_LIST does not export capture logs or violation logs.

• To find the status of users' allow-lists, query the DBA_SQL_FIREWALL_ALLOW_LISTS data dictionary view.

• If you want to export all the SQL Firewall metadata, which includes captures and allow-lists for all users, then instead of using DBMS_SQL_FIREWALL.EXPORT_ALLOW_LIST, use the include=SQL_FIREWALL clause in the Oracle Data Pump expdp command. See Oracle Database Security Guide.

Example

BEGIN
  DBMS_SQL_FIREWALL.EXPORT_ALLOW_LIST (
    username       => 'PFITCH',
    allow_list     => ALLOW_LIST_CLOB;
   );
END;
/

Syntax

DBMS_SQL_FIREWALL.FLUSH_LOGS;

Parameters

None

Usage Notes

• Usually you do not need to invoke this procedure explicitly, because logs in the memory are flushed to the log tables frequently in the background. But in case if you want to see the capture logs or violation logs immediately after the action during when SQL Firewall is running, you can run this procedure before looking at the logs.
• The DBMS_SQL_FIREWALL.FLUSH_LOGS procedure is equivalent to the DBMS_MEMOPTIMIZE_ADMIN.WRITES_FLUSH procedure. (See WRITES_FLUSH Procedure.)

Example

EXEC DBMS_SQL_FIREWALL.FLUSH_LOGS;

Syntax

DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST (
   username       IN  VARCHAR2;

Parameters

Usage Notes

• To find information about existing generated allow-lists, query the DBA_SQL_FIREWALL_ALLOW_LISTS data dictionary view.
• Before you run this procedure, the following components must be in place:
  • The specified user must exist.
  • A capture (using DBMS_SQL_FIREWALL.CREATE_CAPTURE) has been created for this user. This capture must be disabled (using DBMS_SQL_FIREWALL.STOP_CAPTURE) before you can generate an allow-list for the user.
  • No allow-list exists yet for the user.

Example

EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('PFITCH');

Syntax

DBMS_SQL_FIREWALL.IMPORT_ALLOW_LIST (
   username       IN      VARCHAR2,
   allow_list     IN      CLOB;

Parameters

Usage Notes

• If this user does not have an allow-list in the target database, a new allow-list will be created for this user using the allow-list from the JSON payload. The new allow-list will have the same settings (status, top_level_only, enforce, block, generated_on, status_updated_on), same allowed contexts and same allowed SQL as the one in the JSON. If the specified user already has an allow-list in the target database, then all the settings (status, top_level_only, enforce, block, and various timestamps) of the existing allow-list will remain untouched, but only the allowed SQL and allowed contexts from the JSON will be merged into the ones for the existing allow-list.

• In addition, the import operation includes all the referenced SQL logs (by the allowed SQL).

• To find the status of users' allow-lists, query the DBA_SQL_FIREWALL_ALLOW_LISTS data dictionary view.

• If you want to import all the SQL Firewall metadata, which includes captures and allow-lists, then instead of using DBMS_SQL_FIREWALL.IMPORT_ALLOW_LIST, use the include=SQL_FIREWALL clause in the Oracle Data Pump impdp command. See Oracle Database Security Guide.

Example

BEGIN
  DBMS_SQL_FIREWALL.IMPORT_ALLOW_LIST (
    username       => 'PFITCH',
    allow_list     => ALLOW_LIST_CLOB;
   );
END;
/

Syntax

DBMS_SQL_FIREWALL.INCLUDE (
   FEATURE        IN NUMBER);

Parameters

Usage Notes

• To find the status of whether SQL Firewall is enforced during Oracle Scheduler operations, query the EXCLUDE_JOBS column of the DBA_SQL_FIREWALL_STATUS data dictionary view. If the output is N, then SQL Firewall can perform during Oracle Scheduler operations.

• To prevent SQL Firewall from running during Oracle Scheduler operations, run the DBMS_SQL_FIREWALL.EXCLUDE procedure.

Example

EXEC DBMS_SQL_FIREWALL.INCLUDE (DBMS_SQL_FIREWALL.SCHEDULER_JOB);

Syntax

BEGIN
  DBMS_SQL_FIREWALL.PURGE_LOG (
   username       IN  VARCHAR2,
   purge_time     IN  TIMESTAMP WITH TIME ZONE,
   log_type       IN  NUMBER);

Parameters

Usage Notes

To find information about SQL Firewall logs, query the DBA_SQL_FIREWALL_VIOLATIONS data dictionary view.

Example

BEGIN
  DBMS_SQL_FIREWALL.PURGE_LOG (
    username    => 'PSMITH',
    purge_time  => TO_TIMESTAMP_TZ('23-JAN-22 18.44.42 -07:00', 'DD/MM/YY HH24:MI:SS TZH:TZM'),
    log_type    => DBMS_SQL_FIREWALL.VIOLATION_LOG
   );
END;
/

Syntax

DBMS_SQL_FIREWALL.START_CAPTURE (
   username       IN  VARCHAR2);

Parameters

Usage Notes

• A user can only have one SQL Firewall capture. To find if the user already has been configured for a capture, query the DBA_SQL_FIREWALL_CAPTURES data dictionary view.

• After you start the capture process, all SQL the user enters is captured into the SQL Firewall capture log table. You can periodically check the this SQL by querying the DBA_SQL_FIREWALL_CAPTURE_LOGS data dictionary view.

Example

EXEC DBMS_SQL_FIREWALL.START_CAPTURE ('PFITCH');

Syntax

DBMS_SQL_FIREWALL.STOP_CAPTURE (
   username       IN  VARCHAR2);

Parameters

Usage Notes

• The capture process must be currently running before you can run this procedure. You can check its status by querying the DBA_SQL_FIREWALL_CAPTURES data dictionary view.

• After you stop the capture process, you can generate an allow-list for the user by running the DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST procedure.

Example

EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE ('PFITCH');

Syntax

BEGIN
  DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT (
   username       IN  VARCHAR2,
   enforce        IN  NUMBER,
   block          IN  BOOLEAN);

Parameters

Usage Notes

To find the status of users' allow-lists, query the DBA_SQL_FIREWALL_ALLOW_LISTS data dictionary view.

Example

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