DBMS_CLOUD.EXPORT_DATAを使ってデータを移行する

 DBMS_CLOUD.EXPORT_DATAを呼び出すことにより、表に含まれるデータをオブジェクト・ストレージに保存することができます。オブジェクト・ストレージに保存したデータをデータベースに読み込むことによって、データを移行してみます。

オブジェクト・ストレージにバケットを作成する

データのエクスポート先とするバケットをオブジェクト・ストレージに作成します。すでに作成済みのバケットがあれば、それを使ってもかまいません。

オブジェクト・ストレージとアーカイブ・ストレージのバケットを開きます。

バケットの作成をクリックします。バケットを作成するコンパートメントを覚えておきます。以下のスクリーンショットではMyAPEXDomainとなっています。

バケット名はDataExchangeとしました。それ以外はデフォルトの設定を変更しません。デフォルト・ストレージ層は標準です。作成をクリックします。

バケットが作成されます。

作成されたバケットのURLを調べるために、ファイルをアップロードします。バケットDataExchangeのリンクをクリックし、詳細画面を開きます。

詳細画面にネームスペースが表示されています。このネームスペースよりバケットのURLがわかる方は以降の作業は不要です。そうでない方は、ファイルをアップロードして確認しましょう。

リソースのオブジェクトを選択し、オブジェクトをひとつアップロードします。アップロードをクリックします。アップロードするファイルはなんでも構いません。小さなファイルが良いでしょう。

ドロワーが開くので、ファイルを選択しアップロードをクリックします。

選択したファイルが終了済になっていることを確認し、閉じるをクリックします。

オブジェクトの一覧にアップロードしたファイル(スクリーンショット上はreport.csv)が現れます。右端のハンバーガー・メニューを開いてオブジェクト詳細の表示を実行します。

URLパス(URI)のバケット名までの部分が、このバケットのURLになります。フォーマットとしては以下になっています。

https://objectstorage.リージョン名.oraclecloud.com/n/ネームスペース/b/バケット名/

以上でバケットの準備は完了です。

リソース・プリンシパルを作成する

Autonomous Databaseからオブジェクト・ストレージのバケットを操作するためのクリデンシャルとして、リソース・プリンシパルを作成します。こちらのマニュアルの記述に従って作業を行います。

オブジェクト・ストレージへの操作を許可する対象を含める動的グループを作成します。対象の選択方法は色々とありますが、今回はAutonomous Databaseを許可する対象とします。

Autonomous DatabaseのOCIDを確認します。対象となるAutonomous Databaseの詳細画面を開き、OCIDをコピーします。

アイデンティティの動的グループを開きます。動的グループの作成をクリックします。

以下では、動的グループの名前をAllowDataExchangeGroupとしています。説明は、バケットDataExchangeの操作を許可と記述しています。

ルール1として、以下を設定します。resource.idとして操作を許可するAutonomous DatabaseのOCIDを指定します。

resource.id = 'ocid1.上記でコピーしたADBのOCID'

作成をクリックします。

動的グループAllowDataExchangeGroupが作成されました。

ポリシーを作成し、この動的グループに含まれるリソースから行われるオブジェクト・ストレージの操作を許可します。

アイデンティティのポリシーを開きます。ポリシーの作成をクリックします。

ポリシー名はAllowDataExchangePolicy、説明は動的グループと同じく、バケットDataExchangeの操作を許可と記述しています。ポリシー・ビルダーは手動エディタの表示をONにし、以下のポリシーを記載します。コンパートメント名MyAPEXDomainの部分は、それぞれの環境にあわせて置き換えます。

Allow dynamic-group AllowDataExchangeGroup to read buckets in compartment MyAPEXDomain

Allow dynamic-group AllowDataExchangeGroup to manage objects in compartment MyAPEXDomain

ポリシーが作成されます。これで、Oracle Cloudのコンソールで行う準備作業は完了です。

Autonomous Databaseのデータベース・アクションを開きます。

管理者ユーザーADMINでサインインし、開発のSQLを実行します。

リソース・プリンシパルを有効にします。DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPALを実行します。初回呼び出時は必ず引数なしで実行します。リソース・プリンシパルの所有者がユーザーADMINになります。

BEGIN

dbms_cloud_admin.enable_resource_principal();

END; 

有効にしたリソース・プリンシパルを確認します。ビューDBA_CREDENTIALSを検索します。クリデンシャル名としてOCI$RESOURCE_PRINCIPALが見つかれば、有効になっています。

select * from dba_credentials

DBMS_CLOUD.EXPORT_DATAを実行する

有効になったリソース・プリンシパルを使って、DBMS_CLOUD.EXPORT_DATAを呼び出してみます。file_uri_listとして与えるURIのリージョンとネームスペースは環境に合わせて置き換えます。スキーマAPEXDEVに表EMPがあることを前提として、queryにselect * from apexdev.empを与えています。

BEGIN

DBMS_CLOUD.EXPORT_DATA(

credential_name =>'OCI$RESOURCE_PRINCIPAL',

file_uri_list =>'https://objectstorage.リージョン.oraclecloud.com/n/ネームスペース/b/DataExchange/o/emp',

format => json_object('type' value 'json'),

query => 'SELECT * FROM apexdev.emp'

);

END;

/

オブジェクト・ストレージのバケットDataExchangeの内容を確認します。file_uri_listに与えた接頭辞のempに日付の情報が付いて、拡張子がjsonとなっているオブジェクトが作成されていることが確認できます。

右端のハンバーガー・メニューを開き、オブジェクトをダウンロードします。ファイルが作成されるので、その内容を確認します。

ファイルの内容は改行で区切られたJSON形式であることが確認できます。

{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"1981-11-17T00:00:00","SAL":5000,"COMM":null,"DEPTNO":10}

{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-05-01T00:00:00","SAL":2850,"COMM":null,"DEPTNO":30}

{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-06-09T00:00:00","SAL":2450,"COMM":null,"DEPTNO":10}

{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-04-02T00:00:00","SAL":2975,"COMM":null,"DEPTNO":20}

{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"1982-12-09T00:00:00","SAL":3000,"COMM":null,"DEPTNO":20}

{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"1981-12-03T00:00:00","SAL":3000,"COMM":null,"DEPTNO":20}

{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","MGR":7902,"HIREDATE":"1980-12-17T00:00:00","SAL":800,"COMM":null,"DEPTNO":20}

{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-20T00:00:00","SAL":1600,"COMM":300,"DEPTNO":30}

{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-22T00:00:00","SAL":1250,"COMM":500,"DEPTNO":30}

{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-09-28T00:00:00","SAL":1250,"COMM":1400,"DEPTNO":30}

{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-09-08T00:00:00","SAL":1500,"COMM":0,"DEPTNO":30}

{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"1983-01-12T00:00:00","SAL":1100,"COMM":null,"DEPTNO":20}

{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"1981-12-03T00:00:00","SAL":950,"COMM":null,"DEPTNO":30}

{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"1982-01-23T00:00:00","SAL":1300,"COMM":null,"DEPTNO":10

管理者であるADMINではなく、APEXのワークスペースからも実行できるようします。APEXのスキーマがAPEXDEVであることを前提として、以下のコマンドを実行します。リソース・プリンシパルをAPEXDEVで利用可能にします。

BEGIN

dbms_cloud_admin.enable_resource_principal(

username => 'APEXDEV'

);

END;

パッケージDBMS_CLOUDの実行権限も付与します。

grant execute on dbms_cloud to apexdev;

不思議なことにビューDBA_CREDENTIALSを検索しても、usernameがAPEXDEVとなっているリソース・プリンシパルは見つかりません。そのため、管理者ユーザー以外のユーザーに対して、リソース・プリンシパルが有効になっているかどうかを確認する方法がありません。

APEXのSQLコマンドからDBMS_CLOUD.EXPORT_DATAを実行してみます。

バケットDataExchangeに含まれているオブジェクトが増えていることが確認できます。

オブジェクトをデータベースに取り込む

DBMS_CLOUD.COPY_COLLECTIONを呼び出して、オブジェクト・ストレージからJSONファイルを取り込むには、SODAを利用可能にする必要があります。ユーザーAPEXDEVにロールSODA_APPを割り当てます。

grant soda_app to apexdev;

オブジェクト・ストレージのデータを読み出す際に、一旦オブジェクトをディレクトリDATA_PUMP_DIRにダウンロードしているらしく、DATA_PUMP_DIRの操作権限がないとエラーが発生します。そのため、ユーザーAPEXDEVにDATA_PUMP_DIRの操作権限を与えます。

grant all on directory DATA_PUMP_DIR to apexdev;

以上で、ユーザーAPEXDEVでDBMS_CLOUD.COPY_COLLECTIONの実行ができるようになりました。以下を実行してみます。

begin

dbms_cloud.copy_collection(

collection_name => 'EmpCol',

credential_name => 'OCI$RESOURCE_PRINCIPAL',

file_uri_list => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/ネームスペース/b/DataExchange/o/emp_1_日付.json',

format => json_object('unpackarrays' value 'true')

);

end;

コレクションEmpColが作成されました。作成されたSODAコレクション"EmpCol"の内容を確認してみます。

select json_object(json_document) from "EmpCol";

表にロードするには、SODAコレクションよりJSONのデータを読み出し、INSERT文を発行する必要がありそうです。

DBMS_CLOUD.COPY_DATAを使ってJSONを直接表に読み込むことができるのかは不明です。マニュアルには記載が見つかりませんでした。DBMS_CLOUD.CREATE_EXTERNAL_TABLEについても同様に、改行区切りのJSONを元にして外部表を作る方法は見つかりませんでした。

おまけ

色々と操作がうまくいかず、途中で諦めてオブジェクト・ストレージを操作するPL/SQL SDKを使ってコードを書きました。

• オブジェクト・ストレージのPL/SQL SDKの使い方については、こちらの記事で紹介しています。
• OCIのSDKを呼び出すのに必要なクリデンシャルの作り方については、こちらの記事で紹介しています。
• BLOBに保存されているNDJSONを読み出す方法は、こちらの記事で紹介しています。
• 表の内容をNDJSON形式でBLOBに保存する方法は、こちらの記事で紹介しています。

以上の記事に含まれているコードを組み合わせています。

任意のSQLの検索結果をNDJSON形式にして、オブジェクト・ストレージに保存するコードのサンプルが以下になります。

declare

l_source_sql varchar2(4000);

l_sql varchar2(800);

l_put_response dbms_cloud_oci_obs_object_storage_put_object_response_t;

l_status_code integer;

l_blob blob;

plsql_sdk_error exception;

l_line varchar2(32767);

l_raw raw(32767);

l_len integer;

type t_ndjson is ref cursor;

c_ndjson t_ndjson;

begin

-- NDJSON形式で出力するSELECT文を記述する。

l_source_sql := 'select * from emp';

-- 一行ずつJSON形式で取り出し、BLOBに書き込む。

l_sql := 'select json_object(*) as l from (' || l_source_sql || ')';

dbms_lob.createtemporary(l_blob, TRUE, dbms_lob.session);

dbms_lob.open(l_blob, dbms_lob.lob_readwrite);

open c_ndjson for l_sql;

loop

fetch c_ndjson into l_line;

exit when c_ndjson%notfound;

l_line := l_line || chr(10); -- 改行の追加

l_raw := utl_raw.cast_to_raw(l_line);

l_len := utl_raw.length(l_raw);

dbms_lob.writeappend(l_blob, l_len, l_raw); -- BLOBに追記

end loop;

close c_ndjson;

dbms_lob.close(l_blob);

-- 検索結果をすべてBLOBに書き込んだので、Object Storageにアップロードする。

l_put_response := dbms_cloud_oci_obs_object_storage.put_object

(

namespace_name => 'ネームスペース'

, bucket_name => 'バケット'

, object_name => 'オブジェクト名'

, content_type => 'application/json'

, put_object_body => l_blob

, region => 'リージョン'

, credential_name => 'クリデンシャル'

);

l_status_code := l_put_response.status_code;

-- status 200で書き込み成功。

dbms_output.put_line('status = ' || l_status_code);

end;

オブジェクト・ストレージからNDJSON形式のデータを取得し、パースして表に保存するコードのサンプルです。オブジェクト・ストレージ上のデータが、サンプル・データセットに含まれる表EMPであると仮定して、その内容を表EMP_NEWに保存しています。

declare

l_response dbms_cloud_oci_obs_object_storage_get_object_response_t;

l_status_code number;

l_blob blob;

plsql_sdk_error exception;

C_NL constant raw(1) := utl_raw.cast_to_raw(chr(10));

l_current integer;

l_start integer := 1;

l_line varchar2(32767);

l_json json_object_t;

begin

-- オブジェクト・ストレージから改行区切りJSONのデータを取り込む。

l_response := dbms_cloud_oci_obs_object_storage.get_object(

namespace_name => 'ネームスペース'

,bucket_name => 'バケット'

,object_name => 'オブジェクト名'

,region => 'リージョン'

,credential_name => 'クリデンシャル'

);

l_status_code := l_response.status_code;

if l_status_code != 200 then

raise plsql_sdk_error;

end if;

l_blob := l_response.response_body;

---

while true

loop

-- 改行位置を見つける。

l_current := dbms_lob.instr(l_blob, C_NL, l_start);

-- 一行を取り出す。

l_line := utl_raw.cast_to_varchar2(

dbms_lob.substr(l_blob, (l_current - l_start), l_start)

);

-- 改行が見つからなければ終了。

-- ファイルの最終行でも改行がある - いきなりEOFにはならないのが前提。

exit when (l_current = 0);

if l_line is not null then

l_json := json_object_t.parse(l_line);

-- INSERT文はデータの投入先に合わせて変更する。

execute immediate

'insert into emp_new(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(:1,:2,:3,:4,:5,:6,:7,:8)'

using l_json.get_number('EMPNO'), l_json.get_string('ENAME'), l_json.get_string('JOB'),

l_json.get_string('MGR'), l_json.get_date('HIREDATE'), l_json.get_number('SAL'),

l_json.get_number('COMM'), l_json.get_number('DEPTNO');

-- dbms_output.put_line(l_json.to_string);

end if;

-- 次の行の処理へ移る。

l_start := l_current + 1;

end loop;

end;

以上でDBMS_CLOUD.EXPORT_DATAの使い方の紹介は終了です。

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完

DATA_PUMP_DIRにあるファイルの内容を表示する

 Autonomous Databaseでのエクスポート処理がうまくいかず、色々と対応していたところ、ディレクトリのDATA_PUMP_DIR以下にログ・ファイルが出力されているようでした。

select * from dbms_cloud.list_files('DATA_PUMP_DIR');

出力されているファイルの内容を確認するために、簡単なスクリプトをPL/SQLで書いてみました。ファイルの拡張子が.logであるファイルをすべて表示しています。

DECLARE

log_text clob;

BEGIN

for r in (

select * from dbms_cloud.list_files('DATA_PUMP_DIR')

where object_name like '%.log' order by created

)

LOOP

log_text := to_clob(bfilename('DATA_PUMP_DIR', r.object_name));

dbms_output.put_line('---- BEGIN ' || r.object_name || ' ----');

dbms_output.put_line(log_text);

dbms_output.put_line('---- END ' || r.object_name || ' ----');

end loop;

end;

ファイルの削除に使ったスクリプトです。

BEGIN

for r in (

select * from dbms_cloud.list_files('DATA_PUMP_DIR')

where object_name like 'report.csv' order by created

)

LOOP

dbms_cloud.delete_file('DATA_PUMP_DIR', r.object_name);

dbms_output.put_line('DELETED ' || r.object_name);

end loop;

end;

何かの参考になれば幸いです。

完

Vanity URLからの管理ツール、開発ツールのアクセスをブロックする

 Oracle Autonomous Databaseのプロダクト・マネージャTodd Bottgerのブログ記事Oracle Autonomous Database Vanity URLs Part 2: Blocking Developer and Administrator Toolsの内容を確認してみました。

確認に使用した環境は元記事とは異なり(プライベート・エンドポイントでADBを構成するには費用がかかるため)、こちらの記事で作成した環境を使っています。

サンプル・アプリケーションを準備する

Oracle APEXの環境およびワークスペースが作成された直後を想定していますが、すでにワークスペースにアプリケーションが作成済みであれば、それをサンプルとして使うこともできます。その場合は、以下のサンプル・アプリケーションの作成作業は実施不要です。

今のところVanity URL経由でも管理ツールや開発ツールにアクセスできますが、Oracle CloudのコンソールからOracle APEXにアクセスして作業を進めます。

テストに使うためのアプリケーションを作成します。SQLワークショップのユーティリティに含まれるサンプル・データセットを開きます。

EMP/DEPTをインストールし、アプリケーションの作成までを実行します。

アプリケーション・ビルダーを開き、作成されたアプリケーションを確認します。

アプリケーションのプロパティである簡易URLがデフォルトでONであるため、アプリケーションにアクセスするURLにはアプリケーションの別名が含まれます。アプリケーションの別名を確認するため、アプリケーション・プロパティの編集画面を開きます。

アプリケーションの別名を確認します。アプリケーションの別名に日本語が使用されている場合は、英数字と一部の記号（ハイフンなど）に限定するように別名を変更します。今回の例ではサンプル・データセットのEMP/DEPTを日本語でインストールしています。そのため別名がデモ - 従業員 / 部門となっています。これはDEMONSTRATION-EMP-DEPTに変更します。

作成したアプリケーションがVanity URL経由でアクセスできることを確認します。URLは以下の形式に従います。

https://Vanity URLとなるホスト名/ords/r/<ワークスペース名>/<アプリケーション別名/

EMP/DEPTのアプリケーションでは以下になります。ワークスペース名がAPEXDEVであると仮定しています。

https://<ホスト名>/ords/r/apexdev/demonstration-emp-dept/

以上で事前作業は完了です。これ以降、ロード・バランサの設定を行います。

デフォルトのアプリケーションを変更する

Oracle APEXのルートとなるURLにアクセスすると、開発ツールへのサインイン画面が開きます。以下のようなURLにアクセスします。

https://<ホスト名>/ords/

Vanity URLを構成して外部にサービスを公開している場合、開発ツールへのサインイン画面がデフォルトというのは望ましくありません。本来はポータルとなるようなアプリケーションを作成し、そのアプリケーションをデフォルトのアプリケーションとすべきですが、今回はEMP/DEPTのアプリケーションをデフォルトにしてみます。

Oracle Cloudのコンソールより、ネットワーキングのロード・バランサを開きます。作成済みのロード・バランサを開きます。以前の記事の通りに作業を行なっていると、名前はapexlbになります。

ロード・バランサの詳細画面より、リソースのルール・セットを選択します。作成済みのルール・セットの一覧が表示されます。ルール・セットの作成をクリックします。

ドロアーとしてルール・セットの作成が開きます。名前はADBPublicAccessとします。URLリダイレクト・ルールの指定にチェックを入れると、URLリダイレクト・ルールの設定項目が開きます。

ソース・パスを/ords/とし、一致タイプに接尾後一致を選択します。URLが/ords/で終了しているときに、このルールが適用されます。パスに/ords/r/apexdev/demonstration-emp-dept/、問合せを空白にし、レスポンス・コードは302 - Foundを選択します。

作成をクリックします。

ブラウザが/ords/にアクセスすると、ロード・バランサは/ords/r/apexdev/demonstration-emp-dept/へリダイレクトするよう応答します。

ステータスが成功になったら、ダイアログを閉じます。

ルール・セットが作成されたことを確認します。この状態では、まだ作成したルール・セットは適用されていません。

元記事ではルール・セットの追加設定を継続し、最後にリスナーにルール・セットを適用しています。設定の効果を感じないと作業を継続する気持ちが減るので、この記事ではとりあえず現状でリスナーにルール・セットを適用します。

リスナーを開いて、作成したルール・セットをリスナーに適用します。その後、ルール・セットが有効になります。リスナーの編集を実行します。

リスナーの編集のドロワーが開くので、ルール・セットの追加ルール・セットをクリックします。

先ほど作成したルール・セットADBPublicAccessをルール・セットに含め、変更の保存をクリックします。

ステータスが成功になったらダイアログを閉じます。

適用されたルール・セットを確認するために、先ほどと同じURLにアクセスします。設定したアプリケーション（今回の例ではデモ - 従業員 / 部門のアプリケーション）が開きます。

APEXのツールをブロックする

APEXの管理ツールには、以下のURLでアクセスできます。

https://<ホスト名>/ords/apex_admin

外部に公開しているURLから管理ツールや開発ツールの画面が開けるのは望ましくありません。Vanity URLからの、これらのツールのアクセスをブロックします。

元記事によると、Oracle APEXのインスタンス・パラメータRESTRICT_DEV_HEADERとしてヘッダー名を設定すると、そのヘッダーを含んだHTTPリクエストによるAPEXの管理ツールおよび開発ツールのアクセスが制限される、という機能があるとのことです。

マニュアルのAvailable Parameter Valueに記載のないパラメータなのですが、オラクルのプロダクト・マネージャが書いた記事に載っている機能なので問題なく使用できるでしょう。

以下のスクリプトにより、パタメータRESTRICT_DEV_HEADERとしてADB-Public-Accessを設定します。

begin
    apex_instance_admin.set_parameter('RESTRICT_DEV_HEADER', 'ADB-Public-Access');
    commit;
end;
/

Oracle Cloudのコンソールからデータベース・アクションを開きます。

開発のSQLを開きます。

ワークシートに先ほどのスクリプトを貼り付け、スクリプトの実行をクリックします。PL/SQL procedure successfully completed.と表示されると設定完了です。

以下のスクリプトを同様に実行すると、設定済みのRESTRICT_DEV_HEADERを確認できます。

begin
    dbms_output.put_line(apex_instance_admin.get_parameter('RESTRICT_DEV_HEADER'));
end;
/

ロード・バランサを経由したリクエストすべてに、HTTPヘッダーとしてADB-Public-Accessを追加します。

先ほど作成したルール・セットADBPublicAccessの詳細画面を開きます。編集をクリックします。

リクエスト・ヘッダー・ルールの指定にチェックを入れると、リクエスト・ヘッダー・ルールの設定画面が開きます。

アクションとしてリクエスト・ヘッダーの追加を選択し、ヘッダーにRESTRICT_DEV_HEADERとして設定済みのADB-Public-Accessを指定し、値は1とします。この設定によりロード・バランサを経由するリクエストにHTTPヘッダーADB-Public-Accessが追加されます。

変更の保存をクリックします。

ステータスが成功になったら、先ほどのURLにアクセスしてみます。

先ほどと同じURLで管理ツールにアクセスすると、404 Not Foundのエラーが発生します。開発ツールも同様にブロックされます。

データベース・アクションをブロックする

データベース・アクションは以下のURLからアクセスします。

https://<ホスト名>/ords/sql-developer

サインイン後のURLは以下になります。

https://<ホスト名>/ords/<ユーザー名>/_sdw/

データベース・アクションへのアクセスをブロックするために、ルール・セットADBPublicAccessにリダイレクト・ルールを追加します。ルール・セットの編集画面を開き、別のURLリダイレクト・ルールをクリックします。

ソース・パスとして/_sdw/を指定します。一致タイプは接尾語一致です。リダイレクト先のパスは/ords/blockedとします。この宛先は存在しないため、ORDSは404 - Not Foundを返します。レスポンス・コードは302 - Foundを選択します。

変更の保存をクリックします。

変更が成功したら、データベース・アクションにサインインしてみます。ユーザー名およびパスワードの入力をしてサインインすると、以下のように404 Not Foundが発生します。

エラー・メッセージは、blockedという名前のプロシージャにアクセスできませんでした。となっており、設定した通りにデータベース・アクションへのアクセスがブロックされていることが確認できます。

REST対応SQLをブロックする

最初にREST対応SQLを有効にします。Oracle Cloudのコンソールからデータベース・アクションにアクセスし、管理のデータベース・ユーザーを開きます。

REST対応SQLを有効にするユーザーのハンバーガー・メニューを開き、RESTの有効化を実行します。この例ではAPEXDEVというユーザーのREST対応を有効にしています。

REST対応ユーザーをクリックすると、そのスキーマのオブジェクトに対してREST対応SQLを実行できるようになります。

スキーマAPEXDEVに表EMPがあれば、以下のcurlコマンドを実行することにより表EMPの内容を取り出すことができます。

curl -X "POST" "https://<ホスト名>/ords/apexdev/_/sql" --header "Content-Type: application/sql" --user APEXDEV:APEXDEVのパスワード --data $'SELECT * FROM emp'

(以下は出力行数を制限しています)

% curl -X "POST" "https://<hostname>/ords/apexdev/_/sql" --header "Content-Type: application/sql" --user APEXDEV:************* --data $'SELECT * FROM emp where rownum < 2'

{"env":{"defaultTimeZone":"UTC"},"items":[{"statementId":1,"statementType":"query","statementPos":{"startLine":1,"endLine":2},"statementText":"SELECT * FROM emp where rownum < 2","resultSet":{"metadata":[{"columnName":"EMPNO","jsonColumnName":"empno","columnTypeName":"NUMBER","precision":4,"scale":0,"isNullable":0},{"columnName":"ENAME","jsonColumnName":"ename","columnTypeName":"VARCHAR2","precision":50,"scale":0,"isNullable":1},{"columnName":"JOB","jsonColumnName":"job","columnTypeName":"VARCHAR2","precision":50,"scale":0,"isNullable":1},{"columnName":"MGR","jsonColumnName":"mgr","columnTypeName":"NUMBER","precision":4,"scale":0,"isNullable":1},{"columnName":"HIREDATE","jsonColumnName":"hiredate","columnTypeName":"DATE","precision":0,"scale":0,"isNullable":1},{"columnName":"SAL","jsonColumnName":"sal","columnTypeName":"NUMBER","precision":7,"scale":2,"isNullable":1},{"columnName":"COMM","jsonColumnName":"comm","columnTypeName":"NUMBER","precision":7,"scale":2,"isNullable":1},{"columnName":"DEPTNO","jsonColumnName":"deptno","columnTypeName":"NUMBER","precision":2,"scale":0,"isNullable":1}],"items":[{"empno":7839,"ename":"中島 亜希子","job":"社長","mgr":null,"hiredate":"1981-11-17T00:00:00Z","sal":5000,"comm":null,"deptno":10}],"hasMore":false,"limit":10000,"offset":0,"count":1},"response":[],"result":0}]}

% 

スキーマのパスワードといった認証は必要で誰でもSQLを実行できるわけではありませんが、公開されているサイトではREST対応SQLは不要な場合が多いでしょう。

REST対応SQLのアクセスをブロックします。ルール・セットADBPublicAccessの編集画面を開き、別のURLリダイレクト・ルールをクリックします。

ソース・パスを/_/sqlとします。それ以外はデータベース・アクションをブロックしたルールと同じ設定を行います。変更の保存をクリックします。

再度、REST対応SQLを実行してみます。通信内容を確認するため、-vオプションを追加します。

% curl -v -X "POST" "https://<hostname>/ords/apexdev/_/sql" --header "Content-Type: application/sql" --user APEXDEV:********* --data $'SELECT * FROM emp where rownum < 2'

Note: Unnecessary use of -X or --request, POST is already inferred.

*   Trying 158.101.94.209...

* TCP_NODELAY set

* Connected to www.**********.dev (158.101.94.209) port 443 (#0)

* ALPN, offering h2

* ALPN, offering http/1.1

* successfully set certificate verify locations:

*   CAfile: /etc/ssl/cert.pem

  CApath: none

* TLSv1.2 (OUT), TLS handshake, Client hello (1):

* TLSv1.2 (IN), TLS handshake, Server hello (2):

* TLSv1.2 (IN), TLS handshake, Certificate (11):

* TLSv1.2 (IN), TLS handshake, Server key exchange (12):

* TLSv1.2 (IN), TLS handshake, Server finished (14):

* TLSv1.2 (OUT), TLS handshake, Client key exchange (16):

* TLSv1.2 (OUT), TLS change cipher, Change cipher spec (1):

* TLSv1.2 (OUT), TLS handshake, Finished (20):

* TLSv1.2 (IN), TLS change cipher, Change cipher spec (1):

* TLSv1.2 (IN), TLS handshake, Finished (20):

* SSL connection using TLSv1.2 / ECDHE-RSA-AES128-GCM-SHA256

* ALPN, server accepted to use http/1.1

* Server certificate:

*  subject: CN=*******.dev

*  start date: Oct  4 00:00:00 2021 GMT

*  expire date: Jan  2 23:59:59 2022 GMT

*  subjectAltName: host "www.*********.dev" matched cert's "www.********.dev"

*  issuer: C=AT; O=ZeroSSL; CN=ZeroSSL RSA Domain Secure Site CA

*  SSL certificate verify ok.

* Server auth using Basic with user 'APEXDEV'

> POST /ords/apexdev/_/sql HTTP/1.1

> Host: www.apexugj.dev

> Authorization: Basic QVBF*********************zkyTg==

> User-Agent: curl/7.64.1

> Accept: */*

> Content-Type: application/sql

> Content-Length: 34

> 

* upload completely sent off: 34 out of 34 bytes

< HTTP/1.1 302 Moved Temporarily

< Date: Wed, 20 Oct 2021 06:34:34 GMT

< Content-Type: text/html

< Content-Length: 133

< Connection: keep-alive

< Location: https://www.*********.dev:443/ords/blocked

< 

<html>

<head><title>302 Found</title></head>

<body>

<center><h1>302 Found</h1></center>

<hr><center></center>

</body>

</html>

* Connection #0 to host www.apexugj.dev left intact

* Closing connection 0

ynakakoshi@yujis-macbook-pro Downloads % 

レスポンスとして302を受け取っていて、リダイレクト先であるLocationとして/ords/blockedが返されていることが確認できます。

以上で今回の記事は終了です。

Oracle APEXのシステム構成作業の参考になれば幸いです。

完

要塞(Bastion)を使ってプライベート・ネットワーク上のコンピュート・インスタンスに接続する

 要塞(Bastion)を使って、プライベート・ネットワークにあるコンピュート・インスタンスに接続する方法を記述します。

以下の記述では、コンパートメント名がMyAPEXDomain、仮想クラウド・ネットワークの名前もコンパートメント名と同じくMyAPEXDomain、その中に作成されているプライベート・ネットワークの名前がPrivate Subnet-MyAPEXDomainであると仮定します。記事中のそれらの名称は、それぞれの環境に合わせて読み直す必要があります。接続先となるコンピュート・インスタンスの名前はCMORDS1としています。

コンパートメント名と仮想クラウド・ネットワークに同じ名前MyAPEXDomainが付けられています。そのため、説明が若干わかりにくくなってしまいました。

要塞(Bastion)を作成する

Identity & Securityの要塞を開きます。要塞の作成をクリックします。

要塞名はBastion1とします。ネットワーキングの構成のMyAPEXDomainのターゲット仮想クラウド・ネットワーク(このMyAPEXDomainはコンパートメント名)としてMyAPEXDomain(このMyAPEXDomainはVCNの名前)、MyAPEXDomainのターゲット・サブネットとして、接続するコンピュート・インスタンスが配置されているサブネットを選択します。ここではPrivate Subnet-MyAPEXDomainを選択しています。CIDRブロック許可リストには0.0.0.0/0を指定します。とりあえず、ネットワークのどこからでも要塞に接続できるようにしています。

CIDRブロック許可リストとして0.0.0.0/0を入力した後にEnterを入力すると0.0.0.0/0の入力が確定します。

要塞の作成をクリックします。

Bastion1の状態がアクティブになると、要塞の完成です。

コンピュート・インスタンスへファイルをアップロードする

要塞(Bastion)を通して、ファイルをコンピュート・インスタンスへアップロードします。そのために、要塞にてSSHポート転送セッションを作成します。

Identity & Securityの要塞のページを開きます。作成済みの要塞Bastion1を開きます。

Bastion1にて、セッションの作成をクリックします。

 ドロワーが開きます。セッション・タイプとしてSSHポート転送セッションを選びます。セッション名はSession-年月日-時刻となるので、それはそのまま使います。作成したセッションの有効期間はデフォルトで３時間なので、セッション名から有効期間が分かります。ターゲット・ホストに接続する指定としてインスタンス名を使うように選択し、接続先となるコンピュート・インスタンスを指定します。今回はCMORDS1を指定しています。SSHのポート番号である22を転送先に選択します。

SSHキーの追加としてSSHキー・ファイルの選択を選び、コンピュート・インスタンスCMORDS1を指定または生成した公開鍵ファイル(スクリーンショットではssh-key-cmords1.key.pubというファイルを指定しています)をSSHキーとして指定します。

以上を設定して、セッションの作成をクリックします。

作成されたセッションが要塞の画面に一覧されます。

セッションの右端にあるハンバーガー・メニューを開き、SSHコマンドの表示を実行します。

SSH接続を確立するためのSSHコマンドが表示されます。クリップ・ボードにコピーし、ダイアログを閉じます。

コピーしたsshコマンドは以下のような形式になります。

ssh -i <privateKey> -N -L <localPort>:10.0.1.82:22 -p 22 ocid1.bastionsession.oc1.ap-tokyo-1.amaaaaaawzoefcia4dtbwg5kixkgv3ixgsupn2625puytgcsvk6dyrch27bq@host.bastion.ap-tokyo-1.oci.oraclecloud.com

このコマンドに含まれる<privateKey>と<localPort>の部分を置き換えます。要塞のOCIDやホスト名は適切な値が入っているので変更はしません。秘密キーのファイルは、セッションの作成時に与えた証明書(公開鍵)と対になる秘密キーになります(ここではssh-key-cmords1.keyとしています)。ローカル・ポートとして10022を使うことにすると、上記のコマンドは次のようになります。

% ssh -i ssh-key-cmords1.key -N -L 10022:10.0.1.82:22 -p 22 ocid1.bastionsession.oc1.ap-tokyo-1.amaaaaaawzoefcia4dtbwg5kixkgv3ixgsupn2625puytgcsvk6dyrch27bq@host.bastion.ap-tokyo-1.oci.oraclecloud.com

上記コマンドは実行したままにしておきます。

別のターミナルよりファイルのアップロードを実行します。sftpコマンドでローカルのポートである10022に接続し、putコマンドを実行します。以下の実行例ではWallet_APEXDEV.zipおよびapexugj.dev.zipというファイルをアップロードしています。

% sftp -i ssh-key-cmords1.key -P 10022 opc@localhost

Connected to localhost.

sftp> put Wallet_APEXDEV.zip

Uploading Wallet_APEXDEV.zip to /home/opc/Wallet_APEXDEV.zip

Wallet_APEXDEV.zip                            100%   21KB   1.3MB/s   00:00    

sftp> put apexugj.dev.zip

Uploading apexugj.dev.zip to /home/opc/apexugj.dev.zip

apexugj.dev.zip                               100% 6762   752.1KB/s   00:00    

sftp> exit

% 

以上で、ファイルがユーザーopcのホーム・ディレクトリにアップロードされました。

これ以上アップロードするファイルがなければ、ポート転送を行なっているsshコマンドは終了できます。

コンピュート・インスタンスへSSHで接続する

SSHでコンピュート・インスタンスに接続します。今度は要塞で管理対象SSHセッションを作成します。要塞のBastion1を開き、セッションの作成を実行します。

セッション・タイプとして管理対象SSHセッション、ユーザー名にopcを指定する以外は、先ほどのポート転送セッションと同様の設定を行い、セッションの作成をクリックします。

セッションが作成されたら右端のハンバーガー・メニューを開き、SSHコマンドのコピーを実行します。

以下のようなsshコマンドがコピーされます。

ssh -i <privateKey> -o ProxyCommand="ssh -i <privateKey> -W %h:%p -p 22 ocid1.bastionsession.oc1.ap-tokyo-1.amaaaaaawzoefciakq7z6smcwfodqn2ebveyeca7z7brmbn2dfgdmpox4dyq@host.bastion.ap-tokyo-1.oci.oraclecloud.com" -p 22 opc@10.0.1.82

<privateKey>の部分を秘密キーのファイル名に置き換え、sshコマンドを実行します。

% ssh -i ssh-key-cmords1.key -o ProxyCommand="ssh -i ssh-key-cmords1.key -W %h:%p -p 22 ocid1.bastionsession.oc1.ap-tokyo-1.amaaaaaawzoefciakq7z6smcwfodqn2ebveyeca7z7brmbn2dfgdmpox4dyq@host.bastion.ap-tokyo-1.oci.oraclecloud.com" -p 22 opc@10.0.1.82

The authenticity of host '10.0.1.82 (<no hostip for proxy command>)' can't be established.

ECDSA key fingerprint is SHA256:oTItIYbGj7E26UZlk2dcXFqcBeZ5GXGUVbJXyn+IkJY.

Are you sure you want to continue connecting (yes/no/[fingerprint])? yes

Warning: Permanently added '10.0.1.82' (ECDSA) to the list of known hosts.

Activate the web console with: systemctl enable --now cockpit.socket

[opc@cmords1 ~]$ 

初回接続時はfingerprintをknown_hostsファイルに追加するかどうか確認を求められます。yesを入力すると宛先のコンピュート・インスタンスに接続されます。

以上で要塞を使って使ってプライベート・ネットワーク上のコンピュート・インスタンスに接続する方法の記事は終了です。

Oracle APEXをOracle Cloud上で構成する際の参考になれば幸いです。

完