Monday, April 24, 2023

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

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 Shared, this is a thing of the past.

With Cloud Links, new functionality in Autonomous Database Shared, 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:

Oracle Database Career, Database Skills, Database Jobs, Database Learning, Database Prep, Database Preparation

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

Oracle Database Career, Database Skills, Database Jobs, Database Learning, Database Prep, Database Preparation

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:

Oracle Database Career, Database Skills, Database Jobs, Database Learning, Database Prep, Database Preparation

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.

Oracle Database Career, Database Skills, Database Jobs, Database Learning, Database Prep, Database Preparation

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.

Oracle Database Career, Database Skills, Database Jobs, Database Learning, Database Prep, Database Preparation

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:

Oracle Database Career, Database Skills, Database Jobs, Database Learning, Database Prep, Database Preparation

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

Related Posts

0 comments:

Post a Comment