Oracle ADB Pre-Authenticated URL - Part V

Pre-Authenticated URLs(PAR URLs) provides secure, time limited access to specific datasets on Autonomous platform, which can be shared with any REST clients, this feature allows the team to easily share sensitive data without complex permission, ensuring that this access is temporary and controlled. Also this PAR URLs can be invalidated at anytime, adding another layer of security to data sharing, for more information of this feature refer to the earlier blogpost on this topic.
 
Over the last few months, a series of new features focusing on improving user experience and functionality was added, these updates aimed to enhance convenience for both data providers – who create and share the data – and data consumers – who analyse and use the data. Each of these new features were created to provide more control, flexibility and efficiency while maintaining the highest standard of data security.
 
Data consumers can now enjoy, user-friendly web interface for viewing and interacting with shared data. This enhanced web output UI is highly functional and designed to simplify consumer experience.
 
Grouping and sorting: ability to group and sort the data directly from the web interface.
 
Providers can pick columns where GROUP BY is possible, and consumers easily can set it up, for example in the original dataset.
 

 
Will be shown like this

 
In addition to grouping and sorting, the improved filtering makes navigating large datasets much simpler, consumers can filter by various fields to narrow down exactly what they are looking for. The goal is to make data exploration as intuitive as possible, allowing consumer to focus on analysis rather than struggling with user interface.
 
The filter will be applied.
 

 
And the user will see the results like this.
 
 

 
 
With the updated PAR capabilities, time/access limited URL that only exposes the necessary data, keeping everything behind the door will be like this
 
demo@ATP19C> declare
  2     l_result long;
  3  begin
  4     dbms_data_access.get_preauthenticated_url(
  5             schema_name => user
  6             , schema_object_name => 'T'
  7             , expiration_count => 100
  8             , result => l_result
  9             , column_lists => '{ "group_by_columns":["PRODUCT_TYPE","STATUS"]
 10                     , "order_by_columns":["PRODUCT_ID"]
 11                     , "filter_columns":["CUSTOMER"] }');
 12             select json_query(l_result,'$' pretty) into l_result from dual;
 13             dbms_output.put_line( l_result );
 14  end;
 15  /
{
  "status" : "SUCCESS",
  "id" : "W7CFSsa69dZFkWIB11lg1wp5iehHXjESei4DfLSXnYwihT44VBYRLt9qMA0rEE1C",
  "preauth_url" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/4YXCQr5oxuEOqbpq
DeSc8VdhJf8UKVfhw6Sgi1y2xYLszanujxUkcXm1rb4GgMlVEu8h2SSgoiM/data",
  "expiration_ts" : "2025-04-03T01:59:11.904Z",
  "expiration_count" : 100
}
 
PL/SQL procedure successfully completed.
 
demo@ATP19C> select jt.*
  2  from json_table( dbms_data_access.list_active_urls ,'$[*]'
  3      columns (
  4           schema_name varchar2(10) path '$.schema_name'
  5          , object_name varchar2(10) path '$.schema_object_name'
  6          , expiration_count number path '$.expiration_count'
  7          , access_count number path '$.access_count'
  8          , column_lists varchar2(140) format json path '$.column_lists'
  9      )
 10  ) jt ;
 
SCHEM OBJECT_ EXPIRATION_COUNT ACCESS_COUNT COLUMN_LISTS
----- ------- ---------------- ------------ --------------------------------------------------------------------------------------------------------------------------------------------
DEMO  T                    100            0 {"order_by_columns":["PRODUCT_ID"],"group_by_columns":["PRODUCT_TYPE","STATUS"],"filter_columns":["PRODUCT_TYPE","STATUS","CUSTOMER"]}
 
If the consumer needs more access, it is not a problem – PAR URL can be easily extended. Previously providers were limited in how long they could make the data accessible, this often resulted in a rush to get the work done before the link get expired. Now, ADB allows us to extend the PAR validity with just a few API calls, this means longer hassle-free data sharing periods. This feature offers providers from regenerating URLs and offer much more flexibility.
 
demo@ATP19C> declare
  2     l_result long ;
  3  begin
  4     dbms_data_access.extend_url(
  5             id => 'W7CFSsa69dZFkWIB11lg1wp5iehHXjESei4DfLSXnYwihT44VBYRLt9qMA0rEE1C'
  6             , extend_expiration_count_by => 30
  7             , result => l_result );
  8     select json_query(l_result,'$' pretty) into l_result from dual;
  9     dbms_output.put_line( l_result );
 10  end;
 11  /
{
  "status" : "SUCCESS"
}
 
PL/SQL procedure successfully completed.
 
demo@ATP19C> select jt.*
  2  from json_table( dbms_data_access.list_active_urls ,'$[*]'
  3      columns (
  4           schema_name varchar2(10) path '$.schema_name'
  5          , object_name varchar2(10) path '$.schema_object_name'
  6          , expiration_count number path '$.expiration_count'
  7          , access_count number path '$.access_count'
  8          , column_lists varchar2(140) format json path '$.column_lists'
  9      )
 10  ) jt ;
 
SCHEM OBJECT_ EXPIRATION_COUNT ACCESS_COUNT COLUMN_LISTS
----- ------- ---------------- ------------ --------------------------------------------------------------------------------------------------------------------------------------------
DEMO  T                    130            0 {"order_by_columns":["PRODUCT_ID"],"group_by_columns":["PRODUCT_TYPE","STATUS"],"filter_columns":["PRODUCT_TYPE","STATUS","CUSTOMER"]}
 
demo@ATP19C>
 
However it should be noted that we cannot reduce the access, by supplying negative value to the parameter EXTEND_EXPIRATION_COUNT_BY, doing so will end up with error like this.
 
demo@ATP19C> declare
  2     l_result long ;
  3  begin
  4     dbms_data_access.extend_url(
  5             id => 'W7CFSsa69dZFkWIB11lg1wp5iehHXjESei4DfLSXnYwihT44VBYRLt9qMA0rEE1C'
  6             , extend_expiration_count_by => -70
  7             , result => l_result );
  8     select json_query(l_result,'$' pretty) into l_result from dual;
  9     dbms_output.put_line( l_result );
 10  end;
 11  /
{
  "status" : "FAILURE",
  "return_message" : "ORA-20001: extend_url attribute extend_expiration_count_by cannot be negative"
}
 
PL/SQL procedure successfully completed.
 
demo@ATP19C>