描述
SQL 防火墙 的介绍
SQL 防火墙通过监控和阻止未经授权的SQL 和SQL 注入攻击来工作。它内置了一系列规则，可以识别和阻止常见的SQL 注入攻击，如布尔型注入、报错注入、联合查询注入、堆叠查询注入等。此外，它还可以通过基于角色的访问控制（RBAC ）来限制用户的访问权限，从而防止SQL 注入攻击。
防火墙的优点在于它内置在数据库内部，不需要任何外部组件或代理，因此可以提供更高效和可靠的防护。此外，它可以自动识别和阻止SQL 注入攻击，而无需手动配置或更新防火墙规则。
SQL 防火墙的注意事项
BLOCKENABLE_ALLOW_LIST
设置为
false
意味着它将记录，但不会阻止。
防火墙测试
创建测试用户
创建用户脚本:
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba drop user if exists fwadmin cascade; create user fwadmin identified by fwadmin; grant create session to fwadmin; grant sql_firewall_admin to fwadmin; drop user if exists schema_owner cascade; create user schema_owner identified by schema_owner quota unlimited on users; grant db_developer_role to schema_owner; drop user if exists app_user_1 cascade; create user app_user_1 identified by app_user_1; grant create session to app_user_1; grant select any table on schema schema_owner to app_user_1;
创建两张表用于测试
drop table if exists schema_owner.t1 purge; create table schema_owner.t1 (id number); insert into schema_owner.t1 values (1); drop table if exists schema_owner.t2 purge; create table schema_owner.t2 (id number); insert into schema_owner.t2 values (2); commit;
防火墙抓取（审计）会话
登录防火墙权限用户，开启防火墙
conn fwadmin/fwadmin@//localhost:1521/freepdb1 exec dbms_sql_firewall.enable;
使用以下查询检查 SQL 防火墙的状态。
select status from dba_sql_firewall_status; STATUS -------- ENABLED
# 启用对应用户跟踪日志
begin dbms_sql_firewall.create_capture ( username => 'app_user_1', top_level_only => true, start_capture => true); end; /
新建会话使用app_user_1 用户连接，执行查询操作
# 用 sqlplus 连接执行
select * from schema_owner.t1; select * from schema_owner.t2;
# 用 pl/sql 也登录一个会话，执行同样的操作
select * from schema_owner.t1; select * from schema_owner.t2;
回到防火墙管理员会话中，查询日志
select command_type,
current_user,
client_program,
os_user,
ip_address,
sql_text
from dba_sql_firewall_capture_logs
where username = 'APP_USER_1';
COMMAND_TYPE CURRENT_USER CLIENT_PROGRAM OS_USER IP_ADDRESS SQL_TEXT
------------ --------------- --------------------------------------------- ---------- ---------- ------------------------------
SELECT APP_USER_1 sqlplus@localhost.localdomain (TNS V1-V3) oracle 127.0.0.1 SELECT DECODE (USER,:"SYS_B_0"
,XS_SYS_CONTEXT (:"SYS_B_1",:"
SYS_B_2"),USER) FROM SYS.DUAL
SELECT APP_USER_1 sqlplus@localhost.localdomain (TNS V1-V3) oracle 127.0.0.1 SELECT * FROM SCHEMA_OWNER.T2
SELECT APP_USER_1 PL/SQL tim_hall 10.0.2.2 SELECT * FROM SCHEMA_OWNER.T2
SELECT APP_USER_1 sqlplus@localhost.localdomain (TNS V1-V3) oracle 127.0.0.1 SELECT * FROM SCHEMA_OWNER.T1
SELECT APP_USER_1 PL/SQL tim_hall 10.0.2.2 SELECT * FROM SCHEMA_OWNER.T1
SQL>
停止抓取
exec dbms_sql_firewall.stop_capture('app_user_1');
创建防火墙规则
根据日志为用户生成允许列表。
exec dbms_sql_firewall.generate_allow_list ('app_user_1');
可以通过 IP 地址、程序、系统用户、 SQL 语句进行配置规则。
#IP 地址
select * from dba_sql_firewall_allowed_ip_addr where username = 'APP_USER_1'; USERNAME IP_ADDRESS -------------------- ---------- APP_USER_1 10.0.2.2 APP_USER_1 127.0.0.1
# 应用程序
select * from dba_sql_firewall_allowed_os_prog where username = 'APP_USER_1'; USERNAME OS_PROGRAM -------------------- -------------------------------------------------- APP_USER_1 PL/SQL APP_USER_1 sqlplus@localhost.localdomain (TNS V1-V3)
# 系统用户
select * from dba_sql_firewall_allowed_os_user where username = 'APP_USER_1'; USERNAME OS_USER -------------------- ---------- APP_USER_1 oracle APP_USER_1 tim_hall
#SQL 语句内容
select current_user, sql_text from dba_sql_firewall_allowed_sql where username = 'APP_USER_1'; CURRENT_USER SQL_TEXT --------------- -------------------------------------------------- APP_USER_1 SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"S YS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL APP_USER_1 SELECT * FROM SCHEMA_OWNER.T2 APP_USER_1 SELECT * FROM SCHEMA_OWNER.T1 SQL>
使用以下过程配置允许下列 IP 地址可进行删除操作。
## 添加 IP 地址
begin dbms_sql_firewall.add_allowed_context ( username => 'app_user_1', context_type => dbms_sql_firewall.ip_address, value => '192.168.56.1'); end; /
## 确认 IP 列表
column ip_address format a12 select * from dba_sql_firewall_allowed_ip_addr where username = 'APP_USER_1'; USERNAME IP_ADDRESS -------------------- ------------ APP_USER_1 10.0.2.2 APP_USER_1 127.0.0.1 APP_USER_1 192.168.56.1 SQL>
## 删除 IP 地址
begin dbms_sql_firewall.delete_allowed_context ( username => 'app_user_1', context_type => dbms_sql_firewall.ip_address, value => '192.168.56.1'); end; /
## 确认 IP 列表
select * from dba_sql_firewall_allowed_ip_addr where username = 'APP_USER_1'; USERNAME IP_ADDRESS -------------------- ------------ APP_USER_1 10.0.2.2 APP_USER_1 127.0.0.1 SQL>
下面示例，我们同时启用上下文和 SQL 允许列表。若将参数设置为 false 只会记录异常操作，但不会阻止操作。
begin dbms_sql_firewall.enable_allow_list ( username => 'app_user_1', enforce => dbms_sql_firewall.enforce_all, block => true); end; /
可以使用视图查看允许列表的状态。 DBA_SQL_FIREWALL_ALLOW_LISTS
select username, status, top_level_only, enforce, block from dba_sql_firewall_allow_lists where username='APP_USER_1'; USERNAME STATUS TOP_LEVEL_ONLY ENFORCE BLOCK -------------------- -------- -------------- --------------- -------------- APP_USER_1 ENABLED Y ENFORCE_ALL Y SQL>
可以使用该视图检查 SQL 防火墙的违规情况。 DBA_SQL_FIREWALL_VIOLATIONS
select sql_text, firewall_action, ip_address, cause, occurred_at from dba_sql_firewall_violations where username = 'APP_USER_1';
使用不同的会话，向数据库发出未知和已知的 SQL 语句。请注意，未知语句失败，并显示 “ORA-47605 ： SQL 防火墙冲突 ” 错误。
-- 未知语句
select count(*) from schema_owner.t1; Error starting at line : 1 in command - select count(*) from schema_owner.t1 Error at Command Line : 1 Column : 1 Error report - SQL Error: ORA-47605: SQL Firewall violation SQL>
-- 列表内语句
select * from schema_owner.t1; COUNT(*) ---------- 1 SQL>
登录防火墙管理员用户并检查违规行为。
select sql_text, firewall_action, ip_address, cause, occurred_at from dba_sql_firewall_violations where username = 'APP_USER_1' and sql_text like '%COUNT%'; SQL_TEXT FIREWAL IP_ADDRESS CAUSE OCCURRED_AT -------------------------------------------------- ------- ------------ -------------------- ----------------------------------- SELECT COUNT (*) FROM SCHEMA_OWNER.T1 Blocked 10.0.2.2 SQL violation 03-JUL-23 09.35.55.318805 PM +00:00 SQL>
如果我们发现应该允许通过的违规行为，我们可以手动将它们添加到允许列表中，也可以通过从日志中提取它们来添加它们。
exec dbms_sql_firewall.append_allow_list('app_user_1', dbms_sql_firewall.violation_log);
尝试再次运行导致冲突的查询，会正常通过
select count(*) from schema_owner.t1; COUNT(*) ---------- 1 SQL>
防火墙相关视图
select view_name from dba_views where view_name like 'DBA_SQL_FIREWALL%' order by 1; VIEW_NAME -------------------------------------------------------------------------------- DBA_SQL_FIREWALL_ALLOWED_IP_ADDR DBA_SQL_FIREWALL_ALLOWED_OS_PROG DBA_SQL_FIREWALL_ALLOWED_OS_USER DBA_SQL_FIREWALL_ALLOWED_SQL DBA_SQL_FIREWALL_ALLOW_LISTS DBA_SQL_FIREWALL_CAPTURES DBA_SQL_FIREWALL_CAPTURE_LOGS DBA_SQL_FIREWALL_SESSION_LOGS DBA_SQL_FIREWALL_SQL_LOGS DBA_SQL_FIREWALL_STATUS DBA_SQL_FIREWALL_VIOLATIONS
