Live HTTP access to JSON document with Oracle Database 23ai data set using Pre Authenticated Request URL

A very nice, powerful and potentially somewhat dangerous new feature in Oracle Database 23ai: Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database. In summary: with a single statement we can create a URL that provides direct HTTP access to a dataset (that is returned as a JSON document). Nothing needs to be configured to make this happen – at least on Oracle Cloud – as I will demonstrate next.

Of course the user that creates the Pre Authenticated Request URL requires special privileges (execute on dbms_data_access).

The PAR URL can be created for a table or a view or for a specific SELECT statement. The URL returns the live results – not a materialized document at the time of creation of the URL. The PAR has an expiry date or alternatively an expiration count (the number of times the URL can be accessed, which can be one for single use only) and of course it can be deleted at any moment.

When a dataset is accessed on behalf of a PAR, a new application context variable can be accessed: sys_context(‘DATA_ACCESS_CONTEXT$’, ‘USER_IDENTITY’). The value returned is the value passed to GET_PREAUTHENTICATED_URL in the application_user_id parameter. This value can be used in a VPD policy to restrict the access to specific records.

A quick demonstration:

I have created a SCOTT schema in my 23ai database (autonomous, running on Oracle Cloud free tier). I have created the EMP and DEPT tables and loaded the familiar dataset into them.

As a user with DBA privileges I then create the Pre Authenticated Request URL:

The statement:

DECLARE

status CLOB;

BEGIN

      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(

schema_name => ‘SCOTT’,

          schema_object_name    => ‘EMP’,

          expiration_minutes    => 360,

result => status);

dbms_output.put_line(status);

END;

returns:

{“status”:”SUCCESS”,”id”:”S3InuiOWxnJGX4zl121EgCszsGVqMxtMEWTUc57wuByV4TZNpHImG1
9UdHNDmR-c”,”preauth_url”:”https://dataaccess.adb.us-ashburn-1.oraclecloudapps.c
om/adb/p/-4fmoMsZn_8QWr4asIab_zpKXo4QoumT5goL0twKv8KzbE69AZgF7vTAJG6UQq1RU3cwlTs
_36s/data“,”expiration_ts”:”2024-05-09T13:56:29.959Z”,”expiration_count”:null}

The URL is shown in bold.

Accessing the data is as simple as accessing that URL. In a browser this looks like this:

This means for example that web applications can fetch data from Oracle Database directly –without intermediate components and server side programming and setups. Note however that anyone with the URL can access the data. There is no security enforced and no network limitation.

For specific use cases however this feature offers tremendous value. Implementing mock APIs, providing (generated/anonymized) test data come to mind but that is just scratching the surface.

Resources

Oracle Database documentation:

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

package DBMS_DATA_ACCESS: https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-data-access.html#GUID-42E40F61-8BD9-42E2-AF38-8572C6E00BE4