Oracle 23ai 新機能 – SQLファイアウォール[設定・ブロック編]

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 をログから確認する機能もあります。ログの確認については次回の記事で触れる予定ですのでお楽しみに!

コーソルカナダのメンバー紹介 (YKさん)

こんにちは!
今回も GENYA がメンバー紹介していきます。

メンバー紹介3回目はYKさんです♪

さっそくですが自己紹介からお願いします!

YKと申します。
コーソルカナダには 2024 年 10 月に第二新卒枠で入社しました。
出身は大阪府です。前職は商社で勤務していました。

コーソルカナダを知ったきっかけは??

カナダで就職活動をしていたところ、カナダ在住の知人にコーソルカナダの存在を教えてもらったことがきっかけです。

入社を決めた理由は何でしょうか?

面談の際に非常に丁寧に対応していただいたこと、また社内の研修が充実しているところに魅力を感じ、入社を決めました。

僕も入社した時に研修の豊富さにびっくりした覚えがあります!

仕事での目標は?

製品はもちろん、OSやネットワークなども理解して
多面的なアプローチで物事を考えられるエンジニアになること
が目標です。

カナダチームに来てまだ2か月ですがすごく頑張り屋さんなのが伝わってきます。
お互いに頑張って成長しましょう♪

入社後6か月間、研修のために日本に滞在していたYKさん。
日本での思い出は何かありますか?

新潟にスキーに行ったことです。ちょうどその日が積雪280cmの大雪で
日本でこんなに雪が積もるものかと感動した覚えがあります。

280cm!? そのレベルの雪はトロントでも中々見ないからびっくりですね😲

なぜカナダに来ようと思ったのですか?

多文化、多国籍で様々なバックグラウンドに対して寛容なところが素敵だと思ったからです。

カナダに来て一番驚いたことは?

思っていた以上に多国籍で驚きました。
場所によっては英語よりも中国語のほうが通じる、なんてこともざらです。

カナダでの休日は何をしていますか?

日によりますが、近くのモールに行ったり、ジムで運動したりしています

趣味は何ですか?

趣味といえるほどのことではないですが
食べることが好きなので、飲み歩きやカフェ巡りが好きです。

食べることが好き。。
近いうちにカナダde FOOD ADVENTURE回に誘います😎

トロントのオススメスポットは?

High Parkは景色がよくておすすめです。
4月には満開の桜(しかもソメイヨシノ)が見られます。
私も4月末に High Park の桜を見に行っていました!

High Parkの桜を見に行ったYKさん

HighParkはトロントにある市立公園で日本から寄贈された桜を見ることができる場所として知られています。
僕も今年High Parkに桜を見に行っていました😆

トロント以外だとどんな所がオススメですか?

Pacific Mall という中華系の小さなお店が凝縮されたモールがおすすめです。
オフィスから20分ほど車で北上した、Markhamという町にあるのですが
日本含めアジアの製品が手に入るので重宝しています。

以上です!
連続メンバー紹介は一旦今回で最後になります。

今年で6周年を迎えるコーソルカナダは引き続きメンバー全員で突っ走っていくのでよろしくお願いします!

それではまた次回の更新でお会いしましょう★
Take it easy 🙂

P.S. トロントも暖かくなってきたので最近外出が増えています。
色々なイベント参加のブログ記事を作成する予定なのでお楽しみに!