DBMS_DATA_ACCESS Package
The
DBMS_DATA_ACCESS
package provides routines to generate and manage
Pre-Authenticated Request (PAR) URLs for data sets.
- DBMS_DATA_ACCESS Overview
Describes the use of theDBMS_DATA_ACCESS
package. - DBMS_DATA_ACCESS Security Model
Security on this package can be controlled by grantingEXECUTE
on this package to selected users or roles. - Summary of DBMS_DATA_ACCESS Subprograms
This section covers theDBMS_DATA_ACCESS
subprograms provided with Autonomous Database.
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.
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 hasEXECUTE
privilege onDBMS_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 |
---|---|
This procedure generates a PAR URL. |
|
This procedure extends the life of a PAR URL. |
|
This procedure invalidates a PAR URL. |
|
This function lists all the currently active PAR URLs. |
- GET_PREAUTHENTICATED_URL Procedure
This procedure generates a PAR URL. - EXTEND_URL Procedure
This procedure extends the life of 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
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,
service_name IN VARCHAR2,
column_lists IN CLOB,
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,
service_name IN VARCHAR2,
column_lists IN CLOB,
result OUT CLOB);
Parameters
Parameter | Description |
---|---|
|
Specifies the owner of the object. |
|
Specifies the schema object (table or view). |
|
Specifies the |
|
Specifies an application user ID
value. When the PAR URL is accessed, the value of
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. |
|
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 Default value: when
|
|
Number of accesses allowed on the PAR URL. There is no default value. If If |
|
The database service to use for data retrieval when
using the PAR URL. Specify the service-level guarantee and
resources used to service this PAR URL. 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. Supported values are
The default value is |
|
A JSON value that specifies options by column. The
supported options specified in the
The
The form is:
For example:
Default values: If If |
|
JSON that indicates the outcome of the operation. |
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, wherecolumn_name_1
throughcolumn_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 supporteddata_type
parameter values areVARCHAR
andNONE
.
- View the returned data in table format with no coloring (default), by
appending the
Example - PAR URL Generated for a Specific Object
The following example generates a PAR URL for
STUDENTS_VIEW
:
DECLARE
status CLOB;
BEGIN
DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL
(
schema_name => 'USER1',
schema_object_name => 'STUDENTS_VIEW',
expiration_minutes => 120,
service_name => 'HIGH',
result => status);
dbms_output.put_line(status);
END;
/
Example - PAR URL Generated for a SQL Statement
The following example generates a PAR URL for a SELECT
SQL statement:
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;
/
Example - PAR URL Generated for a SQL Statement with a Bind Variable
The following example uses a bind variable in the SELECT
statement to generate the PAR URL:
set serveroutput on
DECLARE
status clob;
BEGIN
DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL
(
sql_statement => 'select * from TREE_DATA WHERE COUNTY = :county',
expiration_minutes => 3000,
result => status);
dbms_output.put_line('status : '||status);
END;
/
To use the generated PAR URL, the bind variable value must be passed. The following example uses the generated PAR URL to access tree data for the first county:
https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/gTlbq...example/data?county=First
Example - PAR URL Generated for a Specific Object with Group By Columns
The following example generates a PAR URL for a specific table with Group By columns specified:
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;
/
Parent topic: Summary of DBMS_DATA_ACCESS Subprograms
EXTEND_URL Procedure
This procedure extends the life of a PAR URL.
Syntax:
DBMS_DATA_ACCESS.EXTEND_URL(
id IN VARCHAR2,
extend_expiration_minutes_by IN NUMBER,
extend_expiration_count_by IN NUMBER,
result OUT CLOB);
Parameters
Parameter | Description |
---|---|
|
Specifies the ID of the PAR URL to extend. |
|
Number of minutes by which to extend expiration time
of the PAR URL. The expiration time is set to the current
expiration time plus the value of
The value for
If Default value is NULL. |
|
The number of accesses on the PAR URL is extended by
this count. The expiration count is set to the current
expiration count plus the value of
If Default value is null. |
|
JSON that indicates the outcome of the operation. |
Example - Extend Expiration Minutes of PAR URL
set serveroutput on
declare
status clob;
js_status json_object_t;
js_arr json_array_t;
url_id varchar2(4000);
begin
-- Initially sets the expiration time to 60 minutes
dbms_data_access.get_preauthenticated_url(
schema_name => 'SCOTT', -- Schema name
schema_object_name => 'EMPLOYEE', -- Schema object name
expiration_minutes => 60, -- Expiration minutes
service_name => 'HIGH',
result => status);
js_status := json_object_t.parse(status);
url_id := js_status.get_string('id');
dbms_output.put_line('The url id of url: ' || url_id);
dbms_output.put_line('Initial Expiration Time: ' ||
js_status.get_string('expiration_ts'));
-- Extend the expiration minutes by 1 day, the url would now expire
-- 24 hours later than the previous expiration time
dbms_data_access.extend_url(
id => url_id,
extend_expiration_minutes_by => 1440,
result => status);
-- List urls created
status := dbms_data_access.list_active_urls;
js_arr := json_array_t.parse(status);
for indx in 0.. js_arr.get_size - 1
loop
js_status := TREAT (js_arr.get (indx) AS json_object_t);
if js_status.get_string('id') = url_id then
dbms_output.put_line('New Expiration Time : ' ||
js_status.get_string('expiration_time'));
exit;
end if;
end loop;
end;
/
Example - Extend Expiration Count of PAR URL
set serveroutput on
declare status clob;
js_status json_object_t;
js_arr json_array_t;
url_id varchar2(4000);
begin
-- Initially sets the expiration count to 100
dbms_data_access.get_preauthenticated_url(
schema_name => 'SCOTT', -- Schema name
schema_object_name => 'EMPLOYEE', -- Schema object name
expiration_count => 100, -- Expiration count
service_name => 'HIGH',
result => status);
js_status := json_object_t.parse(status);
url_id := js_status.get_string('id');
dbms_output.put_line('The url id of url: ' || url_id);
dbms_output.put_line('Initial Expiration Count: ' ||
js_status.get_string('expiration_count'));
-- Extends access count by 100 so url would expire after 200 accesses
dbms_data_access.extend_url(
id => url_id,
extend_expiration_count_by => 100,
result => status);
-- List urls created
status := dbms_data_access.list_active_urls;
js_arr := json_array_t.parse(status);
for indx in 0.. js_arr.get_size - 1
loop
js_status := TREAT (js_arr.get (indx) AS json_object_t);
if js_status.get_string('id') = url_id then
dbms_output.put_line('New Expiration Count : ' ||
js_status.get_string('expiration_count'));
exit;
end if;
end loop;
end;
/
Parent topic: Summary of DBMS_DATA_ACCESS Subprograms
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 |
---|---|
|
Specifies the owner of the object. |
|
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: |
|
Provides JSON to indicate whether invalidation is a
success or a failure ( |
Parent topic: Summary of DBMS_DATA_ACCESS Subprograms
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 withPDB_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 withPDB_DBA
role, the list includes only the active PAR URLs generated by the invoker.
Parent topic: Summary of DBMS_DATA_ACCESS Subprograms