2021年4月1日木曜日

PL/SQL SDKのクリデンシャルを操作するアプリケーションの作成

 OCIの操作をPL/SQL SDKを使って行うアプリケーションを作り始めたのですが、DBMS_CLOUD.CREATE_CREDENTIALを呼び出してクリデンシャルを登録するのが思いのほか面倒でした。

エラーが発生したときに登録手順のミスなのか、その他に問題があるのか、色々と切り分けをするために条件の違うクリデンシャルを多数作ったり、削除と再作成を簡単に繰り返せるよう、アプリケーションを作ることにしました。

以下から、その作業ログを記述します。

最初にAPEXのワークスペース・スキーマに以下の権限を与えます。今のところPL/SQL SDKが使えるのはAutonomous Databaseのみ(もしかしたらDBCSも使えるかも - 未確認)だったはずなので、SQL Developer WebにADMINで接続してSQLを実行するのが早いでしょう。

grant execute on dbms_cloud to APEXワークスペース・スキーマ;
grant execute on dbms_cloud_oci_obs_object_storage to APEXワークスペース・スキーマ;
grant execute on dbms_cloud_oci_obs_object_storage_get_namespace_response_t to APEXワークスペース・スキーマ;
APEXのワークスペース・スキーマがAPEXDEVである場合の実行例は以下になります。


APEXのアプリケーション・ビルダーにサインインし、空のアプケーションを作成します。名前は任意ですが、今回はクリデンシャル管理としました。今のところPL/SQL SDKはNLS_LANGUAGEがAmerican以外では正常に動作しないようなので、言語英語(en)を選択します。以上の設定で、アプリケーションの作成を行います。


*) エラーについてはMOS NoteのList Compartments Through DBMS_CLOUD.SEND_REQUEST With HTTP GET Method Failed With Error ORA-20401: Authorization failed (Doc ID 2680726.1)に該当している模様です。

アプリケーションが作成されたら、ホーム・ページを開いて、登録済みのクリデンシャルをリストする対話モード・レポートを追加します。

Content Body以下でリージョンの作成を行い、新規に作成されたリージョンの識別タイトル登録済みクリデンシャルと入力し、タイプとして対話モード・レポートを選択します。ソース位置ローカル・データベースタイプSQL問合せを選択し、SQL問合せとして、以下を指定します。

select * from user_credentials


作成したページを実行してみます。すでに登録済みのクリデンシャルがあれば、対話モード・レポートに表示されます。


次にフォームのページを作成します。ページ・デザイナに戻り、作成メニューからページを実行します。


ページ・タイプコンポーネントを選択し、その中のフォームをクリックします。


フォームをクリックします。


ページ名クリデンシャルとします。ページ・モードモーダル・ダイアログを選択します。ページ番号はあとで参照するのでであることを確認し、そうでなければ2に変更します。に進みます。


ページ・タイプはモーダル・ダイアログなので、左メニューにナビゲーションとして現れる必要はありません。ナビゲーションのプリファレンスこのページとナビゲーション・メニュー・エントリを関連付けないを選択し、に進みます。


データ・ソースとしてローカル・データベースを選択し、ソース・タイプSQL問合せとします。SQL SELECT文を入力として、クリデンシャルの登録時に引数となる列の検索に限定したSELECT文を指定します。これ以外の引数は、フォームの作成後にページ・アイテムとして追加します。に進みます。

select credential_name, username from user_credentials


主キー列としてCREDENTIAL_NAME(Varchar2)を選択し、作成を実行します。


以上で、クリデンシャルの登録と削除を行うフォームが作成されました。クリデンシャルの更新を行うAPIはありません。ですので、このフォームの実行可能な操作から行の更新を除きます。フォーム・リージョンのAttributesを開き、チェックを外します。


ファンクションDBMS_CLOUD.CREATE_CREDENTIALの引数には、credential_name、user_ocid、tenancy_ocid、private_key、fingerprintがあります。この内、credential_nameとuser_ocidはそれぞれP2_CREDENTIAL_NAME、P2_USERNAMEとして、値の受け皿となるページ・アイテムが作成されています。それ以外のtenancy_ocid、private_key、fingerprintを指定するためのページ・アイテムを作成します。

それぞれ、P2_TENANCY、P2_PRIVATE_KEY、P2_FINGERPRINTとします。

最初にページ・アイテムP2_TENANCYを作成します。リージョンでページ・アイテムの作成を実行し、識別名前P2_TENANCYタイプテキスト・フィールドを選択します。ラベルTenancy OCIDを設定します。ソースセッション・ステートの保持リクエストごと(メモリーのみ)を選択します。できるだけ隠しておきたい値はセッション・ステート(つまりデータベース)に保存すべきではないし、保存する場合でも、セッション・ステートに暗号化された値を保存ONにすべきです。

次にページ・アイテムP2_FINGERPRINTを作成します。設定内容はP2_TENANCYと同じなので、P2_TENANCY上でコンテキスト・メニューを表示し、重複を実行します。作成されたページ・アイテムP2_TENANCY_1の名前P2_FINGERPRINTラベルFingerprintに変更します。


秘密鍵を入力するページ・アイテムP2_PRIVATE_KEYを作成します。リージョンでページ・アイテムの作成を実行し、識別名前P2_PRIVATE_KEY、複数行の入力になるため、タイプテキスト領域を選択します。ラベルPrivate Keyを設定します。ソースセッション・ステートの保持リクエストごと(メモリーのみ)を選択します。


また、すでに作成されているページ・アイテムも調整します。

ページ・アイテムP2_CREDENTIAL_NAMEは主キー列として定義されているため、タイプが非表示になっています。これを入力が可能なテキスト・フィールドに変更し、ラベルCredential Nameを指定します。


さらに削除時にクリデンシャル名が不用意に変更されないよう、読取り専用の設定を行います。タイプアイテムはNULLではないを選択し、アイテムP2_CREDENTIAL_NAMEを指定します。これでフォームを開いた時点で指定されているクリデンシャル名は、変更できなくなります。


ページ・アイテムP2_USERNAMEは、タイプテキスト・フィールドに変更し、ラベルをより適切なUser OCIDとします。


以上でクリデンシャルの登録に必要な引数を保持するページ・アイテムが作成されました。

これらのページ・アイテムを受け取って、実際にクリデンシャルの作成と削除を行うプロセスの設定を行います。リージョン・ソースはビューUSER_CREDENTIALSを参照していて、これは更新できません。そのため、フォームを処理するプロセスを選択し、設定ターゲット・タイプPL/SQL Codeに変更します。挿入/更新/削除するPL/SQLコードとして以下を記述します。秘密鍵を1行にする部分以外は、受け取ったページ・アイテムを引数としてプロシージャを呼び出しているだけの、簡単な処理です。
declare
    l_private_key varchar2(4000);
    l_end integer;
begin
    case
    when :APEX$ROW_STATUS = 'C' then
        -- 以下より、秘密鍵を一行にする。
        l_private_key := :P2_PRIVATE_KEY;
        -- MIIより前の文字を削除する。
        l_private_key := substr(l_private_key, instr(l_private_key, 'MII'));
        -- '-----END'以降の文字を削除する。見つからない場合は変更なし。
        l_end := instr(l_private_key, '-----END');
        if l_end > 0 then
            l_private_key := substr(l_private_key, 1, (l_end-1));
        end if;
        -- 文字列から CR(13) と LF(10) を取り除く。
        l_private_key := replace(l_private_key, CHR(10));
        l_private_key := replace(l_private_key, CHR(13));
        -- クリデンシャルを登録する。
        dbms_cloud.create_credential(
            credential_name => :P2_CREDENTIAL_NAME
            , user_ocid     => :P2_USERNAME
            , tenancy_ocid  => :P2_TENANCY
            , private_key   => :P2_PRIVATE_KEY
            , fingerprint   => :P2_FINGERPRINT
        );
    when :APEX$ROW_STATUS = 'D' then
        -- クリデンシャルを削除する。
        dbms_cloud.drop_credential(
            credential_name => :P2_CREDENTIAL_NAME
        );
    end case;
end;
その他、失われた更新の防止OFF行のロックOFFとします。


以上でクリデンシャルの作成と削除を行うフォームのページが完成しました。

対話モード・レポートから、作成したフォームを呼び出すリンクを追加します。 

対話モード・レポートのページを開き、登録済みクリデンシャルのリージョンでボタンの作成を実行します。識別ボタン名B_CREATEとし、ラベル作成とします。レイアウトボタン位置対話モード・レポートの検索バーの右とします。外観ホットONにします。動作アクションとしてこのアプリケーションのページにリダイレクトを選択します。


ターゲットタイプこのアプリケーションのページページはフォームのページであるを指定します。キャッシュのクリアにもを設定し、OKをクリックしターゲットの設定を完了します。


クリデンシャルの削除を行うため、対話モード・レポートにリンク列を追加します。Attributesを開き、リンク列としてカスタム・ターゲットへのリンクを選択します。 趣味の問題なので変更しなくても良いのですが、リンク・アイコンを写実的な鉛筆アイコンから、フラットなアイコンへ変更するため、<span class="fa fa-edit"></span>に変更しています。


ターゲットは作成と同様に、タイプこのアプリケーションのページページはフォームのページ番号のを指定します。アイテムの設定として、名前P2_CREDENTIAL_NAMEに値#CREDENTIAL_NAME#を渡すようにします。CREDENTIAL_NAMEが主キーの扱いなので、それ以外の値はフォームのリージョン・ソースから取得されます。キャッシュのクリアとしてを設定し、OKをクリックします。


以上で、登録されているクリデンシャルの一覧表示、クリデンシャルの新規作成および削除は実行できるようになりました。ページの保存して、アプリケーションを実行して確認します。

作成をクリックします。


フォームにCredential NameUser OCIDTenancy OCIDFingerprintPrivate Keyを入力し、Createをクリックします。


ここで設定する情報は秘密鍵を除いて、OCIのコンソールのアイデンティティ/ユーザー/ユーザーの詳細/APIキー構成ファイルの表示から参照することができます。


構成ファイルの表示を行うと、構成ファイルがプレビューされます。user、tenancy、fingerprintの情報を参照できます。


さて、フォームを閉じてレポートに戻っても、登録したクリデンシャルがリストされません。ページをリロードすると表示されます。これはレポートとフォームをそれぞれ独立で作成したときには、ページ作成ウィザードで同時に作成される場合とは異なり、フォームが閉じられたときにレポートをリフレッシュする動的アクションが作成されないためです。


レポートをリフレッシュする動的アクションを追加します。

対話モード・レポートのページをページ・デザイナで開き、左ペインで動的アクション・ビューを表示させます。ダイアログのクローズ上でコンテキスト・メニューを表示させ、動的アクションの作成を実行します。


作成した動的アクションの名前クリデンシャルの編集とし、タイミングイベントダイアログのクローズ選択タイプリージョンリージョンとして登録済みクリデンシャルを指定します。


Trueアクションを選択し、アクションリフレッシュ(最初からリフレッシュで作成されているはずです)、影響を受ける要素選択タイプリージョン、実際にリフレッシュされるリージョンとして登録済みクリデンシャルを指定します。


クリデンシャルを作成、削除してフォームを閉じると対話モード・レポートが更新されるようになりました。クリデンシャルの作成、削除を実際に行ってアプリケーションの動作を確認してみてください。

作成したクリデンシャルを使ってオブジェクト・ストレージのネームスペースを取得することで、クリデンシャルが正しく登録されていかどうか確認する機能を追加します。

クリデンシャルを選択して開くフォームから、OCIのPL/SQL SDKを呼び出すようにします。フォームをページ・デザイナで開きます。

静的コンテンツのリージョンをクリデンシャルの下に作成します。コンポーネント・ギャラリリージョンを選択し、静的コンテンツレイアウト・ビューにドラッグ&ドロップします。


作成したリージョンのタイトルクリデンシャルのテストとし、サーバー側の条件としてタイプアイテムはNULLではないを選択し、アイテムP2_CREDENTIAL_NAMEを選択します。これで、P2_CREDENTIAL_NAMEがNULLでない、つまりすでにクリデンシャルが登録されている場合にのみ、このリージョンが表示されます。


テストで発行するのはオブジェクト・ストレージのネームスペースを取得するファンクションDBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.GET_NAMESPACEです。このファンクションは引数としてクリデンシャル名とリージョンを受け取り、ネームスペースを返します。クリデンシャル名はP2_CREDENTIAL_NAMEから取得できるので、残りのリージョンの入力と、戻り値であるネームスペースを表示するページ・アイテムを作成します。

リージョンを入力するページ・アイテムP2_REGIONを作成します。リージョンのクリデンシャルのテスト上でコンテキスト・メニューを開き、ページ・アイテムの作成を実行します。新規に作成されたページ・アイテムの名前P2_REGIONとし、タイプテキスト・フィールドソースセッション・ステートの保持リクエストごと(メモリーのみ)を選択します。


続いてネームスペースを表示するページ・アイテムP2_NAMESPACEを作成します。 名前P2_NAMESPACEタイプ表示のみを選びます。ラベルNamespaceとし、設定ページの送信時に送信OFFにします。ソースセッション・ステートの保持リクエスト(メモリーのみ)を選択します。


PL/SQL SDKの呼び出しは動的アクションから行います。SAVEは使用することのないボタンなので、リージョンクリデンシャルのテストに移動し、設定を変更します。ボタン名B_GET_NAMESPACEに変更し、ラベルGet Namespaceとします。レイアウトボタン位置リージョンの下部とし、動作アクション動的アクションで定義を指定します。


ボタンをクリックしたときに、PL/SQLコードを実行しネームスペースを取得する動的アクションを作成します。ボタンB_GET_NAMESPACE上でコンテキスト・メニューを表示し、動的アクションの作成を実行します。作成した動的アクションの名前ネームスペースの取得とします。ボタン上で作成した動的アクションのタイミングはデフォルトで、イベントクリック選択タイプボタンボタンとしてB_GET_NAMESPACEとなりますが、一応、確認します。


続いてTrueアクションの設定を行います。識別アクションとしてサーバー側のコードを実行を選択します。設定言語PL/SQLで、PL/SQLコードとして以下を記述します。
declare
    response dbms_cloud_oci_obs_object_storage_get_namespace_response_t;
begin
    response := dbms_cloud_oci_obs_object_storage.get_namespace(
        compartment_id => null
        , region => :P2_REGION
        , credential_name => :P2_CREDENTIAL_NAME
    );
    if response.status_code != 200 then
        :P2_NAMESPACE := to_char(response.status_code, '9999');
    else
        :P2_NAMESPACE := response.response_body;
    end if;
end;
単にPL/SQL SDKを呼び出しているだけですが、呼び出しに失敗している場合はネームスペースの代わりにHTTPのステータスコードを返すようにしています。

送信するアイテムとしてP2_CREDENTIAL_NAMEP2_REGION戻すアイテムとしてP2_NAMESPACEを設定します。


以上でクリデンシャルのテストとして、オブジェクト・ストレージのネームスペースを取得する機能が追加されました。

アプリケーションを実行し、作成したクリデンシャルの編集アイコンをクリックしてフォームを開き、Get Namespaceのボタンをクリックします。ネームスペースが返されればクリデンシャルが正しく登録できています。


今回作成したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/ocicredentialmanager.sql

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