Autonomous Database：SELECT文に対する事前認証済みリクエストURL（PAR URL）でバインド変数を利用する

はじめに

Autonomous Databaseでは、従来から表やビュー、クエリに対して事前認証済みリクエストURL（PAR URL）が作成できましたが、バインド変数を使用したクエリに対してもPAR URLが作成できるようになったので、試してみました。

以前書いたPAR URLの作成に関する記事はこちら。

1. 事前準備

検証用に表empを作成し、3件のレコードを追加しました。

SQL> CREATE TABLE EMP (
  2      empno NUMBER,
  3      empname VARCHAR2(20) 
  4  );

Table created.

SQL> INSERT INTO emp VALUES (10,'JACKSON');

1 row created.

SQL> INSERT INTO emp VALUES (20,'WHITE');

1 row created.

SQL> INSERT INTO emp VALUES (30,'HARTMAN');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM emp;

     EMPNO EMPNAME
---------- --------------------
	    10 JACKSON
	    20 WHITE
	    30 HARTMAN

SQL> 

2. バインド変数を使用したSELECT文に対するPAR URLの作成

DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URLプロシージャを使用して、こちらのバインド変数を使用したSELECT文に対するPAR URLを作成します。

SELECT * FROM emp WHERE empno = :empno

こちらのPL/SQLブロックでDBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URLプロシージャを実行し、実行結果をDBMS_OUTPUT.PUT_LINEプロシージャで表示します。

DECLARE
    status CLOB;
    BEGIN
        DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
            sql_statement        => 'SELECT * FROM emp WHERE empno = :empno',
            expiration_minutes    => 360,
            service_name         => 'HIGH',
            result               => status);
        DBMS_OUTPUT.PUT_LINE(status);
    END;
/

set serveroutput onでDBMS_OUTPUT.PUT_LINEプロシージャの出力をオンにし、上記のPL/SQLブロックを実行します。

SQL> set serveroutput on
SQL> DECLARE
  2      status CLOB;
  3      BEGIN
  4          DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
  5              sql_statement        => 'SELECT * FROM emp WHERE empno = :empno',
  6              expiration_minutes    => 360,
  7              service_name         => 'HIGH',
  8              result               => status);
  9          DBMS_OUTPUT.PUT_LINE(status);
 10      END;
 11  /
{"status":"SUCCESS","id":"UThN6ffRNyzP-hwAyuiTz5yT75hPvjWf9c859B9KQQtJkiWbHdWW5i
MBSN5ce9tH","preauth_url":"https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com
/adb/p/kUQxRLBRvM17ms5BRkvGA5Yu7SQTg04yLHys7sEzhRyDJB7TtGISMe_0wmx0iQgAq1Acu-lTS
TQ/data","expiration_ts":"2024-07-30T06:33:21.302Z","expiration_count":null}

PL/SQL procedure successfully completed.

SQL>

出力された結果内のpreauth_urlの値が、生成されたPAR URLになります。

https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com
/adb/p/kUQxRLBRvM17ms5BRkvGA5Yu7SQTg04yLHys7sEzhRyDJB7TtGISMe_0wmx0iQgAq1Acu-lTS
TQ/data

3. 動作確認

PAR URLにバインド変数の値を渡すには、クエリ・ストリング（URLパラメータ）を使用します。

今回作成したPAR URLでは、empnoというバインド変数を使用していますので、表示されたPAR URLにクエリ・ストリング「?empno=＜empnoの値＞」を追加します。

PAR URLにクエリ・ストリング「?empno=10」を追加して、ブラウザからアクセスしてみます。

https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com/adb/p/kUQxRLBRvM17ms5BRkvGA5Yu7SQTg04yLHys7sEzhRyDJB7TtGISMe_0wmx0iQgAq1Acu-lTSTQ/data?empno=10

以下のようなページが表示されました。
empnoが10のデータが抽出されていることがわかります。

次にPAR URLにクエリ・ストリング「?empno=20」を追加して、ブラウザからアクセスしてみます。

https://dataaccess.adb.ap-tokyo-1.oraclecloudapps.com/adb/p/kUQxRLBRvM17ms5BRkvGA5Yu7SQTg04yLHys7sEzhRyDJB7TtGISMe_0wmx0iQgAq1Acu-lTSTQ/data?empno=20

以下のようなページが表示されました。
empnoが20のデータが抽出されていることがわかります。

以上で、SELECT文に対する事前認証済みリクエストURL（PAR URL）でバインド変数を利用できることが確認できました。

4. PAR URLの無効化

作成したPAR URLを有効期限、有効アクセス回数に到達する前に無効化するには、DBMS_DATA_ACCESS.INVALIDATE_URLプロシージャを使用します。
idにはPAR URL生成時に表示されるPAR URLのidの値を指定します。

SQL> set serveroutput on
SQL> DECLARE
  2      status CLOB;
  3      BEGIN
  4          DBMS_DATA_ACCESS.INVALIDATE_URL(
  5              id => 'UThN6ffRNyzP-hwAyuiTz5yT75hPvjWf9c859B9KQQtJkiWbHdWW5iMBSN5ce9tH',
  6              result => status);
  7          DBMS_OUTPUT.PUT_LINE(status);
  8      END;
  9  /
{"status":"SUCCESS"}

PL/SQL procedure successfully completed.

SQL>

作成したPAR URLが無効化できました。

参考情報

・Autonomous Databaseでの読取り専用データ・アクセスに対する事前認証済リクエストURLの使用
・DBMS_DATA_ACCESS Package