データベース・ユーザーごとに許可するSQLと接続元を定め、想定外のアクセスを制限する仕組みとしてSQL Firewall を前回の記事で扱いました。

本記事では、特定のDBユーザーを対象にSQL Firewallを構成し、許可リストにあるSQLと、ないSQLの動作を比較してみます。
そして、実行できていたSQLを許可リストから削除し、同じSQLでもブロックされるようになることを確認してみます。


0. はじめに

本記事で実施する内容は次のとおりです。

実施内容SQL Firewallの設定と動作確認
・SQL Firewallの有効化とSQLのキャプチャ
・許可リストの生成と適用
・許可とブロックの比較、違反ログの確認
・許可SQLの個別削除と動作確認
・許可リストとキャプチャの削除
作業時間の目安約 40 分
実行環境以下の環境を想定しています。
・Oracle AI Database 26ai FREE
・Oracle Linux 9
・CDB名: FREE
・PDB名: FREEPDB1
・サービス名: freepdb1
・HRサンプルスキーマv23.3
・クライアントツール: SQL*Plus
※ 本記事では、管理セッションAの検索結果をCSV形式で表示するため、SQL*Plusで`SET MARKUP CSV ON`を設定しています。
また、掲載する実行結果は、見やすさのために一部を省略または整形しています。

1. 環境の準備

1-1. データベースとHRスキーマを準備する

Oracle AI Database 26ai FreeとHRサンプルスキーマを準備します。
データベースの準備は、第7回記事の「0. はじめに」と「1-1. Oracle Database の準備」を参照ください。
また、HRサンプルスキーマの作成は、第5回記事の「1-2. サンプルスキーマの準備」を参照
ください。
すでにあるHRスキーマを上書きしないまま、本記事ではHR.EMPLOYEESとHR.JOB_HISTORYを読み取りにだけに使用します。

また、SQL Firewall は 26ai FREE で利用が可能ですが、商用環境の際はOracle AI Databaseのライセンス情報をご参照ください。

1-2. 接続ユーザーとクライアントを準備する

動作確認には、HR.EMPLOYEESとHR.JOB_HISTORYを参照できるSALES_APPユーザーを使用します。
検証用ユーザーを準備する際は、第14回の記事の「1-3. SALES_APP ユーザーの準備」も参照してください。
第14回の対象表はHR.EMPLOYEESですが、今回はHR.JOB_HISTORYへの参照権限も必要です。

grant select on HR.JOB_HISTORY to sales_app;

操作には二つの端末を使います。どちらもデータベース・サーバー上の同じOSユーザーでSQL*Plusを起動し、FREEPDB1へ接続できるようにしておきます。

セッション接続ユーザー用途
管理セッションASYS AS SYSDBA設定とログの確認
検証セッションBSALES_APP業務SQLを想定した動作確認

管理操作には、検証手順を簡潔にするためSYS AS SYSDBAを使用します。
この手順は、既存のSQL Firewall設定がない、破棄または復元できる専用の検証用PDBで行ってください。

今回のハンズオンでは、表への参照権限は変えずに、SQL Firewallの許可リストで実行できるSQLを変えます。

許可リストは、SQLと接続元の両方を照合しますが、ここでは同じ接続元から実行するSQLの違いを比較するのみに留めることにします


2. SQL Firewallを有効化する

管理セッションAで、SQL Firewallの状態を確認します。

SQL> set markup csv on
SQL> select * from DBA_SQL_FIREWALL_STATUS;
"STATUS"  ,"STATUS_UPDATED_ON"                  ,"EXCLUDE_JOBS"
"DISABLED","06-DEC-24 05.50.51.263690 AM +00:00","Y"

この出力では、STATUSがDISABLEDとなっており、SQL Firewallが無効になっています。
この場合は、次のコマンドで有効にし、状態を確認します。

EXEC DBMS_SQL_FIREWALL.ENABLE;

-- 有効化されたことを確認
SQL> select * from DBA_SQL_FIREWALL_STATUS;
"STATUS" ,"STATUS_UPDATED_ON"                  ,"EXCLUDE_JOBS"
"ENABLED","06-DEC-24 05.52.21.098865 AM +00:00","Y"

STATUSがENABLEDになれば、有効化は完了です。


3. SQLをキャプチャする

3-1. SALES_APPのキャプチャを開始する

管理セッションAで、SALES_APPのキャプチャを作成し、その場で収集を開始します。

BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE (
    username         => 'SALES_APP',
    top_level_only   => TRUE,
    start_capture    => TRUE
  );
END;
/

top_level_only => TRUEは、ユーザーが直接発行したSQLを対象にする指定です。
start_capture => TRUEにより、作成と同時にキャプチャを開始します。

3-2. 対象ユーザーでSQLを実行する

検証セッションBで、SALES_APPとして次のSQLを実行します。最後のSQLは、意図的に失敗させるものして実行してみます。
なお、ここでは大文字・小文字を混ぜた SQL も試すため、一部頭文字が大文字になっているものも含めています。

-- 以下のSQLを実行
Select * From hr.job_history;
select job_id from hr.job_history;
desc hr.job_history;
Select first_name From hr.employees where employee_id IN(102,200);
select first_name, email, job_id from hr.employees where job_id like 'SA_%';


-- 意図的に失敗するSQLを実行する
select jfiros from aaiorwae;

3-3. キャプチャを停止し、収集内容を確認する

管理セッションAへ戻り、キャプチャを停止します。

EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('SALES_APP');

続いて、キャプチャしたSQLを確認します。

SELECT USERNAME, COMMAND_TYPE, SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS;
SQL> set markup csv on
SQL> SELECT USERNAME, COMMAND_TYPE, SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS;
"USERNAME" ,"COMMAND_TYPE","SQL_TEXT"
"SALES_APP","DESCRIBE"    ,"DESCRIBE HR.JOB_HISTORY"
"SALES_APP","SELECT"      ,"SELECT FIRST_NAME,EMAIL,JOB_ID FROM HR.EMPLOYEES WHERE JOB_ID LIKE :""SYS_B_0"""
"SALES_APP","SELECT"      ,"SELECT * FROM HR.JOB_HISTORY"
"SALES_APP","SELECT"      ,"SELECT FIRST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE_ID IN (:""SYS_B_0"",:""SYS_B_1"")"
"SALES_APP","SELECT"      ,"SELECT DECODE (USER,:""SYS_B_0"",XS_SYS_CONTEXT (:""SYS_B_1"",:""SYS_B_2""),USER) FROM SYS.DUAL"
"SALES_APP","SELECT"      ,"SELECT DECODE (USER,:""SYS_B_0"",XS_SYS_CONTEXT (:""SYS_B_1"",:""SYS_B_2""),USER) FROM SYS.DUAL"
"SALES_APP","SELECT"      ,"SELECT JOB_ID FROM HR.JOB_HISTORY"


7 rows selected.

ここから従業員IDや'SA_%'などのリテラル値は、バインド変数の表記へ置き換わっていることが確認できます。
また、クライアントが内部で発行したSQLも含まれていますが、失敗させたAAIORWAEへのSQLは含まれていないことが分かります。

SQLシグネチャも確認します。

SELECT SQL_SIGNATURE, SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS;
SQL> SELECT SQL_SIGNATURE,  SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS;
"SQL_SIGNATURE"                                                   ,"SQL_TEXT"
"CE7BB58501B74B27A8E1839074BA3866D07C76F126DA7BC6C57B77DF6C55D4AC","DESCRIBE HR.JOB_HISTORY"
"68D80167ADB6D37A341214538593A79D192612A3261CD26E51201721028FFC47","SELECT FIRST_NAME,EMAIL,JOB_ID FROM HR.EMPLOYEES WHERE JOB_ID LIKE :""SYS_B_0"""
"D2955375D61F67FC60E03D3DD01FBD14A4FA0BEABC10610F5E3624575D07B119","SELECT * FROM HR.JOB_HISTORY"
"CE83846971309C2BD065492F7D870A0C980AB72022723B732E262DF160E1F14A","SELECT FIRST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE_ID IN (:""SYS_B_0"",:""SYS_B_1"")"
"8CD0E5550A8AF32553BDED7C77B8CC1FD103C51F438E11F1BC5F9CA315102794","SELECT DECODE (USER,:""SYS_B_0"",XS_SYS_CONTEXT (:""SYS_B_1"",:""SYS_B_2""),USER) FROM SYS.DUAL"
"8CD0E5550A8AF32553BDED7C77B8CC1FD103C51F438E11F1BC5F9CA315102794","SELECT DECODE (USER,:""SYS_B_0"",XS_SYS_CONTEXT (:""SYS_B_1"",:""SYS_B_2""),USER) FROM SYS.DUAL"
"C71BC97035DA56172F8F38C346D00906948E90E717ED309ADFD6B316BDD2101A","SELECT JOB_ID FROM HR.JOB_HISTORY"


7 rows selected.

SQL_SIGNATUREは、SQLの照合に使用する識別情報になっています。同じ内部SQLには同じシグネチャが付いていることも結果から分かります。


4. 許可リストを生成して適用する

4-1. キャプチャから許可リストを生成する

管理セッションAで、現在の許可リストを確認します。

select ALLOWED_SQL_ID, SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL;
SQL> select ALLOWED_SQL_ID, SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL;


no rows selected

初期状態では、許可リストは空になっています。そのため、キャプチャしたSQLログを使用して、許可リストを生成します。

EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('SALES_APP');

再度、許可リストを確認します。

select ALLOWED_SQL_ID, SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL;
SQL> select ALLOWED_SQL_ID, SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL;
"ALLOWED_SQL_ID","SQL_TEXT"
1               ,"SELECT DECODE (USER,:""SYS_B_0"",XS_SYS_CONTEXT (:""SYS_B_1"",:""SYS_B_2""),USER) FROM SYS.DUAL"
5               ,"SELECT FIRST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE_ID IN (:""SYS_B_0"",:""SYS_B_1"")"
2               ,"SELECT * FROM HR.JOB_HISTORY"
6               ,"SELECT FIRST_NAME,EMAIL,JOB_ID FROM HR.EMPLOYEES WHERE JOB_ID LIKE :""SYS_B_0"""
3               ,"DESCRIBE HR.JOB_HISTORY"
4               ,"SELECT JOB_ID FROM HR.JOB_HISTORY"


6 rows selected.

キャプチャログの7行に対して、許可リストには6件のSQLが登録されていますが、これは同じシグネチャを持つSQLがキャプチャログに重複していたためです。
ALLOWED_SQL_IDは、後で許可SQLを個別に削除するときに指定します。

4-2. 許可リストを有効にする

次の操作で、SALES_APPのSQLと接続元を許可リストに基づいて制限します。
この操作以後、登録されていないSQLや接続元はブロックされるようになります。

BEGIN
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
    username => 'SALES_APP',
    enforce  => DBMS_SQL_FIREWALL.ENFORCE_ALL,
    block    => TRUE
    );
END;
/

ENFORCE_ALLはSQLと接続元の両方を、block => TRUEは違反時にブロックすることを指定しています。


5. SQLの許可とブロックを比較する

5-1. 許可リストを確認する

管理セッションAで、登録されたSQLを確認します。

select ALLOWED_SQL_ID, SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL;
SQL> select ALLOWED_SQL_ID, SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL;
"ALLOWED_SQL_ID","SQL_TEXT"
1               ,"SELECT DECODE (USER,:""SYS_B_0"",XS_SYS_CONTEXT (:""SYS_B_1"",:""SYS_B_2""),USER) FROM SYS.DUAL"
5               ,"SELECT FIRST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE_ID IN (:""SYS_B_0"",:""SYS_B_1"")"
2               ,"SELECT * FROM HR.JOB_HISTORY"
6               ,"SELECT FIRST_NAME,EMAIL,JOB_ID FROM HR.EMPLOYEES WHERE JOB_ID LIKE :""SYS_B_0"""
3               ,"DESCRIBE HR.JOB_HISTORY"
4               ,"SELECT JOB_ID FROM HR.JOB_HISTORY"

5-2. 許可したSQLを実行する

検証セッションBで、許可リストに登録されたSQLを実行してみます。

SELECT JOB_ID FROM HR.JOB_HISTORY;

DESCRIBE HR.JOB_HISTORY
SQL> SELECT JOB_ID FROM HR.JOB_HISTORY;


JOB_ID
----------
AC_ACCOUNT
AC_ACCOUNT
...


SQL> DESCRIBE HR.JOB_HISTORY
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                               NOT NULL NUMBER(6)
START_DATE                                NOT NULL DATE
END_DATE                                  NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
DEPARTMENT_ID                                      NUMBER(4)

JOB_IDの検索結果と、JOB_HISTORY表の列定義が表示されています。

5-3. 許可していない構造のSQLを実行する

続いて、検索する列にEMPLOYEE_IDを追加したSQLを実行します。

SELECT JOB_ID, EMPLOYEE_ID FROM HR.JOB_HISTORY;
SQL> SELECT JOB_ID, EMPLOYEE_ID FROM HR.JOB_HISTORY;
SELECT JOB_ID, EMPLOYEE_ID FROM HR.JOB_HISTORY
                                *
ERROR at line 1:
ORA-47605: SQL Firewall violation
Help: https://docs.oracle.com/error-help/db/ora-47605/

この構造のSQLはキャプチャしていないため、許可リストに登録されていません。
HR.JOB_HISTORYへの参照権限があっても、SQL Firewallによって実行を拒否されます。

5-4. 違反ログを確認する

管理セッションAで、先ほどの違反を確認します。

select USERNAME, SQL_TEXT, IP_ADDRESS, CLIENT_PROGRAM, OS_USER from DBA_SQL_FIREWALL_VIOLATIONS;
SQL> set markup csv on
SQL> select USERNAME, SQL_TEXT, IP_ADDRESS, CLIENT_PROGRAM, OS_USER from DBA_SQL_FIREWALL_VIOLATIONS;
"USERNAME" ,"SQL_TEXT"                                     ,"IP_ADDRESS","CLIENT_PROGRAM"               ,"OS_USER"
"SALES_APP","SELECT JOB_ID,EMPLOYEE_ID FROM HR.JOB_HISTORY","127.0.0.1" ,"sqlplus@db-vm23ai (TNS V1-V3)","oracle"

列を追加したSQLが、SALES_APPの違反として記録されています。
IP_ADDRESS、CLIENT_PROGRAM、OS_USERから、実行元の情報も確認できます。


6. 許可SQLを削除して動作の変化を確認する

6-1. DESCRIBEのエントリーを確認して削除する

管理セッションAで、許可リストから削除するSQLのIDを確認します。

select ALLOWED_SQL_ID,SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL;
SQL> select ALLOWED_SQL_ID,SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL;
"ALLOWED_SQL_ID","SQL_TEXT"
1               ,"SELECT DECODE (USER,:""SYS_B_0"",XS_SYS_CONTEXT (:""SYS_B_1"",:""SYS_B_2""),USER) FROM SYS.DUAL"
5               ,"SELECT FIRST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE_ID IN (:""SYS_B_0"",:""SYS_B_1"")"
2               ,"SELECT * FROM HR.JOB_HISTORY"
6               ,"SELECT FIRST_NAME,EMAIL,JOB_ID FROM HR.EMPLOYEES WHERE JOB_ID LIKE :""SYS_B_0"""
3               ,"DESCRIBE HR.JOB_HISTORY"
4               ,"SELECT JOB_ID FROM HR.JOB_HISTORY"


6 rows selected.

DESCRIBE HR.JOB_HISTORYのALLOWED_SQL_IDは3になっています。
次のコマンドにて、このIDのエントリーを削除します。

お手元の環境でもID3が同じSQLを指していることを確認してから実行してください。

BEGIN
  DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL (
    username       => 'SALES_APP',
    allowed_sql_id => 3
    );
END;
/

再度、許可リストを確認します。

SQL> select ALLOWED_SQL_ID,SQL_TEXT from DBA_SQL_FIREWALL_ALLOWED_SQL;
"ALLOWED_SQL_ID","SQL_TEXT"
1               ,"SELECT DECODE (USER,:""SYS_B_0"",XS_SYS_CONTEXT (:""SYS_B_1"",:""SYS_B_2""),USER) FROM SYS.DUAL"
5               ,"SELECT FIRST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE_ID IN (:""SYS_B_0"",:""SYS_B_1"")"
2               ,"SELECT * FROM HR.JOB_HISTORY"
6               ,"SELECT FIRST_NAME,EMAIL,JOB_ID FROM HR.EMPLOYEES WHERE JOB_ID LIKE :""SYS_B_0"""
4               ,"SELECT JOB_ID FROM HR.JOB_HISTORY"

DESCRIBE HR.JOB_HISTORYのエントリーがなくなっていることを確認します。

6-2. 同じ操作を再実行する

検証セッションBで、先ほど成功した操作を再実行します。

-- SALES_APPユーザーで実行
SQL> DESCRIBE HR.JOB_HISTORY
ERROR:
ORA-47605: SQL Firewall violation

許可リストから削除すると実行できなくなることを確認しました。


7. クリーンアップ

最後に、今回作成したSALES_APPの許可リストとキャプチャを削除します。
管理セッションAのSYSユーザーで、FREEPDB1に接続した状態で実行してください。

まず、削除する設定が今回の検証で作成したもので、キャプチャが停止済み(DISABLED)であることを確認します。

SELECT USERNAME, STATUS FROM DBA_SQL_FIREWALL_ALLOW_LISTS
WHERE USERNAME = 'SALES_APP';

SELECT USERNAME, STATUS FROM DBA_SQL_FIREWALL_CAPTURES
WHERE USERNAME = 'SALES_APP';

次の操作で、SALES_APPへの制限を解除し、許可リストとキャプチャを削除します。
キャプチャの削除に伴い、収集したSQLのキャプチャログも削除されます。

EXEC DBMS_SQL_FIREWALL.DISABLE_ALLOW_LIST('SALES_APP');
EXEC DBMS_SQL_FIREWALL.DROP_ALLOW_LIST('SALES_APP');
EXEC DBMS_SQL_FIREWALL.DROP_CAPTURE('SALES_APP');

先ほどの二つの確認SQLを再実行し、どちらも行が返らなければ削除は完了です。HRスキーマとSALES_APPユーザー、違反ログ、統合監査証跡はここでは残しています。
なお、SQL Firewall全体も有効ではありますが、削除したSALES_APPの許可リストによる制限は適用されません。


8. まとめ

このハンズオンでは、SALES_APPのSQLをキャプチャし、許可リストを使って、同じ参照権限でも実行できるSQLが変わることを確かめました。ポイントは以下の点になります。

  • キャプチャして許可リストに登録したSQLは実行可能
  • 列を追加するなど、許可していない構造のSQLはブロックされ違反ログに記録
  • 許可SQLのエントリーを削除すると、先ほどまで実行できていた操作もブロック

このように SQL Firewall は、対象ユーザーの参照権限を変えずに、実行するSQLと接続元の範囲を制限できます。
許可リストはキャプチャしたSQLを基に生成するため、実際のワークロードに沿って、許可するSQLを整理できます。利用者ごとに参照できる行を分ける認可と組み合わせて、それぞれの制御が必要な要件を整理してみてください。