AIです。（Artificial Intelligence ではありません）
トロントは夏まっさかり！AI も Brewery 巡りや Summerlicious とカナダの夏を楽しんでおります！※ Summerlicious とは何ぞや？という方は是非「カナダde FOOD ADVENTURE ⑨」の記事をご覧ください
さて、前回の宣言通り今回は 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 の新機能について掘り下げていく予定ですので、次回の記事をお楽しみに！