Autonomous Database：表やクエリに対する事前認証済リクエスト（PAR）URLを作成してデータを共有してみた

Last updated at 2024-02-22Posted at 2024-02-21

はじめに

Autonomous Databaseの新機能として、表やビュー、SELECT文の結果に対して事前認証済リクエスト（PAR）URLを作成してデータを共有できる機能（DBMS_DATA_ACCESSパッケージ）が追加されたので、早速検証してみました。

こちらの機能によって、組織内外へのセキュアなデータの共有が簡単に実現できるようになりました。

1. 事前準備

adminユーザとしてAutonomous Databaseに接続します。

opc@tools ~]$ sql admin/************@adb


SQLcl: Release 23.2 Production on Wed Feb 21 18:47:07 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.1.0

SQL>

事前認証済リクエスト（PAR）URLを作成するユーザ（ここではmoviestream）にDBMS_DATA_ACCESSパッケージの実行権限を付与します。

SQL> GRANT EXECUTE ON DBMS_DATA_ACCESS TO moviestream;

Grantが正常に実行されました。

SQL>

ログアウトします。

SQL> exit
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.1.0から切断されました
[opc@tools ~]$

事前認証済リクエスト（PAR）URLを作成するユーザとしてAutonomous Databaseに接続します。

opc@tools ~]$ sql moviestream/************@adb


SQLcl: Release 23.2 Production on Wed Feb 21 18:47:07 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.1.0

SQL>

事前認証済リクエスト（PAR）URLを作成して共有する表（ここではtest）を作成し、テストデータをINSERTします。

SQL> CREATE TABLE test (
  2      id   NUMBER,
  3      name VARCHAR2(100)
  4* );

Table TESTは作成されました。

SQL> INSERT INTO test VALUES (1, 'Michael');

1行挿入しました。

SQL> INSERT INTO test VALUES (2, 'Jane');

1行挿入しました。

SQL> INSERT INTO test VALUES (3, 'Matt');

1行挿入しました。

SQL>

2. 事前認証済リクエスト（PAR）URLの作成

事前認証済リクエスト（PAR）URLの作成にはDBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URLプロシージャを使用します。

主なパラメータを以下の表にまとめました。

パラメータ	設定内容
schema_name	PAR URLの作成対象となるオブジェクトがあるスキーマ名
schema_object_name	PAR URLの作成対象となるオブジェクト名
sql_statement	PAR URLの作成対象となるSELECT文
expiration_minutes	PAR URLの有効期間（分）
expiration_count	PAR URLへのアクセスを許可する回数
result	PAR URLを含む実行結果を格納する変数を指定

先ほど作成したmoviestreamスキーマ内のtest表に対して、有効期間を360分の事前認証済リクエスト（PAR）URLを作成します。

SQL> set serveroutput on
SQL> DECLARE
  2     status CLOB;
  3  BEGIN
  4      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
  5          schema_name => 'MOVIESTREAM',
  6          schema_object_name    => 'TEST',
  7          expiration_minutes    => 360,
  8          result                => status
  9      );
 10     
 11     DBMS_OUTPUT.PUT_LINE(status);
 12  END;
 13* /
{"status":"SUCCESS","id":"nFTqVCxG27g4KjGKWDyPq1HbTkczqpc6ab0NMH2KFOGkBw0kU7c4UQXFdA1Ed8xt","preauth_url":"https://dataaccess.adb.ap-to
kyo-1.oraclecloudapps.com/adb/p/ifDZPs5iUn-88HJS1haFykn1ZBhfKa9bBNaHHKvuaPIU_Ld6QhZuSa3BWQW8NEE5lFvyZXyo_K8/data","expiration_ts":"2024
-02-21T15:52:34.985Z","expiration_count":null}


PL/SQLプロシージャが正常に完了しました。

SQL>

DBMS_OUTPUT.PUT_LINEプロシージャによって、DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URLプロシージャの実行結果を格納した変数statusの内容が表示されています。

{"status":"SUCCESS","id":"nFTqVCxG27g4KjGKWDyPq1HbTkczqpc6ab0NMH2KFOGkBw0kU7c4UQXFdA1Ed8xt","preauth_url":"https://dataaccess.adb.ap-to
kyo-1.oraclecloudapps.com/adb/p/ifDZPs5iUn-88HJS1haFykn1ZBhfKa9bBNaHHKvuaPIU_Ld6QhZuSa3BWQW8NEE5lFvyZXyo_K8/data","expiration_ts":"2024
-02-21T15:52:34.985Z","expiration_count":null}

この中のpreauth_urlの値がDBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URLプロシージャによって作成されたtest表のPAR URLです。

※事前認証済リクエスト（PAR）URLは、作成時にしか表示されませんのでご注意ください。

ブラウザからこのURLにアクセスしてみます。

こちらの内容が表示されました。

{"items":[{"ID":1,"NAME":"Michael"},{"ID":2,"NAME":"Jane"},{"ID":3,"NAME":"Matt"}],"hasMore":false,"limit":100,"offset":0,"count":3,"links":[{"rel":"self","href":"https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com/adb/p/ifDZPs5iUn-88HJS1haFykn1ZBhfKa9bBNaHHKvuaPIU_Ld6QhZuSa3BWQW8NEE5lFvyZXyo_K8/data"}]}

配列itemsの中に、test表のデータがセットされていることがわかります。

次に、先ほど作成したmoviestreamスキーマ内のtest表に対するSELECT文に対して、有効アクセス回数が10回の事前認証済リクエスト（PAR）URLを作成します。

SQL> DECLARE
  2      status CLOB;
  3  BEGIN
  4      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
  5          sql_statement     => 'SELECT id, name FROM test WHERE id < 3',
  6          expiration_count  => 10,
  7          result            => status
  8      );
  9  
 10      DBMS_OUTPUT.PUT_LINE(status);
 11  END;
 12* /
{"status":"SUCCESS","id":"nbL9N3njr8I1Ta04hQRtzzBMCyQR-fctKMZbhcdzqGG-DIoTH4bOtTW6ZLt_GqDE","preauth_url":"https://dataaccess.adb.ap-to
kyo-1.oraclecloudapps.com/adb/p/tGcvWKWLP5b6eBvu6Gbmis_pYDlKPtWRib6HFCO3o-fncD0N3ESbdBNpveCHI7fAE7C_JIMYq-E/data","expiration_ts":"2024
-05-21T10:07:53.532Z","expiration_count":10}


PL/SQLプロシージャが正常に完了しました。

SQL>

DBMS_OUTPUT.PUT_LINEプロシージャによって、DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URLプロシージャの実行結果を格納した変数statusの内容が表示されています。

この中のpreauth_urlの値がDBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URLプロシージャによって作成されたtest表のPAR URLです。

先ほどと同様に、ブラウザからpreauth_urlのURLににアクセスしてみます。

こちらの内容が表示されました。

{"items":[{"ID":1,"NAME":"Michael"},{"ID":2,"NAME":"Jane"}],"hasMore":false,"limit":100,"offset":0,"count":2,"links":[{"rel":"self","href":"https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com/adb/p/tGcvWKWLP5b6eBvu6Gbmis_pYDlKPtWRib6HFCO3o-fncD0N3ESbdBNpveCHI7fAE7C_JIMYq-E/data"}]}

配列itemsの中に、SELECT文の結果のデータがセットされていることがわかります。

なお、設定した有効期間やアクセス回数を超えた場合は、以下のようなエラーが返ります。

3. アクティブな事前認証済リクエスト（PAR）URLの確認

アクティブな事前認証済リクエスト（PAR）URLの情報はDBMS_DATA_ACCESS.LIST_ACTIVE_URLSファンクションで確認できます。

SQL> SELECT JSON_SERIALIZE(DBMS_DATA_ACCESS.LIST_ACTIVE_URLS PRETTY) active_urls FROM dual;

ACTIVE_URLS
________________________________________________________________________________________
[
  {
    "id" : "nbL9N3njr8I1Ta04hQRtzzBMCyQR-fctKMZbhcdzqGG-DIoTH4bOtTW6ZLt_GqDE",
    "sql_statement" : "SELECT id, name FROM test WHERE id < 3",
    "created_by" : "MOVIESTREAM",
    "consistent" : false,
    "expiration_time" : "2024-05-21T10:07:53.532Z",
    "expiration_count" : 10,
    "access_count" : 1,
    "created" : "2024-02-21T10:07:53.562Z"
  },
  {
    "id" : "nFTqVCxG27g4KjGKWDyPq1HbTkczqpc6ab0NMH2KFOGkBw0kU7c4UQXFdA1Ed8xt",
    "schema_name" : "MOVIESTREAM",
    "schema_object_name" : "TEST",
    "created_by" : "MOVIESTREAM",
    "consistent" : false,
    "expiration_time" : "2024-02-21T15:52:34.985Z",
    "access_count" : 1,
    "created" : "2024-02-21T09:52:35.027Z"
  }
]    

SQL>

4. 事前認証済リクエスト（PAR）URLの無効化

DBMS_DATA_ACCESS.INVALIDATE_URLプロシージャを使用して、有効な事前認証済リクエスト（PAR）URLを無効化できます。
パラメータidにBMS_DATA_ACCESS.LIST_ACTIVE_URLSファンクションを実行して確認したidを指定します。

SQL> DECLARE
  2      status CLOB;
  3  BEGIN
  4      DBMS_DATA_ACCESS.INVALIDATE_URL(
  5          id => 'nbL9N3njr8I1Ta04hQRtzzBMCyQR-fctKMZbhcdzqGG-DIoTH4bOtTW6ZLt_GqDE',
  6          result => status
  7      );
  8      DBMS_OUTPUT.PUT_LINE(status);
  9  END;
 10* /
{"status":"SUCCESS"}


PL/SQLプロシージャが正常に完了しました。

SQL> DECLARE
  2      status CLOB;
  3  BEGIN
  4      DBMS_DATA_ACCESS.INVALIDATE_URL(
  5          id => 'nFTqVCxG27g4KjGKWDyPq1HbTkczqpc6ab0NMH2KFOGkBw0kU7c4UQXFdA1Ed8xt',
  6          result => status
  7      );
  8      DBMS_OUTPUT.PUT_LINE(status);
  9  END;
 10* /
{"status":"SUCCESS"}


PL/SQLプロシージャが正常に完了しました。

SQL>

DBMS_DATA_ACCESS.LIST_ACTIVE_URLSファンクションで有効な事前認証済リクエスト（PAR）URLを確認します。

SQL> SELECT JSON_SERIALIZE(DBMS_DATA_ACCESS.LIST_ACTIVE_URLS PRETTY) active_urls FROM dual;

ACTIVE_URLS    
______________ 
[
]            

SQL>

作成した事前認証済リクエスト（PAR）URLが全て無効になり、有効な事前認証済リクエスト（PAR）URLがないことが確認できました。

注意点

マニュアルに記載のある通り、以下のような制限があります。（2024/2現在）

・DB内のアクティブなPAR URLの数は最大128個
・PAR URLの有効期間または最大アクセス回数のいずれかの指定が必要（有効期間を無期限にしたり、回数の制限なくアクセス可能にすることは不可）
・データアクセスに使用するSQLはSELECT文のみ利用可能、SQL内でバインド変数の利用は不可
・1回のアクセスで取得できるレコード数は最大100レコード（出力結果が100レコードを超える場合はページ送りが必要、レスポンス内に次ページへのリンクが含まれる）
・1回のレスポンスの最大サイズは1MB

参考情報

・Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database
・DBMS_DATA_ACCESS Package
・DBMS_OUTPUT.PUT_LINEプロシージャ

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up