投稿者: AI

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

最近は朝晩は冷え込み、秋が少しずつ近づいているのを感じます。雑貨屋さんに行けばハロウィングッズも見かけるようになり…ってちょっと早いような気もしますが笑

さて、今回は 23ai のエラーの変更について見ていきます！

■エラーの出力

Oracle Database 23ai の SQL*Plus から、エラーメッセージが親切になることを皆さまご存知でしょうか。実際に 19c と 23ai で SQL*Plus から ORA-1017 のメッセージを発生させて見比べてみましょう。

$ sqlplus AI/AI ★19c にログイン

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 4 06:45:15 2024
Version 19.7.0.0.0

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

ERROR:
ORA-01017: ユーザー名/パスワードが無効です。ログオンは拒否されました。


Enter user-name:

$ sqlplus AI/AI ★23ai にログイン

SQL*Plus: Release 23.0.0.0.0 - Production on 火 9月 3 17:46:19 2024
Version 23.4.0.24.05

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

ERROR:
ORA-01017: 資格証明が無効であるか、権限がありません。ログオンは拒否されました ヘルプ:
https://docs.oracle.com/error-help/db/ora-01017/★


ユーザー名を入力してください:

はい。23ai では URL が表示されていますね。

この URL を開き、該当のリリースを選択するとそのエラーの原因や取るべきアクションを確認することができます。

エラーの出力内容については、SET ERRORDETAILS で制御可能です。このパラメータを VERBOSE に設定するとエラーの原因、とるべきアクションが表示されるようになります。※デフォルトは ON です。

SET ERRORDETAILS { OFF | ON | VERBOSE }

SQL> conn AI/AI
ERROR:
ORA-01017: 資格証明が無効であるか、権限がありません。ログオンは拒否されました ヘルプ:
https://docs.oracle.com/error-help/db/ora-01017/


警告: Oracleにはもう接続されていません。

SQL> set errordetails VERBOSE
SQL> conn AI/AI
ERROR:
ORA-01017: 資格証明が無効であるか、権限がありません。ログオンは拒否されました ヘルプ:
https://docs.oracle.com/error-help/db/ora-01017/
Cause:  An invalid credential was provided when accessing the Oracle
        Database or you were not authorized to access this database.
Action: Depending on your authentication method, ensure that the correct
        credential is provided when logging in to Oracle Database.
        Retry your credential after checking it. If your credential is
        correct, you may not be authorized to access the database.
        You will need to contact your database administrator or
        identity management administrator to confirm that you are
        authorized to access the database.


警告: Oracleにはもう接続されていません。

■OERR コマンド

詳細なエラーの説明は SQL*Plus の OERR コマンドでいつでも確認することができるようになりました。こちらにも URL が載っていますね。

実際にエラーが発生していない状態でも、原因ととるべきアクションがわかるので調査に便利そう。

SQL> OERR ORA-1017
Message: "invalid credential or not authorized; logon denied"
ヘルプ: https://docs.oracle.com/error-help/db/ora-01017/
Cause:  An invalid credential was provided when accessing the Oracle
        Database or you were not authorized to access this database.
Action: Depending on your authentication method, ensure that the correct
        credential is provided when logging in to Oracle Database.
        Retry your credential after checking it. If your credential is
        correct, you may not be authorized to access the database.
        You will need to contact your database administrator or
        identity management administrator to confirm that you are
        authorized to access the database.

ちなみに OS 側から oerr コマンドを実行することもできます。実はこちらの機能は、10.2.0.5 からあったのだとか。（AIは初めて知りました…）書いてある内容はそう変わりませんが、URL はこちらでは出力されませんね。

$ oerr ORA 1017
01017, 00000, "invalid credential or not authorized; logon denied"
// *Cause:  An invalid credential was provided when accessing the Oracle
//          Database or you were not authorized to access this database.
// *Action: Depending on your authentication method, ensure that the correct
//          credential is provided when logging in to Oracle Database.
//          Retry your credential after checking it. If your credential is
//          correct, you may not be authorized to access the database.
//          You will need to contact your database administrator or
//          identity management administrator to confirm that you are
//          authorized to access the database.

■まとめ

いかがでしたでしょうか。今までは Oracle のエラーに遭遇したら、Database エラー・メッセージのマニュアルを開いて、該当するエラー番号のページを探して、エラーを検索して…といった作業をしていました。地味に工程が多い…。

23ai からは、OERR コマンドやエラーの URL から即座にエラーの概要を確認できるようになり、とても便利になる予感がしております！

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

最近スーパーでマイナーなお肉が売られているのを発見し、ちょっと興味がわいているこの頃です。カンガルー肉…エルク肉…どんなお味なんでしょうか…

さて、今回は 23ai から新たに追加された ブール・データ型やブール式について、見ていきます！

■ブール・データ型（BOOLEAN データ型）とは

表内に TRUE / FALSE で値を格納するデータ型です。このデータ型を使うと SQL 文でブール式を使った条件を指定することができるようになります。

また、TRUE / FALSE に変換可能な文字をブール・データ型の列に格納した場合、自動で TRUE / FALSE に変換されて格納されます。

■文字の自動変換

以下の文字列リテラルをブール・データ型の列に格納した場合、それぞれ以下のように TRUE/FALSE に変換されます。

TRUE                   FALSE
--------------         --------------
'true'                 'false'
'yes'                  'no'
'on'                   'off'
0以外の数値            '0'
't'                    'f'
'y'                    'n'

では、実際に表に値を格納してみましょう。

SQL> CREATE TABLE Woodcutter.bool_test (id number(6),value varchar2(100),bool_value boolean);

表が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(1,'TRUE','TRUE'); ★value(VARCHAR2データ型) と bool_value(BOOLEANデータ型)に同じ値を格納

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(2,'FALSE','FALSE');

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(3,'ON','ON');

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(4,'OFF','OFF');

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(5,'1',1);

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(6,'0',0);

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(7,'YES','YES');

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(8,'NO','NO');

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(9,'T','T');

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(10,'F','F');

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(11,'Y','Y');

1行が作成されました。

SQL> INSERT INTO Woodcutter.bool_test VALUES(12,'N','N');

1行が作成されました。


SQL> SELECT * FROM Woodcutter.bool_test;

        ID VALUE      BOOL_VALUE
---------- ---------- -----------
         1 TRUE       TRUE
         2 FALSE      FALSE
         3 ON         TRUE  ★それぞれの値に応じて TRUE / FALSE として格納されています。
         4 OFF        FALSE
         5 1          TRUE
         6 0          FALSE
         7 YES        TRUE
         8 NO         FALSE
         9 T          TRUE
        10 F          FALSE
        11 Y          TRUE
        12 N          FALSE

12行が選択されました。

表に記載の通りに ブール・データ型の列は、TRUE / FALSE に変換されました！

■ブール式を使用した SELECT

ブール・データ型の実装と併せて、SQL 内でブール式を使用することができるようになりました。
例えば、先ほど作成した表から BOOL_VALUE が TRUE の値のレコードのみ SELECT したい場合
以下の様に IS TRUE を使って条件を指定することができます。

SQL> SELECT * FROM Woodcutter.bool_test WHERE bool_value IS TRUE;

        ID VALUE      BOOL_VALUE
---------- ---------- -----------
         1 TRUE       TRUE
         3 ON         TRUE
         5 1          TRUE
         7 YES        TRUE
         9 T          TRUE
        11 Y          TRUE

6行が選択されました。

IS NOT TRUE を使うこともできます。

SQL> SELECT * FROM Woodcutter.bool_test WHERE bool_value IS NOT TRUE;

        ID VALUE      BOOL_VALUE
---------- ---------- -----------
         2 FALSE      FALSE
         4 OFF        FALSE
         6 0          FALSE
         8 NO         FALSE
        10 F          FALSE
        12 N          FALSE

6行が選択されました。

もちろん IS FALSE や IS NOT FALSE も利用可能です。

また、IS をつけなくてもブール・データ型は列名のみを条件として指定することが可能です。この場合、TRUE でないものを選択したい場合、NOT <ブール・データ型の列名>と指定します。

SQL> SELECT * FROM Woodcutter.bool_test WHERE bool_value; ★列名だけの指定

        ID VALUE      BOOL_VALUE
---------- ---------- -----------
         1 TRUE       TRUE
         3 ON         TRUE
         5 1          TRUE
         7 YES        TRUE
         9 T          TRUE
        11 Y          TRUE

6行が選択されました。

SQL> SELECT * FROM Woodcutter.bool_test WHERE NOT bool_value;

        ID VALUE      BOOL_VALUE
---------- ---------- -----------
         2 FALSE      FALSE
         4 OFF        FALSE
         6 0          FALSE
         8 NO         FALSE
        10 F          FALSE
        12 N          FALSE

6行が選択されました。

■ブール・データ型ではない列に対してブール式を使用

更に、ブール・データ型ではない列に対してもブール式で問合せをすることが可能です。その場合「■文字の自動変換」で記載した文字列に従って TRUE/FALSE が判断されます。

SQL> SELECT * FROM Woodcutter.bool_test WHERE value IS FALSE;

        ID VALUE      BOOL_VALUE
---------- ---------- -----------
         2 FALSE      FALSE
         4 OFF        FALSE
         6 0          FALSE
         8 NO         FALSE
        10 F          FALSE
        12 N          FALSE

6行が選択されました。

SQL> SELECT * FROM Woodcutter.bool_test WHERE value;

        ID VALUE      BOOL_VALUE
---------- ---------- -----------
         1 TRUE       TRUE
         3 ON         TRUE
         5 1          TRUE
         7 YES        TRUE
         9 T          TRUE
        11 Y          TRUE

6行が選択されました。

これはつまり、dba_users などの静的ディクショナリビューで Y/N や YES/NO で
データが格納されている列に対してもブール式で問合せができるということですね…！

SQL> desc dba_users
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(128)
 :
 COMMON                                             VARCHAR2(3)★


SQL> SELECT username,common FROM dba_users WHERE common;

USERNAME                  COM
------------------------- ---
SYS                       YES
SYSTEM                    YES
SYSRAC                    YES
:(一部割愛)
SYSKM                     YES
SYS$UMF                   YES
SYSDG                     YES

33行が選択されました。

■ブール・データ型に変換できないデータの場合

さて、ブール・データ型やブール式の特徴について見てきましたが、次はブール・データ型に変換できないデータが入っていた場合の挙動を確認します。

・ブール・データ型の列に変換できないデータを挿入した場合

SQL> INSERT INTO Woodcutter.bool_test VALUES(13,'AI','I');
INSERT INTO Woodcutter.bool_test VALUES(13,'AI','I')
                                                *
行1でエラーが発生しました。:
ORA-61800: 無効なブール・リテラル: I ヘルプ:
https://docs.oracle.com/error-help/db/ora-61800/

・ブール式で条件指定した列にブール・データ型に変換できないデータが入っていた場合

SQL> SELECT * FROM Woodcutter.bool_test;

        ID VALUE      BOOL_VALUE
---------- ---------- -----------
         1 TRUE       TRUE
         2 FALSE      FALSE
         3 ON         TRUE
         4 OFF        FALSE
         5 1          TRUE
         6 0          FALSE
         7 YES        TRUE
         8 NO         FALSE
         9 T          TRUE
        10 F          FALSE
        11 Y          TRUE
        12 N          FALSE
        13 AI         TRUE

13行が選択されました。

SQL> SELECT * FROM Woodcutter.bool_test WHERE value;
ERROR:
ORA-61800: 無効なブール・リテラル: AI ヘルプ:
https://docs.oracle.com/error-help/db/ora-61800/

レコードが選択されませんでした。

いずれも ORA-61800 が返されました。

■AND / OR 条件

ブール式での条件を二つ以上組み合わせて AND/OR 条件で絞ることもできます。

SQL> SELECT * FROM Woodcutter.bool_test2;

        ID BOOL_VALUE1 BOOL_VALUE2
---------- ----------- -----------
         1 TRUE        TRUE
         2 TRUE        FALSE
         3 FALSE       TRUE
         4 FALSE       FALSE


SQL> SELECT * FROM Woodcutter.bool_test2 WHERE bool_value1 AND bool_value2;

        ID BOOL_VALUE1 BOOL_VALUE2
---------- ----------- -----------
         1 TRUE        TRUE

SQL> SELECT * FROM Woodcutter.bool_test2 WHERE bool_value1 OR bool_value2;

        ID BOOL_VALUE1 BOOL_VALUE2
---------- ----------- -----------
         1 TRUE        TRUE
         2 TRUE        FALSE
         3 FALSE       TRUE

■TO_BOOLEAN 関数

また、ブール・データ型に変換可能なデータであれば、TO_BOOLEAN 関数を使ってブール・データ型として表示させることも可能です。

SQL> SELECT TO_BOOLEAN(1),TO_BOOLEAN('ON'),TO_BOOLEAN('F');

TO_BOOLEAN( TO_BOOLEAN( TO_BOOLEAN(
----------- ----------- -----------
TRUE        TRUE        FALSE


SQL> SELECT TO_BOOLEAN(1),TO_BOOLEAN('ON'),TO_BOOLEAN('F'),TO_BOOLEAN('I');
SELECT TO_BOOLEAN(1),TO_BOOLEAN('ON'),TO_BOOLEAN('F'),TO_BOOLEAN('I')
                                                      *
行1でエラーが発生しました。:
ORA-61800: 無効なブール・リテラル: I ヘルプ:
https://docs.oracle.com/error-help/db/ora-61800/

仮想列で TO_BOOLEAN 関数を使えば既存のデータを BOOLEAN データ型で表示させることも簡単にできます。

SQL> ALTER TABLE Woodcutter.bool_test ADD (fuction_value AS (TO_BOOLEAN(value)));

表が変更されました。

SQL> SELECT * FROM Woodcutter.bool_test;

        ID VALUE     BOOL_VALUE  FUCTION_VAL
---------- --------- ----------- -----------
         1 TRUE      TRUE        TRUE
         2 FALSE     FALSE       FALSE
         3 ON        TRUE        TRUE
         4 OFF       FALSE       FALSE
         5 1         TRUE        TRUE
         6 0         FALSE       FALSE
         7 YES       TRUE        TRUE
         8 NO        FALSE       FALSE
         9 T         TRUE        TRUE
        10 F         FALSE       FALSE
        11 Y         TRUE        TRUE
        12 N         FALSE       FALSE

12行が選択されました。

■おわりに

さて、ブール・データ型およびブール式について色々と検証してきましたがいかがでしょうか。

AI的には、ブール・データ型が実装されたことにより、今まで静的ディクショナリビューでTRUE/FALSE で表現できそうなデータに変更が出るのか気になっていましたが、特定の文字列であれば特に意識することなくブール型で条件を指定できることが驚きでした！

今後、調査で静的ディクショナリビューや v$ ビューを見るときの SQL の作り方にも影響しそうですね。

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 の新機能について掘り下げていく予定ですので、次回の記事をお楽しみに！