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: ,

Thursday, May 2, 2024

Using database links with ADB-S without wallet

In the previous post, we saw about how to setup a database link connecting two different ADB-S accessible over the public endpoint using wallet in place. In this blogpost we will see about how to establish database link connecting two different ADB-S accessible over the public endpoint without wallet in place.
 
On the target ADB details page, under Network, click Edit in the Mutual TLS (mTLS) authentication field & change the value to allow TLS Authentication by deselecting Require mutual TLS (mTLS) authentication and click update, the ADB lifecycle state changes to updating and post that mutual TLS (mTLS) authentication field changes to show Not required. 
 



To create database link to a public target, the target database must be accessible, some database including ADB limit access (using ACL), so make sure to enable target database to allow access from source database using database link, if we limit access with ACL, then make sure to find the outbound IP address of source database and allow that IP address to connect to your target database. By adding outbound IP address of source database to ACL of the target database.
 
On the source database
 
demo-user@ATP19C> select jt.*
  2  from v$pdbs, json_table( cloud_identity,'$.OUTBOUND_IP_ADDRESS[*]'
  3      columns( outbound_ips varchar2(20) path '$') ) jt;
 
OUTBOUND_IPS
----------------
150.136.133.92
 
On the target Autonomous database 
 

 
Then create credentials to access the target database, the username and the password for the dbms_cloud.create_credentials are the credentials to the target database.
 
demo-user@ATP19C> begin
  2     dbms_cloud.create_credential(
  3             credential_name =>'DB_LINK_CRED',
  4             username =>'DEMO_USER',
  5             password=>'Good2go1!1234' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
Then create the database link to the target database using DBMS_CLOUD_ADMIN package, like this
 
demo-user@ATP19C> begin
  2     dbms_cloud_admin.create_database_link(
  3             db_link_name => 'DB_LINK_TEST',
  4             hostname => 'adb.us-ashburn-1.oraclecloud.com',
  5             port => 1522,
  6             service_name => 'g26be7c92912cdb_atp21c_low.adb.oraclecloud.com',
  7             credential_name =>'DB_LINK_CRED',
  8             directory_name => null );
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select * from dual@DB_LINK_TEST;
 
D
-
X
 
To create a database link with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to a target Autonomous Database on a public endpoint using a secure TCP connection without a wallet, the directory_name parameter must be NULL.
 
demo-user@ATP19C> select banner_full from v$version;
 
BANNER_FULL
------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0
 
 
demo-user@ATP19C> select banner_full from v$version@db_link_test;
 
BANNER_FULL
------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0


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