- Overview:
- Oracle 23ai has SQL Firewall built into the database to effectively address both SQL injection attacks and compromised account issues.
- By building Oracle SQL Firewall inside the database and streamlining its implementation, the performance overhead of Oracle SQL Firewall is negligible, making it suitable for all production workloads. Because Oracle SQL Firewall is inside the database, you don’t need to deploy or manage any external components.
- There are two methods of configuring SQL Firewall, either with Oracle Data Safe or with the DBMS_SQL_FIREWALL package. Both methods have their advantages, depending on how you want to use SQL Firewall.
In this blog, I'll demonstrate the SQL Firewall functionality using the DBMS_SQL_FIREWALL Package.
Prerequisites:
- Oracle Database 23ai Free Developer Release.
- HR database schema.
SQL Firewall Process Flow:
SQL Firewall Configuration Steps
1. Connect to pluggable database as a user who has been granted the SQL_FIREWALL_ADMIN role. SYS user will be used for this example.
2. Enable SQL Firewall.
EXEC DBMS_SQL_FIREWALL.ENABLE;
3. Create and enable the SQL firewall capture for HR database user.
BEGIN
DBMS_SQL_FIREWALL.CREATE_CAPTURE (
username => 'HR',
top_level_only => TRUE,
start_capture => TRUE
);
END;
/
top_level_only controls the level of SQL statements that are required. The default is FALSE.
- TRUE generates capture logs only for top-level SQL statements that the user directly runs.
- FALSE generates capture logs for both top-level SQL statements and commands issued from PL/SQL units.
start_capture controls when the capture will be effective. The default is TRUE.
- TRUE enables SQL Firewall to start capturing the target user's activities right away.
- FALSE creates a capture for the user, but does not start the capture right away. You can start the capture later on by running DBMS_SQL_FIREWALL.START_CAPTURE('<USERNAME>')
4. At this stage, assuming that you have set START_CAPTURE to TRUE. Connect to the database as a user HR to run the SQL statements that are expected to be executed directly later.
For example,
select FIRST_NAME,LAST_NAME,JOB_ID from employees where DEPARTMENT_ID=110;
select FIRST_NAME,LAST_NAME,MANAGER_ID from employees where JOB_ID='AD_VP';
select * from departments where DEPARTMENT_NAME='Payroll';
5. As s SYS user, review the capture logs and sessions logs to determine the adequacy of the capture.
SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS WHERE USERNAME = 'HR';
6. As a SYS user, stop the capture.
EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE ('HR')
7. As a SYS user, generate the allow-list for the user. The allow-list defines the SQL statements that the user will be allowed to perform. SQL Firewall creates the allow-list based on data collected from existing capture logs for the user.
EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('HR')
8. As a SYS user, query DBA_SQL_FIREWALL_ALLOWED_SQL to find information about activities that the HR user is allowed to perform.
SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE USERNAME = 'HR';
9. As SYS user, enable SQL Firewall protection by enabling the allow-list that was generated for the user. This enablement becomes effective immediately, even in the existing sessions of HR user.
BEGIN
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
username => 'HR',
enforce => DBMS_SQL_FIREWALL.ENFORCE_SQL,
block => TRUE
);
END;
/
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
- 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.
10. At this stage, if HR 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 error appears.
Connect as HR user and run SQL statements that are different than queries which have been captured.
select * from employees;
select FIRST_NAME,LAST_NAME,JOB_ID,salary from employees where DEPARTMENT_ID=110;
SELECT * FROM EMPLOYEES WHERE SALARY > 2000;
select * from departments where DEPARTMENT_NAME='Payroll';
11. As as SYS user, Monitor the violation log for abnormal SQL connection attempts or SQL queries that are reported if they are not in allow-list.
SELECT SQL_TEXT, FIREWALL_ACTION, IP_ADDRESS, CAUSE, OCCURRED_AT FROM DBA_SQL_FIREWALL_VIOLATIONS WHERE USERNAME = 'HR';
- Reference
.png)
