Tuesday, May 14, 2024

Oracle ADB Pre-Authenticated URL

Providing temporary access to Oracle database information is challenging, traditional access control mechanism, which granted access to certain data requires continuous management and updates. Limited duration or limited access count for certain tables or sql query result sets were not efficient and straightforward, leading to potential exposure of sensitive data and error prone.
 
Another big challenge is security, how to share critical data with your partners and customers.
 
To solve these problem, oracle has a great tool called ORDS (Oracle REST Data Services) and this tool lets you to talk to the database using a REST interface. It’s like a middleman , who helps you ask for data, make tables and change data sets all through REST clients, while ORDS is the default option in vast majority of use cases, there are some where it can’t be used.
  
  • Expiration dates : data provider wants to setup an expiration time for URL
  • Use count limits : data provider needs a cap on how many times a recipient  can use these links to access data.
  • Endpoint transparency: data provider want to hide the database name in the URL and use the abstraction endpoint.
 
All those above use cases can now handled by using a new feature : Pre-authenticated database URLs and this feature is not a replaced for ORDS, instead it’s a rather complementary technology that can help to solve some specific problems.
 
To generate the pre-authenticated URLs, the user need to run the PL/SQL code, with specified parameters
 
demo@ATP19C> ---
demo@ATP19C> --- Generate PAR url for table/view
demo@ATP19C> --- and set the expiration time to 120 minutes
demo@ATP19C> ---
demo@ATP19C>
demo@ATP19C> declare
  2     l_output clob;
  3  begin
  4     dbms_data_access.get_preauthenticated_url(
  5             schema_name => user,
  6             schema_object_name => 'EMP',
  7             expiration_minutes => 120 ,
  8             result => l_output );
  9
 10     dbms_output.put_line( l_output );
 11  end;
 12  /
{"status":"SUCCESS","id":"lpv6c1-1l6jJAVG8XCplqKGPaE7sdZ_t90-h58Y64UekWo8YLqCBHPCf2CJfsom1","preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/RBleuB3MqUYHp7uAEtpMZ613mIYQ7cEw0BZa6rD3YOM9UMfCj5DX3qUNqzFOEklFbUQaSpBgvCM/data","expiration_ts":"2024-05-07T08:18:28.801Z","expir
ation_count":null}
 
PL/SQL procedure successfully completed.
 
 
The user will get JSON that contains the URL to the dataset like this
 
{
  "status" : "SUCCESS",
  "id" : "lpv6c1-1l6jJAVG8XCplqKGPaE7sdZ_t90-h58Y64UekWo8YLqCBHPCf2CJfsom1",
  "preauth_url" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/RBleuB3MqUYHp7uAEtpMZ613mIYQ7cEw0BZa6rD3YOM9UMfCj5DX3qUNqzFOEklFbUQaSpBgvCM/data",
  "expiration_ts" : "2024-05-07T08:18:28.801Z",
  "expiration_count" : null
}
 
To read the data, simply paste the URL from JSON above in a browser or using the CURL command or any REST clients
 
demo@ATP19C> $ curl -i --location https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/RBleuB3MqUYHp7uAEtpMZ613mIYQ7cEw0BZa6rD3YOM9UMfCj5DX3qUNqzFOEklFbUQaSpBgvCM/data
HTTP/1.1 200 OK
Date: Tue, 07 May 2024 06:19:04 GMT
Content-Type: application/json
Content-Length: 1862
Connection: keep-alive
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Content-Type-Options: nosniff
Request-Id: defaultaBX5iDhXOqlRPkRgvwYgoN0w4g34kBUB2bQiireG5B3WawsQyE3XKo53
X-Request-ID: 8daa595c33ae6586affd89d329eaf229
 
{"items":[{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","HIREDATE":"1981-11-17T00:00:00","SAL":5001,"DEPTNO":10},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-05-01T00:00:00","SAL":2850,"DEPTNO":30},{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-06-09T00:00:00","SAL":2451,"DEPTNO":10}, …………………….
, …………………….
, …………………….
{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"1987-05-23T00:00:00","SAL":1100,"DEPTNO":20},{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"1981-12-03T00:00:00","SAL":950,"DEPTNO":30},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"1982-01-23T00:00:00","SAL":1301,"DEPTNO":10}],"hasMore":false,"limit":100,"offset":0,"count":14,"links":[{"rel":"self","href":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/RBleuB3MqUYHp7uAEtpMZ613mIYQ7cEw0BZa6rD3YOM9UMfCj5DX3qUNqzFOEklFbUQaSpBgvCM/data"}]}
demo@ATP19C>
 
Similarly possible to set expiration count while generating pre-authenticated request URLs.
 
demo@ATP19C> ---
demo@ATP19C> --- Generate PAR url for table/view
demo@ATP19C> --- and set the expiration count to 'n' times
demo@ATP19C> ---
demo@ATP19C>
demo@ATP19C> declare
  2     l_output clob;
  3  begin
  4     dbms_data_access.get_preauthenticated_url(
  5             schema_name => user,
  6             schema_object_name => 'EMP',
  7             expiration_count => 2 ,
  8             result => l_output );
  9
 10     dbms_output.put_line( l_output );
 11  end;
 12  /
{"status":"SUCCESS","id":"Reatqul0fDB6u2EIsAvl8J0On7W7ul5SrYa0-uc5pv4EAHZzz2n6CFXqyBnE-G7v","preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/V9bzugzkD523o7s0ToXQg8SykHsVamFiem60kslurzi85av65VpzvyLGoLTYFMUabUQaSpBgvCM/data","expiration_ts":"2024-08-05T06:22:41.935Z","expir
ation_count":2}
 
PL/SQL procedure successfully completed.
 
demo@ATP19C>
demo@ATP19C>
demo@ATP19C> ---
demo@ATP19C> --- First two access to endpoint,should work,the third access should demo@ATP19C> --- fail
demo@ATP19C> ---
demo@ATP19C>
demo@ATP19C>
demo@ATP19C> $ curl -i --location https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/V9bzugzkD523o7s0ToXQg8SykHsVamFiem60kslurzi85av65VpzvyLGoLTYFMUabUQaSpBgvCM/data
HTTP/1.1 200 OK
Date: Tue, 07 May 2024 06:25:28 GMT
Content-Type: application/json
Content-Length: 1862
Connection: keep-alive
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Content-Type-Options: nosniff
Request-Id: default9GNiJhw8bAytLX96I8WEvU5X4fUpcdnvri2eRQtK11os5STuDvdL27ge
X-Request-ID: dc1bdb1fb6d575bb11ac71af7a1b2418
 
{"items":[{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","HIREDATE":"1981-11-17T00:00:00","SAL":5001,"DEPTNO":10},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-05-01T00:00:00","SAL":2850,"DEPTNO":30
…………………….
, …………………….
, …………………….
{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"1981-12-03T00:00:00","SAL":950,"DEPTNO":30},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"1982-01-23T00:00:00","SAL":1301,"DEPTNO":10}],"hasMore":false,"limit":100,"offset":0,"count":14,"links":[{"rel":"self","href":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/V9bzugzkD523o7s0ToXQg8SykHsVamFiem60kslurzi85av65VpzvyLGoLTYFMUabUQaSpBgvCM/data"}]}
demo@ATP19C>
demo@ATP19C>
demo@ATP19C>
demo@ATP19C> $ curl -i --location https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/V9bzugzkD523o7s0ToXQg8SykHsVamFiem60kslurzi85av65VpzvyLGoLTYFMUabUQaSpBgvCM/data
HTTP/1.1 200 OK
Date: Tue, 07 May 2024 06:25:33 GMT
Content-Type: application/json
Content-Length: 1862
Connection: keep-alive
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Content-Type-Options: nosniff
Request-Id: defaultnTJ02ZbGdwlbDr4sC59QltnXyEe9pp7fki7fykwpJglUdrlM4akuraoS
X-Request-ID: db8366f1f1017cd50ef2a0f173295a8c
 
{"items":[{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","HIREDATE":"1981-11-17T00:00:00","SAL":5001,"DEPTNO":10},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-05-01T00:00:00","SAL":2850,"DEPTNO":30},{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-06-09T00:00:00","SAL":2451,"DEPTNO":10}
…………………….
, …………………….
, …………………….
{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"1981-12-03T00:00:00","SAL":950,"DEPTNO":30},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"1982-01-23T00:00:00","SAL":1301,"DEPTNO":10}],"hasMore":false,"limit":100,"offset":0,"count":14,"links":[{"rel":"self","href":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/V9bzugzkD523o7s0ToXQg8SykHsVamFiem60kslurzi85av65VpzvyLGoLTYFMUabUQaSpBgvCM/data"}]}
demo@ATP19C>
demo@ATP19C>
demo@ATP19C> $ curl -i --location https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/V9bzugzkD523o7s0ToXQg8SykHsVamFiem60kslurzi85av65VpzvyLGoLTYFMUabUQaSpBgvCM/data
HTTP/1.1 404 Not Found
Date: Tue, 07 May 2024 06:25:47 GMT
Content-Type: application/json
Content-Length: 83
Connection: keep-alive
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Content-Type-Options: nosniff
Request-Id: defaultq4gSpqVP0LyGDKOf1mVSS1jLW8zvVqc8zSozYOUsKPR8qFYwNrevC9c0
X-Request-ID: e629d4c2837ddc881ce18e40e009c7e4
 
{"code":"ADB-09009","message":"Pre-authenticated url not found or not authorized."}
demo@ATP19C>
 
 
Not only an entire table/view can be shared, but the users can specify the SQL query instead.
 
demo@ATP19C> ---
demo@ATP19C> --- Generate PAR url for query statement.
demo@ATP19C> ---
demo@ATP19C>
demo@ATP19C> declare
  2     l_output clob;
  3  begin
  4     dbms_data_access.get_preauthenticated_url(
  5             sql_statement => ' select empno , ename , hiredate from emp ',
  6             expiration_count => 2 ,
  7             result => l_output );
  8
  9     dbms_output.put_line( l_output );
 10  end;
 11  /
{"status":"SUCCESS","id":"nj4WXm9bscirplCDNgz9xtU1gHl6kgXApAdPk5h4irDPBdSYr6cJBSdXZ66YwK2d","preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Iom13j0a_JjxCHIE7G_n17pmlqvhCreCoaVJz56JIEZf5WQ4_tHr1eGM0A-LrN5mh_rQ3yE6gR8/data","expiration_ts":"2024-08-05T09:21:47.497Z","expir
ation_count":2}
 
PL/SQL procedure successfully completed.
 
demo@ATP19C>
demo@ATP19C> $ curl -i --location https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Iom13j0a_JjxCHIE7G_n17pmlqvhCreCoaVJz56JIEZf5WQ4_tHr1eGM0A-LrN5mh_rQ3yE6gR8/data
HTTP/1.1 200 OK
Date: Tue, 07 May 2024 09:22:52 GMT
Content-Type: application/json
Content-Length: 1134
Connection: keep-alive
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Content-Type-Options: nosniff
Request-Id: defaultXdgraZsyDa3KMbdQIU79MH5Gmi8QrPxrR54rPPMA432Hked2Zkq8cwcl
X-Request-ID: 7b55f0999704ca81cc896616b42f33c2
 
{"items":[{"EMPNO":7839,"ENAME":"KING","HIREDATE":"1981-11-17T00:00:00"},{"EMPNO":7698,"ENAME":"BLAKE","HIREDATE":"1981-05-01T00:00:00"},{"EMPNO":7782,"ENAME":"CLARK","HIREDATE":"1981-06-09T00:00:00"}
, …………………….
, …………………….
, …………………….
,{"EMPNO":7900,"ENAME":"JAMES","HIREDATE":"1981-12-03T00:00:00"},{"EMPNO":7934,"ENAME":"MILLER","HIREDATE":"1982-01-23T00:00:00"}],"hasMore":false,"limit":100,"offset":0,"count":14,"links":[{"rel":"self","href":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Iom13j0a_JjxCHIE7G_n17pmlqvhCreCoaVJz56JIEZf5WQ4_tHr1eGM0A-LrN5mh_rQ3yE6gR8/data"}]}
demo@ATP19C>
 
 
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.
 
This Pre-authenticated URLs are always read only and we cannot associate anything other than SELECT statement, failing to do so will result in error.
 
demo@ATP19C> ---
demo@ATP19C> --- when the query statement got anything apart from SELECT it will demo@ATP19C> --- fail
demo@ATP19C> ---
demo@ATP19C>
demo@ATP19C> declare
  2     l_output clob;
  3  begin
  4     dbms_data_access.get_preauthenticated_url(
  5             sql_statement => ' update emp set ename = lower(ename) ',
  6             expiration_count => 2 ,
  7             result => l_output );
  8
  9     dbms_output.put_line( l_output );
 10  end;
 11  /
{"status":"FAILURE","return_message":"ORA-20001: Error occurred during sql statement validation. Error Code: -903, Error Message: ORA-00903: invalid table name"}
 
PL/SQL procedure successfully completed.

Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)