2020年12月30日水曜日

アクセス制御の実装サンプル解説(3) - ファセット検索

 こちらの記事の継続です。

ファセット検索ページの追加


アプリケーションにファセット検索のページを追加します。アプリケーション・ビルダーからページの作成を実行します。


ページ・タイプがコンポーネントに含まれるレポートをクリックします。


ファセット検索をクリックします。


ページ名は任意ですが、以下ではファセット検索を指定しています。次に進みます。


ナビゲーションのプリファレンスは新規ナビゲーション・メニュー・エントリの作成を選択します。新規ナビゲーション・メニュー・エントリはページ名と同一のファセット検索になるので、そのままにして、次へ進みます。


表/ビューの名前としてPAC_TASKS(表)を選択します。

それ以外はデフォルトの設定とします。データ・ソースはローカル・データベース、ソース・タイプは表、表示形式はレポートです。ファセットとして生成される列は、PROJECT_ID(number)、ASSIGNED_TO(number)、STATUS(varchar2)、COST(number)、BUDGET(number)の5つです。確認して作成をクリックします。画面が小さい場合、開くダイアログが小さく、ファセットとして選択される列の一覧が下部に隠れている場合があります。画面をスクロールすることで確認できます。


ページが作成されたら、実行して作成されたファセット検索のページを確認します。サインインする従業員は誰でも構いません。


ファセット検索のページが表示されます。保存されているデータの全件である73件がリストされています。


ページ作成ウィザードによってファセット検索がページとして作成される場合、ソースとなる表に参照制約が設定されていると、その参照先を共有コンポーネントにLOVとして作成した上で、レポートの列とファセットがLOVを元に表示するように自動的に構成されます。今回は表PAC_TASKSをソースとして指定しています。ページ作成ウィザードは、列PROJECT_ID(レポート上はProject)には表PAC_PROJECTSのPROJECT_NAMEの値を、列ASSIGNED_TOには表PAC_EMPLOYEESのEMPLOYEE_NAMEの値を表示するようにレポートを作成しています。

共有コンポーネントのLOVを開くと、PAC_EMPLOYEES.EMPLOYEE_NAMEとPAC_PROJECTS.PROJECT_NAMEの2つのLOVが作成されていることが確認できます。


以上でファセット検索のページの追加は完了しました。

検索結果のアクセス制限


ファセット検索のリスト表示には、リージョンとしてクラシック・レポートが使用されています。このリストされる内容を今回の要件である
  1. 管理者ロールを持っていれば、すべてのタスクが一覧される。
  2. 管理者以外は、自分が所属している部門のタスクのみが一覧される。
という条件で制限します。宣言的に認可スキームを設定するとリスト表示が変わる、といった機能はありません。SQLを記述することで制限を加えます。

ページ作成直後のファセット検索の検索結果を表示するリージョンのソースは、表PAC_TASKSが設定されています。


以下のWHERE句を設定することで、今回の要件を達成できます。
:G_IS_ADMINISTRATOR = 'Y'
or
assigned_to in (
    select employee_id from pac_employees where group_name = :G_GROUP_NAME
)

WHERE句の中で、あらかじめ設定したアプリケーション・アイテムを使うことでSQLの記述を簡単にしています。

さて、アプリケーション・アイテムを設定する際に、セッション・ステート保護は制限付き - ブラウザから設定不可とするように指示しています。

ブラウザから、これらのアプリケーション・アイテムを変更できると、管理者ロールなしで他部門のタスクを参照することができる穴があることになります。一般にSQLを文字列処理により生成することをせず、バインド変数を使用することでSQLインジェクション攻撃を防ぐことができる、と言われていますが、それだけではないことに注意が必要です。

先ほどのWHERE句の設定で要件は達成していますが、部門名がレポートに含まれていないので結果の確認がしにくいです。そのため、ソースの設定を以下のSQLに変更します。表PAC_EMPLOYEESを結合して、列GROUP_NAMEの表示を追加しています。

select PROJECT_ID,
       TASK_ID,
       TASK_NAME,
       ASSIGNED_TO,
       STATUS,
       START_DATE,
       END_DATE,
       COST,
       BUDGET,
       e.GROUP_NAME
from PAC_TASKS t join PAC_EMPLOYEES e 
    on t.assigned_to = e.employee_id
where :G_IS_ADMINISTRATOR = 'Y'
or
e.GROUP_NAME = :G_GROUP_NAME

結果を確認してみましょう。

管理者ロールを持つ従業員でサインインすると、73件全件、リスト表示されます。Group Nameも複数の部門がリストされていることが確認できます。

例えば、経理部の一般従業員でサインインすると、経理部のみの16件がリストされること確認できます。


これで、レポートのアクセス制御が実装されたことが確認できました。

コンポーネント毎のセキュリティ - 認可スキームの働き


ページ、リージョン、その他、ほとんどすべてのコンポーネントはセキュリティのプロパティを持っており、認可スキームを設定することができます。今回のファセット検索の制御には使用していません。では、いつ使うのか?という疑問はあるかと思います。

認可スキームを設定して動作を確認してみましょう。

最初にクラシック・レポートの列GROUP_NAMEに設定して効果を確認します。管理者ロールがない場合、部門つまりGROUP_NAMEは、サインインした従業員の部門に一致するので表示する必要がなく、管理者ロールを持った従業員のみに意味のある情報です。

列GROUP_NAMEの認可スキームを管理権限に設定します。


管理者ロールを持たない従業員でサインインし効果を確認すると、列GROUP_NAMEが表示されていないことが確認できます。管理者ロールがあれば、表示されます。



画面に表示されるコンポーネントにたいしての認可スキームの効果は、そのコンポーネントの表示/非表示です。サーバー側の条件でも同等の設定は可能ですが、認可スキームによって表示/非表示を決定できる場合は、そちらを使う方が合理的です。

ほとんど意味はありませんが、挙動をより理解するため、検索結果のリージョンの認可スキームを管理権限にしてみます。


管理者ロールのない従業員でサインインすると、検索結果のレポート自体が表示されなくなります。管理者ロールがあれば表示されます。


今後の作業に支障がでるので、こちらの設定は元に戻しておきましょう。

画面表示に関するコンポーネントへの認可スキームの効果は以上です。

表示ではなく、アプリケーションの操作に関するコンポーネントの場合は違った効果になります。

ファセット検索のページのプロパティに含まれる認可スキームを管理権限に設定し、確認してみます。


管理者ロールのない従業員がファセット検索のページをアクセスすると、以下のように権限が不十分です。というエラーが発生し、ファセット検索にアクセスできません。(アプリケーション・ビルダーを開いていない場合、技術情報の部分は表示されません。)


管理者ロールがある従業員であれば、アクセスが許可されます。このようにエラーが発生するとアプリケーションの操作ができなくなるので、基本的にはタブを閉じるかブラウザを終了し、再度、新規にセッションを開始しサインインし直す必要があります。

通常はこのようなエラーをアプリケーションの利用者に経験させるのは望ましくないため、保護されているページへのナビゲーションも同時に抑制することが望まれます。今回の例でいえば、サイド・メニューのファセット検索のエントリを非表示にします。

共有コンポーネントのナビゲーション・メニューからデスクトップ・ナビゲーション・メニューを開き、ファセット検索のエントリの、認可スキームを管理権限にします。


管理者ロールのない従業員でサインインすると、ファセット検索のメニュー自体が表示されないことが確認できます。


ナビゲーション・パスがなければ、保護されているページに対するアクセスは、本来許されていない直接URLを編集するといった方法によるので、エラーの発生によってタブを閉じる、ブラウザを終了するといった作業を要求することになるのは妥当な結果でしょう。

ファセット検索でのアクセス制御の実装についての説明は以上です。

次は対話モード・レポートとフォームを実装します。

検索結果のリージョンとファセット検索のページ、それと、ナビゲーション・メニューに設定した認可スキームは管理権限から、元の設定の無い状態- 選択 -または- 認可の必要なし -へ戻しておきましょう。

2020年12月29日火曜日

アクセス制御の実装サンプル解説(2) - データ・ローディング

こちらの記事の継続になります。 

元データとなるCSVファイルのダウンロード


以下のURLより、CSVファイルをダウンロードします。

https://apex.oracle.com/pls/apex/japancommunity/r/simcontents/download?id=Project_and_Tasks_ja.csv

Project, Task Name, Start Date, End Date, Status, Assigned To, Cost, Budgetの列が含まれています。以下はデータの一部ですが、全部で73行含まれます。


データ・ローディング・ウィザードの追加


ページ作成ウィザードを実行し、データ・ローディング・ウィザードのページ群をアプリケーションに追加します。

ページの作成を実行します。


データのロードをクリックします。


データ・ロード定義を新規作成します。定義名は任意ですが、ここではタスクとしています。所有者はそれぞれの環境で異なります。表名はPAC_TASKS(表)を選択します。一意列の列1として、TASK_ID(Number)を選択し、次に進みます。


トランスフォーメーション・ルールを追加します。CSVファイル中のStatus列はOpen、Closed、Pending、On-Holdと大文字と小文字で構成されていますが、表PAC_TASKSのSTATUS列はOPEN、CLOSED、PENDING、ON-HOLDで、大文字のみが登録可能です。ですので、取り込む際に大文字に変換するトランスフォーメーション・ルールを登録します。

トランスフォーメーション・ルールを作成するための列の選択としてSTATUSを選びます。ルール名は任意ですが、ここではSTATUSを大文字にすると設定しています。タイプとして大文字にするを選択し、トランスフォーメーションの追加をクリックします。


ルールの追加を確認し、次へ進みます。


CSVファイルに含まれるProject列はプロジェクト名ですが、表PAC_TASKSのPROJECT_IDは表PAC_PROJECTSのPROJECT_IDを参照する数値です。そのため、表ルックアップを設定することにより、PROJECT_NAMEをPROJECT_IDヘ変換します。

列の新規表ルックアップの追加として、PROJECT_ID(Number)を選択します。ルックアップ定義の設定項目が表示されるので、表名のルックアップとして、PAC_PROJECTS(表)、戻り列として、PROJECT_ID(Number)、アップロード列として、PROJECT_NAME(Varchar2)を指定し、ルックアップの追加をクリックします。


PROJECT_IDのルックアップが登録されているのを確認し、次にASSIGNED_TOについても同様に、人名から表PAC_EMPLOYEESのEMPLOYEE_IDを参照するようにルックアップを追加します。

列の新規表ルックアップの追加として、ASSIGNED_TO(Number)を選択します。ルックアップ定義の設定項目が表示されるので、表名のルックアップとして、PAC_EMPLOYEES(表)、戻り列として、EMPLOYEE_ID(Number)、アップロード列として、EMPLOYEE_NAME(Varchar2)を指定し、ルックアップの追加をクリックします。


追加されたASSIGNED_TOのルックアップを確認し、次に進みます。


ページ・モードをモーダル・ダイアログに変更し、次へ進みます。


ナビゲーションのプリファレンスは新規ナビゲーション・メニュー・エントリの作成を選びます。新規ナビゲーション・メニュー・エントリはデフォルトのデータのロードのまま、変更はしません。次に進みます。


「取消」ボタンでブランチするページ、ページへの「終了」ボタン・ブランチ、双方ともに、とりあえずホームであるページ1に設定します。設定項目はこれで全てなので、作成をクリックします。


これで、データ・ローディング・ウィザードを構成する4枚のページが作成されます。

データのロードを実行する前に、データ・ロード定義に必要な変更があります。共有コンポーネントのデータ・ロード定義を開きます。


直前に作成したデータ・ロード定義、タスクを開きます。


表ルックアップを参照します。リストに新しい値を挿入という列がありますが、PROJECT_IDはこの値がいいえになっています。ASSGINED_TOが参照する表PAC_EMPLOYEESにはすでに従業員情報が投入済みですが、表PAC_PROJECTSには、まだデータが投入されていません。表PAC_TASKSへのデータ・ロード時に表PAC_PROJECTSにデータが投入されるよう、この値をはいへ変更します。

列名のPROJECT_IDをクリックし、表ルックアップの定義を開きます。


新しい値を挿入をはいに変更し、変更の適用をクリックします。


必要な変更は以上です。

アプリケーションを実行し、データのロードを行います。

まだアクセス制御は実装していないので、ユーザーは誰を選択しても構いません。サインインをした後、サイド・メニューよりデータのロードを呼び出します。

インポート元として、ファイルをアップロード(カンマ区切り(*.csv)またはタブ区切り)を選択し、ファイル名として記事の最初に指示しているリンク先よりダウンロードしたProject_and_Tasks_ja.csvを設定します。1行目に列名があるのチェックを確認し、その下の詳細設定の使用にチェックを入れます。ファイルの文字セットの指定が現れるので、日本語(Shift JIS)を選択します。以上で次に進みます。


データと列のマッピングでソース列がPROJECTのみターゲット列がロードしないになっています。このターゲット列をPROJECT_ID(Number)に変更します。これ以外はCSVの一列目のソース列名と、表PAC_TASKSの列名が一致しているため、ターゲット列が適切に選択されます。ターゲット列がすべて適切に指定されていることを確認し、次へ進みます。


データ検証の結果を確認し、データのロードをクリックします。


挿入された行が73行であれば全行ロードが成功しています。終了をクリックします。


これでCSVファイルのアップロードが完了し、表PAC_TASKS、PAC_PROJECTSへデータがロードされました。データが準備できたので、次からはデータの操作を行う画面を実装していきます。

最初に実装するのは、ファセット検索です。

アクセス制御の実装サンプル解説(1) - はじめに

 アクセス制御の実装サンプルとして、こちらのアプリケーションを作ってみました。


よくデモに使用しているタスク一覧のCSVファイルを元に、アプリケーションを作成しています。

サンプルは複数のセッションの間で、干渉せずに更新できるようにしています(セッションIDにより分離している)。その部分は特殊なので省いて、データの操作で使われるファセット検索、対話モード・レポートとフォーム、対話グリッドで、どのようにアクセス制御を実装しているかを紹介しようと思います。

4本の記事を予定しています。
  1. 初期アプリケーションの作成(この記事)
  2. データ・ローディングの実装
  3. ファセット検索ページの追加
  4. 対話モード・レポートとフォームの追加
  5. 対話グリッドの追加
  6. 認可スキームについて
データ・ローディングを実装してCSVファイルを読み込んだ後に、データを操作する機能を提供するページを追加します。その際に、それぞれにアクセス制御を実装していきます。

作成するアプリケーションですが、以下の従業員でサインインします。


  • ロールが管理者であれば、すべてのタスクを参照することができる。
  • ロールがコントリビュータであれば、自分が所属している部署に関するタスクの作成、更新、削除ができる。
  • ロールのない従業員は、自分が所属している部署のタスクの参照と自分がアサインされているタスクの更新ができる(タスクの作成と削除は不可)。
以上の条件をそれぞれのページに実装します。

では、元になるアプリケーションを作成していきます。

クイックSQLによる表の作成


クイックSQLに以下の定義を与え、3つの表PAC_EMPLOYEES、PAC_PROJECTS、PAC_TASKSを作成します。

employees
  employee_id num /pk
  employee_name vc255 /nn
  group_name vc255 /nn

projects
  project_id num /pk
  project_name vc255 /nn

tasks
  task_id num /pk
  project_id num /references projects
  task_name vc255 /nn
  assigned_to num /references employees
  status vc8 /check OPEN,CLOSED,PENDING,ON-HOLD
  start_date
  end_date
  cost num
  budget num

DDLを生成する設定として、以下を指定します。

  • オブジェクト接頭辞: PAC
  • 主キー: ID列
  • 日付データ型: TIMESTAMP WITH LOCAL TIME ZONE
  • セマンティクス: デフォルト
  • 自動主キー: OFF
  • 互換性: 18c以上
  • 主キーに表名を接頭辞として付けます: OFF
SQLの生成、SQLスクリプトの保存、レビューおよび実行を順次実行し、生成されたDDLより表を作成します。最後にアプリケーションの作成を行うこともできますが、それは行いません。


アプリケーションの作成


アプリケーション作成ウィザードを呼び出し、空のアプリケーションを作成します。名前は任意です。機能はすべてをチェックします(最低限、アクセス制御が必要です)。そして、アプリケーションの作成をクリックします。


アプリケーションが作成されたら、PAC_EMPLOYEES表に従業員データを投入します。SQLワークショップのSQLコマンドから以下を実行します。

begin
insert into pac_employees(employee_name, group_name) values('山田太郎','経理部');
insert into pac_employees(employee_name, group_name) values('岡本一','開発部');
insert into pac_employees(employee_name, group_name) values('北本三郎','営業部');
insert into pac_employees(employee_name, group_name) values('田中節子','営業部');
insert into pac_employees(employee_name, group_name) values('大谷一朗','開発部');
insert into pac_employees(employee_name, group_name) values('虎尾十兵衛','開発部');
insert into pac_employees(employee_name, group_name) values('木下愛','経理部');
insert into pac_employees(employee_name, group_name) values('三浦康太','経理部');
insert into pac_employees(employee_name, group_name) values('森花子','開発部');
insert into pac_employees(employee_name, group_name) values('伊藤和子','営業部');
insert into pac_employees(employee_name, group_name) values('高橋実','経理部');
insert into pac_employees(employee_name, group_name) values('川口けいこ','営業部');
commit;
end;


投入されたデータは以下のSQLで確認できます。

select * from pac_employees

先ほど作成したアプリケーションのアプリケーションIDを確認します。

何人かのユーザーに、API呼び出しによってロールを割り当てます。

declare
  C_APP_ID constant number := <アプリケーションID>;
begin
  apex_acl.add_user_role(c_app_id, '虎尾十兵衛', 'ADMINISTRATOR');
  apex_acl.add_user_role(c_app_id, '木下愛', 'ADMINISTRATOR');
  apex_acl.add_user_role(c_app_id, '三浦康太', 'CONTRIBUTOR');
  apex_acl.add_user_role(c_app_id, '森花子', 'CONTRIBUTOR');
  apex_acl.add_user_role(c_app_id, '伊藤和子', 'ADMINISTRATOR');
  apex_acl.add_user_role(c_app_id, '川口けいこ', 'CONTRIBUTOR');
end;

ADMINISTRATORが管理者、CONTRIBUTORがコントリビュータのロールです。

この作業は作成したアプリケーションに組み込まれたアクセス制御の画面から行うこともできますし、共有コンポーネントのアプリケーション・アクセス制御からも操作することが可能です。

共有コンポーネントからの操作は、以下の画面から行います。


作成したアプリケーションに組み込まれているアクセス制御は以下です。


アプリケーション作成直後は、アプリケーションを作成した開発者アカウントだけが管理者ロールを持っています。アプリケーションにアクセス制御を組み込むと、アプリケーションにアクセスするために、ユーザーにはリーダー権限(ロール)が必須の状態がデフォルトになります。ですので、アプリケーション作成直後は、管理者以外のアクセスは拒否されます。

リーダー権限を不要にするには、アクセス制御の構成を変更する必要があります。アプリケーションからは以下の画面になります。今回はリーダー権限を不要にするため、以下に説明する操作を実施してください。


または、共有コンポーネントに含まれるアプリケーション設定の ACCESS_CONTROL_SCOPEをACL_USERSへ変更します。



認証スキームの変更


登録された従業員がアプリケーションのユーザーとなるよう、認証スキームを変更します。カスタム認証を作成します。認証に使用するPL/SQLコードは以下になります。従業員の存在だけを確認し、パスワードは無視しています。あくまでもサンプルですので、そのまま使わないよう、お願いします。

function existing_employee (
    p_username in varchar2,
    p_password in varchar2 )
    return boolean
is
  l_exists number;
begin
  select 1 into l_exists
  from pac_employees
  where employee_name = p_username;
  return true;
exception
  when no_data_found then
    return false;
end;

PL/SQLコードに登録した認証ファンクションexisting_employeeは、認証ファンクション名として設定し、認証スキームの作成を実行します。

これで認証スキームは作成したものに切り替わります。すでにサインイン済みのセッションは、一旦サインアウトする必要があります。

日本語の従業員名をユーザー名として指定するのは大変なので、ユーザー名を選択リストで選べるようにします。SQLによる動的なLOVを作成します。共有コンポーネントからLOVを開き、タイプがDynamicであるLOV、名前をPAC_EMPLOYEES.EMPLOYEE_NAME_SIGNINとして作成します。

LOVに使用するコードとしては以下を指定します。

select
  e.employee_name || ' - ' || e.group_name || ' - ' || r.roles display_name,
  e.employee_name return_value
from pac_employees e 
left outer join
(
    select user_name, listagg(role_name, ',') within group (order by role_name desc) roles
    from apex_appl_acl_user_roles
    where workspace_id = :WORKSPACE_ID 
    and application_id = :APP_ID
    group by user_name
) r on e.employee_name = r.user_name
order by e.group_name, r.roles desc nulls last

従業員が所属している部署と割り当たっているロールを、一行で表示します。

作成したLOVをログイン・ページに組み込みます。ログイン・ページをページ・デザイナで開き、タイプを選択リスト、テンプレートをOptional Floating、LOVのタイプとして共有コンポーネント、LOVは先ほど作成したPAC_EMPLOYEES.EMPLOYEE_NAME_SIGNIN、追加値の表示はOFF、NULL表示値を- 従業員を選択 -とします。

保存を行い、アプリケーションを実行します。

ログイン画面が変更されています。

選択リストから従業員を選びます。

従業員を選んでサインインします。


アプリケーションにアクセスできます。左上にサインインしたユーザー名が表示されます。


管理者ロールを持ったユーザーでサインインすると、管理メニューにアクセスできます。

この機能はアプリケーション作成時にチェックしていると、標準機能として組み込まれます。


アプリケーション・アイテムとアプリケーションの計算の登録


アプリケーションの雛形はほぼ出来上がっていますが、これからの開発を容易にするため、ユーザー認証直後にいくつかアプリケーション全体で使用可能な属性をアプリケーション・アイテムとして設定します。

以下の3つのアプリケーション・アイテムを登録します。
  • G_GROUP_NAME = サインインしたユーザーが所属している部門名
  • G_IS_ADMINISTRATOR = 管理者ロールを持っているとY、そうでない場合はN
  • G_IS_CONTRIBUTOR = コントリビュータ・ロールを持っているとY、そうでない場合はN
共有コンポーネントのアプリケーション・アイテムを開き、作成をクリックします。名前としてG_GROUP_NAMEを指定し、セッション・ステート保護は必ず制限付き - ブラウザからの設定不可、とします。そして、アプリケーション・アイテムの作成を実行します。同様の作業をG_IS_ADMINISTRATOR、G_IS_CONTRIBUTORについても実施します。


次に、これらのアイテムに値を設定するため、アプリケーションの計算を登録します。共有コンポーネントのアプリケーションの計算を開き、作成をクリックします。

最初にG_GROUP_NAMEの計算を登録します。計算アイテムとしてG_GROUP_NAMEを選択します。計算ポイントは認証後です。計算タイプはSQL問合せ(単一の値を返す)を選びます。計算として指定するSQLは以下になります。
select group_name from pac_employees where employee_name = :APP_USER

サインインしたユーザー名より、その所属部門を検索しています。

同様にして、G_IS_ADMINISTRATOR、G_IS_CONTRIBUTORも登録します。

G_IS_ADMINISTRATORは計算タイプとして、ファンクション本体を選びます。計算には以下のPL/SQLコードを指定します。

if apex_acl.has_user_role(
  p_role_static_id => 'ADMINISTRATOR'
) then
  return 'Y';
end if;
return 'N';

サインインしているユーザーが管理者ロールを持っているときに、Y、そうでないときにNが設定されるように、APEX_ACL.HAS_USER_ROLEファンクションを呼び出しています。

G_IS_CONTRIBUTORも同様ですが、p_role_static_idはCONTRIBUTORになります。

if apex_acl.has_user_role(
  p_role_static_id => 'CONTRIBUTOR'
) then
  return 'Y';
end if;
return 'N';

準備作業となるアプリケーションの作成は以上で完了です。

アプリケーションを実行し(すでにサインインしているセッションがあれば、一旦サインアウトし)、サインインを実施してみましょう。

設定内容を確認するため、開発者ツール・バーよりセッションを実行します。ビューにアプリケーション・アイテムを選択し、設定をクリックすると、今までに登録したアプリケーション・アイテムG_GROUP_NAME、G_IS_ADMINISTRATOR、G_IS_CONTRIBUTORに値が設定されていることが確認できるはずです。

次は、データ・ローディングを実装して、CSVファイルを表に取り込みます。

続く