Oracle ADB Pre-Authenticated URL - Part VI

Having discussed the various capabilities of Data Sharing in the previous blog post, it’s now time to explore a recent enhancement added from a security standpoint. Yes—Data Sharing can now be protected using a password. In addition to password protection, all existing security controls that apply to table hyperlinks, such as expiration time and expiration count, also apply to password-protected table hyperlinks.
 
demo-user@ATP19C> ---
demo-user@ATP19C> --- The value for the password parameter should have atleast one upper case letter
demo-user@ATP19C> --- one lower case letter, one numeric character and the minimum length should be 12
demo-user@ATP19C> ---
demo-user@ATP19C> ---
demo-user@ATP19C>
demo-user@ATP19C> declare
  2     l_status long;
  3  begin
  4     dbms_data_access.create_url(
  5             schema_name => user
  6             , schema_object_name => 'DEPT'
  7             , service_name =>'LOW'
  8             , column_lists => json_object('order_by_columns' value json_array('DNAME'))
  9             , password => 'Helloworld1!'
 10             , max_failed_access_attempts => 3
 11             , result => l_status );
 12     dbms_output.put_line( l_status );
 13  end;
 14  /
{
  "status" : "SUCCESS",
  "id" : "b9xMbSTG-mtXlGSYfOrwmgtjo3ovG2vZk8-t9IKR4ZvKFpbwLDJ_CkMaV4Nsn6A4",
  "url" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data",
  "expiration_ts" : "2026-02-13T07:33:09.592Z"
}
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C>
 
 
The result includes the url attribute containing the Table Hyperlink URL value. When no password is used, the corresponding attribute is preauth_url. This difference allows you to identify whether a Table Hyperlink is password-protected.
 
Accessing a password-protected Table Hyperlink URL without providing the password will result in an error.
 
demo-user@ATP19C>
demo-user@ATP19C> ---
demo-user@ATP19C> --- accessing the URL without password will return error
demo-user@ATP19C> ---
 
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data'
{"code":"ADB-09009","message":"Table Hyperlink not found or not authorized."}
 
The correct approach to access a password-protected Table Hyperlink is to Base64-encode the password and include it as a Basic Authorization header in the request.
 
The value of the Basic Authorization header is: Basic base64_encode(username:password)
 
If there is no username, the format becomes: Basic base64_encode(:password)
 
For our case: Basic base64_encode(:Helloworld1!)
 
which results in: Basic OkhlbGxvd29ybGQxIQ==
 
$ echo :Helloworld1! | base64
OkhlbGxvd29ybGQxIQo=
 
Now, the cURL command to access a password-protected Table Hyperlink using Basic Authorization in the header is:
 
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data' --header 'Authorization: Basic OkhlbGxvd29ybGQxIQ=='
 
{
  "items": [
    {
      "DEPTNO": 10,
      "DNAME": "ACCOUNTING",
      "LOC": "NEW YORK"
    },
    {
      "DEPTNO": 20,
      "DNAME": "RESEARCH",
      "LOC": "DALLAS"
    },
    {
      "DEPTNO": 30,
      "DNAME": "SALES",
      "LOC": "CHICAGO"
    },
    {
      "DEPTNO": 40,
      "DNAME": "OPERATIONS",
      "LOC": "BOSTON"
    }
  ],
  "hasMore": false,
  "limit": 1000,
  "offset": 0,
  "count": 4,
  "links": [
    {
      "rel": "self",
      "href": "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data"
    }
  ]
}
 
 
We can also access the password-protected Table Hyperlink by providing the password directly, without Base64 encoding, as shown below:
 
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data' -u :Helloworld1!
 
 
{
  "items": [
    {
      "DEPTNO": 10,
      "DNAME": "ACCOUNTING",
      "LOC": "NEW YORK"
    },
    {
      "DEPTNO": 20,
      "DNAME": "RESEARCH",
      "LOC": "DALLAS"
    },
    {
      "DEPTNO": 30,
      "DNAME": "SALES",
      "LOC": "CHICAGO"
    },
    {
      "DEPTNO": 40,
      "DNAME": "OPERATIONS",
      "LOC": "BOSTON"
    }
  ],
  "hasMore": false,
  "limit": 1000,
  "offset": 0,
  "count": 4,
  "links": [
    {
      "rel": "self",
      "href": "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data"
    }
  ]
}
 
The below query retrieves the list of active Data Sharing URLs generated through DBMS_DATA_ACCESS.LIST_ACTIVE_URLS and converts the JSON output into a relational format using the JSON_TABLE function.
 
For each active URL, it extracts the following attributes: 

• created_by – The user who created the table hyperlink.
• service_name – The Autonomous Database service name associated with the hyperlink.
• access_count – The number of times the hyperlink has been accessed.
• password_protected – Indicates whether the hyperlink is protected by a password.
• schema_name – The schema that owns the shared object.
• schema_object_name – The name of the shared table or object.
• max_failed_access_attempts -  The maximum number of allowed failed access attempts.

 
demo-user@ATP19C> select *
  2  from json_table( dbms_data_access.list_active_urls ,'$[*]'
  3      columns( created_by,service_name,access_count
  4          ,password_protected,schema_name,schema_object_name,max_failed_access_attempts)
  5* ) ;
 
CREATED_BY SERVICE ACCESS PASSWORD SCHEMA_NAME SCHEMA_OBJ MAX_
__________ _______ ______ ________ ___________ __________ ____
DEMO_USER  LOW     2      true     DEMO_USER   DEPT       3
 
 
When a Table Hyperlink is created with the max_failed_access_attempts parameter, Oracle enforces a security rule:
 
If the number of consecutive incorrect password attempts exceeds this limit, the hyperlink becomes invalidated automatically.
 
To observe this behavior in action, you can try accessing the password-protected URL, using an incorrect password three times (or more), thereby exceeding the configured max_failed_access_attempts value.
 
Rajeshwaran Jeyabal@rajeyaba-JLRVB94 MINGW64 ~
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data' -u :Hello
{"code":"ADB-09009","message":"Table Hyperlink not found or not authorized."}
 
Rajeshwaran Jeyabal@rajeyaba-JLRVB94 MINGW64 ~
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data' -u :Hello
{"code":"ADB-09009","message":"Table Hyperlink not found or not authorized."}
 
Rajeshwaran Jeyabal@rajeyaba-JLRVB94 MINGW64 ~
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data' -u :Hello
{"code":"ADB-09009","message":"Table Hyperlink not found or not authorized."}
 
Rajeshwaran Jeyabal@rajeyaba-JLRVB94 MINGW64 ~
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data' -u :Hello
{"code":"ADB-09009","message":"Table Hyperlink not found or not authorized."}
 
After these failed attempts, try accessing the URL again using the correct password.
 
Rajeshwaran Jeyabal@rajeyaba-JLRVB94 MINGW64 ~
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/fK_VYQG__YZmsxfHadXpHbq9s8yUJ8J8r0HstB4_2scLKRvMAyxHX6zME8JWy5Ok6uhR__GCWo8/data' -u :Helloworld1!
{"code":"ADB-09009","message":"Table Hyperlink not found or not authorized."}
Rajeshwaran Jeyabal@rajeyaba-JLRVB94 MINGW64 ~
$
 
Even with the correct password, the URL will no longer work, because it has already been invalidated due to surpassing the allowed number of failed attempts.
 
demo-user@ATP19C> select *
  2  from
  3      json_table ( dbms_data_access.list_active_urls, '$[*]'
  4          columns (
  5              created_by
  6          , service_name
  7          , access_count
  8          , password_protected
  9          , schema_name
 10          , schema_object_name
 11          , max_failed_access_attempts
 12          )
 13      ) ;
 
no rows selected
 
This demonstrates the built-in security measure that helps prevent brute-force or repeated unauthorized access of password-protected Table Hyperlinks.