データベース・ユーザーごとに許可する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へ接続できるようにしておきます。
| セッション | 接続ユーザー | 用途 |
|---|---|---|
| 管理セッションA | SYS AS SYSDBA | 設定とログの確認 |
| 検証セッションB | SALES_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を整理できます。利用者ごとに参照できる行を分ける認可と組み合わせて、それぞれの制御が必要な要件を整理してみてください。
