Oracle Cloud Infrastructure Documentation

Create, Manage, and Monitor Table Hyperlinks

Shows you the steps to create, manage, and monitor Table Hyperlinks.

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

Create a Table Hyperlink for a Table or a View

Shows you the steps to create 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.CREATE_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.CREATE_URL to generate the Table Hyperlink.
    DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.CREATE_URL(
          schema_name => 'SCOTT',
          schema_object_name    => 'TREE_DATA',
          expiration_minutes    => 360,
          result                => status);
       dbms_output.put_line(status);
    END;
/

    The expiration_minutes parameter specifies that the Table Hyperlink expires and is invalidated after 360 minutes.

    See CREATE_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-gioxOndiKVlqVF585xqJs14CIp9M1qHd-m8bqJi-QCahwfwGesG",
  "preauth_url" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Y0uxyx-gioxOndiKVlqVF585xqJs14CIp9M1qHhVJUCWm5OEG3fNq2m0HCjaMt8s/data",
  "expiration_ts" : "2025-08-12T04:30:41.484Z"
}

Create a Table Hyperlink with a Select Statement

Shows you the steps to create 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.CREATE_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.CREATE_URL to generate the Table Hyperlink.

    You have two options, depending on whether you want to include default bind variable values (if you include bind variables in the select statement).

    1. Provide a select statement and do not use the optional default_bind_values parameter.

      For example:

      DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.CREATE_URL(
          sql_statement        => 'SELECT species, height FROM TREE_DATA',
          expiration_minutes   => 360,
          result               => status);
       dbms_output.put_line(status);
    END;
/
      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 values must be appended to the generated Table Hyperlink as a query parameter when accessing the data.
    2. Provide a select statement and include the default_bind_values parameter.

      For example:

      DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.CREATE_URL(
          sql_statement => 'select * FROM TREE_DATA WHERE COUNTY = :countyNAME',
          default_bind_values  => '{"countyNAME" : "First"}',
          expiration_minutes   => 360,
          result               => status);
       dbms_output.put_line(status);
    END;
/

      The sql_statement parameter must be a SELECT statement. The SELECT statement supports bind variables.

      The default_bind_values parameter specifies values for one or more bind variables in the sql_statement. When default_bind_values is included with DBMS_DATA_ACCESS.CREATE_URL:

      • For bind variables specified in default_bind_values, you can omit the bind variable values when you access the data. The default value is used for a specified bind variable if an override is not provided in the Table Hyperlink URL as a query parameter when you access the data.

      • You can override a default bind variable value specified in default_bind_values if you append the bind variable value in the Table Hyperlink URL as a query parameter when you access the data.

      • If a bind variable is included in the select statement and a default value for the bind variable is not specified in the default_bind_values parameter, you must append a bind variable value to the generated Table Hyperlink URL as a query parameter when you access the data.

    Bind variable support is available for NUMBER and VARCHAR2 column types.

    In these examples the expiration_minutes parameter specifies that the Table Hyperlink expires and is invalidated after 360 minutes.

  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/GL4Lxyzabcxyza-QVEFngwh1UGhg8n4GfPaHCgdGoLJl-V1tTUxMDgdQ/data",
  "expiration_ts" : "2025-08-12T04:33:40.162Z"
}

Create a Table Hyperlink with UI Features Specified on Columns

When you create 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 Create a Table Hyperlink for a Table or a View and Create a Table Hyperlink with a Select Statement for more information.

  2. Run DBMS_DATA_ACCESS.CREATE_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.CREATE_URL(
          schema_name => 'SCOTT',
          schema_object_name    => 'TREE_DATA',
          expiration_minutes    => 360,
          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 CREATE_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_xyzV198nGw-3yFYctMNm1p3atJr",
  "preauth_url" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/-P46uQtYRt_yRfTxbcabc_xyzWlANP5RmM9Ys/data",
  "expiration_ts" : "2025-08-12T04:37:22.190Z"
}

    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.CREATE_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.CREATE_URL(
          schema_name => 'SCOTT',
          schema_object_name    => 'TREE_DATA',
          expiration_minutes    => 360,
          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 CREATE_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" : "tCz2IRLIaDDymwOe1o105WQMGtyw4Z_0mGwfbv0ARcjI5SPkzR_xyz_abceyMgV",
  "preauth_url" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/yfWQoyYxyz_abcGxQnCx0trQhH72m0HCjaMt8s/data",
  "expiration_ts" : "2025-08-12T04:41:05.115Z"
}

    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 and Table Hyperlink Groups 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 CREATE_URL Procedure for more information.

Create a Password Protected Table Hyperlink

When you create a Table Hyperlink you can specify a Table Hyperlink password.

When a user accesses a password protected Table Hyperlink they must authenticate using the password specified when the Table Hyperlink is created. This provides an additional security step to avoid malicious access, in the case where a Table Hyperlink is exposed to a wider audience than intended.

To create a password protected Table Hyperlink:

  1. Identify the table, view, or query statement for the data that you want to share.

    You can specify a password protected Table Hyperlink or Table Hyperlink Group.

  2. When you create a Table Hyperlink or a Table Hyperlink Group, specify the password parameter.

    For example:

    DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.CREATE_URL(
          schema_name => 'SCOTT',
          schema_object_name          => 'TREE_DATA',
          password                    => passwd,
          max_failed_access_attempts  => 15,
          result                      => status);
       dbms_output.put_line(status);
    END;
/

    The password parameter specifies the password required to access the Table Hyperlink (or Table Hyperlink Group). In addition to the password, all the security protections that apply to a Table Hyperlink, such as expiration time and expiration count, also apply to a password protected Table Hyperlink.

    The max_failed_access_attempts parameter specifies the maximum number of sequential failed password attempts; if this value is exceeded the Table Hyperlink is invalidated. The default value for this parameter is 10. The count for tracking the number of sequential failed access attempts is reset to 0 when the user provides the correct password.

    See CREATE_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-gioxOndiKVlqVF585xqJs14CIp9M1qHabc_1",
  "url" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/-pPLdrbUTcLUr1XetvO0tekak4p92m0HCjaMt8s/data",
  "expiration_ts" : "2025-11-06T22:41:14.779Z"
}

Note the following when you include the password parameter with DBMS_DATA_ACCESS.CREATE_URL:

  • The result includes the url attribute with the Table Hyperlink URL value. Without the password parameter the result attribute is preauth_url. This difference allows you to distinguish a password protected Table Hyperlink in the result.

  • The minimum password length is 12 characters and the password must include at least one upper case letter, one lower case letter, and one numeric character. These rules are the same as what are enforced for password complexity rules for a database user associated with a NULL PVF (Password Verification Function). If CREATE_URL is invoked with the password parameter and the supplied password does not conform to these rules, for example, the password is shorter than 12 characters, the URL creation fails with an error.

    See Manage Password Complexity on Autonomous AI Database for more information.

  • The is no option to change or reset a password after you set a password. If you run CREATE_URL and subsequently you have not saved or you are not able to remember the password you set, you must create a new Table Hyperlink.

  • There is no association between the password for a password protected Table Hyperlink and the password of the user creating the URL. If user SCOTT creates a password protected Table Hyperlink with CREATE_URL, there is no association between the password for the Table Hyperlink URL that is created and the password for the user SCOTT.

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 generate a list of Table Hyperlinks and their 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;
/

You can also use DBMS_DATA_ACCESS.INVALIDATE_URL to invalidate a Table Hyperlink Group.

See Invalidate a Table Hyperlink Group and 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 AI 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.

Notes for Creating a Table Hyperlink or a Table Hyperlink Group

Notes for creating a Table Hyperlink or a Table Hyperlink Group with DBMS_DATA_ACCESS.CREATE_URL:

  • You can use the optional service_name parameter to specify that a 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.

  • You can use the one or both of the optional inherit_acl and acl parameters if you want to limit access to Table Hyperlink data:

    • inherit_acl: Use the optional inherit_acl parameter if you want to limit access to Table Hyperlink data. Set this parameter to TRUE to inherit ACLs. When this parameter's value is TRUE, 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 inherit_acl value is ignored and data access is allowed without any ACL checks.

    • acl: Use the optional acl parameter to specify an ACL that applies for the Table Hyperlink. The parameter value specifies the list of allowed IP addresses, CIDR blocks, or OCI VCN OCIDs. When the acl parameter is specified a Table Hyperlink consumer can only access the data from the hosts specified in the ACL.

    When inherit_acl is TRUE and the acl parameter is set to specify an ACL, a Table Hyperlink consumer can access a Table Hyperlink's data from the hosts specified with the acl parameter or from the inherited hosts defined in the ACL specified for the Autonomous AI Database instance.

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

  • When you set the optional parameter consistent to TRUE, a Table Hyperlink producer produces data consistently across different pages. This allows a consumer to access the data for all pages associated with a Table Hyperlink using the same data snapshot (SCN) as the SCN associated with the access for the first page. This option enables a producer to return consistent data to a consumer where responses are paginated and multiple pages are returned (the data is returned page by page, as it is accessed).

    When consistent is set to TRUE and a Table Hyperlink references schema objects from another user's schema, the database user that creates the Table Hyperlink must have FLASHBACK privilege on all schema objects used in the Table Hyperlink that belong to the other user's schema.

    For example:

    GRANT FLASHBACK ON TREE_SCHEMA.TREE_DATA TO SCOTT;

    Note: if there is a high amount of database activity and enough time passes between retrieval of the first page and retrieval of a subsequent page, it may not be possible to retrieve subsequent data that is consistent with the first access. In this case, retrieval results in an error.

    See Access Data with Table Hyperlinks or with a Table Hyperlink Group for more information.

  • When you create a Table Hyperlink on an Autonomous AI 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 create a Table Hyperlink on an Autonomous AI 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 and Table Hyperlink Groups for details.

  • Use DBMS_DATA_ACCESS.EXTEND_URL to extend the life of a Table Hyperlink. See EXTEND_URL Procedure for more information.