Contents
We live in the data era, where every organization invests significant resources in securing its IT infrastructure. Protecting data is crucial because modern businesses rely heavily on data analysis. Ensuring the security of sensitive data has become the primary responsibility of security engineers and database administrators. Due to the critical nature of this data, hackers often attempt to gain control through :
- SQL Injection Attacks
- Compromised Accounts
Even though OCI introduced Web Application Firewalls (WAFs) to mitigate SQL injection attacks, attackers can still bypass these pattern-matching techniques. Allowlist-based and network-based SQL firewalls offer stronger protection but are ineffective for local and encrypted traffic. Moreover, they require more run-time context to thoroughly analyze SQL queries.
In this article, I will demonstrate how to enable the firewall and block suspicious access.
For testing the SQL firewall functionality created below-mentioned users.
- Main Schema: Scott
- User: Chanaka
A separate SQL Developer session for the database will be needed for the user Chanaka.
Initial Setup
Here are the scripts used to create the Scott and Chanaka schemas.
Create Scott Schema:
SQL> CREATE USER SCOTT identified by ******** default tablespace DATA temporary tablespace TEMP;
User created.
ALTER USER SCOTT quota unlimited on DATA;
GRANT CONNECT, RESOURCE TO SCOTT;
create table dept(
deDowntime: Upgrades and patching can require system downtime, which may affect business operations.ptno number(2,0) not null,
dname varchar2(14),
loc varchar2(13));
create table emp( empno number(4,0) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0) not null);
create table bonus( ename varchar2(10),
job varchar2(9),
sal number,
comm number);
create table salgrade(
grade number,
losal number,
hisal number);
create table dummy (
dummy number);
insert into dummy values (0);
insert into DEPT (DEPTNO, DNAME, LOC)
select 10, 'ACCOUNTING', 'NEW YORK' from dummy union all
select 20, 'RESEARCH', 'DALLAS' from dummy union all
select 30, 'SALES', 'CHICAGO' from dummy union all
select 40, 'OPERATIONS', 'BOSTON' from dummy;
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
select 7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10 from dummy union all
select 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30 from dummy union all
select 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10 from dummy union all
select 7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20 from dummy union all
select 7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20 from dummy union all
select 7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20 from dummy union all
select 7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20 from dummy union all
select 7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30 from dummy union all
select 7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30 from dummy union all
select 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30 from dummy union all
select 7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30 from dummy union all
select 7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20 from dummy union all
select 7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30 from dummy union all
select 7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10 from dummy;
insert into salgrade
select 1, 700, 1200 from dummy union all
select 2, 1201, 1400 from dummy union all
select 3, 1401, 2000 from dummy union all
select 4, 2001, 3000 from dummy union all
select 5, 3001, 9999 from dummy;
commit;
Create User Chanaka:
SQL> alter session set container=C23AI_PDB;
Session altered.
SQL> CREATE USER CHANAKA identified by ****** default tablespace USERS temporary tablespace TEMP;
User created.
########## Grants
grant create session to chanaka;
GRANT SELECT ON SCOTT.dept TO chanaka;
GRANT SELECT ON SCOTT.emp TO chanaka;
GRANT SELECT ON SCOTT.bonus TO chanaka;
GRANT SELECT ON SCOTT.salgrade TO chanaka;
Configure Firewall
The next step is to enable the firewall by executing the following command:
SQL> exec dbms_sql_firewall.enable;
PL/SQL procedure successfully completed.
SQL>
Validate Firewall Status
SQL firewall can be validated from the dba_sql_firewall_status table.
SQL> select status from dba_sql_firewall_status;
STATUS
--------
ENABLED
SQL>
You can enable capture and execute a few queries from both SQL Developer and SQL*Plus sessions as the user CHANAKA.
Enable Capture:
begin
dbms_sql_firewall.create_capture (
username => 'CHANAKA',
top_level_only => true,
start_capture => true);
end;
/
Before stopping the capture, ensure that all executed operations are recorded. You can validate the capture by running the following command. The capture logs will show the operations that have been recorded. In addition to the SQL text, various session attributes will also be logged.
set linesize 150 pagesize 40
column command_type format a12
column current_user format a15
column client_program format a45
column os_user format a10
column ip_address format a10
column sql_text format a30
select command_type,
current_user,
client_program,
os_user,
ip_address,
sql_text
from dba_sql_firewall_capture_logs
where username = 'CHANAKA';
Sample Output:
SQL> set linesize 150 pagesize 40
column command_type format a12
SQL> SQL> column current_user format a15
SQL> column client_program format a45
column os_user format a10
column ip_address format a10
column sql_text format a30
select command_type,
current_user,
client_program,
SQL> os_user,
ip_address,
sql_text
from dba_sql_firewall_capture_logs
where username = 'CHANAKA';SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8
COMMAND_TYPE CURRENT_USER CLIENT_PROGRAM OS_USER IP_ADDRESS SQL_TEXT
------------ --------------- --------------------------------------------- ---------- ---------- ------------------------------
SELECT CHANAKA SQL Developer opc 10.0.0.43 SELECT * FROM SCOTT.EMP
EXECUTE CHANAKA SQL Developer opc 10.0.0.43 BEGIN SYS.DBMS_UTILITY.NAME_RE
SOLVE (:1,:2,:3,:4,:5,:6,:7,:8
); END;
SELECT CHANAKA SQL Developer opc 10.0.0.43 SELECT * FROM SCOTT.SALGRADE
SELECT CHANAKA SQL Developer opc 10.0.0.43 SELECT * FROM SCOTT.BONUS
EXECUTE CHANAKA SQL Developer opc 10.0.0.43 BEGIN DBMS_UTILITY.EXPAND_SQL_
TEXT (INPUT_SQL_TEXT => :SQL,O
UTPUT_SQL_TEXT => :X); END;
SELECT CHANAKA SQL Developer opc 10.0.0.43 SELECT :"SYS_B_0" TYPE,OWNER,T
ABLE_NAME OBJECT_NAME,COLUMN_N
Once everything is captured, you can stop the capturing process. Use below below-mentioned command to stop the capture process.
exec dbms_sql_firewall.stop_capture('CHANAKA');
The next step is to generate the allowed list.
exec dbms_sql_firewall.generate_allow_list ('CHANAKA');
Check the allow-list contents
Get allow-list IP address
SQL> select *
from dba_sql_firewall_allowed_ip_addr
where username = 'CHANAKA'; 2 3
USERNAME IP_ADDRESS
-------------------------------- ------------
CHANAKA 10.0.0.43
Get allow-list OS – Program
SQL> select *
from dba_sql_firewall_allowed_os_prog
where username = 'CHANAKA'; 2 3
USERNAME OS_PROGRAM
---------- ------------------------------
CHANAKA SQL Developer
Get allow-list SQL- Text
column sql_text format A100
select current_user,
sql_text
from dba_sql_firewall_allowed_sql
where username = 'CHANAKA';
CURRENT_USER SQL_TEXT
--------------- ----------------------------------------------------------------------------------------------------
CHANAKA SELECT * FROM SCOTT.EMP
CHANAKA BEGIN SYS.DBMS_UTILITY.NAME_RESOLVE (:1,:2,:3,:4,:5,:6,:7,:8); END;
CHANAKA SELECT * FROM SCOTT.SALGRADE
CHANAKA SELECT * FROM SCOTT.BONUS
CHANAKA BEGIN DBMS_UTILITY.EXPAND_SQL_TEXT (INPUT_SQL_TEXT => :SQL,OUTPUT_SQL_TEXT => :X); END;
Enforce Types
We enforce the allow list using the ENABLE_ALLOW_LIST procedure. The type of enforcement is determined by the constant used:
- ENFORCE_CONTEXT: Enforces only the context (IP Address, OS User, and OS Program) allow-list
- ENFORCE_SQL: Enforces only the SQL allow-list
- ENFORCE_ALL: Enforces both the context and SQL allow-lists
Blocking
To block access, you need to remove the capture result from the allow list. For testing purposes, I will remove the IP address from the allowed list.
SQL> begin
dbms_sql_firewall.delete_allowed_context (
username => 'CHANAKA',
context_type => dbms_sql_firewall.ip_address,
value => '10.0.0.43');
end;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL>
Let’s assume some unknown party trying to access the database using IP address: 10.0.0.43.
Figure 1: New database connection
Figure 2: New database connection – 02
Figure 3: shows ORA-47605: SQL Firewall violation when attempting to start a new session
Figure 3: ORA- Error
Disable the entire allowed list
SQL> exec dbms_sql_firewall.disable_allow_list ('CHANAKA');
PL/SQL procedure successfully completed.
Conclusion
In conclusion, the SQL Firewall provides robust security measures by monitoring and controlling SQL statements executed in your database environment. It offers comprehensive insights into SQL activities, detects potential threats, and enforces security policies to protect your data. Utilizing the SQL Firewall, you can ensure a secure and compliant database environment.