異なるスキーマに表があるとき、その表を元にビューを作成してアクセス制御を行なってみます。また、異なるスキーマのオブジェクトを扱うAPEXアプリケーションをエクスポートしたのち、エスクポートしたときと異なる環境にインポートする際に考慮する点について、いくつか説明をしてみたいと思います。 属性ディレクトリを使ったラベルの置き換え方法の説明も含みます。
解析対象スキーマについて
APEXではワークスペースを作って、そこにアプリケーションを作成します。アプリケーションが参照するスキーマは、アプリケーション定義の解析対象スキーマ（パーシング・スキーマ）として設定します。詳しくはこちらに記事に解説しています。
リージョンのソースのタイプが表/ビューで表の所有者がParsing Schemaとなっていると、アプリケーション定義の解析対象スキーマとして設定されているスキーマが参照されます。
実際には以下のSELECT文が実行されます。
select * from "表名"
表の所有者としてParsing Schemaを設定しているか、または、SQL問合せにスキーマの指定を含めなければ、作成しているAPEXアプリケーションはスキーマへの依存がないため、どのスキーマにインポートしても変更なく動作します。移行先のスキーマに、同じ表やビューが作成されていることは必須です。
異なるスキーマに存在するオブジェクトの参照
同じインスタンスに追加でワークスペースHRを作成します。作成手順については、こちらの記事で紹介しています。
作成したワークスペースに表HR_EMPLOYEESを作成します。
SQLワークショップのユーティリティに含まれるクイックSQLを開き、以下のモデルを使って表HR_EMPLOYEESを作成します。
# prefix: hr
# auditcols: true
employees
name vc80 /nn
address vc200
work_location vc40
hire_date date
department /check sales,development,marketing,research
gender /check f,m
age num
nationality vc80
SQLの生成、生成されたSQLスクリプトを保存、続いて、レビューおよび実行を行います。
スクリプトの編集画面で実行をクリックします。
即時実行します。
２行成功していると、表HR_EMPLOYEESおよび監査列にデータを設定するトリガーが作成されています。
他のスキーマからは表HR_EMPLOYEESを直接操作するのではなく、ビューを通して許可するDML操作を制限します。
表HR_EMPLOYEESへのアクセス権限は所有者以外に持ちません。
最初に監査列を除いたすべての列を含むビューHR_EMPLOYEES_ALL_Vを作成します。すべてのDML操作を許可します。
SQLコマンドより実行します。
create or replace view hr_employees_all_v
as
select
id
,name
,address
,work_location
,hire_date
,department
,gender
,age
,nationality
from hr_employees;
このビューでは、すべてのDML操作をユーザーAPEXDEV（APEXアプリケーションを作る予定のスキーマ - それぞれの環境に合わせて置き換えてください）に許可します。
grant all on hr_employees_all_v to apexdev;
同様に、列gender、age、nationalityを除いたビューHR_EMPLOYEES_Vを作成します。
create or replace view hr_employees_v
as
select
id
,name
,address
,work_location
,hire_date
,department
from hr_employees;
このビューについても、すべてのDML操作を行なう権限をユーザーAPEXDEVに与えます。
grant all on hr_employees_v to apexdev;
列DEPARTMENTの値がSALESのものだけ操作できるビューHR_EMPLOYEES_SALES_Vを作成します。
create or replace view hr_employees_sales_v
as
select
id
,name
,address
,work_location
,hire_date
,department
from hr_employees
where department = 'SALES';
このビューには、SELECTとUPDATEを実行する権限を与えます。
grant select,update on hr_employees_sales_v to apexdev;
最後に列DEPARTMENTの値がRESEARCHのものだけ操作できるビューHR_EMPLOYEES_RESEARCH_Vを作成します。
create or replace view hr_employees_research_v
as
select
id
,name
,address
,work_location
,hire_date
,department
from hr_employees
where department = 'RESEARCH';
このビューにも同様に、SELECTとUPDATEを実行する権限を与えます。
grant select,update on hr_employees_research_v to apexdev;
以上で、検証に使う表とビューの作成は完了です。
ワークスペースHRでの作業は以上なので、サインアウトします。
パブリック・シノニムの作成
ビューHR_EMPLOYEES_ALL_VとHR_EMPLOYEES_Vを参照するパブリック・シノニムを作成します。
パブリック・シノニムは管理ユーザーADMINで作成します。
データベース・アクションのSQLを開き、以下の２行を実行します。
create public synonym hr_employees_all_v for hr.hr_employees_all_v;
create public synonym hr_employees_v for hr.hr_employees_v;
APEXアプリケーションの作成と動作確認
APEXアプリケーションを開発するワークスペース（この例ではAPEXDEV）にサインインします。
最初にシノニムを２つ作成します。
create synonym hr_employees_sales_v for hr.hr_employees_sales_v;
create synonym hr_employees_research_v for hr.hr_employees_research_v;
それぞれ、SQLコマンドより実行します。
アプリケーション作成ウィザードを起動し、空のアプリケーションを作成します。
名前をビューによるアクセス制御とし、アプリケーションの作成を実行します。
アプリケーションが作成されます。
最初にビューHR_EMPLOYEES_ALL_Vを扱う対話モード・レポートとフォームのページを作成します。
ページの作成をクリックします。
フォームを選択します。
フォーム付きレポートを選択します。
レポート・タイプとして対話モード・レポート、レポート・ページ名およびフォーム・ページ名は共に対象とするビューの名前HR_EMPLOYEES_ALL_Vを設定します。フォーム・ページ・モードには、モーダル・ダイアログを選択します。
次へ進みます。
ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。
次へ進みます。
データ・ソースの設定に移ります。
ここでは表/ビューの所有者の選択が必須で、デフォルト（Parsing Schemaという選択肢）の選択肢はありません。
表/ビューの所有者としてHRを選択すると、表/ビューの名前としてHR_EMPLOYEES_ALL_Vが選択可能になります。
ビューHR.HR_EMPLOYEES_ALL_Vについては、すでにパブリック・シノニムHR_EMPLOYEES_ALL_Vを作成しているので、ソース・タイプをSQL問合せに変更し、SQL SELECT文を入力として以下を記述します。
select * from hr_employees_all_v
列の指定に*を使っているので、ビューから参照できるすべての列がレポートの表示対象になります。
データ・ソースにはスキーマの指定を含まないため、HR_EMPLOYEES_ALL_Vとしてビューがアクセスできる構成になっているインスタンスであれば、インポートしてもAPEXアプリケーションの修正は不要です。
次へ進みます。
フォームの主キー列としてID (Number)を選択します。
列名に対して、フォームで設定した日本語のラベルが設定されていることが確認できます。
select * from hr_employees_v
select * from hr_employees_sales_v
ビューHR_EMPLOYEES_RESEARCH_Vの対話モード・レポートとフォームのソースは以下になります。
select * from hr_employees_research_v
すべてのビューについて対話モード・レポートとフォームのページが作成されると、以下のように８ページ追加されます。
次へ進みます。
対話モード・レポートとフォームのページが作成されます。
対話モード・レポートのページのソースを参照します。ページ作成ウィザードで設定したSELECT文になっています。
フォームのページになるフォーム・リージョンのソースも同様です。
ページのプロパティのソースの定義では表の所有者としてParsing Schemaを選択できます。そのため、表/ビューの名前としてHR_EMPLOYEES_ALL_Vを入力することができます。ただし、選択リストには一覧されないので、シノニム名を直接入力する必要があります。
Parsing Schemaに作成したシノニムであれば、選択リストに表示されます。
SELECT文として記述しても、表の所有者としてParsing Schemaを選択して表名を指定しても、効果は同じです。
作成したページを実行します。
１行データを挿入するために、作成をクリックします。
開いたフォームの項目にそれぞれ適当な値を入力し、作成をクリックします。
データの挿入を確認します。
ビューやシノニムを使っていても、与えられた権限の範囲でDML操作ができています。
ラベルの日本語化
対話モード・レポートとフォームに含まれるラベルや、ページ・アイテムのタイプを調整します。
フォームのページ（ページ番号３）を開き、ページ・アイテムP3_NAME、P3_WORK_LOCATION、P3_NATIONALITYを選択します。
識別のタイプをテキスト領域からテキスト・フィールドに変更します。
それぞれのページ・アイテムのラベルを変更します。
P3_NAME - 名前
P3_ADDRESS - 住所
P3_WORK_LOCATION - 作業場所
P3_HIRE_DATE - 採用日
P3_DEPARTMENT - 所属
P3_GENDER - 性別
P3_AGE - 年齢
P3_NATIONALITY - 国籍
同じラベルの設定をレポートにも適用するため、ページ・アイテムのでラベル設定を属性ディクショナリに保存します。
アプリケーションのユーティリティを開きます。
属性ディクショナリを開きます。
アプリケーションに含まれるページの一覧が表示されます。ラベルを日本語に置き換えたDMLフォームのページ３を開きます。
左側のページ・アイテムの属性ディクショナリの更新のセクションに含まれる、属性ディクショナリへの挿入用の８アイテムの確認のリンクをクリックします。
すべてのアイテムを選択し、属性ディクショナリの更新をクリックします。
選択したアイテムの設定が属性ディクショナリにコピーされました。
右端のタスクより属性ディクショナリへのアクセスのリンクをクリックします。
ちなみに右端のタスクより分かりますが、属性ディクショナリはエクスポートおよびインポートすることができます。
再度、アプリケーションのユーティリティより属性ディクショナリを開きます。
今度は対話モード・レポートのページ（ページ番号２）を開きます。
レポート列のセクションページの更新に含まれる、更新用の８レポート列の確認のリンクをクリックします。
属性ディクショナリの設定と異なる列が一覧されます。すべての列を選択し、属性ディクショナリに設定されている値が反映されるよう、レポート列の更新をクリックします。
ラベルが日本語に更新されます。
アプリケーションを実行し、結果を確認します。
対話モード・レポートの列名も日本語になっていることが確認できます。
他のビューのレポートとフォームの作成
他に作成しているビューHR_EMPLOYEES_V、HR_EMPLOYEES_SALES_V、HR_EMPLOYEES_RESEARCH_Vについても、対話モード・レポートとフォームのページを作成します。
作成手順はビューHR_EMPLOYEES_ALL_Vとほぼ同じです。
ビューHR_EMPLOYEES_Vの対話モード・レポートとフォームのソースは以下になります。
対話モード・レポートとフォームのページが作成されたのち、属性ディクショナリの適用や、いくつかのページ・アイテムのタイプをテキスト領域からテキスト・フィールドに変更しておきます。
ビューHR_EMPLOYEES_SALES_Vの対話モード・レポートとフォームのソースは以下になります。
select * from hr_employees_sales_v
select * from hr_employees_research_v
動作確認
ビューHR_EMPLOYEES_Vを通して、所属がRESEARCHである従業員を一名作成します。
所属にはRESEARCHと入力し、その他は適当な値を入力して作成をクリックします。
さらに所属をDEVELOPMENTとした従業員を作成します。
作成した行をビューHR_EMPLOYEES_ALL_Vより確認します。ビューHR_EMPLOYEES_Vには列GENDER、AGEおよびNATIONALITYは含まれていないため、その列は空白になっています。
ビューHR_EMPLOYEES_SALES_Vのレポートを開くと、所属がSALESの従業員のみリストされます。
ページ作成ウィザードは表やビューの権限は確認しないため、新規行を挿入する作成ボタンが生成されますが、実際に作成しようとするとORA-01031: 権限が不足していますが発生します。これは削除についても同様です。
今回の確認のために作成したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/accesscontrolviaviews.sql
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
