PAR URL Use Cases

Generating and providing PAR URLs supports the following use cases:

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.

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

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:

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:

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

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.

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.

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:

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:

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:

After clicking Enter, only rows for Spruce trees are shown:

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.

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

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

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.