LoginSignup
2
1
@500InternalServerErrorin日本オラクル株式会社

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

Last updated at Posted 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にアクセスしてみます。

スクリーンショット 2024-02-21 18.59.29.png

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

{"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ににアクセスしてみます。
スクリーンショット 2024-02-21 19.32.48.png

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

{"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文の結果のデータがセットされていることがわかります。

なお、設定した有効期間やアクセス回数を超えた場合は、以下のようなエラーが返ります。
スクリーンショット 2024-02-21 19.00.34.png

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プロシージャ

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1