AIです。（Artificial Intelligence ではありません）
トロントは初夏にさしかかり、半袖で過ごせる日も多くなってきました。晴れているとインドア派な AI でさえ、でかけたくなるくらいのお散歩日和となります。実はトロントでは人だけではなく、カナダガン（カナダグース）も街を散歩していたりします。道端で遭遇するとびっくりするくらいには大きいですよ。カナダガン。
さて、前回に引き続き、Oracle Database 23ai の新機能を触っていきたいと思います。今回フィーチャーする機能は「SQL ファイアウォール」です！SQL ファイアウォールは、事前にユーザーごとの SQL の許可リストを作成し、許可リストにない SQL の実行をブロックしたりロギングしたりすることができる機能です。
■設定
今回は goat ユーザーに対して SQL ファイアウォールを設定します。設定は以下の流れで行います。
- SQL ファイアウォールの有効化
- キャプチャの作成・有効化
- SQL のキャプチャ
- キャプチャの無効化
- 許可リストの生成
- 許可リストの有効化
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 Y2.キャプチャの作成・有効化
続いて、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 ENABLED3.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 ClockSQL がキャプチャされたかをキャプチャ・ログから確認。先ほど実行した 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 DISABLED5.許可リストの生成
以下のプロシージャを実行して、既存のキャプチャ・ログから許可リストを生成します。
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 をログから確認する機能もあります。ログの確認については次回の記事で触れる予定ですのでお楽しみに！