Challenges with providing temporary secure access Oracle Autonomous Database data

Providing temporary access to Oracle database information is challenging. Traditional access control mechanisms, which granted persistent access to certain data, required continuous management and updates. Limited duration or limited-count access for certain tables or SQL query results wasn’t straightforward and efficient, leading to the potential overexposure of sensitive data and error-prone. So, how database user can provide secure acces to some dataset? How to do this for non-Oracle clients?

The specific problems encountered include:

Emergency Situations: During urgent incidents that necessitate rapid data access for investigation, the existing process of creating new database accounts or altering permissions is too slow and cumbersome, delaying response times and potentially exacerbating the situation.
Collaboration with Partners: Businesses struggle to efficiently share specific data or reports with partners. The traditional approach of manually generating reports and distributing them via email is time-consuming and inefficient, hindering smooth operation and collaboration.
Data as a product: For vendors looking to sell data as a product, providing customers with access to purchased content or data poses a challenge. Establishing a system where access is both secure and easily manageable, with adjustable expiration timelines, is not straightforward under traditional mechanisms.

Announcement: Pre-Authenticated Request URLs on Autonomous Database

Oracle recently made a several enhancements to simplify secure access to the data. First Cloud Links been introduced, that significantly simplifies database links expirience. While it’s a good solution for a use case when two databases need to interact, it’s limited to database to database interaction.

Today, I’m excited to share a new feature with you: Pre-Authenticated Database URLs. This functionality allows the creation of special links for database tables or queries, which can be accessed through the REST protocol (by non-database clients). It offers customers the flexibility to set up various security aspects, such as:

  • Expiration time: The data provider can specify when a URL will expire.
  • Read count limits: Limits the number of times a link can be used to access data.
  • Manual invalidation: Users have the option to invalidate a database link manually.
  • Enforcement of Virtual Private Database policies.

These features make it not only convenient but also secure.

Code in action

To generate Pre-Auth Database URL, the user needs to run PLSQL code, where specified needed parameters. Not only entire table can be shared, but the user can also specify the exact query:

declare
  status clob;
begin
  dbms_data_access.GET_PREAUTHENTICATED_URL(
    sql_statement     => 'select * from custsales',
    expiration_minutes => 60,
    result            => status
  );
  dbms_output.put_line(status);
end;
/

In return, the user will get JSON that contains the URL to the data set:

{
  "status": "SUCCESS",
  "id": "-k7Qs8V02nVYKKT0r6FR96N5S-PNLeFJD3ox5BTdFqYL0mXvNd9ChnMXuwRQSUi",
  "preauth_url": "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/u-b7W-aIZDhcc/data",
  "expiration_ts": "2024-03-06T02:26:05.156Z",
  "expiration_count": null
}

To read the data, simply paste URL from JSON above in a browser, or use the CURL command:

{
  "items": [
    {"CUST_ID": 1071415, "MOVIE_ID": 660, "APP": "edge"},
    {"CUST_ID": 1362538, "MOVIE_ID": 748, "APP": "edge"},
    {"CUST_ID": 1377923, "MOVIE_ID": 801, "APP": "firefox"}
  ],
  "hasMore": false,
  "limit": 100,
  "offset": 0,
  "count": 3,
  "links": [
    {
      "rel": "self",
      "href": "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/u-b7W-aIZDhcc/data"
    }
  ]
}

Conclusion

Providing temporary access to Oracle database datasets has been complex in the past. However, the introduction of Pre-Authenticated Database URLs offers a simplified approach, especially for businesses seeking to monetize their data. Complementing ORDS, these URLs are specifically designed for controlled, time-bound access, making the process of selling data as a product straightforward. Whether for B2B interactions or direct consumer sales, these URLs offer a streamlined solution for data sharing. Stay tuned, more is comming!