Use Pre-Authenticated Request URLs for Read Only Data Access 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.
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):
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:
Define a Virtual Private Database Policy to Secure PAR URL Data
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.
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.
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.
?view=table
query parameter to any PAR URL.
?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:
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
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 thesql_statement
parameter, the SQL statement must be aSELECT
statement. -
There is a limit of 128 active PAR URLs on an Autonomous Database instance.