Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

You can generate and manage Pre-Authenticated Request (PAR) URLs for data on Autonomous Database.

Using a PAR URL allows you to easily retrieve data from the database, without requiring you to provide additional information other than the PAR URL to access the data. Any user can access the data by supplying the PAR URL in a browser or using a REST client, subject to security controls enforced by the database. Users can view the retrieved data in tabular format from a browser by appending a query parameter to the PAR URL.

About Pre-Authenticated Request (PAR) URLs on Autonomous Database
Depending on how you generate a Pre-Authenticated Request (PAR) URL, a PAR URL provides access to data in tables or views or by running a SQL query.
Generate a PAR URL for a Table or a View
Shows you the steps to generate a PAR URL that you can use to share access for a schema object (table or view).
Generate a PAR URL with a Select Statement
Shows you the steps to generate a PAR URL that provides access to data using a SQL query statement.
Define a Virtual Private Database Policy to Secure PAR URL Data
By defining Oracle Virtual Private Database (VPD) policies for data that you share with a PAR URL, you can provide fine-grained access control so that only a subset of data, rows, is visible for a specific PAR URL.
List PAR URLs
You can list the active PAR URLs that you generated on an Autonomous Database instance and the ADMIN user can list all active PAR URLs.
Use a PAR URL to Access Data
PAR URL data is retrieved and returned in JSON format and is paginated.
Use PAR URLs to Access Data and View in Table Format
Pre-Authenticated Request (PAR) URL data is retrieved and viewed from a browser in table format.
Invalidate PAR URLs
At any time a user with appropriate privileges can invalidate a PAR URL.
Monitor and View PAR URL Usage
Autonomous Database provides views that allow you to monitor PAR URL usage.
Notes for Using PAR URLs to Share Data
Provides notes for using PAR URLs with Autonomous Database.

Parent topic: Data Sharing

About Pre-Authenticated Request (PAR) URLs on Autonomous Database

Depending on how you generate a Pre-Authenticated Request (PAR) URL, a PAR URL provides access to data in tables or views or by running a SQL query.

When you generate a PAR URL you specify an expiration, either as an expiration time, for example set the PAR URL to expire after 120 minutes, or as an expiration count, for example the PAR URL expires after the PAR URL is used 10 times.

PAR URLs provide the following:

Public Access: Using a PAR URL a data recipient on the public internet can access data when the data resides on an Autonomous Database instance in a private subnet.
Expiration: A data provider specifies expiration for a PAR URL, meaning the PAR URL has a limited time before it expires (up to a maximum of 90 Days).
Expiration Use Count Limits: A data provider can specify a limit on how many times a recipient can use a PAR URL to access data.
Endpoint Transparency: A data provider is able to hide the Autonomous Database name so that it is not visible in a PAR URL.

PAR URL Use Cases

Generating and providing PAR URLs supports the following use cases:

Use Case	Description
Within Organization Collaboration	You can use PAR URLs for emergency data access. In situations where a rapid response is needed, such as during a critical incident investigation, you provide a PAR URL to allow immediate and temporary access to specific data without the need to create new database accounts or modify existing permissions.
BB (Business to Business) Applications	A business partner can easily access data. Using a PAR URL, a business can provide a business partner with a simple way to access data or reports. This can eliminate the need for manual report generation and email distribution.
Third-party Audits and Reviews	When an external auditor or reviewer requires access to specific data for a limited time, a PAR URL can give them the access they need without compromising the overall security of the database.
Data as a Product (Digital Commerce)	Vendors can grant limited or single-use access to purchased content or data using a PAR URL. Once accessed, the URL expires, protecting the product’s exclusivity and ensuring efficient, secure delivery.

Security Best Practices for PAR URLs

Following are some best practices for generating and using PAR URLs:

Set a Short Expiration Time: A PAR URL should only be valid for the minimum time required. The shorter the validity period, the lower the risk if the PAR URL is compromised.
PAR Invalidation: Invalidate a PAR URL immediately when it is no longer required.
Use Appropriate Permissions: A PAR URL runs using the privileges granted to the database user who generates the PAR URL. The user that generates a PAR URL should have the minimum privileges required for providing access to the data.
Content Security: To mitigate risk of sharing unintended dynamic data:
- Create a view on top of the data that you want to share in a PAR URL, and monitor that the view definition is up to date.
- As needed, create a VPD policy when you generate a PAR URL. You can use VPD policies to restrict the rows visible to the PAR URL users.
Load Monitoring: Monitor the PAR URL query load using PerfHub and SQL monitoring.

Enable Compute auto scaling and make sure that the CPU count is appropriately sized for the data set size and PAR URL query load.

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

Generate a PAR URL for a Table or a View

Shows you the steps to generate a PAR URL that you can use to share access for a schema object (table or view).

When a PAR URL runs it uses the privileges granted to the database user who generates the PAR URL. The user that generates a PAR URL should have the minimum privileges required for providing access to the data. To maintain security, Oracle recommends that you do not run DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL as the ADMIN user.

To use a PAR URL to provide access to data as a schema object (table or view):

Identify the table or view that you want to share.

If there are restrictions on the data you want to make available, use the application_user_id parameter when you generate the PAR URL and create a VPD policy to restrict the data that you expose. See Define a Virtual Private Database Policy to Secure PAR URL Data for more information.

Run DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL to generate the PAR URL.

DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
          schema_name => 'ADMIN',
          schema_object_name    => 'TREE_DATA',
          expiration_minutes    => 360,
          result                => status);
       dbms_output.put_line(status);
    END;
/

The DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL procedure does not accept bind variables.

See GET_PREAUTHENTICATED_URL Procedure for more information.

Check the result.
In this example status contains the result that includes information about the PAR URL.
```
{
"status":"SUCCESS",
"id":"Vd1Px7QWASdqDbnndiuwTA_example",
"preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Vdtv..._example_wxd0PN/data",
"expiration_ts":"2023-12-04T23:51:35.334Z"
}
```
You can also use DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to show PAR URLs. See List PAR URLs for more information.

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

Generate a PAR URL with a Select Statement

Shows you the steps to generate a PAR URL that provides access to data using a SQL query statement.

To use a PAR URL to provide to access to data as an arbitrary SQL query statement:

Identify the table or view that contains the information you want to share, as well as the SELECT statement on the table or view that you want to use.

If there are restrictions on the data you want to make available, use the application_user_id parameter when you generate the PAR URL and create a VPD policy to restrict the data that you expose. See Define a Virtual Private Database Policy to Secure PAR URL Data for more information.
Run DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL to generate the PAR URL.
```
DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
          sql_statement        => 'SELECT species, height FROM TREE_DATA',
          expiration_count     => 10,
          result               => status);
       dbms_output.put_line(status);
    END;
/
```
The DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL procedure does not accept bind variables in the SQL statement you provide with sql_statement.

Note:
The sql_statement value must be a SELECT statement.

This expiration_count parameter specifies the PAR URL expires and is invalidated after 10 uses and without an expiration_time specified, the expiration time is set to the default 90 days.

See GET_PREAUTHENTICATED_URL Procedure for more information.

Check the result.

In this example status contains the result that includes the PAR URL.

{
   "status":"SUCCESS", 
   "id":"xA6iRGexmapleARc_zz",
   "preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/XA2_example_m4s/data",
   "expiration_ts":"2024-05-16T17:01:08.226Z",
   "expiration_count":10
}

You can use DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to show the PAR URLs. See List PAR URLs for more information.

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

Define a Virtual Private Database Policy to Secure PAR URL Data

By defining Oracle Virtual Private Database (VPD) policies for data that you share with a PAR URL, you can provide fine-grained access control so that only a subset of data, rows, is visible for a specific PAR URL.

Oracle Virtual Private Database (VPD) is a security feature that lets you control data access dynamically at row level for users and applications by applying filters on the same data set. When a PAR URL is accessed, the value of application_user_id specified during PAR URL generation is available through sys_context('DATA_ACCESS_CONTEXT$', 'USER_IDENTITY'). You can define VPD Policies that make use of the value of this Application Context to restrict the data, rows, visible to the application user.

Any user who is granted access to read data with a PAR URL can access and use the data (either a table, a view, or the data provided with a select statement). By defining a VPD policy on the database that generated a PAR URL, you can use the application_user_id value in a SYS_CONTEXT rule to provide more fine-grained control. Consider an example where data is made available with a PAR URL. If you want to restrict access to some of the data you can add a VPD policy.

For example:

Obtain the application_user_id value that you specified when you generated the PAR URL.

Create VPD policy on the database where you generated the PAR URL.

CREATE OR REPLACE FUNCTION limit_sal (v_schema IN VARCHAR2, v_objname IN VARCHAR2)
  RETURN VARCHAR2 authid current_user AS
BEGIN
     RETURN 'employee_id = SYS_CONTEXT(''DATA_ACCESS_CONTEXT$'', ''USER_IDENTITY'')';
END;

See DBMS_RLS for more information.

BEGIN
   DBMS_RLS.ADD_POLICY( 
        object_schema        => 'HR',
        object_name          => 'EMPLOYEE',
        policy_name          => 'POL',
        policy_function      => 'LIMIT_SAL');
END;
/

See DBMS_RLS for more information.

See Using Oracle Virtual Private Database to Control Data Access for more information.

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

List PAR URLs

You can list the active PAR URLs that you generated on an Autonomous Database instance and the ADMIN user can list all active PAR URLs.

Run DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to list the active PAR URLs. For example:

DECLARE    
    result CLOB;
    BEGIN
       result := DBMS_DATA_ACCESS.LIST_ACTIVE_URLS;           
       dbms_output.put_line(result);
    END;
/

See LIST_ACTIVE_URLS Function for more information.

Note:

The behavior of DBMS_DATA_ACCESS.LIST_ACTIVE_URLS is dependent on the invoker. If the invoker is ADMIN or any user with PDB_DBA role, the function lists all active PAR URLs, regardless of the user who generated the PAR URL. If the invoker is not the ADMIN user and not a user with PDB_DBA role, the list includes only the active PAR URLs generated by the invoker.

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

Use a PAR URL to Access Data

PAR URL data is retrieved and returned in JSON format and is paginated.

You can access the data using a PAR URL with a browser or using any REST client. The data returned is paginated to allow you to access a maximum of 100 records at a time, with the total data size in the response limited to 1 MB. You can provide the limit query parameter to limit the number of records fetched. PAR URL data retrieval is blocked if PAR URL authentication fails or if the requested PAR URL has expired.

To facilitate with human readability, the returned data can be viewed in tabular format when accessed from a browser and a query parameter is appended to the PAR URL. See Use PAR URLs to Access Data and View in Table Format for instructions.

For example, use a PAR URL:

curl https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6XExample/data

The PAR URL response includes links for any previous or next pages, when the data includes more than one page. This allows you to navigate in either direction while fetching data. The JSON also includes a self link that points to the current page, as well as a hasMore attribute that indicates if there is more data available to fetch.

The following is the response format:

{
    "items": [],                 <-- Array of records from database
    "hasMore": true OR false,    <-- Indicates if there are more records to fetch or not
    "limit": Number,             <-- Indicates number of records in the page. Maximum allowed number is 100.
    "offset": Number,            <-- Offset indicating the start of the current page
    "count": Number,             <-- Count of records in the current page
    "links": [
        {
            "rel": "self",
            "href": "{Link to preauth url for the current page}"
        },
        {
            "rel": "previous",
            "href": "{Link to preauth url for the previous page}"
        },
        {
            "rel": "next",
            "href": "{Link to preauth url for the next page}"
        }
    ]
}

For example, the following is a sample response from a PAR URL (with newlines added for clarity):

{"items":[
{"COUNTY":"Main","SPECIES":"Alder","HEIGHT":45},
{"COUNTY":"First","SPECIES":"Chestnut","HEIGHT":51},{"COUNTY":"Main","SPECIES":"Hemlock","HEIGHT":17},
{"COUNTY":"Main","SPECIES":"Douglas-fir","HEIGHT":34},{"COUNTY":"First","SPECIES":"Larch","HEIGHT":12},
{"COUNTY":"Main","SPECIES":"Cedar","HEIGHT":21},{"COUNTY":"First","SPECIES":"Douglas-fir","HEIGHT":10},
{"COUNTY":"Main","SPECIES":"Yew","HEIGHT":11},{"COUNTY":"First","SPECIES":"Willow","HEIGHT":17},
{"COUNTY":"Main","SPECIES":"Pine","HEIGHT":29},{"COUNTY":"First","SPECIES":"Pine","HEIGHT":16},
{"COUNTY":"First","SPECIES":"Spruce","HEIGHT":6},{"COUNTY":"Main","SPECIES":"Spruce","HEIGHT":8},
{"COUNTY":"First","SPECIES":"Hawthorn","HEIGHT":19},{"COUNTY":"First","SPECIES":"Maple","HEIGHT":16},
{"COUNTY":"Main","SPECIES":"Aspen","HEIGHT":35},{"COUNTY":"First","SPECIES":"Larch","HEIGHT":27},
{"COUNTY":"First","SPECIES":"Cherry","HEIGHT":20},{"COUNTY":"Main","SPECIES":"Pine","HEIGHT":37},
{"COUNTY":"Main","SPECIES":"Redwood","HEIGHT":78},{"COUNTY":"Main","SPECIES":"Alder","HEIGHT":45},
{"COUNTY":"First","SPECIES":"Chestnut","HEIGHT":51},{"COUNTY":"Main","SPECIES":"Hemlock","HEIGHT":17},
{"COUNTY":"Main","SPECIES":"Douglas-fir","HEIGHT":34},{"COUNTY":"First","SPECIES":"Larch","HEIGHT":12},
{"COUNTY":"Main","SPECIES":"Cedar","HEIGHT":21},{"COUNTY":"First","SPECIES":"Douglas-fir","HEIGHT":10},
{"COUNTY":"Main","SPECIES":"Redwood","HEIGHT":78}],

"hasMore":false,
"limit":100,
"offset":0,
"count":30,
"links":
[
{"rel":"self",
"href":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/F5Sn..._example/data"}
]}

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

Use PAR URLs to Access Data and View in Table Format

Pre-Authenticated Request (PAR) URL data is retrieved and viewed from a browser in table format.

You can access the data using a PAR URL with a browser and view the returned data in database table like format. The table format supports scrolling, sorting, filtering and coloring of cell values. To return data in table format, append the ?view=table query parameter to any PAR URL.

For example, from a browser use a PAR URL with ?view=table appended:

https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/F5Sn..._example/data?view=table

Here is a sample response to a PAR URL with ?view=table appended:

Description of par_url_ui_default.png follows
Description of the illustration par_url_ui_default.png

Sorting Column Data

You can sort columns in ascending or descending order. To do this, click the column drop down arrow, and select Sort Ascending or Sort Descending.

The following is a screenshot of a sample table with the HEIGHT column sorted in descending order:

Description of par_url_ui_descending.png follows
Description of the illustration par_url_ui_descending.png

Filtering Column Data

You can filter column values. To filter a column, click the column drop down arrow, enter the filter text in the input text box, and click Enter.

The following is a screenshot of a sample table with the SPECIES column filtered to only display data for Spruce trees:

Description of par_url_ui_filtered.png follows
Description of the illustration par_url_ui_filtered.png

After clicking Enter, only rows for Spruce trees are shown: Description of par_url_ui_spruce.png follows
Description of the illustration par_url_ui_spruce.png

Coloring Column Data

By default, none of the columns are colored. You can select the columns you want colored with preset colors based on column values. To specify which columns you want colored, provide the column names as colored_column_names query parameter.

For example, to color column1 and column2:

https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/F5Sn..._example/data?view=table&colored_column_names=column1,column2

The following is a sample PAR URL and a screenshot of a portion of the resulting table. The resulting table has colored data for the COUNTY and SPECIES columns. The SPECIES column is sorted in ascending alphabetic order:

https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/F5Sn..._example/data?view=table&colored_column_names=COUNTY,SPECIES

Description of par_url_ui_colored.png follows
Description of the illustration par_url_ui_colored.png

Coloring Column Data Types

You can also select a specific column data type to be colored by providing the data type as colored_column_types query parameter.

This parameter supports colored_column_types=VARCHAR to color all the string (VARCHAR) columns, and colored_column_types=NONE to not color any data types.

For example:

https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/F5Sn..._example/data?view=table&colored_column_types=VARCHAR

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

Invalidate PAR URLs

At any time a user with appropriate privileges can invalidate a PAR URL.

To invalidate a PAR URL, you need the PAR URL id. Use DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to list each PAR URLs and its associated id.

Use DBMS_DATA_ACCESS.INVALIDATE_URL to invalidate a PAR URL. For example:

DECLARE
    status CLOB;
    BEGIN
       DBMS_DATA_ACCESS.INVALIDATE_URL(
        id => 'Vd1Px7QWASdqDbnndiuwTAyyEstv82PCHqS_example',
        result => status);           
       dbms_output.put_line(status);
    END;
/

See INVALIDATE_URL Procedure for more information.

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

Monitor and View PAR URL Usage

Autonomous Database provides views that allow you to monitor PAR URL usage.

Views	Description
V$DATA_ACCESS_URL_STATS and GV$DATA_ACCESS_URL_STATS Views	These views track PAR URL usage, including elapsed time, CPU time, and additional information.

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database

Notes for Using PAR URLs to Share Data

Provides notes for using PAR URLs with Autonomous Database.

The DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL procedure does not accept bind variables, whether the PAR URL is on a SQL statement or on a specific object.
When you run the DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL procedure with the sql_statement parameter, the SQL statement must be a SELECT statement.
There is a limit of 128 active PAR URLs on an Autonomous Database instance.

Parent topic: Use Pre-Authenticated Request URLs for Read Only Data Access on Autonomous Database