Oracle Cloud Infrastructure Documentation

Generate, List, Invalidate, and Monitor PAR URLs

Shows you the steps to generate. list, invalidate, or monitor PAR URLs.

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

  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":"wPY0uxyx-gioxOndiKVlqVF585xqJs14CIp9M1qH",
"preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/-pPLdrbUTcLUrZLExyzxyzabc_1w/data",
"expiration_ts":"2024-10-22T22:37:18.805Z",
"expiration_count":null
}
    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;
/

    The sql_statement parameter 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 sample expiration_count parameter specifies that the PAR URL expires and is invalidated after 10 uses. When an expiration_time is not specified, the expiration time is set to the default value, 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":"LCvtpALqZgcHGL4Lxyzabcxyza-QVEFngwh1UGhg8jjuFAHOQJLGFi",
"preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/RIabc_xyz7Kw8ZLyZ2JzCXZQXpc/data",
"expiration_ts":"2025-01-20T16:42:49.527Z",
"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.

Generate a PAR URL with UI Features Specified on Columns

When you generate a PAR URL you can use the column_lists parameter to specify UI features for specified columns.

The column_lists parameter is a JSON value that specifies options by column. The supported values in column_lists are one or more of the following:

column_lists Value Description
order_by_columns

Specifies the columns that support sorting. The columns are specified in a JSON array.
filter_columns

Specifies the columns that support filtering. The columns are specified in a JSON array.
default_color_columns

Specifies to only use the default coloring for the specified columns. The columns are specified in a JSON array.
group_by_columns

Specifies that group by is allowed for the specified columns (viewing the data by grouping the specified column is allowed). The columns are specified in a JSON array.

To specify table view column level UI features for a PAR URL:

  1. Identify the table or view or select statement that you want to share.

    This example generates a PAR URL using the column_lists parameter with a table. You can also use this parameter when you generate a PAR URL with a SELECT statement.

    See Generate a PAR URL for a Table or a View and Generate a PAR URL with a Select Statement for more information.

  2. Run DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL to generate the PAR URL and specify group by columns option for viewing with a browser:

    For example, to specify group by columns:

    DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
          schema_name => 'ADMIN',
          schema_object_name    => 'TREE_DATA',
          expiration_minutes    => 360,
          service_name          => 'HIGH',
          column_lists          => '{ "group_by_columns": ["COUNTY", "SPECIES"] }',
          result                => status);

       dbms_output.put_line(status);
    END;
/

    The column_lists parameter is JSON that contains a list of JSON arrays of columns defining PAR-URL functionality. Use this parameter to specify the columns for one or more of the options: order_by_columns, filter_columns, default_color_columns, or group_by_columns.

    For example:

    column_lists => '{ 
        "group_by_columns":["COUNTY", "SPECIES"], 
        "order_by_columns":["COUNTY"] }'

    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":"LLUZjJ5Yy8d0txydMiuxCVL_j4abc_xyzV1989GmgRInaBYTw",
"preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Rt_yRfTxbcabc_xyzWlANP5RmM9Y/data",
"expiration_ts":"2024-10-25T02:30:39.810Z",
"expiration_count":null
}

    See Use PAR URLs to Access Data with the Group By Option in Table Format for an example showing group by columns usage.

  4. Run DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL to generate the PAR URL and specify a UI format option.

    For example, to set the columns that can be sorted use the column_lists order_by_columns option: 

    DECLARE
   status CLOB;
   column_lists CLOB;
   BEGIN
      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
          schema_name => 'ADMIN',
          schema_object_name    => 'TREE_DATA',
          expiration_minutes    => 360,
          service_name          => 'HIGH',
          column_lists          => '{ "order_by_columns": ["COUNTY", "SPECIES"] }',
          result                => status);
       dbms_output.put_line(status);
    END;
/

    The column_lists parameter is JSON that contains a list of JSON arrays of columns defining PAR-URL functionality. Use this parameter to specify the columns for one or more of the options: order_by_columns, filter_columns, default_color_columns, or group_by_columns.

    See GET_PREAUTHENTICATED_URL Procedure for more information.

  5. Check the result.

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

    {
"status":"SUCCESS",
"id":"LLUZjJ5Yy8d0txydMiuxCVL_j4abc_xyzV1989GmgRInaBYTw",
"preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Rt_yRfTxbcabc_xyzk3EmM9Y/data",
"expiration_ts":"2024-10-25T02:30:39.810Z",
"expiration_count":null
}

    See Use PAR URLs in Table Format with Column Sorting for an example showing order by columns usage.

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.

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;
/

This example shows the following output:

[{
"id":"lsB5yJzrG2E-o_Diwf7O86p1TphhHs3abcdefgoPuK",
"schema_name":"ADMIN",
"schema_object_name":"TREE_DATA",
"created_by":"ADMIN",
"service_name":"HIGH",
"expiration_time":"2024-10-31T03:02:31.996Z",
"access_count":0,
"created":"2024-10-30T21:02:32.041Z",
"column_lists":{"group_by_columns":["COUNTY","SPECIES"]}
}]

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.

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.

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.

See Using Oracle Virtual Private Database to Control Data Access 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.