Oracle Database 23ai 新功能：SQL 防火牆 - 資料庫安全的強力守門員

-- 建立 FWADMIN 用戶，專門用來管理 SQL Firewall，該用戶會被授予 SQL_FIREWALL_ADMIN 角色

create user fwadmin identified by "password";

grant create session to fwadmin;

grant sql_firewall_admin to fwadmin;

-- 建立 SCHEMA_OWNER 用戶，用來建立測試需要的資料庫物件

create user schema_owner identified by "password" quota unlimited on users;

grant db_developer_role to schema_owner;

-- 建立 APP_USER_1 用戶，當作應用程序所使用的用戶，用來存取 SCHEMA_OWNER 所建立的物件

create user app_user_1 identified by "password";

grant create session to app_user_1;

grant select any table on schema schema_owner to app_user_1;

create table schema_owner.t1 (id number);

insert into schema_owner.t1 values (1);

create table schema_owner.t2 (id number);

insert into schema_owner.t2 values (2);

commit;

-- 啟用 SQL Firewall

exec dbms_sql_firewall.enable;

--對 APP_USER_1 用戶的活動開啟補捉

begin

  dbms_sql_firewall.create_capture (

    username       => 'app_user_1',

    top_level_only => true,

    start_capture  => true);

end;

/

select * from schema_owner.t1;

select * from schema_owner.t2;

--我們可以看到 APP_USER_1 的 SQL操作已被補捉

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      SQL Developer   chengyichang  60.250.39.10  SELECT * FROM SCHEMA_OWNER.T2

SELECT       APP_USER_1      SQL Developer   chengyichang  60.250.39.10  SELECT * FROM SCHEMA_OWNER.T1

--停止APP_USER_1 用戶活動補捉

exec dbms_sql_firewall.stop_capture('app_user_1');

--基於補捉到的日誌，產生白名單

exec dbms_sql_firewall.generate_allow_list ('app_user_1');

--查看白名單的內容，分別查看IP_ADDRESS、OS_PROGRAM、OS_USER、SQL_TEXT 內容

select *

from   dba_sql_firewall_allowed_ip_addr

where  username = 'APP_USER_1';

USERNAME             IP_ADDRESS

-------------------- ----------

APP_USER_1           60.250.39.10

select *

from   dba_sql_firewall_allowed_os_prog

where  username = 'APP_USER_1';

USERNAME             OS_PROGRAM

-------------------- --------------------------------------------------

APP_USER_1           SQL Developer

select *

from   dba_sql_firewall_allowed_os_user

where  username = 'APP_USER_1';

USERNAME             OS_USER

-------------------- ----------

APP_USER_1           chengyichang

select current_user,

       sql_text

from   dba_sql_firewall_allowed_sql

where  username = 'APP_USER_1' and sql_text like '%SCHEMA_OWNER%';

CURRENT_USER    SQL_TEXT

--------------- --------------------------------------------------

APP_USER_1      SELECT * FROM SCHEMA_OWNER.T2

APP_USER_1      SELECT * FROM SCHEMA_OWNER.T1

--啟用白名單： 以下例子將啟用 context (上下文資訊) 與 SQL 語句白名單，另外 block 參數設置為 true，表示不在允許清單內的操作將被阻擋

begin

  dbms_sql_firewall.enable_allow_list (

    username => 'app_user_1',

    enforce  => dbms_sql_firewall.enforce_all,

    block    => true);

end;

/

--以下 SQL 語句不在白名單中，執行報錯

select count(*) from schema_owner.t1;

ORA-47605: SQL Firewall violation

https://docs.oracle.com/error-help/db/ora-47605/47605. 00000 -  "SQL Firewall violation"

*Cause:    Operation blocked by the SQL Firewall due to enforcement of

           the firewall policy.

*Action:   Review the SQL Firewall policy. If it is not intended to block the

           operation, review and modify the SQL Firewall policy.

--以下 SQL 語句在白名單中，執行正常

select * from schema_owner.t1;

  COUNT(*)

----------

         1