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

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】スキーマサンプルを入れてみた](https://qiita.com/ritokuna/items/382ef519f5abf40bf733)
### [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> 

```