Database links in Autonomous Database Serverless are the past - Cloud links are the future

March 29, 2023 | 7 minute read
Hermann Baer
Senior Director Product Management
Text Size 100%:

As many of you know, database links are an established mechanism to allow other remote databases to access specific tables or views in your database.

Database links have been around for decades and always require a two-way interaction in one of two ways: The remote (accessing) side contacts you, the data owner, to ask for access and to get the specifics of how to connect to your system. Alternatively, as the data owner, you must proactively contact the remote side and share the access details for the remote side to set up the database link. With Oracle Autonomous Database Serverless, this is a thing of the past.

With Cloud Links, new functionality in Autonomous Database Serverless, the data owner registers a table or view for remote access for a selected audience defined by the data owner. The data is then instantaneously accessible by everybody who got remote access granted at registration time. No further actions are required to set up a Cloud Link, and whoever is supposed to see and access your data will be able to discover and work with the data made available to them. 

Sounds almost too cool to be true, doesn't it? Let's step through it how it works.

Cloud Links at work

Let's assume I have central sales information in my autonomous database that other autonomous databases need to access remotely now and then. "Trusted" autonomous databases in the same compartment than my system should be able to access all my detail sales data, whereas other satellite databases within my tenancy should only be able to see the aggregated sales information per sales channel. 

The objects I want to give remote access to look as follows:

shows table sales and aggregated view sales_view_agg

 

You see that there is a base table SALES_ALL and a view SALES_VIEW_AGG defined on top of it that removes the customer information and aggregates the sales information for the other dimensions. The base table will be accessible within my trusted compartment, whereas the aggregated sales information without any customer information should be accessible for everybody in my tenancy.

After the Administrator of my Autonomous Database has given me the privilege to register a table (or view) for remote access with the scope needed for the task at hand, I simply register my table SALES for compartmental access and view SALES_VIEW_AGG for my tenancy (for brevity reasons, pls. consult the documentation for the privilege details):

 

register table and view for remote access

 

What is it about this registration? 

Cloud Links introduce a new concept of regional namespace and name for any data that is made remotely accessible. Think of it as something similar to the database today, where one of the most famous Oracle tables ever has the name "EMP" and lives in the namespace "SCOTT". There can only be one SCOTT.EMP in your database. With Cloud Links, it's the same concept, just on a regional level and without being tied to a single database. And since it's not linked to a single database but needs some boundaries of visibility, there's a new concept of scope. The scope defines who can access your table or view through a cloud link remotely. The scope can be a region, tenancy, compartment, individual databases, or a combination of those. 

That was it. My view CLOUDLINK.SALES_VIEW_AGG will be remotely accessible within my tenancy as REGIONAL_SALES.SALES_AGG, and table CLOUDLINK.SALES_ALL as TRUSTED_COMPARTMENT.SALES without exposing its origin.

After a brief period of central metadata synchronization, my trusted databases in my compartment can access all my sales data, whereas all databases in my tenancy can access my high-level aggregated information (it normally takes 5 to 10 minutes). Any future database in my tenancy or the same compartment as my database will be able to access the same data, safely and filtered as required for their work based on the registration policies. 

I can verify what objects I registered for remote access for the different scopes in the data dictionary:

 

registered cloud link objects

 

The scope of my two registered objects are, as expected, on the tenancy level for REGIONAL_SALES.SALES_AGG and on the compartment level inside my tenancy for TRUSTED_COMPARTMENT.SALES

On the remote (receiving) end, every autonomous database can see what remote objects they have access to by querying the data dictionary:

select * from dba_cloud_link_access;

Let's see what my trusted autonomous databases (in the same compartment) and others in my tenancy will see.

different visibility based on registration scope

 

If I connect to a trusted autonomous database, I will see the following output on the left: I can see both remote data sets. In contrast, when I connect to an arbitrary autonomous database within the same tenancy as my autonomous database that registered the objects, the output will look different, as shown on the right: I can only see the data set shared on the tenant level.

Besides the trusted autonomous database and other autonomous databases in my tenancy, no one else will be able to discover or see the table and view that I registered in this example.

Now it's probably only sometimes known to you what data is made remotely available to you, so you can discover what was made available to you or even find particular data of interest by yourself. If you know the data (namespace, name) you can describe it explicitly, or, the more interesting case, you can see what's out there using free text search.

 

find registered data sets using free text search

 

Voilà, we found the dataset that was shared with everybody without necessarily knowing about its existence.

How to work with registered data?

We registered some objects for remote access and verified that we can see these objects within the scope they were defined, but how do I access them now? I do not have any username/password or other means of authentication and authorization that I shared with a remote database that wants to access my data.

The authentication is done at the registration time of an object. In our example, the trusted autonomous database got access to my sales data by being a trusted database within the same compartment. The same is true for the autonomous database in my tenancy for the aggregated sales data. You only need your Administrator to give you the read privilege on cloud links for authorization (again, please consult the documentation for details here), and you're ready to read the remote data.

After having gotten the proper privilege, any remote object that is made accessible for your autonomous database and your user can be queried with standard "cloud link syntax", namely:

select .. from <namespace>.<name>@cloud$link;

You access remote data without any location knowledge:

 

use cloud link object

 

That was not too hard to set up. If I can do it, you can do it for sure as well.

What's next?

Update, 04/19/2023: We just added a cool example to the doc that shows you how you can even further control access to data dynamically at row level, using Virtual Private Database functionality. You should check this out!

As always, there's more to a feature than what you can cover with a brief blog post, and some topics we skipped over consciously for brevity (like the privilege maintenance to register and read). There is also a framework for the database owning the registered data set(s) to see which remote database has accessed the data, how often, or to even use the remote database's identifier to further limit data access, all that is doable. The documentation and possibly further blog posts come to the rescue here for you.

Note that Cloud Links only provide READ ONLY access to remote objects. They also only work for Autonomous Database Serverless at this point in time. If you want to use database links with other Oracle databases or even non-Oracle databases, or your remote data is used for DML, you need to set up database links the conventional, old way.

I hope this blog post was helpful for you. Feel free to leave any comments here for us and look out for more snippets of valuable information on this platform in the future.

Last but not least: we are also working on a nice UI integrated into Database Studio for you. Looking forward, we will make it very easy for you to manage your cloud links without getting close to code if you want. 

Hermann Baer

Senior Director Product Management

Hermann is a Senior Director of Product Management in the Oracle Database organization. He and his team focus on Oracle's core functionality, such as Oracle Partitioning, the Oracle Optimizer, and analytical SQL. His team also looks after semi-structured data processing, such as the relational SQL/JSON capabilities, Oracle Text, and more recently Autonomous JSON Database and the Oracle Database API for Mongo DB.

Hermann has held several positions at Oracle in Sales Consulting and Consulting, dealing with the database for quite some time


Previous Post

How to Export Data in Parquet format from Autonomous Database to your cloud object store

Nilay Panchal | 3 min read

Next Post


Upgrade Your Tables with Analytic Views

William Endress | 25 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider