データベース・セキュリティの活用(2) - 権限の追加

本記事に記載されている手順で、準備として実行が必要な操作は、データベース・アクションからのCREATE ROLE文とGRANT文の実行のみです。

Oracle APEXのアプリケーションはアプリケーション定義属性のセキュリティ、データベース・セッションの解析対象スキーマとして設定されたユーザーの権限でアプリケーション（正確にはアプリケーションに記載のあるSQLおよびPL/SQLコード)を実行します。

今回はアプリケーションをワークスペースAPEXDEVに作成し、そのアプリケーションからスキーマHRに存在する表EMPおよびDEPTにアクセスします。作成するアプリケーションの解析対象スキーマはAPEXDEVとするため、データベース・ユーザーAPEXDEVにスキーマHRの表EMP、DEPTのアクセス権限が必要です。

ユーザーAPEXDEVに必要な権限を与えるために実行するスクリプトは以下になります。ロールHR_ROLEを作成し、必要な権限をHR_ROLEに割り当てたのち、ユーザーAPEXDEVにロールHR_ROLEを割り当てます。

ユーザーADMINにてデータベース・アクションに接続し、開発のSQLより上記のスクリプトを実行します。

アプリケーションの解析対象スキーマと、表やビューなどのデータベース・オブジェクトが含まれるスキーマが異なると、データベースの標準機能であるGRANT文による権限の割り当ておよびロールを活用できます。

解析対象スキーマに指定したユーザーが、表へのアクセス権限を持っていない場合は以下のようにORA-00942: 表またはビューが存在しません。といったエラーが発生します。

アプリケーションの解析対象スキーマとして割り当て可能なスキーマ(データベース・ユーザー)は、Oracle APEXの管理サービスより、ワークスペースの管理のワークスペースとスキーマの割当ての管理から行います。

デフォルトではワークスペース作成時に指定したスキーマがひとつだけ割り当てられています。そのスキーマがデフォルトの解析対象スキーマとなります。

ワークスペースには複数のスキーマを割り当てることができます。ここで複数のスキーマが割り当てられていると、APEXのアプリケーション開発者は作成しているアプリケーションの解析対象スキーマを、割り当て済みのスキーマのどれかに変更できます。

例えば上記の画面より、ワークスペースAPEXDEVにスキーマHRを割り当てます。スキーマの追加をクリックします。（以下は作業の例示であり、実施は不要です。）

割り当てるスキーマは既存のHRなので、スキーマとして既存を選び、次へ進みます。

ワークスペースとしてAPEXDEVを選択します。次へ進みます。

スキーマとしてHRを選択します。次へ進みます。

スキーマの追加をクリックすると操作は完了です。

ワークスペースAPEXDEVにスキーマHRの割り当てが追加されました。

ワークスペースに複数のスキーマが割り当たっていると、開発者によってアプリケーションの解析対象スキーマが選択できるようになります。

アプリケーションに記述されているSQLやPL/SQLは解析対象スキーマの権限で実行されるため、不必要に強い権限をもったスキーマ(データベース・ユーザー)をワークスペースに割り当てることにはセキュリティ上の問題があります。

本記事は解析対象スキーマはつねにAPEXDEVとして作業を進めます。開発者によって恣意的にアプリケーションの解析対象スキーマを変更されないよう、スキーマHRをワークスペースAPEXDEVに割り当てるといった作業は行いません。

続く

データベース・セキュリティの活用(1) - ワークスペースの準備

Always FreeのAutonomous Database(Autonomous Transaction Processing)のインスタンスをひとつ作成した直後から作業を始めます。

APEXアプリケーションを作成するワークスペースをAPEXDEV、データを保持するワークスペースをHRとして作成します。ワークスペースHRを作成するときにデータベースのスキーマHRも作成しますが、これはサンプル・データセットEMP/DEPTをスキーマHRに導入するためです。スキーマHRは上記のサンプル・データセットのインストール以外の用途（例えばAPEXのアプリケーション開発等）では使用しません。

Autonomous Database作成直後にAPEXにアクセスすると、開発に使用できるワークスペースが未作成であるため管理サービスへのサインインを求められます。作成したAutonomous DatabaseのユーザーADMINのパスワードを入力し、管理にサインインします。サインインの前に言語を日本語に切り替えておきます。

ワークスペースがひとつもないため、ワークスペースの作成を求められます。ボタンをクリックします。

データベース・ユーザー(スキーマ名)、パスワード、ワークスペース名の入力を求められます。今回はAPEXDEVとします。ここで指定するデータベース・ユーザー名とパスワードを、ワークスペースへのサインインに使用します。パスワードが意図した通りに入力できているか注意が必要です。大文字ではなく小文字が入っていたとしても後から確認する方法はなく、パスワードのリセット(ALTER USER ... IDENTIFIED BY ...といったコマンドの実行)が必要になります。

ワークスペースAPEXDEVが作成されたら、すぐにワークスペースHRを作成します。ワークスペースの作成をクリックします。

データベース・ユーザーはHR、パスワードを設定し、ワークスペース名はHRとします。ワークスペースの作成をクリックします。

ワークスペースHR(およびスキーマHR)が作成されます。管理サービスからサインアウトしてワークスペースHRにサインインします。メッセージに含まれるHRのリンクをクリックします。

ワークスペースHRへのサインイン画面が開きます。データベース・ユーザーHRのパスワードを入力し、ワークスペースにサインインします。

ワークスペースHRにサインインしたら、SQLワークショップのユーティリティよりサンプル・データセットを呼び出します。

サンプル・データセットのEMP/DEPTをインストールします。

言語はEnglish、インストール先のスキーマはHRとします。今回は表EMPのENAMEを使う認証スキームを作成するため、言語はEnglishを選択します。次に進みます。

確認画面が表示されるので、データセットのインストールを実行します。表EMPとDEPT、ビューEMP_DEPT_VがスキーマHRに作成されます。

データセットのインストールが完了したら終了します。アプリケーションの作成は行いません。

ワークスペースの準備作業は以上で終了です。ワークスペースHRからサインアウトします。左上のアイコンをクリックし、サインアウトを実行します。

これ以降、ワークスペースHRにサインインして行う作業はありません。ワークスペースAPEXDEVのみ使用します。

続く

データベース・セキュリティの活用(0) - はじめに

 Oracle APEXのアプリケーションを開発するにあたって、Oracle Databaseが提供するセキュリティを強化する機能を活用することができます。それらの機能をAlways FreeのAutonomous Database上に実装することにより、機能の概要と活用方法について理解します。

作業および機能ごとに、それぞれ以下のように記事にしています。

0. はじめに - こちらの記事

1. ワークスペースの準備

アプリケーションを開発するためのワークスペースAPEXDEVと表EMP/DEPTを保持するワークスペース(=データベース・スキーマ)HRを作成します。

2. 権限の追加

ユーザーAPEXDEVからスキーマHRの表EMPおよびDEPTにアクセスする権限を与えます。標準のGRANT文を実行します。 

3. アプリケーションの作成

 テストに使用するため、意図的にSQLインジェクションへの脆弱性を持つアプリケーションを作成します。

4. 統合監査(Unified Audit)

 統合監査ポリシーを定義し、表EMPへのアクセスの監査証跡を取得します。

5. ファイングレイン監査(Fine Grained Audit)

 監査証跡を取得する際に、詳細な条件を与えます。

6. 権限分析(Privilege Analysis)

 アプリケーションの実行時に使用されているデータベースの権限と、使用されていない権限を分析したレポートを生成します。

7. 仮想プライベート・データベース(Virtual Private Database)

 仮想プライベート・データベースを構成し、SQLインジェクションを防ぎます。

8. Real Application Security

Real Application Securityを構成し、SQLインジェクションを防ぎます。

9. Data Redaction

Data Redactionを構成し、機密データを伏字に変えて表示します。 

10. 透過型機密データ保護(Transparent Sensitive Data Protection)

透過型機密データ保持を構成し、機密データを保護します。 

11. Database Vault

 管理者権限を持つユーザーによる、ユーザー・データのへのアクセスを禁止します。

記事の1から3までは準備作業になります。それ以降はできるだけ独立して参照できるように記述しています。例外として、仮想プライベート・データベースとReal Application Securityでの認証スキームは前出の構成を引き継いでいます。

記事の順番に従って作業を行い、内容が正しく実施できることを確認しています。

それぞれの機能についての説明は限られています。無料で利用可能な環境を使って、実際に手を動かして動作を確認することを目的としています。

続く

Autonomous DatabaseのAPEXでUTL_HTTPを使用する

 UTL_HTTPが動かない、との相談があったので検証してみました。

Autonomous DatabaseでのUTL_HTTPの使用には制限があります。以下の内容に該当していないのが前提です。

共有Exadataインフラストラクチャ上のOracle Autonomous Databaseの使用
PL/SQLパッケージの制限 - UTL_HTTPの制限

以下のURLをテストで呼び出してみます。

https://static.oracle.com/cdn/apex/21.1.2/apex_version.txt

DECLARE
    req   UTL_HTTP.REQ;
    resp  UTL_HTTP.RESP;
    value raw(1024);
BEGIN
    utl_http.set_wallet('');
    req := UTL_HTTP.BEGIN_REQUEST('https://static.oracle.com/cdn/apex/21.1.2/apex_version.txt');
    UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
    resp := UTL_HTTP.GET_RESPONSE(req);
    UTL_HTTP.READ_raw(resp, value, 1024);
    dbms_output.put_line(utl_raw.cast_to_varchar2(value));
    UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
        UTL_HTTP.END_RESPONSE(resp);
END;

実行すると以下が表示されます。

Application Express Version: 21.1

以下のコードによってACLを定義します。principalとして、ワークスペースの解析対象スキーマを指定します。

begin
dbms_network_acl_admin.create_acl(acl => 'test-oracle-cdn.xml',
description => 'Oracle CDN Access',
principal => 'APEXDEV',
is_grant => true,
privilege => 'connect');
--
dbms_network_acl_admin.assign_acl(acl => 'test-oracle-cdn.xml',
host => 'static.oracle.com');
--
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'test-oracle-cdn.xml',
principal => 'APEXDEV',
is_grant => true,
privilege => 'resolve');
end;
/

または以下のコードでも定義できます。ACLの名称はシステムによって決められます。

begin
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'static.oracle.com',
    ace => xs$ace_type(
        privilege_list => xs$name_list('connect','resolve'),
        principal_name => 'APEXDEV',
        principal_type => xs_acl.ptype_db));
  commit; 
end;
/

データベース・アクションの開発のSQLから実行します。

登録されているACLの確認には以下のSQLが使用できます。APEXDEVはワークスペースのスキーマになります。connectの部分はresolveに変更すると、resolveの権限について確認できます。

select host, acl,

decode(dbms_network_acl_admin.check_privilege_aclid(aclid, 'APEXDEV', 'connect'), 1, 'GRANTED', 0, 'DENIED', NULL) privilege

from dba_network_acls;

ACLからホストを取り除いたり、ACLを削除するには以下のスクリプトを呼び出します。ACL名やhostなどの引数は環境に合わせて変更します。

begin

dbms_network_acl_admin.unassign_acl(acl => 'test-oracle-cdn.xml',

host => 'static.oracle.com');

dbms_network_acl_admin.drop_acl(acl => 'test-oracle-cdn.xml');

end;

/

以上になります。

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

完

対話グリッドのセルのデータが負数の場合に修飾する

 表題の件で相談があって、以前の記事[対話グリッドの列の計算方法について]を紹介しました。ただこれは計算する方法だけで、修飾する方法は含んでいないので、ちょっと確認してみました。

行いたいのはRevenueが負数のときは、矢印の追加と文字を赤にすることです。

最初に対話グリッドのSQL問合せを更新します。REVENUEが負数になるときに-1、そうでないときには1を返す列FLAGを追加します。

select ID,

PRODUCT_NAME,

PRICE,

QUANTITY,

NVL(PRICE,0) * NVL(QUANTITY,0) as REVENUE,

case

when (NVL(PRICE,0) * NVL(QUANTITY,0)) < 0 then

-1

else

1

end FLAG

from DMT_ORDERS

列REVENUEの表示にTemplate Directiveを使うので、その条件の指定に使います。

列FLAGは更新されることはありません。タイプは数値フィールド、ソースの問合せのみをONにします。

列REVENUEと同様に、列PRICEかQUANTITYが更新されたときに対話グリッドの値を更新します。JavaScript初期化コードを以下のように記述します。

function(options){
    const formatter = Intl.NumberFormat("ja-JP");
    options.defaultGridColumnOptions = {
        dependsOn: ['PRICE', 'QUANTITY'],
        calcValue: function(argsArray, model, record){
            const price = parseInt(model.getValue(record, 'PRICE').replaceAll(",","") || 0);
            const quantity = parseInt(model.getValue(record, 'QUANTITY').replaceAll(",","") || 0);
            if(isNaN(price) || isNaN(quantity)){
                return 'error';
            } else {
                if ((price * quantity) < 0)
                {
                    return "-1";
                };
                return "1";
            }
        }
    };
    return options;
}

列REVENUEのJavaScript初期化コードにcellTemplateの指定を追加します。また、dependsOnの列としてFLAGを追加します。

function(options){
    const formatter = Intl.NumberFormat("ja-JP");
    options.defaultGridColumnOptions = {
        cellTemplate: '{case FLAG/}{when -1/}<div class="u-danger-text"><span aria-hidden="true" class="fa fa-arrow-down-alt"></span>&REVENUE.</div>{when 1/}&REVENUE.{endcase/}',
        dependsOn: ['PRICE', 'QUANTITY', 'FLAG'],
        calcValue: function(argsArray, model, record){
            const price = parseInt(model.getValue(record, 'PRICE').replaceAll(",","") || 0);
            const quantity = parseInt(model.getValue(record, 'QUANTITY').replaceAll(",","") || 0);
            if(isNaN(price) || isNaN(quantity)){
                return 'error';
            } else {
                return formatter.format(price * quantity);
            }
        }
    };
    return options;
}

対話グリッド上の列FLAGは表示する必要がないので、列の設定で非表示にします。デフォルトのレポートも更新すると良いでしょう。 

 以上で完成です。 

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

完

日付データの変換

 YYYY/MM/DDのフォーマットでページ・アイテムに入力した日付を、YYYY/MM/DD(DY)に変換したいとの相談がありました。

Oracle APEX 21.1より日付と数値として入力した値は、すぐに書式マスクが適用されます。

ですので、以下の方法は21.1以前のバージョンでのみ有効です。

PL/SQLでの日付データの変換

ページ・アイテムP1_DATEPがあり、タイプは日付ピッカーとします。書式マスクにYYYY/MM/DD(DY)を指定します。

日付ピッカーを使って日付を入力すると書式マスクに沿った値が設定されますが、手動で入力するときには曜日の指定は省きたいです。そのため、YYYY/MM/DDを入力したらYYYY/MM/DD(DY)に変換します。

ページ・アイテムP1_DATEPに動的アクションを作成します。タイミングのイベントは変更です。

TRUEアクションは値の設定とし、設定のタイプの設定としてPL/SQL Function Bodyを選択します。PL/SQL Function Bodyとして以下を記述します。

declare

l_valid number;

l_date varchar2(20);

begin

select validate_conversion(:P1_DATEP as date, 'YYYY/MM/DD(DY)') into l_valid

from dual;

if l_valid = 1 then

-- 変換不要

return :P1_DATEP;

end if;

l_date := substr(:P1_DATEP,1,10);

select validate_conversion(l_date as date, 'YYYY/MM/DD') into l_valid

from dual;

if l_valid = 1 then

-- 曜日を追加する

return to_char(to_date(l_date,'YYYY/MM/DD'), 'YYYY/MM/DD(DY)');

end if;

-- それ以外はそのまま

return :P1_DATEP;

end;

送信するアイテムにP1_DATEPを指定します。特殊文字をエスケープをOFF、変更イベントの禁止をONにします。P1_DATEPが変更された際にP1_DATEPを変更しているため、変更イベントを禁止しないと動的アクションが呼び出され続けます。影響を受ける要素のアイテムはP1_DATEPです。

これで日付データは変更されます。ただし、変更の際にはP1_DATEPの値がブラウザからデータベース・サーバーに送信され、PL/SQLコードが実行された結果がブラウザに返されるという動作になります。そのためレスポンスはよくありません。

JavaScriptでの日付データの変換

ブラウザ側で日付データを変換してみます。Dayjsというライブラリを使用します。

最初に必要なライブラリをロードします。今回はページでロードします。ページ・プロパティのJavaScriptのファイルURLとして以下を指定します。

https://unpkg.com/dayjs@1.8.21/dayjs.min.js

https://unpkg.com/dayjs@1.8.21/locale/ja.js

ページ・ロード時にライブラリを日本語で初期化します。ページ・ロード時に実行に以下を記述します。

dayjs.locale('ja')

動的アクションはPL/SQLと同様ですが、TRUEアクションとしてJavaScriptコードの実行を選択します。設定のコードとして以下を記述します。

let ds = apex.item("P1_DATEJ").getValue();

if (dayjs(ds,"YYYY/MM/DD").isValid())

{

let dt = dayjs(ds, "YYYY/MM/DD").format("YYYY/MM/DD(dd)");

// 第３引数のpSuppressChangeEventはtrue

apex.item("P1_DATEJ").setValue(dt,dt,true);

}

apex.item("P1_DATEJ").setValue()を呼び出してブラウザ上のページ・アイテムに直接値を設定しているため、影響を受ける要素の指定は不要です。また、第３引数のpSuppressChangeEventにtrueを渡すことにより、変更イベントが禁止されます。

以上で日付データが変更されます。

ページ・アイテムの値を動的アクションによって変更するときは、変更イベントの禁止の設定に注意しましょう。

以上になります。

確認に使用したアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/date-format-conversion.sql

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

完

Oracle REST Data ServicesでのSQLの実行

 以前にOracle APEXによるSQLの実行という題で、Oracle APEXではSQLがどのように実行されているか紹介しています。Oracle REST Data ServicesでのSQLの実行はAPEXとは異なり、Oracle Databaseのプロキシ接続を使っています。

以下、確認作業のログになります。

最初にAutonomous Databaseで確認します。Always FreeのAutonomous Transaction ProcessingのインスタンスにユーザーAPEXDEVが作成されています。

データベース・アクションのデータベース・ユーザーを開いて確認します。

RESTの有効化がされているのはユーザーADMINのみです。

ビューPROXY_USERSを確認します。

select * from proxy_users

PROXY            CLIENT             AUTHENTICATION FLAGS                               
---------------- ------------------ -------------- ----------------------------------- 
C##CLOUD$SERVICE ADMIN              NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 
ORDS_PUBLIC_USER ADMIN              NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 
OMLMOD$PROXY     OML$MODELS         NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 
ORDS_PUBLIC_USER ORDS_PLSQL_GATEWAY NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 
ADMIN            RMAN$VPC           NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 

Autonomous Databaseでのプロキシ・ユーザーの初期状態です。

ユーザーAPEXDEVのRESTの有効化を実行します。

ポップアップされるダイアログのREST対応ユーザーをクリックします。

再度ビューPROXY_USERSを確認します。ORDS_PUBLIC_USERをプロキシとして、ユーザーAPEXDEVにて接続できるようになっています。

PROXY            CLIENT             AUTHENTICATION FLAGS                               
---------------- ------------------ -------------- ----------------------------------- 
C##CLOUD$SERVICE ADMIN              NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 
ORDS_PUBLIC_USER ADMIN              NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 
OMLMOD$PROXY     OML$MODELS         NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 
ORDS_PUBLIC_USER ORDS_PLSQL_GATEWAY NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 
ADMIN            RMAN$VPC           NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 
ORDS_PUBLIC_USER APEXDEV            NO             PROXY MAY ACTIVATE ALL CLIENT ROLES 

RESTの有効化はプロシージャORDS_ADMIN.ENABLE_SCHEMAを呼び出しています。この中で行われている処理のひとつとして、プロキシ接続の有効化が行われています。

簡単なRESTサービスを実装して、RESTサービスを実行しているセッションの情報を確認してみます。

データベース・アクションにユーザーAPEXDEVでサインインし、RESTを開きます。モジュールから作成します。

左上にあるモジュールの作成をクリックします。

モジュール名をtest、ベース・パスを/test/として、モジュールを作成します。公開はONにします。モジュール名、ベース・パスはtestでなくてもかまいません。

モジュールが作成されたら、続いてテンプレートの作成を実行します。

URIテンプレートをsessionとし、作成をクリックします。URIテンプレートについても、sessionでなければいけない、ということはありません。

テンプレートが作成されたら、ハンドラの作成を実行します。

ハンドラのソースとして、以下のSELECT文を指定します。

select

sys_context('userenv','session_user') as session_user,

sys_context('userenv','session_schema') as session_schema,

sys_context('userenv','current_schema') as current_schema,

sys_context('userenv','proxy_user') as proxy_user

from　dual

メソッドはGET、ソース・タイプには収集問合せを選択します。

以上でOracle REST Data Sources側の設定は完了です。実際に呼び出して結果を確認してみます。

RESTサービスを呼び出した結果です。

{"items": [{"session_user": "APEXDEV","session_schema": "APEXDEV","current_schema": "APEXDEV","proxy_user": "ORDS_PUBLIC_USER"}],"hasMore": false,"limit": 25,"offset": 0,"count": 1,"links": []}

proxy_userがORDS_PUBLIC_USERになっていることが確認できます。

Autonomous Databaseでは、データベースの利用者はORDS_PUBLIC_USERを使ってデータベースには接続できません。プロキシ・ユーザーによる接続方法の参考として、オンプレミスの環境でsqlplusを使って接続してみます。

SQL> connect ords_public_user[apexdev]/******@localhost/xepdb1.world

Connected.

SQL> select 

sys_context('userenv','session_user') as session_user,

sys_context('userenv','session_schema') as session_schema,

sys_context('userenv','current_schema') as current_schema,

sys_context('userenv','proxy_user') as proxy_user

from　dual  2    3    4    5    6  

  7  /

SESSION_USER SESSION_SCHEMA   CURRENT_SCHEMA   PROXY_USER

---------------- ---------------- ---------------- ----------------

APEXDEV  APEXDEV   APEXDEV   ORDS_PUBLIC_USER

SQL> 

ユーザーの指定のords_public_user[apexdev]/*****の部分がプロキシ接続の指定方法です。パスワードはユーザーORDS_PUBLIC_USERのものでAPEXDEVではありません。ユーザーAPEXDEVの代わりにORDS_PUBLIC_USERを使っているため、プロキシ(代理という意味）接続になります。

プロキシ接続を許可するコマンドは以下になります。

ALTER USER APEXDEV GRANT CONNECT THROUGH ORDS_PUBLIC_USER;

GRANTの代わりにREVOKEを使うと、プロキシ接続の許可が解除されます。

Oracle APEXとデータベースへの接続方法が異なるため、Oracle REST Data Servicesでは使用するコネクション・プールを分けています。APEX向けのコネクション・プールはapex.xml、Oracle REST Data ServicesのRESTサービス向けはapex_pu.xmlが、コネクション・プールの構成ファイルになります。

APEXでもORDSでも、アプリケーションを開発している時点で接続方法の違いを意識することは無いかと思います。どちらも指定したユーザー(今回の場合ではAPEXDEV)の権限でSQLは実行されます。とはいえ、頭の片隅にでも入れていただき、障害が発生したときなどに役立ていただけると幸いです。

完