はじめに

本記事はOracle Cloud Infrastructure Advent Calendar 2023 の 18 日目の記事です。

以前、こちらの記事で、Oracle APEXで地理空間データと、Oracle Spatial(Oracle Databaseの地理空間データを扱う機能)を使ったアプリケーションを作成しました。

そのアプリケーションでは、アメリカの州と距離を指定することで、その州内と、州の境界から指定した距離の範囲内にある空港をマッピングしていました。

本記事ではその発展として、APEXにログインするユーザーごとに、表示する範囲を制限する仕組みを作ってみたいと思います。
使用する機能としては、Oracle Databaseの仮想プライベート・データベース(VPD)です。機能自体はOracle Database 8iから存在しています。
詳しくはこちらをご参照ください。

環境

Autonomous Data Warehouse: 4ECPU

手順

APEXユーザー作成

ADMINでAutonomous Databaseにログインし、SPATIALDEMOユーザーを作成します。

-- USER SQL
CREATE USER SPATIALDEMO IDENTIFIED BY Welcome12345#;
-- ADD ROLES
GRANT CONNECT, DWROLE, RESOURCE TO SPATIALDEMO;
-- ENABLE REST(Database Actions 利用のため)
BEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'SPATIALDEMO',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'spatialdemo',
        p_auto_rest_auth=> TRUE
    );
    commit;
END;
/
-- QUOTA
ALTER USER SPATIALDEMO QUOTA UNLIMITED ON DATA;

APEX ワークスペース作成

管理サービスにログインし、ワークスペースを作成します。
既存のスキーマを選択し、以下を入力します。

データベース・ユーザー：SPATIALDEMO
ワークスペース名：SPATIALDEMO(自動入力されます)
ワークスペース・ユーザー名：SPATIALDEMO
ワークスペース・パスワード：SPATIALDEMO のパスワード(Welcome12345#)

データの準備

ロードするデータをダウンロードしておきます。
以下から空港データairports-full.jsonと、州データstates-full.jsonをダウンロードします。
ロード手順は、こちらの記事
のサンプルアプリのインストールを参照してください。

ユーザー表の作成

今回は VPD を使ったアクセス制御をしやすくするため、APEXアプリをAUTH_USERS表で認証情報を管理することにします。デフォルトでは、APEXアプリの認証のスキーム・タイプはOracle APEX アカウント(Oracle APEXのユーザー・リポジトリで作成および管理される内部ユーザー・アカウント)です。今回のように、データベース内の表でアプリの認証をしたい場合は、スキーム・タイプをカスタムとし、認証ファンクションなどをソースとして認証スキームを設定します。

function auth_employees_only (
    p_username in varchar2,
    p_password in varchar2 )
    return boolean
is
    l number;
begin
    select 1 into l from auth_users where ename = p_username;
    return true;
exception
    when others then
        return false;
end;

ワークスペースのページから、SQLワークショップへ移動
SQLコマンドをクリック。ここでAPEX内でもSQLの実行ができます。
以下を実行
```
CREATE TABLE auth_users (
     ID NUMBER(5) PRIMARY KEY,
     username VARCHAR2(30),
     unitid NUMBER(3),
     unitname VARCHAR2(50),
     pw VARCHAR2(30)
);
```
```
INSERT ALL
INTO AUTH_USERS VALUES (1, 'ADMIN', 1, 'ADMINISTRATORS', 'Welcome12345#')
INTO AUTH_USERS VALUES (2, 'SATO', 2, 'WEST', 'oracle')
INTO AUTH_USERS VALUES (3, 'TANAKA', 3, 'EAST', 'oracle')
INTO AUTH_USERS VALUES (4, 'SUZUKI', 4, 'TEXAS', 'oracle')
SELECT * FROM DUAL;
```
AUTH_USERSには認証用のユーザー名とパスワードに加え、unitid列とunitname列があります。
- ADMISTRATORS(unitid: 1)のユーザーは、管理者権限として、何も制限をかけない
- WEST(unitid: 2)のユーザーはアメリカ西部の州の空港のみ表示、EAST(unitid: 3)のユーザーは東部の州の空港み表示
- TEXAS(unitid: 4)のように州で指定されているユーザーは、その州の空港のみ表示
というようにVPDを設定することにします。

VPDの設定

まずはVPD設定用のユーザーVPDADMINを作成します。ADMINユーザーで以下を実行します。

-- USER SQL
CREATE USER VPDADMIN IDENTIFIED BY Welcome12345#;
-- ADD ROLES
GRANT CONNECT, RESOURCE, CREATE ANY CONTEXT, CREATE PROCEDURE TO VPDADMIN; 
GRANT EXECUTE ON DBMS_RLS TO VPDADMIN;
GRANT EXECUTE ON DBMS_SESSION TO VPDADMIN;
GRANT SELECT ON SPATIALDEMO.AUTH_USERS TO VPDADMIN;
-- ENABLE REST(Database Actions 利用のため)
BEGIN
    ORDS.ENABLE_SCHEMA(
    p_enabled => TRUE,
    p_schema => 'VPDADMIN',
    p_url_mapping_type => 'BASE_PATH',
    p_url_mapping_pattern => 'vpdadmin',
    p_auto_rest_auth=> TRUE
);
commit;
END;
/
-- QUOTA
ALTER USER VPDADMIN QUOTA UNLIMITED ON DATA;

VPDADMINユーザーでアプリケーション・コンテキストを設定するパッケージを作成します。

CREATE OR REPLACE CONTEXT USERS_CTX USING USERS_CTX_PKG;

CREATE OR REPLACE PACKAGE USERS_CTX_PKG IS
PROCEDURE INIT;
PROCEDURE CLEAR;
END;
/

CREATE OR REPLACE PACKAGE BODY USERS_CTX_PKG IS
PROCEDURE INIT
IS
    L_ID SPATIALDEMO.AUTH_USERS.ID%TYPE;
    L_UNITID SPATIALDEMO.AUTH_USERS.UNITID%TYPE;
    L_UNITNAME SPATIALDEMO.AUTH_USERS.UNITNAME%TYPE;
BEGIN
    SELECT ID, UNITID, UNITNAME INTO L_ID, L_UNITID, L_UNITNAME
    FROM SPATIALDEMO.AUTH_USERS
    WHERE USERNAME = SYS_CONTEXT('APEX$SESSION', 'APP_USER');
    DBMS_SESSION.SET_CONTEXT('USERS_CTX','ID',L_ID);
    DBMS_SESSION.SET_CONTEXT('USERS_CTX','UNITID',L_UNITID);
    DBMS_SESSION.SET_CONTEXT('USERS_CTX','UNITNAME',L_UNITNAME);
    EXCEPTION
    WHEN OTHERS THEN
        -- EMPTY APP CONTEXT
        NULL;
END INIT;
PROCEDURE CLEAR
IS
BEGIN
    DBMS_SESSION.CLEAR_CONTEXT('USERS_CTX');
END CLEAR;
END USERS_CTX_PKG;
/
-- ADD ROLE
GRANT EXECUTE ON USERS_CTX_PKG TO SPATIALDEMO;

東部か西部、もしくは担当の州で問合せできる州を制限する VPD ポリシーREGIONの作成と、州の表EBA_SAMPLE_MAP_SIMPLE_STATESへの付与を行います。IF文で指定した条件のユーザーがEBA_SAMPLE_MAP_SIMPLE_STATES表に問合せクエリを実行すると、自動的にWHERE条件が付与され、州が絞り込まれます。(元表にWESTやEASTという地域区分の列がないため、直接IDで指定しています。)

-- CREATE FUNCTION(ユーザーのUNITIDごとに表示を絞る)
CREATE OR REPLACE FUNCTION REGION_FUNC
(
    SCHEMA_P IN VARCHAR2,
    TABLE_P IN VARCHAR2
)
RETURN VARCHAR2
AS
    V_TEXT VARCHAR2(200);
BEGIN
    -- 西地域のみ
    IF SYS_CONTEXT('USERS_CTX','UNITID') = '2'
        THEN V_TEXT := Q'~ID IN(7, 13, 14, 17, 18, 20, 24, 26, 27, 29, 31, 32, 33, 40, 41, 47,
48, 51, 56)~';
    -- 東地域のみ
    ELSIF SYS_CONTEXT('USERS_CTX','UNITID') = '3'
        THEN V_TEXT := Q'~ID NOT IN(7, 13, 14, 17, 18, 20, 24, 26, 27, 29, 31, 32, 33, 40, 41,
47, 48, 51, 56)~';
    -- 州担当ユーザーの場合、その州内のみ
    ELSIF SYS_CONTEXT('USERS_CTX','UNITID') NOT IN(1, 2, 3)
        THEN V_TEXT := Q'~UPPER(NAME) = SYS_CONTEXT('USERS_CTX', 'UNITNAME')~';
    END IF;
    RETURN V_TEXT;
END;
/

-- ADD POLICY
BEGIN
DBMS_RLS.ADD_POLICY(
    OBJECT_SCHEMA => 'SPATIALDEMO',
    OBJECT_NAME => 'EBA_SAMPLE_MAP_SIMPLE_STATES',
    POLICY_NAME => 'REGION',
    FUNCTION_SCHEMA => 'VPDADMIN',
    POLICY_FUNCTION => 'REGION_FUNC',
    STATEMENT_TYPES => 'SELECT',
    POLICY_TYPE => DBMS_RLS.CONTEXT_SENSITIVE,
    NAMESPACE => 'USERS_CTX',
    ATTRIBUTE => 'UNITID'
);
END;
/

西・東地域の人が距離を指定しても、自分の地域内の空港しか表示されない VPD ポリシーDISTANCE 作成と空港の表EBA_SAMPLE_MAP_AIRPORTSへの付与を行います。REGIONと同様に、IF文で指定した条件のユーザーがEBA_SAMPLE_MAP_AIRPORTS表への問合せクエリを実行すると、自動的にWHERE条件が付与されます。

-- CREATE FUNCTION(ユーザーのUNITIDごとに表示を絞る)
CREATE OR REPLACE FUNCTION DISTANCE_FUNC
(
    SCHEMA_P IN VARCHAR2,
    TABLE_P IN VARCHAR2
)
RETURN VARCHAR2
AS
    V_TEXT VARCHAR2(400);
BEGIN
    -- 西地域のみ
    IF SYS_CONTEXT('USERS_CTX','UNITID') = '2'
    THEN V_TEXT := Q'~STATE_NAME IN('IDAHO','NEW MEXICO','CALIFORNIA','OREGON','NEBRASKA','WASHINGTON','UTAH','TEXAS','COLORADO','OKLAHOMA','WYOMING','HAWAII','NORTH DAKOTA','NEVADA','ALASKA','MONTANA','KANSAS','SOUTH DAKOTA','ARIZONA')~';
    -- 東地域のみ
    ELSIF SYS_CONTEXT('USERS_CTX','UNITID') = '3'
    THEN V_TEXT := Q'~STATE_NAME NOT IN('IDAHO','NEW MEXICO','CALIFORNIA','OREGON','NEBRASKA','WASHINGTON','UTAH','TEXAS','COLORADO','OKLAHOMA','WYOMING','HAWAII', 'NORTH DAKOTA','NEVADA','ALASKA','MONTANA','KANSAS','SOUTH DAKOTA','ARIZONA')~';
    -- 州担当ユーザーの場合、その州内のみ
    ELSIF SYS_CONTEXT('USERS_CTX','UNITID') NOT IN(1, 2, 3)
    THEN V_TEXT := Q'~STATE_NAME = SYS_CONTEXT('USERS_CTX', 'UNITNAME')~';
    END IF;
    RETURN V_TEXT;
END;
/

-- ADD POLICY
BEGIN
DBMS_RLS.ADD_POLICY(
    OBJECT_SCHEMA => 'SPATIALDEMO',
    OBJECT_NAME => 'EBA_SAMPLE_MAP_AIRPORTS',
    POLICY_NAME => 'DISTANCE',
    FUNCTION_SCHEMA => 'VPDADMIN',
    POLICY_FUNCTION => 'DISTANCE_FUNC',
    STATEMENT_TYPES => 'SELECT',
    POLICY_TYPE => DBMS_RLS.CONTEXT_SENSITIVE,
    NAMESPACE => 'USERS_CTX',
    ATTRIBUTE => 'UNITID'
);
END;
/

アプリケーションの作成

今回はすでに作成しているアプリケーションのファイルをインポートします。

こちらからダウンロードします。
ダウンロードしたSQLファイルをアプリケーション・ビルダーで**インポート*します。

アプリケーションの確認

アプリを実行して、まずはADMINユーザーでログインしてみます。AUTH_USERS表のユーザー名とパスワードでサインインします。

ADMINユーザーは全州・全空港を表示できるようにしている(VPDで制限していない)ので、このように表示されます。
また、Stateで表示する州を絞り込んだり、Distanceで州の境界からここで指定した距離まで表示範囲を拡大したい場合も、以下のように、条件に合う空港は全て表示されます。
続いて、WESTユニットのSATOユーザーでログインしてみます。
VPDによって、西地域のみの表示になっています。

また、Stateでテキサス州を指定し、Distanceで500kmを指定しても、東地域の空港は表示されません。
最後に、TEXASユニットのSUZUKIユーザーでログインしてみます。左のファセット検索State欄でもTexasしか表示されていません。

APEX上ではファセット検索のソースをEBA_SAMPLE_MAP_AIRPORTS表のSTATE_NAME列と設定しています。

VPD設定のPL/SQLコード上では、以下の部分です。
```
-- Texas州などの州担当ユーザーの場合、その州内のみ
ELSIF SYS_CONTEXT('USERS_CTX','UNITID') NOT IN(1, 2, 3)
THEN V_TEXT := Q'~STATE_NAME = SYS_CONTEXT('USERS_CTX', 'UNITNAME')~';
```
これはDistanceで距離を指定した場合も有効です。なので以下のように500kmと指定しても、Texas州内の空港しか表示されません。