Go to Qiita Advent Calendar 2024 Top
LoginSignup
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ritokuna(Ritsuko Tokunaga)

[OCI]BaseDB(Oracle Database 23c)でSQL Firewallを試してみた

Posted at

SQL Firewallを試してみたので、忘れないように手順をアウトプットしておきます。

はじめに

Oracle Database 23cの新機能としてSQL Firewallがあります。
これはSQLレベルで制御するデータベース・ネイティブのファイアーウォールであり、
アクセス許可されるSQLは、列名や条件を含めて完全一致しなければならず、
SQLインジェクションや不必要なデータ参照を防止に使うことができます。

環境

  • Oracle Database 23c

今回はOCI上で、Enterprise Editionを選択し23cを使ってます。

手順

今回は以下の2ユーザを切り替えて、SQL Firewallの設定と動作確認をします。

  • SQL Firewallの設定を行うsysユーザー
  • SQLの動作確認用をするHRユーザー

サンプルスキーマをインストールする（環境準備）

こちらのQiita記事にまとめたので、以下の記事を参考にサンプルスキーマをインストールします。
【Oracle】スキーマサンプルを入れてみた

[sysユーザー]キャプチャを開始

既に、SQL Firewallを試す環境があるよって人はサンプルスキーマを用意しなくて大丈夫です。

  • BaseDBにssh
ritsuko_to@cloudshell:~ (us-ashburn-1)$ ssh opc@xxx.xxx.xxx.xxx -i ssh-key-2024-03-22.key
FIPS mode initialized
Last failed login: Sat Apr 13 21:06:17 UTC 2024 from xxx.xxx.xxx.xxx on ssh:notty
There was 1 failed login attempt since the last successful login.
Last login: Fri Apr 12 02:10:01 2024 from 129.213.201.220
  • oracleユーザに切り替え
[opc@davdays ~]$ sudo su -
Last login: Mon Apr 15 04:05:33 UTC 2024
Last failed login: Mon Apr 15 04:17:50 UTC 2024 from 176.215.255.242 on ssh:notty
There were 14 failed login attempts since the last successful login.
[root@davdays ~]# su - oracle
Last login: Mon Apr 15 04:05:34 UTC 202
  • sysユーザでsqlplusにて接続し、PDBに接続
[oracle@davdays ~]$  sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Mon Apr 15 04:29:38 2024
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DB0328_PDB1                    READ WRITE NO
SQL>  alter session set container = DB0328_PDB1 ;

Session altered.
  • SQL Firewallの有効化
SQL> exec DBMS_SQL_FIREWALL.ENABLE;

PL/SQL procedure successfully completed.
  • キャプチャの定義を作成定義を作成。
    start_captureをTRUEにしているので、作成後キャプチャが自動開始されます。
    また一度キャプチャを作成した場合は、キャプチャを破棄するまで再定義は必要なく、キャプチャの開始と停止だけでOKです。
    ※私はキャプチャを破棄していないのに、キャプチャの定義を実施してエラー出しました
SQL> BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE (
    username         => 'HR',
    top_level_only   => TRUE,
    start_capture    => TRUE
  );
END;
/ 

PL/SQL procedure successfully completed.
SQL>

[HRユーザー]キャプチャさせたいSQLを実行

  • もう一つターミナルを開き、HRユーザでPDBに接続
ritsuko_to@cloudshell:~ (us-ashburn-1)$ ssh opc@132.145.169.139 -i ssh-key-2024-03-22.key
FIPS mode initialized
Last failed login: Fri Apr 19 07:06:05 UTC 2024 from 170.64.206.184 on ssh:notty
There were 11 failed login attempts since the last successful login.
Last login: Mon Apr 15 04:52:21 2024 from 129.213.201.220

[opc@davdays ~]$ sudo su -
Last login: Sat Apr 20 00:35:33 UTC 2024
Last failed login: Sat Apr 20 01:25:11 UTC 2024 from 103.98.131.106 on ssh:notty
There were 102 failed login attempts since the last successful login.

[root@davdays ~]# su - oracle
Last login: Sat Apr 20 01:12:30 UTC 2024
[oracle@davdays ~]$ sqlplus hr/WelCome123#123#@10.0.0.156:1521/DB0328_DB0328_pdb1.paas.oracle.com;

SQL*Plus: Release 23.0.0.0.0 - Production on Sat Apr 20 01:26:28 2024
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Mon Apr 15 2024 08:25:36 +00:00

Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SQL>
  • 以下の3つのselect文を実行。employeesは107行もあるので、select文の結果は省きます。
select * from employees;
select * from employees where employee_id=100;
select employee_id,first_name,salary from employees;

[sysユーザー]キャプチャを停止させ、キャプチャしたSQLを確認

  • キャプチャの停止
SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('HR');

PL/SQL procedure successfully completed.

SQL>
  • キャプチャしたSQLを確認
    順序は違いますが、先ほどキャプチャのさせたSQLが3つあります。
    またWhere句のvalueがバインド変数になっているのが確認できます。
SQL> SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS WHERE USERNAME = 'HR';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES
SELECT * FROM EMPLOYEES
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=:"SYS_B_0"

SQL>

[sysユーザー]許可リスト生成と許可リストの確認と有効化

  • 許可リスト生成
SQL> exec DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('HR')

PL/SQL procedure successfully completed.

SQL>
  • 許可リストの確認(SQL)
SQL> set linesize 1000
SQL> column USERNAME format a10
SQL> column SQL_SIGNATURE format a30
SQL> column SQL_TEXT format a30
SQL> SELECT ALLOWED_SQL_ID, USERNAME, SQL_SIGNATURE, SQL_TEXT FROM DBA_SQL_FIREWALL_ALLOWED_SQL where username = 'HR';

ALLOWED_SQL_ID USERNAME   SQL_SIGNATURE                  SQL_TEXT
-------------- ---------- ------------------------------ ------------------------------
             1 HR         C033600093F3D2FDA4031DE0DCD33D SELECT * FROM EMPLOYEES
                          40C6EE1CB12D5F5630ED9D4B84997D
                          1145

             2 HR         D8A218F976A0DDE478F24A1159E24C SELECT * FROM EMPLOYEES WHERE
                          DEC7919B61AB6BCF4031E245B5290D EMPLOYEE_ID=:"SYS_B_0"
                          91CE

             3 HR         77D1C60B425F055F93F7014437EF68 SELECT EMPLOYEE_ID,FIRST_NAME,
                          99745E4C747C297D0AD799943707DD SALARY FROM EMPLOYEES
                          1189

ALLOWED_SQL_ID USERNAME   SQL_SIGNATURE                  SQL_TEXT
-------------- ---------- ------------------------------ ------------------------------


SQL>
  • 許可リスト有効化
SQL> BEGIN
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
username=>'HR',
enforce=>DBMS_SQL_FIREWALL.ENFORCE_ALL,
block=>TRUE
);
END;
/ 

PL/SQL procedure successfully completed.

SQL>

[HRユーザー]動作確認

以下の二つのSQLを実施し、動作確認をする

select first_name,last_name from employees;
select * from employees where employee_id=110;

  • select first_name,last_name from employees;
    許可していないSQLなのでブロックされる
SQL> select first_name,last_name from employees;
select first_name,last_name from employees
                                 *
ERROR at line 1:
ORA-47605: SQL Firewall violation
Help: https://docs.oracle.com/error-help/db/ora-47605/
SQL>
  • select * from employees where employee_id=110;
    キャプチャさせたwhere句(where employee_id=100)と違っても
    SQLシグネチャが一致するためアクセスができる
SQL> select * from employees where employee_id=110;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        110 John                 Chen
JCHEN                     1.515.555.0110       28-SEP-15 FI_ACCOUNT       8200
                      108           100


SQL>

[sysユーザー]違反ログの確認

  • DBA_SQL_FIREWALL_VIOLATIONSにログが残されてます
  • 先ほど違反させたSQLが確認できます
SQL> SELECT USERNAME,SQL_TEXT,FIREWALL_ACTION,CAUSE FROM DBA_SQL_FIREWALL_VIOLATIONS;

USERNAME   SQL_TEXT                       FIREWAL CAUSE
---------- ------------------------------ ------- --------------------
HR         SELECT FIRST_NAME,LAST_NAME FR Blocked SQL violation
           OM EMPLOYEES

SQL>
4
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?