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.

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.

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):

  1. 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.

  2. 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,
              service_name         => 'HIGH',
              result                => status);
           dbms_output.put_line(status);
        END;
    /

    See GET_PREAUTHENTICATED_URL Procedure for more information.

  3. 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 details.
    • Use DBMS_DATA_ACCESS.EXTEND_URL to extend the life of a PAR URL. See EXTEND_URL Procedure for more information.
    • Generate PAR URLs that are serviced with different service-level guarantees and resources. For example, access to an object or SQL statement can be mapped to services HIGH or MEDIUM, whereas access to another object or SQL statement can be mapped to the LOW service. See GET_PREAUTHENTICATED_URL Procedure for more information.

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.

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 to access to data as an arbitrary SQL query statement:

  1. 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.

  2. 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,
              service_name         => 'HIGH',
              result               => status);
           dbms_output.put_line(status);
        END;
    /
    Note

    The sql_statement value must be a SELECT statement. The SELECT statement supports bind variables. If bind variables are included in the select statement, the bind variable value must be appended to the generated PAR URL as a query parameter when accessing the data. Bind variable support is available for NUMBER and VARCHAR2 column types.

    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.

  3. 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 also:
    • Use DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to show PAR URLs. See List PAR URLs for details.
    • Use DBMS_DATA_ACCESS.EXTEND_URL to extend the life of a PAR URL. See EXTEND_URL Procedure for more information.
    • Generate PAR URLs that are serviced with different service-level guarantees and resources. For example, access to an object or SQL statement can be mapped to services HIGH or MEDIUM, whereas access to another object or SQL statement can be mapped to the LOW service. See GET_PREAUTHENTICATED_URL Procedure for more information.

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:

  1. Obtain the application_user_id value that you specified when you generated the PAR URL.
  2. 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.

  3. Register the VPD policy.
    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.

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.

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"}
]}

If you use a bind variable in the SELECT statement when generating the PAR URL, you must pass the bind variable value as a query parameter to access the PAR URL data. Bind variable support is available for NUMBER and VARCHAR2 column types.

For example, a PAR URL is generated with the following SQL statement:

sql_statement = 'SELECT * FROM TREE_DATA WHERE COUNTY = :COUNTY'

Use the generated PAR URL, with the bind variable value as an appended query parameter to access data:

curl https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6X...example/data?COUNTY=MAIN

See GET_PREAUTHENTICATED_URL Procedure for more information.

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

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

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

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

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

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

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.

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.

Notes for Using PAR URLs to Share Data

Provides notes for using PAR URLs with Autonomous Database.

  • 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.