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.
- 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. - Configuring Oracle SQL Firewall
You can configure Oracle SQL Firewall in either an Oracle database using theDBMS_SQL_FIREWALL
package, or you can configure it in Oracle Data Safe. - How Oracle SQL Firewall Works with Other Oracle Features
Learn how Oracle SQL Firewall works in conjunction with other Oracle features. - 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: 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.
- 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. - Licensing Oracle SQL Firewall
Oracle SQL Firewall must be licensed for use. There are two paths to its license. - 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. - 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. - 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.
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. In addition, SQL Firewall can use session context data such as IP address to restrict database connections. Unauthorized SQL and database connection 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 names, 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.
- A user logs in to the Oracle database through a web application.
- The user runs SQL statements, creating inbound traffic to the Oracle database.
- 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 unauthorized 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.
- Included with Oracle Database Vault. Oracle Database Vault is an extra-cost option of Oracle Database. See Oracle Database Licensing Information User Manual.
- Included with Oracle Audit Vault and Database Firewall (AVDF). AVDF is a separate Oracle product and requires a license. See Oracle Database Licensing Information User Manual.
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.
- Enable SQL Firewall. As an administrator with appropriate privileges, enable SQL Firewall in the Oracle database.
- 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. - 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 theSQL_FIREWALL
trace file setting. - You can export and import SQL Firewall metadata, including existing allow-lists, by using the Oracle Data Pump
EXPDB
andIMPDB
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 theDBMS_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
- Violation log:
- 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 theDBMS_SQL_FIREWALL
package - The
EXECUTE
privilege for theDBMS_SQL_FIREWALL
PL/SQL package - The
READ
privilege for the SQL FirewallDBA_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 FirewallSQL_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.
- About Configuring Oracle SQL Firewall
Both methods of configuring Oracle SQL Firewall, either with Oracle Data Safe or with theDBMS_SQL_FIREWALL
package, have their advantages, depending on how you want to use SQL Firewall. - 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. - 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: 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 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.
- Configuring Oracle SQL Firewall Using the DBMS_SQL_FIREWALL Package
A user who has theSQL_FIREWALL_ADMIN
role can use theDBMS_SQL_FIREWALL
PL/SQL package to configure Oracle SQL Firewall in the root or a pluggable database (PDB). - Modifications to Oracle SQL Firewall Configurations
After you create an Oracle SQL Firewall configuration for a user, you can modify the configuration as necessary. - Managing Performance for Capture Logs
Depending on application workloads, Oracle SQL Firewall may generate a large volume of capture logs. - Purging Oracle SQL Firewall Logs
Periodically, you should purge the logs that Oracle SQL Firewall generates by using theDBMS_SQL_FIREWALL.PURGE_LOG
procedure. - 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. - Troubleshooting Oracle SQL Firewall by Enabling or Disabling SQL Firewall Trace Files
As a user who has been granted theALTER SESSION
orALTER SYSTEM
system privilege, you can generate trace files within the PDB in which you are using Oracle SQL Firewall.
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).
13.2.3.2 Modifications to Oracle SQL Firewall Configurations
After you create an 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 |
|
Manage captures |
|
Manage allow-lists |
|
Manage allowed contexts |
|
Manage allowed SQL |
|
Manage SQL Firewall log tables |
|
Disable SQL Firewall |
|
13.2.3.3 Managing Performance for Capture Logs
Depending on application workloads, Oracle SQL Firewall may generate a large volume of capture logs.
- Allocate at least an additional 2G to the
LARGE_POOL_SIZE
parameter setting, on top of the existingLARGE_POOL_SIZE
requirement. - Resize the
SGA_TARGET
parameter setting to include this additional requirement. Ensure that the final size is 8G or more.
Related Topics
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.
Related Topics
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 FirewallDBMS_SQL_FIREWALL
PL/SQL administrative procedures.
Related Topics
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 byLOW
.HIGHEST
shows the most detailed tracing information, plus the information returned byHIGH
andLOW
.
- 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';
Related Topics
13.3 How Oracle SQL Firewall Works with Other Oracle Features
Learn how Oracle SQL Firewall works in conjunction with other Oracle features.
- 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. - 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. - 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. - 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 theXS$NULL
user. - 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. - 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. - Oracle SQL Firewall in a Multitenant Environment
Oracle SQL Firewall is affected at both the CDB root level and the individual PDB level.
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.
- 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. - 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. - Using Oracle Data Pump with Oracle SQL Firewall
You can use theexpdp
andimpdp
commands to export and import Oracle SQL Firewall captures and allow-lists 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 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).
Parent topic: Oracle SQL Firewall and Oracle Data Pump
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.
Parent topic: Oracle SQL Firewall and Oracle Data Pump
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.
- Log in to the server where SQL Firewall is used.
- 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 theINCLUDE
orEXCLUDE
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
- To export SQL Firewall metadata, use the following syntax:
Related Topics
Parent topic: Oracle SQL Firewall and Oracle Data Pump
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);
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.
- 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. - 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.
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.
|
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 |
No |
Protect database objects |
Yes, traditional or mandatory realms can limit access to your data.
|
Yes, DML or DDL statements against schema objects
|
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 |
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. |
Parent topic: Oracle SQL Firewall and Oracle Database Vault
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.
Related Topics
Parent topic: Oracle SQL Firewall and Oracle Database Vault
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.
- Oracle SQL Firewall Data Dictionary Views
Oracle Database provides a set of data dictionary views that provide information about Oracle SQL Firewall configurations. - Query to Find a User's Allowed SQL and Accessed Objects
TheDBA_SQL_FIREWALL_ALLOWED_SQL
data dictionary view shows the SQL that a user is allowed to use. - Query to Find a User's Allowed IP Address
TheDBA_SQL_FIREWALL_ALLOWED_IP_ADDR
data dictionary view shows the IP address that a user is allowed to use. - Query to Find a User's Oracle SQL Firewall Violations
TheDBA_SQL_FIREWALL_VIOLATIONS
data dictionary view shows the Oracle SQL Firewall violations that a user has committed.
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 |
---|---|
|
Lists the status and generation date of the user's allow-lists |
|
Lists the allowed IP addresses for a user |
|
Lists the allowed operating system programs for a user |
|
Lists the allowed operating system users for a user |
|
Lists information about the allowed SQL statements for a user, including the allowed SQL ID and the allow-list version of the allowed SQL |
|
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 |
|
Lists the status SQL Firewall captures, such as whether they are enabled |
|
Lists information about the SQL Firewall session, such as the session ID, database user name, and client program |
|
Lists information about the SQL logs, such as the SQL text, the command type, the SQL signature, accessed objects, and the character set |
|
Lists the status of an SQL Firewall configuration, such as whether it is enabled and what its timestamp is |
|
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
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
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
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