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

A pre authenticated request URL allows anyone — human user or application — to access data from an Oracle Database as a JSON document using a HTTP request

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…