Oracle 23ai 新機能 – ブール・データ型, ブール式

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 の作り方にも影響しそうですね。

Oracle 23ai 新機能 – SQLファイアウォール[違反ログ編]

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