Oracle Cloud Infrastructure Documentation

DBMS_DATA_ACCESS Package

The DBMS_DATA_ACCESS package provides routines to generate and manage Pre-Authenticated Request (PAR) URLs for data sets.

Parent topic: Autonomous Database Supplied Package Reference

DBMS_DATA_ACCESS Overview

Describes the use of the DBMS_DATA_ACCESS package.

DBMS_DATA_ACCESS supports these operations:

  • Generation of a PAR URL
  • Manual invalidation of a PAR URL
  • Listing of active PAR URLs

Parent topic: DBMS_DATA_ACCESS Package

DBMS_DATA_ACCESS Security Model

Security on this package can be controlled by granting EXECUTE on this package to selected users or roles.

When a user has been granted EXECUTE on DBMS_DATA_ACCESS they are able to create, list or invalidate the PAR URLs that are created by the user. In addition, by default the ADMIN user has the following privileges:
  • The ADMIN user with PDB_DBA role has EXECUTE privilege on DBMS_DATA_ACCESS.
  • The ADMIN user with the PDB_DBA role is able to list or invalidate any PAR URL in an Autonomous Database instance.

Parent topic: DBMS_DATA_ACCESS Package

Summary of DBMS_DATA_ACCESS Subprograms

This section covers the DBMS_DATA_ACCESS subprograms provided with Autonomous Database.

Subprogram Description

GET_PREAUTHENTICATED_URL Procedure

This procedure generates a PAR URL.

INVALIDATE_URL Procedure

This procedure invalidates a PAR URL.

LIST_ACTIVE_URLS Function

This function lists all the currently active PAR URLs.

Parent topic: DBMS_DATA_ACCESS Package

GET_PREAUTHENTICATED_URL Procedure

This procedure generates a PAR URL.

There are two forms, one to generate the PAR URL for a specific object (table or view). The overloaded form, using the sql_statement parameter, generates a PAR URL for a SQL statement.

Syntax

DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL( 
    schema_name           IN VARCHAR2,
    schema_object_name    IN VARCHAR2,
    application_user_id   IN VARCHAR2,
    expiration_minutes    IN NUMBER,
    expiration_count      IN NUMBER,
    result                OUT CLOB);

DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL( 
    sql_statement          IN CLOB,
    application_user_id    IN VARCHAR2,
    expiration_minutes     IN NUMBER,
    expiration_count       IN NUMBER,
    result                 OUT CLOB);

Parameters

Parameter Description

schema_name

Specifies the owner of the object.

schema_object_name

Specifies the schema object (table or view).

sql_statement

Specifies the SELECT statement query text. PAR URLs do not accept bind variables.

application_user_id

Specifies an application user ID value. When the 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 this value in the Application Context to restrict the rows visible to the application user.

expiration_minutes

Duration in minutes of validity of PAR URL.

The maximum allowed expiration time is 90 days (129600 minutes). If the value is set to greater than 129600, the value used is 129600 minutes (90 days).

If expiration_minutes is specified as a non-null value, expiration_count must not be set to a non-null value. Both cannot be non-null at the same time.

Default value: when expiration_minutes is not provided or when expiration_minutes is provided as NULL, the value is set to 90 days (129600 minutes).

expiration_count

Number of accesses allowed on the PAR URL.

There is no default value.

If expiration_count is not specified and expiration_minutes is not specified, expiration_minutes is set to 90 days (129600 minutes).

If expiration_count is specified as a non-null value, expiration_minutes must not be set to a non-null value. Both cannot be non-null at the same time.

result

(CLOB)

Usage Note

  • There is a limit of 128 active PAR URLs on an Autonomous Database instance.

  • When using a PAR URL from a browser, the following options are supported:
    • View the returned data in table format with no coloring (default), by appending the ?view=table query parameter to the PAR URL.
    • View the returned data in table format and select the column or columns you want colored with preset colors based on column values. To do this, append the ?view=table&colored_column_names=column_name_1,column_name_2,...column_name_n query parameter to the PAR URL, where column_name_1 through column_name_n are the names of the columns you want colored.
    • View the returned data in table format and select a specific column data type you want colored with preset colors, by appending the ?view=table&colored_column_types=data_type query parameter. The supported data_type parameter values are VARCHAR and NONE.

Examples

DECLARE
   status CLOB;
   BEGIN
   DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
      schema_name => 'USER1',
      schema_object_name => 'STUDENTS_VIEW',
      expiration_minutes => 120,
      result => status);
   dbms_output.put_line(status);
END;
/
DECLARE
   status CLOB;
   par_url_app_string CLOB;
   BEGIN
       par_url_app_string := 1919292929;
       DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
            sql_statement => 'SELECT student_id, student_name FROM STUDENTS_VIEW ORDER BY student_id',
            application_user_id => par_url_app_string,
            expiration_count => 25,
            result => status);
END;
/

INVALIDATE_URL Procedure

This procedure invalidates a PAR URL.

Syntax

DBMS_DATA_ACCESS.INVALIDATE_URL(
    id                  IN VARCHAR2,
    kill_sessions       IN BOOLEAN DEFAULT FALSE,
    result              OUT CLOB);

Parameters

Parameter Description

id

Specifies the owner of the object.

kill_sessions

By default, existing sessions that may be in the middle of accessing data using a PAR URL are not killed. When TRUE, this parameter specifies that such existing sessions should be killed, so that the invalidation does not leave any ongoing access to the data set.

Valid values: TRUE | FALSE.

result

Provides JSON to indicate whether invalidation is a success or a failure (CLOB).

LIST_ACTIVE_URLS Function

This function lists all the currently active PAR URLs.

Syntax

DBMS_DATA_ACCESS.LIST_ACTIVE_URLS RETURN CLOB;

Parameters

Parameter Description
RETURN

The return value is a JSON array.

Example

DECLARE
   result CLOB;
   BEGIN
       result := DBMS_DATA_ACCESS.LIST_ACTIVE_URLS;
       DBMS_OUTPUT.PUT_LINE(result);
   END;

[
    {
        "id": "89fa6081-ec6b-4179-9b06-a93af8fbd4b7",
        "schema_name": "SCOTT",
        "schema_object_name": "EMPLOYEE",
        "created_by": "ADMIN",
        "application_user_id": "AMIT",
        "expiration_time": "2023-01-14T23:41:01.029Z",
        "expiration_count": 100,
        "access_count": 9,
        "created": "2023-01-10T19:41:01.285Z"
    },
    {
        "id": "263d2cd7-3bc0-41a7-8cb9-438a2d843481",
        "sql_statement": "select name from v$pdbs",
        "created_by": "ADMIN",
        "application_user_id": "AMIT",
        "expiration_time": "2023-01-15T00:04:30.578Z",
        "expiration_count": 100,
        "access_count": 0,
        "created": "2023-01-10T20:04:30.607Z"
    }
]

Usage 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.