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

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

https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-preauthenticated-request-url.html#GUID-976ABD3A-38A0-4E5E-BCA4-9FFB9A748C43

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

# 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プロシージャ](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-data-access.html#GUID-8786637C-FBA3-4B7A-A1BF-DD652B2C67D0)を使用します。

主なパラメータを以下の表にまとめました。
|パラメータ  |設定内容|
|---|---|
|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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/c90d6d82-a65e-7024-e6e3-262eaa7c9ed3.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/443f0cfd-299e-4366-ae1f-7e3668d5f234.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/0b7ce17c-38c5-a4b6-b3bb-b77694136f15.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がないことが確認できました。

# 注意点
[マニュアル](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-preauthenticated-request-url.html#GUID-2B96DB9E-311C-4705-8277-C36B3AF4A3A3)に記載のある通り、以下のような制限があります。（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](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-preauthenticated-request-url.html#GUID-976ABD3A-38A0-4E5E-BCA4-9FFB9A748C43)
・[DBMS_DATA_ACCESS Package](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-data-access.html#GUID-4743FE1F-6D5B-4E39-97D3-0FDEEB848338)
・[DBMS_OUTPUT.PUT_LINEプロシージャ](https://docs.oracle.com/cd/F19136_01/arpls/DBMS_OUTPUT.html#GUID-19FA480D-591E-4584-9650-5D37C4AFA530)