CO-Sol Canada BLOG

AIです。（Artificial Intelligence ではありません）

トロントは夏まっさかり！AI も Brewery 巡りや Summerlicious とカナダの夏を楽しんでおります！※ Summerlicious とは何ぞや？という方は是非「カナダde FOOD ADVENTURE ⑨」の記事をご覧ください

カナダde FOOD ADVENTURE ⑨ Summerlicious 2023 編

さて、前回の宣言通り今回は SQL ファイアウォールの違反ログについて見ていきましょう。

■違反ログとは

違反ログは SQL ファイアウォール違反を記録するログです。例えば、SQLファイアウォールを設定したユーザーが許可リストにない SQL を実行した際に実行された SQL や時間、ユーザー情報、その SQL の実行を許可したかどうかが記録されます。また、前回は、ユーザーのキャプチャ・ログに記録された SQL から許可リストを作成しましたがSQL ファイアウォールでは、違反ログから許可リストに SQL を追加することもできます。

SQL ファイアウォールの違反ログの中身は DBA_SQL_FIREWALL_VIOLATIONS から確認することができます。

Oracle Database
データベース・リファレンス
23c
6.416 DBA_SQL_FIREWALL_VIOLATIONS
https://docs.oracle.com/cd/F82042_01/refrn/DBA_SQL_FIREWALL_VIOLATIONS.html#GUID-855FF31F-7F89-4667-B790-4AD157EA00B5

※ DBA_SQL_FIREWALL_VIOLATIONS の SQL_TEXT 列は最大1000文字しか表示されません。完全な SQL 文を表示させたい場合は、DBA_SQL_FIREWALL_SQL_LOGS の SQL_TEXT 列を参照します。

■ブロックと違反ログへの記録

許可リストにない SQL を実行した際、違反ログにその情報が記録されますが違反している SQL を実行するかブロックするかは設定で変えることが可能です。

では、実際に試してみましょう。以下は現在の Goat ユーザーの許可リストです。許可リストの、BLOCK は N(FALSE) となっています。

SQL> SELECT username,allowed_sql_id,sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL;

USERNAME   ALLOWED_SQL_ID SQL_TEXT
---------- -------------- ----------------------------------------------------------------------------------------------------
GOAT                    1 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"
GOAT                    2 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0" OR FURNITURE=:"SYS_B_1"


SQL> SELECT username,status,block FROM DBA_SQL_FIREWALL_ALLOW_LISTS;

USERNAME   STATUS   BLOCK
---------- -------- --------------
GOAT       ENABLED  N ★

この状態で許可リストにない SQL を実行してみます。BLOCK は N でしたので実行自体はできました。

SQL> SELECT * FROM Goat.house WHERE furniture IN ('Wall Clock','Bed');

FURNITURE
---------------
Wall Clock
Bed

ここで違反ログを確認してみると、先ほど実行した SQL が記録されているのがわかります。

SQL> SELECT username,sql_text,cause,firewall_action,occurred_at FROM DBA_SQL_FIREWALL_VIOLATIONS;

USERNAME   SQL_TEXT                                                                                             CAUSE             FIREWAL   OCCURRED_AT                      
---------- ---------------------------------------------------------------------------------------------------- ----------------- -------   ---------------------------------
GOAT       SELECT * FROM GOAT.HOUSE WHERE FURNITURE IN (:"SYS_B_0",:"SYS_B_1")★                                SQL violation     Allowed★ 24-08-14 15:11:13.793419 -04:00
GOAT       SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL            SQL violation     Allowed   24-08-14 15:02:41.213732 -04:00

続いて、BLOCK を Y(TRUE) に変更して違う SQL を実行してみます。許可リストのブロックの設定を変更する際は DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT を使用します。

SQL> BEGIN
  2    DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT (
  3      username       => 'GOAT',
  4      enforce        => DBMS_SQL_FIREWALL.ENFORCE_SQL,
  5      block          => TRUE ★
  6     );
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。


SQL> SELECT username,status,block FROM DBA_SQL_FIREWALL_ALLOW_LISTS;

USERNAME   STATUS   BLOCK
---------- -------- --------------
GOAT       ENABLED  Y ★

BLOCK を Y(TRUE) にすることで、SQL の実行が拒否されました。

SQL> SELECT * FROM Goat.house;
SELECT * FROM Goat.house
                   *
行1でエラーが発生しました。:
ORA-47605: SQLファイアウォール違反 ヘルプ: ★
https://docs.oracle.com/error-help/db/ora-47605/

違反ログの FIREWALL_ACTION 列を見ると Blocked と出力されており、SQL の実行が拒否されたことがわかります。

SQL> SELECT username,sql_text,cause,firewall_action,occurred_at FROM DBA_SQL_FIREWALL_VIOLATIONS;

USERNAME   SQL_TEXT                                                                                             CAUSE             FIREWAL   OCCURRED_AT
---------- ---------------------------------------------------------------------------------------------------- ----------------- -------   ---------------------------------
GOAT       SELECT * FROM GOAT.HOUSE WHERE FURNITURE IN (:"SYS_B_0",:"SYS_B_1")                                  SQL violation     Allowed   24-08-14 15:11:13.793419 -04:00
GOAT       SELECT * FROM GOAT.HOUSE★                                                                           SQL violation     Blocked★ 24-08-14 15:31:34.987688 -04:00
GOAT       SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL            SQL violation     Allowed   24-08-14 15:02:41.213732 -04:00

■違反ログから許可リストに SQL を追加

次に違反ログに記録された SQL を許可リストに追加します。許可リストへの追加には APPEND_ALLOW_LISTプロシージャを使用します。今回は違反ログから追加するため、source に DBMS_SQL_FIREWALL.VIOLATION_LOG を指定します。

SQL> BEGIN
  2    DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST (
  3      username       => 'GOAT',
  4      source         => DBMS_SQL_FIREWALL.VIOLATION_LOG
  5     );
  6  END;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT username,allowed_sql_id,sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL;

USERNAME   ALLOWED_SQL_ID SQL_TEXT
---------- -------------- ----------------------------------------------------------------------------------------------------
GOAT                    1 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"
GOAT                    2 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0" OR FURNITURE=:"SYS_B_1"
GOAT                    3 SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
GOAT                    4 SELECT * FROM GOAT.HOUSE WHERE FURNITURE IN (:"SYS_B_0",:"SYS_B_1")
GOAT                    5 SELECT * FROM GOAT.HOUSE

違反として記録された SQL が許可リストに追加されました！とはいえ、すべての違反 SQL を追加したいわけではない、というケースもありますよね。許可リストに入れたくない SQL は DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL を使って削除しましょう。

SQL> BEGIN
  2    DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL (
  3      username         => 'GOAT',
  4      allowed_sql_id   => 5
  5     );
  6  END;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT username,allowed_sql_id,sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL;

USERNAME   ALLOWED_SQL_ID SQL_TEXT
---------- -------------- ----------------------------------------------------------------------------------------------------
GOAT                    1 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"
GOAT                    2 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0" OR FURNITURE=:"SYS_B_1"
GOAT                    3 SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
GOAT                    4 SELECT * FROM GOAT.HOUSE WHERE FURNITURE IN (:"SYS_B_0",:"SYS_B_1")

■違反ログのレコード削除

違反ログがたまってきたら、DBMS_SQL_FIREWALL.PURGE_LOG でログを削除しましょう。DBMS_SQL_FIREWALL.PURGE_LOG で指定したログの指定した時間より前のレコードを削除することができます。

SQL> BEGIN
  2    DBMS_SQL_FIREWALL.PURGE_LOG (
  3      username    => 'GOAT',
  4      purge_time  => TO_TIMESTAMP('2024-08-14 15:30:00', 'YYYY-MM-DD HH24:MI:SS'),
  5      log_type    => DBMS_SQL_FIREWALL.VIOLATION_LOG
  6     );
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。


SQL> SELECT username,sql_text,cause,firewall_action,occurred_at FROM DBA_SQL_FIREWALL_VIOLATIONS;

USERNAME   SQL_TEXT                                                                                             CAUSE             FIREWAL OCCURRED_AT
---------- ---------------------------------------------------------------------------------------------------- ----------------- ------- ---------------------------------
GOAT       SELECT * FROM GOAT.HOUSE                                                                             SQL violation     Blocked 24-08-14 15:31:34.987688 -04:00

■まとめ

今回は、SQL ファイアウォールの違反ログについて見てきましたがいかがでしたでしょうか。

SQL ファイアウォールの違反ログは、実行タイミングや SQL 文、OS ユーザー、IP アドレスなども記録されますので仮に不正な SQL が実行されてしまったとしても、違反ログからタイミングや実行 SQL を特定することが可能です。不正 SQL の実行を防止するだけでなく、検知・後追いもできるところが SQL ファイアウォールの強みになりそうですね。

今後も Oracle Database 23ai の新機能について掘り下げていく予定ですので、次回の記事をお楽しみに！

AIです。（Artificial Intelligence ではありません）
トロントは初夏にさしかかり、半袖で過ごせる日も多くなってきました。晴れているとインドア派な AI でさえ、でかけたくなるくらいのお散歩日和となります。実はトロントでは人だけではなく、カナダガン（カナダグース）も街を散歩していたりします。道端で遭遇するとびっくりするくらいには大きいですよ。カナダガン。

さて、前回に引き続き、Oracle Database 23ai の新機能を触っていきたいと思います。今回フィーチャーする機能は「SQL ファイアウォール」です！SQL ファイアウォールは、事前にユーザーごとの SQL の許可リストを作成し、許可リストにない SQL の実行をブロックしたりロギングしたりすることができる機能です。

■設定

今回は goat ユーザーに対して SQL ファイアウォールを設定します。設定は以下の流れで行います。

1. SQL ファイアウォールの有効化
2. キャプチャの作成・有効化
3. SQL のキャプチャ
4. キャプチャの無効化
5. 許可リストの生成
6. 許可リストの有効化

1.SQL ファイアウォールの有効化

現在の SQL ファイアウォールの状態は以下の SQL で確認できます。現在は無効となっていますね。

SQL> SELECT * FROM DBA_SQL_FIREWALL_STATUS;

STATUS   STATUS_UPDATED_ON                                                           EXCLUDE_JOBS
-------- --------------------------------------------------------------------------- ------------
DISABLED 24-05-21 11:35:40.636036 -04:00                                             Y

以下のコマンドで SQL ファイアウォールを有効化します。

SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM DBA_SQL_FIREWALL_STATUS;

STATUS   STATUS_UPDATED_ON                                                           EXCLUDE_JOBS
-------- --------------------------------------------------------------------------- ------------
ENABLED  24-05-22 11:54:26.253441 -04:00                                             Y

2.キャプチャの作成・有効化

続いて、goat ユーザーのキャプチャを作成します。

SQL> BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE (
    username         => 'GOAT',
    top_level_only   => TRUE,
    start_capture    => TRUE
  );
END;
/  2    3    4    5    6    7    8

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT username,status FROM dba_sql_firewall_captures;

USERNAME   STATUS
---------- --------
GOAT       ENABLED

3.SQL のキャプチャ

キャプチャ・ログに SQL を登録するため、goat ユーザーで以下の SQL を実行します。

SQL> SELECT * FROM Goat.house;

FURNITURE
--------------------------------------------------------------------------------
Wall Clock
Table
Bed

SQL> SELECT * FROM Goat.house WHERE furniture = 'Wall Clock';

FURNITURE
--------------------------------------------------------------------------------
Wall Clock

SQL がキャプチャされたかをキャプチャ・ログから確認。先ほど実行した SQL がキャプチャ・ログに登録されていますね。登録された SQL の WHERE 句の条件の値はバインド変数に置換されて登録されているのがわかります。

SQL> SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS WHERE USERNAME = 'GOAT';

SQL_TEXT
---------------------------------------------------------------------------------------------
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
SELECT * FROM GOAT.HOUSE
SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"

4.キャプチャの無効化

きちんとキャプチャがとれていることが確認できたので、キャプチャを無効化します。

SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE ('GOAT');

PL/SQLプロシージャが正常に完了しました。

SQL> col username for a10
SQL> SELECT username,status FROM dba_sql_firewall_captures;

USERNAME   STATUS
---------- --------
GOAT       DISABLED

5.許可リストの生成

以下のプロシージャを実行して、既存のキャプチャ・ログから許可リストを生成します。

SQL> EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('GOAT');

PL/SQLプロシージャが正常に完了しました。

生成された許可リストの一覧や状態は DBA_SQL_FIREWALL_ALLOW_LISTS から確認できます。

SQL> SELECT username,status,block FROM DBA_SQL_FIREWALL_ALLOW_LISTS
  2  WHERE username='GOAT';

USERNAME   STATUS   BLOCK
---------- -------- --------------
GOAT       DISABLED N

許可リストに登録された SQL は DBA_SQL_FIREWALL_ALLOWED_SQL から確認します。

SQL> SELECT username,allowed_sql_id,sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL
  2  WHERE username='GOAT';

USERNAME   ALLOWED_SQL_ID SQL_TEXT
---------- -------------- ----------------------------------------------------------------------------------------------------
GOAT                    1 SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
GOAT                    2 SELECT * FROM GOAT.HOUSE
GOAT                    3 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"

個人的に気を付けたいポイントとして、ALLOWED_SQL_ID が AWR レポートや v$session で SQL の特定に使用する SQL_ID とは異なり、ユーザーごとに一意の ID となっているところですね。項目だけ見ると一瞬いつもの SQL_ID かなと思ってしまいそうなので…

6.許可リストの有効化

最後に許可リストを有効化して、設定は完了です！

SQL> BEGIN
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
    username       => 'GOAT',
    enforce        => DBMS_SQL_FIREWALL.ENFORCE_SQL,
    block          => TRUE
   );
END;
/  2    3    4    5    6    7    8

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT username,status,block FROM DBA_SQL_FIREWALL_ALLOW_LISTS
  2  WHERE username='GOAT';

USERNAME   STATUS   BLOCK
---------- -------- --------------
GOAT       ENABLED  Y

■許可された SQL の実行 / 許可されていない SQL の実行

では早速、GOAT ユーザーで色々な SQL を実行してみましょう。まずは、許可リストにある SQL から。これは実行できますね。

SQL> SELECT * FROM Goat.house;

FURNITURE
--------------------------------------------------------------------------------
Wall Clock
Table
Bed

SQL> SELECT * FROM Goat.house WHERE furniture = 'Wall Clock';

FURNITURE
--------------------------------------------------------------------------------
Wall Clock

続いて、許可リストにない SQL を実行してみましょう。WHERE 句で指定する条件の数を変えたり、IN 条件に変えたりすると ORA-47605 が発生して実行できませんでした。

SQL> SELECT * FROM Goat.house WHERE furniture = 'Wall Clock' OR furniture='Table';
SELECT * FROM Goat.house WHERE furniture = 'Wall Clock' OR furniture='Table'
                   *
行1でエラーが発生しました。:
ORA-47605: SQLファイアウォール違反 ヘルプ:
https://docs.oracle.com/error-help/db/ora-47605/


SQL> SELECT * FROM Goat.house WHERE furniture IN ('Wall Clock','Bed');
SELECT * FROM Goat.house WHERE furniture IN ('Wall Clock','Bed')
                   *
行1でエラーが発生しました。:
ORA-47605: SQLファイアウォール違反 ヘルプ:
https://docs.oracle.com/error-help/db/ora-47605/

一方で、条件の指定の仕方は同じで値だけ異なる場合には実行できます。許可リストの検索条件の値はバインド変数で登録されていましたしね。

SQL> SELECT * FROM Goat.house WHERE furniture = 'Table';

FURNITURE
--------------------------------------------------------------------------------
Table

つまり、[OR ‘1’=’1′] と余計な条件をつけた SQL インジェクション的な SQL は当然ブロックされるというわけですね…！

SQL> SELECT * FROM Goat.house WHERE furniture = 'Table' OR '1'='1';
SELECT * FROM Goat.house WHERE furniture = 'Table' OR '1'='1'
                   *
行1でエラーが発生しました。:
ORA-47605: SQLファイアウォール違反 ヘルプ:
https://docs.oracle.com/error-help/db/ora-47605/

■まとめ

SQL ファイアウォールの設定・ブロック編、いかがでしたでしょうか。設定の際には実際に SQL を実行する必要がありますが、一度設定してしまえば許可した SQL 以外は実行できない、というのはセキュリティのリスク軽減に大きく貢献すると思います。

今回は SQL の許可リストを設定しましたが IP アドレスやプログラム名など実行ユーザーに紐づいた情報に対しても許可リストを作ることができます。

また SQL ファイアウォールは SQL の実行をブロックするだけでなく、違反した SQL をログから確認する機能もあります。ログの確認については次回の記事で触れる予定ですのでお楽しみに！