[Oracle APEX]仮想プライベート・データベース(VPD)を使い地図アプリの表示制限をする

# はじめに
本記事は<a href="https://qiita.com/advent-calendar/2023/oci" target="_blank" rel="noopener noreferrer">Oracle Cloud Infrastructure Advent Calendar 2023 </a>の 18 日目の記事です。

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

https://qiita.com/wahagon/items/6d0dd35ce714a7f2d9f2


そのアプリケーションでは、アメリカの州と距離を指定することで、その州内と、州の境界から指定した距離の範囲内にある空港をマッピングしていました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/496d82df-5e75-8716-d8e5-78b92b38d993.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/f7eb278d-7a5e-c257-9f92-0a974de710df.png)

# 環境
Autonomous Data Warehouse: 4ECPU

# 手順

## APEXユーザー作成
`ADMIN`でAutonomous Databaseにログインし、`SPATIALDEMO`ユーザーを作成します。
```sql
-- 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`をダウンロードします。
ロード手順は、<a href="https://qiita.com/wahagon/items/6d0dd35ce714a7f2d9f2#%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB%E3%82%A2%E3%83%97%E3%83%AA%E3%81%AE%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB" target="_blank" rel="noopener noreferrer">こちらの記事</a>
の**サンプルアプリのインストール**を参照してください。

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

1. ワークスペースのページから、**SQLワークショップ**へ移動
1. **SQLコマンド**をクリック。ここでAPEX内でもSQLの実行ができます。
1. 以下を実行
    ```sql
    CREATE TABLE auth_users (
         ID NUMBER(5) PRIMARY KEY,
         username VARCHAR2(30),
         unitid NUMBER(3),
         unitname VARCHAR2(50),
         pw VARCHAR2(30)
    );
    ```

    ```sql
    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の設定
1. まずはVPD設定用のユーザー`VPDADMIN`を作成します。`ADMIN`ユーザーで以下を実行します。

    ```sql
    -- 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;
    ```

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

    ```sql
    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;
    ```

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

    ```sql
    -- 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;
    /
    ```

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

## アプリケーションの作成
今回はすでに作成しているアプリケーションのファイルをインポートします。
1. <a href="https://github.com/r-deguchi/apex_mapping" target="_blank" rel="noopener noreferrer">こちら</a>からダウンロードします。

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

# アプリケーションの確認
1. アプリを実行して、まずは`ADMIN`ユーザーでログインしてみます。`AUTH_USERS`表のユーザー名とパスワードでサインインします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/c2098688-e116-e755-a858-3b3a70287ee1.png)
`ADMIN`ユーザーは全州・全空港を表示できるようにしている(VPDで制限していない)ので、このように表示されます。
また、`State`で表示する州を絞り込んだり、`Distance`で州の境界からここで指定した距離まで表示範囲を拡大したい場合も、以下のように、条件に合う空港は全て表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/c25db5dc-ab19-0972-9de8-d691b75673f5.png)

1. 続いて、`WEST`ユニットの`SATO`ユーザーでログインしてみます。
VPDによって、西地域のみの表示になっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/787e3556-236e-ad59-6702-03123217671b.png)
また、`State`でテキサス州を指定し、`Distance`で500kmを指定しても、東地域の空港は表示されません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/100157cd-984b-6322-8c70-7f42b558e1b5.png)

1. 最後に、`TEXAS`ユニットの`SUZUKI`ユーザーでログインしてみます。左のファセット検索`State`欄でも`Texas`しか表示されていません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/717435f0-a493-aece-8d11-004bf501697a.png)
APEX上ではファセット検索のソースを`EBA_SAMPLE_MAP_AIRPORTS`表の`STATE_NAME`列と設定しています。
![スクリーンショット 2023-12-16 162519.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/b800814b-e832-0a64-fceb-5329ecab4537.png)
VPD設定のPL/SQLコード上では、以下の部分です。
    ```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州内の空港しか表示されません。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/bd73d513-e49a-4dc3-e637-6a6b305d206d.png)

このように、Oracle DatabaseのVPDを使うことで、アプリにログインするユーザーごとに、表示する地図データを制限することができました。

# 参考
+ <a href="https://qiita.com/wahagon/items/6d0dd35ce714a7f2d9f2" target="_blank" rel="noopener noreferrer">元のアプリを作成する記事
</a>

+ <a href="https://apexugj.blogspot.com/2021/08/database-security07-vpd.html" target="_blank" rel="noopener noreferrer">APEXでの仮想プライベート・データベースの活用</a>