Oracle Cloud Infrastructure Documentation

Generate, List, Invalidate, and Monitor Table Hyperlinks

Shows you the steps to generate. list, invalidate, or monitor Table Hyperlinks.

Parent topic: Use Table Hyperlinks for Read Only Data Access on Autonomous Database

Generate a Table Hyperlink for a Table or a View

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

When a Table Hyperlink runs it uses the privileges granted to the database user who generates the Table Hyperlink. The user that generates a Table Hyperlink 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 Table Hyperlink 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 Table Hyperlink and create a VPD policy to restrict the data that you expose. See Define a Virtual Private Database Policy to Secure Table Hyperlink Data for more information.

  2. Run DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL to generate the Table Hyperlink.
    DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
          schema_name => 'ADMIN',
          schema_object_name    => 'TREE_DATA',
          expiration_minutes    => 360,
          service_name          => 'HIGH',
          enable_acl            => FALSE,
          result                => status);
       dbms_output.put_line(status);
    END;
/

    Use the optional service_name parameter to specify that the generated Table Hyperlink is serviced with a specific service-level guarantee and resources. For example, use the service_name parameter to specify that access to the object is mapped to the HIGH service.

    Use the optional enable_acl parameter if you want to limit access to Table Hyperlink data. Set this parameter to TRUE to enable ACL checking. With ACL checking enabled, an incoming Table Hyperlink consumer's IP address is validated with the ACLs on the producer database before allowing access to data. If the producer database does not have ACLs configured, the enable_acl value is ignored and data access is allowed without any ACL checks.

    See Configuring Network Access with Access Control Rules (ACLs) and GET_PREAUTHENTICATED_URL Procedure for more information.

  3. Check the result.

    In this example status contains the result that includes information about the Table Hyperlink. 

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

Notes for generating a Table Hyperlink with DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL:

  • When you generate a Table Hyperlink on an Autonomous Database instance with a private endpoint, the result includes a name private_preauth_url with the value of the form: "https://private-endpoint/adb/p/parurl-token/data".

    When you generate a Table Hyperlink on an Autonomous Database instance with a private endpoint and the private endpoint is configured with Allow public access enabled, the result includes both the preauth_url for the public endpoint and private_preauth_url.

    See Configure Private Endpoints and Use a Private Endpoint with Public Access Allowed for more information.

  • Use DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to show Table Hyperlinks. See List Table Hyperlinks for details.
  • Use DBMS_DATA_ACCESS.EXTEND_URL to extend the life of a Table Hyperlink. See EXTEND_URL Procedure for more information.

Generate a Table Hyperlink with a Select Statement

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

When a Table Hyperlink runs it uses the privileges granted to the database user who generates the Table Hyperlink. The user that generates a Table Hyperlink 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 Table Hyperlink 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 Table Hyperlink and create a VPD policy to restrict the data that you expose. See Define a Virtual Private Database Policy to Secure Table Hyperlink Data for more information.

  2. Run DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL to generate the Table Hyperlink.
    DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
          sql_statement        => 'SELECT species, height FROM TREE_DATA',
          expiration_count     => 10,
          service_name         => 'HIGH',
          enable_acl            => FALSE,
          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 Table Hyperlink 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 Table Hyperlink 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.

    Use the optional service_name parameter to specify that the generated Table Hyperlink is serviced with a specific service-level guarantee and resources. For example, use the service_name parameter to specify access to the SQL statement is mapped to the HIGH service.

    Use the optional enable_acl parameter if you want to limit access to Table Hyperlink data. Set this parameter to TRUE to enable ACL checking. With ACL checking enabled, an incoming Table Hyperlink consumer's IP address is validated with the ACLs on the producer database before allowing access to data. If the producer database does not have ACLs configured, the enable_acl value is ignored and data access is allowed without any ACL checks.

    See Configuring Network Access with Access Control Rules (ACLs) and GET_PREAUTHENTICATED_URL Procedure for more information.

  3. Check the result.

    In this example status contains the result that includes the Table Hyperlink. 

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

Notes for generating a Table Hyperlink with DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL:

  • When you generate a Table Hyperlink on an Autonomous Database instance with a private endpoint, the result includes a name private_preauth_url with the value of the form: "https://private-endpoint/adb/p/parurl-token/data".

    When you generate a Table Hyperlink on an Autonomous Database instance with a private endpoint and the private endpoint is configured with Allow public access enabled, the result includes both the preauth_url for the public endpoint and private_preauth_url.

    See Configure Private Endpoints and Use a Private Endpoint with Public Access Allowed for more information.

  • Use DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to show Table Hyperlinks. See List Table Hyperlinks for details.
  • Use DBMS_DATA_ACCESS.EXTEND_URL to extend the life of a Table Hyperlink. See EXTEND_URL Procedure for more information.

Generate a Table Hyperlink with UI Features Specified on Columns

When you generate a Table Hyperlink 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 Table Hyperlink:

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

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

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

  2. Run DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL to generate the Table Hyperlink 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 Table Hyperlink 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 Table Hyperlink. 

    {
"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 Table Hyperlinks 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 Table Hyperlink 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 Table Hyperlink 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 Table Hyperlink. 

    {
"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 Table Hyperlinks 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 Table Hyperlinks. See List Table Hyperlinks for details.
  • Use DBMS_DATA_ACCESS.EXTEND_URL to extend the life of a Table Hyperlink. See EXTEND_URL Procedure for more information.
  • Generate Table Hyperlinks 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 Table Hyperlinks

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

Run DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to list the active Table Hyperlinks. 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",
"acl_enabled":false,
"column_lists":{"group_by_columns":["COUNTY","SPECIES"]}}]

See LIST_ACTIVE_URLS Function for more information.

Notes for running DBMS_DATA_ACCESS.LIST_ACTIVE_URLS:

  • 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 Table Hyperlinks, regardless of the user who generated the Table Hyperlink. If the invoker is not the ADMIN user and not a user with PDB_DBA role, the list includes only the active Table Hyperlinks generated by the invoker.

  • When you generate and list a Table Hyperlink on an Autonomous Database instance with a private endpoint, the result includes a name private_preauth_url with the value of the form: "https://private-endpoint/adb/p/parurl-token/data".

    When you generate and list a Table Hyperlink on an Autonomous Database instance with a private endpoint and the private endpoint is configured with Allow public access enabled, the result includes both the preauth_url for the public endpoint and private_preauth_url.

    See Configure Private Endpoints and Use a Private Endpoint with Public Access Allowed for more information.

Invalidate Table Hyperlinks

At any time a user with appropriate privileges can invalidate a Table Hyperlink.

To invalidate a Table Hyperlink, you need the Table Hyperlink id. Use DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to list each Table Hyperlinks and its associated id.

Use DBMS_DATA_ACCESS.INVALIDATE_URL to invalidate a Table Hyperlink. 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 Table Hyperlink Data

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

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 Table Hyperlink is accessed, the value of application_user_id specified during Table Hyperlink 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 Table Hyperlink 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 Table Hyperlink, 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 Table Hyperlink. 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 Table Hyperlink.
  2. Create VPD policy on the database where you generated the Table Hyperlink.
    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 Table Hyperlink Usage

Autonomous Database provides views that allow you to monitor Table Hyperlink usage.

Views Description
V$DATA_ACCESS_URL_STATS and GV$DATA_ACCESS_URL_STATS Views

These views track Table Hyperlink usage, including elapsed time, CPU time, and additional information.