Use Cloud Links for Read Only Data
Access on Autonomous Database
Cloud Links
provide a cloud-based method to remotely access read only data on an
Autonomous Database instance.
About Cloud Links on Autonomous Database With Cloud Links a data owner registers a table or view for remote access for a selected audience as defined by the data owner, and the data is then accessible to those with access granted at registration time. No further actions are required to set up Cloud Links and whoever is supposed to see and access your data is able to discover and work with the data.
Grant Cloud Links Access for Database Users The ADMIN user grants privileges to database users to register data sets. The ADMIN user also grants privileges to database users to access registered data sets.
Register a Data Set Describes the options and steps to register a table or view you own as a registered data set to share with Cloud Links.
Find Data Sets and Use Cloud Links A user who is granted access to read Cloud Links can search for data sets available to an Autonomous Database instance and can access and use registered data sets with their queries.
Use Cloud Links Consumer Options You can set the service name mapping to use to access data from a consumer database and you can enable caching on a data set consumer for the results of a query or for a query fragment that accesses Cloud Link data.
With Cloud Links a data owner registers a table or view for remote
access for a selected audience as defined by the data owner, and the data is then
accessible to those with access granted at registration time. No further actions are
required to set up Cloud Links and whoever is supposed to see and access your data
is able to discover and work with the data.
The Cloud Links implementation leverages Oracle Cloud
Infrastructure access mechanisms to make data accessible within a specific scope. Scope
indicates who can remotely access the data. Scope can be set to various
levels, including to the region where the database resides, to individual
tenancies, or to compartments. In addition, you can specify that
authorization to access a data set is limited to one or more Autonomous Database
instances.
By creating one or more cross-region refreshable clones from the
source (data set owner's) Autonomous Database instance, you can use Cloud Links to share data across
multiple regions.
Cloud Links greatly simplify the sharing of tables or views across
Autonomous Database
instances, as compared to traditional database linking mechanisms. With
Cloud Links you can discover data without requiring a complex database link
setup. Autonomous Database
provides transparent access using SQL, and implements privilege enforcement
with the Cloud Links scope and by granting authorization to individual Autonomous Database
instances.
Cloud Links introduce the concepts of regional namespaces and names
for data that is made remotely accessible. This is similar to existing
Oracle tables where there is a table, for example "EMP"
that resides in a namespace (schema), for example "LWARD".
There can only be one LWARD.EMP in your database. Cloud
Links provide a similar namespace and name on a regional level, that is not
tied to a single database but applies across many Autonomous Database instances
as specified with the scope and optionally with database authorization.
For example, you can register a data set under the namespace
FOREST and for security purposes or for naming
convenience, you can provide a namespace and a name other than the original
schema and object names. In this example, TREE_DATA is
the visible name of the registered data set and this name is not required to
be the name of the source table. In addition to the namespace and the name,
the cloud$link keyword indicates to the database that it
must resolve the source as a Cloud Link.
To access a registered data set, include the namespace, the name, and the
cloud$link keyword in the FROM
clause of a SELECT statement:
SELECT county, species, height FROM FOREST.TREE_DATA@cloud$link;
Optionally, you can specify that access to a data set from one or
more databases is offloaded to a refreshable clone. When a consumer Autonomous Database is listed
in a data set's offload list, access to the data set is directed to the
refreshable clone.
Note
Cloud Links provide read only
access to remote objects on an Autonomous Database instance. If you want to use database
links with other Oracle databases or with non-Oracle databases, or if you
want to use your remote data with DML operations, you need to use database
links. See Use Database Links with Autonomous Database for more information.
Cloud Links support private and public synonyms. For example, you
can define and use a synonym for
FOREST.TREE_DATA@cloud$link:
CREATE SYNONYM S1 for FOREST.TREE_DATA@cloud$link;
SELECT county, species, height FROM S1;
CREATE PUBLIC SYNONYM S2 for FOREST.TREE_DATA@cloud$link;
SELECT * FROM S2;
There are several concepts and terms to use when you work
with Cloud Links:
Registered Data Set (Data Set):
Identifies a table or view that has been enabled for
remote access in an Autonomous Database. A registered data set also
indicates who is allowed to access the data set (its
scope). Data set registration defines a namespace
and a name for use with Cloud Links. After data set
registration, these values together specify the
Fully Qualified Name (FQN) for remote access, and
allow Cloud Links to manage accessibility for the
data set.
Data Set Owner:
Specify a data set owner to provide a point of
contact for questions about a data set.
OCID (Oracle Cloud Identifier):
Identifies a specific Tenancy, Compartment, or
Database. The scope for a registered data set can be
expressed in terms of OCIDs. See Resource Identifiers for more
information.
Data Registration: With data
registration a user makes a table or view available
for remote access, subject to the access
restrictions imposed by scope, and optionally
subject to an additional authorization step. You can
allow remote access with Cloud Links on a table or
view stored in the database or on data stored in
Object Store.
Data Discovery: A registered data set
can be discovered using textual queries from the
database. The discovery only shows a data set if
there is a privilege to access the data set. You can
search to find a registered data set by name or by
description.
Data Describe: Allows a user to
retrieve a description or metadata for a table or
view made available as a registered data set.
Offload Targets: Optionally, you can
specify one or more offload targets. Offload targets
are refreshable clones that provide Cloud Links data
sets to specified Autonomous Database instances. By specifying an offload
target, you can dedicate an Autonomous Database instance to provide data sets to
separate production from development, for
performance, to assure security, or for other
reasons. See Use Refreshable Clones with Autonomous Database for more information.
Cloud Links Auditing
Any access to a registered data set using Cloud Links is
logged for audit purposes. The log includes the tenancy,
compartment, or database that accessed the data set, the amount of
data accessed, and additional information. The V$CLOUD_LINK_ACCESS_STATS and GV$CLOUD_LINK_ACCESS_STATS Views show audit information.
Data Set Metadata and Audit Views
Each Autonomous Database instance provides views that expose data set
metadata and that allow you to monitor and audit data usage. See
Monitor and View Cloud Links Information for more information.
Data Set Scope, Access Control, and Authorization Cloud Links leverage Oracle Cloud Infrastructure access mechanisms to make registered data sets accessible, and to protect your data with access restrictions.
Data Set Scope, Access Control,
and Authorization 🔗
Cloud Links leverage Oracle Cloud
Infrastructure access mechanisms to make registered data sets accessible, and to protect
your data with access restrictions.
Autonomous Database
determines the accessibility of registered data sets as follows:
The ADMIN user specifies a scope for a user that
allows the user to register data sets based on the scope
provided.
A user who has been granted privileges to register data sets
specifies a scope when they register a data set.
Optionally, when you register a data set, a user who
has been granted authorization privileges can specify that
an authorization step required to access a data set. This
authorization step is in addition to scope level access
authorization.
Data Set Scope
The ADMIN sets a user's scope with DBMS_CLOUD_LINK_ADMIN.GRANT_REGISTER
to be one of:
'MY$REGION'
'MY$TENANCY'
'MY$COMPARTMENT'
A user's scope is hierarchical, meaning a user who is granted
one of these scopes can allow access as follows:
MY$REGION: A user can grant
remote data access to other tenancies in the region
of the Autonomous Database instance that is
registering the data set. This is the least
restrictive scope.
MY$TENANCY: A user can grant
remote data access to any resource, tenancy,
compartment, or database in the tenancy of the Autonomous Database instance that is registering the
data set. This scope is more restrictive than
MY$REGION scope.
MY$COMPARTMENT: A user can
grant remote data access to any resource,
compartment, or database in the compartment of the
Autonomous Database instance that is registering the
data set. This is the most restrictive scope you can
set for a user with
GRANT_REGISTER.
Next, the scope you set when you register a data set
determines from where users are allowed to access the data set. The
DBMS_CLOUD_LINK.REGISTERscope is a comma separated list consisting of one
or more of the following:
Database OCID: Access to the data set
is allowed for the specific Autonomous Database instances identified by OCID.
Compartment OCID: Access to the data
set is allowed for databases in the compartments
identified by compartment OCID.
Tenancy OCID: Access to the data set
is allowed for databases in the tenancies identified
by tenancy OCID.
Region name: Access to the data set
is allowed for databases in the region identified by
the named region. In all cases, Cloud Links access
is limited to within a single region and is not
cross-region.
MY$COMPARTMENT:
Access to the data set is allowed for databases in
the same compartment as the data set owner.
MY$TENANCY: Access
to the data set is allowed for databases in the same
tenancy as the data set owner.
MY$REGION: Access to
the data set is allowed for databases in the same
region as the data set owner.
The scope values, MY$REGION,
MY$TENANCY, and
MY$COMPARTMENT are variables that act as
convenience macros and resolve to OCIDs.
Note
The scope you set when
you register a data set is only honored when it matches or is more
restrictive than the value set with DBMS_CLOUD_LINK_ADMIN.GRANT_REGISTER.
For example, assume the ADMIN granted the scope
'MY$TENANCY' with
GRANT_REGISTER, and the user specifies
'MY$REGION' when they register a data set
with DBMS_CLOUD_LINK.REGISTER. In
this case they would see an error such as the
following:
ORA-20001: Share privileges are not enabled for current user or it is enabled but not for scope MY$REGION
You can also use an additional access control mechanism based
on a SYS_CONTEXT value. This mechanism uses the
function DBMS_CLOUD_LINK.GET_DATABASE_ID
that returns an identifier that is available as a
SYS_CONTEXT value.
When you register a data set with DBMS_CLOUD_LINK.REGISTER you
can use the SYS_CONTEXT value in Oracle Virtual
Private Database (VPD) security policies to control database access
to further restrict and control what specific data can be accessed
by individual Autonomous Database instances.
When you register a data set, if you have been granted
authorization privileges you can specify that database OCID
authorization is required to access a data set. To provide database
OCID authorization for a data set, use the DBMS_CLOUD_LINK.GRANT_AUTHORIZATION
procedure to specify the Autonomous Database instances that are authorized to
access the data set. Before you run DBMS_CLOUD_LINK.GRANT_AUTHORIZATION,
the ADMIN must authorize you to run this procedure with DBMS_CLOUD_LINK_ADMIN.GRANT_AUTHORIZE.
Data set registration with authorization required specifies
database level access for a data set, in addition to the scope
access control specified for the data set, as follows:
Databases that are within the
SCOPE specified and have been
authorized with DBMS_CLOUD_LINK.GRANT_AUTHORIZATION
can view rows from the data set.
Any databases that are within the specified
SCOPE but have not been
authorized with DBMS_CLOUD_LINK.GRANT_AUTHORIZATION
cannot view data set rows. In this case, consumers
without authorization see the data set as empty.
Databases that are not within the
SCOPE specified see an error when
attempting to access the data set.
The
ADMIN user grants privileges to database users to register data sets. The ADMIN user also
grants privileges to database users to access registered data sets.
When the ADMIN user grants register privileges, they provide a scope
that specifies the maximum scope that a user can provide when they register a data
set (within the scope hierarchy). The valid scope values for use
with DBMS_CLOUD_LINK_ADMIN.GRANT_REGISTER are:
During data registration you can set the authorization required parameter to
TRUE. When authorization required is TRUE,
the ADMIN user must run DBMS_CLOUD_LINK_ADMIN.GRANT_AUTHORIZE to provide
authorization to invoke DBMS_CLOUD_LINK.GRANT_AUTHORIZATION. Use DBMS_CLOUD_LINK.GRANT_AUTHORIZATION to specify
authorization details.
When the Autonomous Database instance
has Database Vault enabled and the table or view to be registered with Cloud
Links is protected by a realm, the owner of the table or view must be authorized
to the realm as a realm-owner before registration.
If the realm protecting the table or view is a mandatory realm,
the Autonomous Database common
schema named C##DATA$SHARE, which is used internally as the
connecting schema, must be authorized to the realm as a
realm-participant.
Notes for granting privileges to database users to register data sets:
To register data sets or see and access remote data sets, you
have to have granted the appropriate privilege for either registering with
DBMS_CLOUD_LINK_ADMIN.GRANT_REGISTER or for
reading data sets with DBMS_CLOUD_LINK_ADMIN.GRANT_READ.
This is true for ADMIN user as well; however the ADMIN user can grant
privileges to themself.
The views DBA_CLOUD_LINK_PRIVS and
USER_CLOUD_LINK_PRIVS provide information on user
privileges. See Monitor and View Cloud Links Information for more information.
A user can run the following query to check if they are enabled for
authenticating registered, protected data sets:
SELECT SYS_CONTEXT('USERENV', 'CLOUD_LINK_AUTH_ENABLED') FROM DUAL;
Revoke Cloud Links Access for Database Users The ADMIN user can revoke registration to disallow a user from registering data sets for remote access. The ADMIN user can also revoke privileges or database users to access registered data sets.
The
ADMIN user can revoke registration to disallow a user from registering data sets for remote
access. The ADMIN user can also revoke privileges or database users to access registered
data sets.
As the ADMIN user, revoke a user's privileges to register data sets.
This revokes the privileges to register data sets for the user,
DB_USER1.
Running DBMS_CLOUD_LINK_ADMIN.REVOKE_REGISTER does
not affect data sets that are already registered. Use DBMS_CLOUD_LINK.UNREGISTER to remove access
for a registered data set.
Describes
the options and steps to register a table or view you own as a registered data set
to share with Cloud Links.
Register or Unregister a Data Set You can register a table or view you own as a registered data set. When you want to remove or replace the data set, you need to unregister it. After you register a data set you can changes the values of a data set's attributes.
Register or Unregister a Data Set in a Different Region You can use Cloud Links in multiple regions, where the source region contains the data set's source database and one or more remote regions contain refreshable clones of the source database.
Register a Data Set with Authorization Required Optionally, when you register a data set, in addition to the scope you can specify that database level authorization is required to access a data set.
Update Data Set Registration Attributes After you register a data set you can update some data set attributes. You cannot update the schema name, schema object, namespace, or name attributes.
You can
register a table or view you own as a registered data set. When you want to remove or
replace the data set, you need to unregister it. After you register a data set you can
changes the values of a data set's attributes.
Data set registration defines a namespace and a name for use with Cloud Links. After
data set registration, these values together specify the Fully Qualified Name (FQN)
for remote access, and allow Cloud Links to manage accessibility for the data
set.
To register a data set:
Obtain grant register privileges from the ADMIN user.
For example, assuming there is a schema
CLOUDLINK on your Autonomous Database instance, you can register two objects,
SALES_VIEW_AGG and SALES_ALL and
provide different scope parameters to determine how the
objects are accessed.
auth_required: A Boolean value that
specifies if database level authorization is required for the data
set, in addition to the scope access control. When this is set to
TRUE, the data set enforces an additional
authorization step. See Register a Data Set with Authorization Required for more information.
data_set_owner: Text value specifies information
about the individual responsible for the data set or a contact for
questions about the data set. For example, you could supply an email
address for the data set owner.
For this example, after the registration completes the scope for
the two registered objects are different, based on the scope parameter you
supplied with DBMS_CLOUD_LINK.REGISTER:
MY$TENANCY: Specifies tenancy level
scope for REGIONAL_SALES.SALES_AGG.
MY$COMPARTMENT: Specifies the more
restrictive compartment level scope within the tenancy for
TRUSTED_COMPARTMENT.SALES.
You can update some of the values for a data set's attributes after you
register a data set. See Update Data Set Registration Attributes for more information.
If you want to revoke remote access to a registered data set, unregister
the data set.
For example:
BEGIN
DBMS_CLOUD_LINK.UNREGISTER(
namespace => 'TRUSTED_COMPARTMENT',
name => 'SALES');
END;
/
Notes for Registering or
Unregistering a Data Set 🔗
Provides
notes for registering a data set with DBMS_CLOUD_LINK.REGISTER and unregistering a data set with
DBMS_CLOUD_LINK.UNREGISTER.
After you register an object, users may need to wait up to ten (10)
minutes to access the object with Cloud Links.
When you register a data set and you want consumers in a remote
regions to access the data set, you must perform additional steps to make the
data set available in a remote region. See Register or Unregister a Data Set in a Different Region for more information.
Use the procedure DBMS_CLOUD_LINK.UPDATE_REGISTRATION to change the
attributes for an existing data set.
The wait time for the update to complete can be up to 10 minutes for
a registration change to be propagated and accessible through Cloud Links. This
delay can impact the accuracy of the data in the both the
DBA_CLOUD_LINK_REGISTRATIONS and
DBA_CLOUD_LINK_ACCESS views.
You can register a table or view that resides in another user's
schema when the you have READWITHGRANTOPTION privileges for the table or view.
Autonomous Database does
not perform hierarchical validity checks at registration time and registrations
that are outside the scope will never be visible or accessible.
For example, consider the following sequence:
A user with scope MY$COMPARTMENT registers
an object with a scope that specifies an individual database OCID.
When a user requests access to the registered data set, Autonomous Database performs
the check to see that the database OCID of the database where the
request originates is in the OCID list specified with the
scope when the data set was registered.
After this, the namespace.name object
will be discoverable, visible, and usable in the database where the
request originated.
DBMS_CLOUD_LINK.UNREGISTER may take up to ten
(10) minutes to fully propagate, after which the data can longer be accessed
remotely.
Register or Unregister a Data Set in a
Different Region 🔗
You can
use Cloud Links in multiple regions, where the source region contains the data set's source
database and one or more remote regions contain refreshable clones of the source
database.
On the remote Refreshable Clone register the data set using the same arguments
you used to register the data set in the source region.
For example, assuming there is a schema
CLOUDLINK on your Autonomous Database instance, after you the register
SALES_ALL on the source database, register
SALES_ALL on the refreshable clone:
BEGIN
DBMS_CLOUD_LINK.REGISTER(
schema_name => 'CLOUDLINK',
schema_object => 'SALES_ALL',
namespace => 'TRUSTED_COMPARTMENT',
name => 'SALES',
description => 'Trusted Compartment, only accessible within my compartment. Early sales data.',
scope => 'MY$COMPARTMENT',
auth_required => FALSE,
data_set_owner => 'amit@example.com' );
END;
/
The parameters are:
schema_name: is the schema name (the
object owner).
schema_object: is the object's name.
Valid objects are:
Tables (including Heap, External or Hybrid)
Views
Materialized Views
Note
Other objects such
as analytic views or synonyms are not supported.
namespace: is the namespace you provide
as a name for access with Cloud Links (one part of the Cloud Link
FQN).
A NULL value specifies a
system-generated namespace value, unique to the Autonomous Database
instance.
name: is the name you provide for
access with Cloud Links (one part of the Cloud Link FQN).
description: Specifies text to describe
the data.
scope: Specifies the scope. You can use
one of the variables: MY$REGION,
MY$TENANCY, or MY$COMPARTMENT
or specify one or more OCIDs.
auth_required: A Boolean value that
specifies if database level authorization is required for the data
set, in addition to the scope access control. When this is set to
TRUE, the data set enforces an additional
authorization step. See Register a Data Set with Authorization Required for more information.
data_set_owner: Text value specifies
information about the individual responsible for the data set or a
contact for questions about the data set. For example, you could
supply an email address for the data set owner.
After the registration completes on the refreshable clone, the
scope for the registered object is MY$COMPARTMENT:
Specifies the more restrictive compartment level scope for my compartment
within my tenancy for TRUSTED_COMPARTMENT.SALES.
You can unregister a remote data set only in the remote regions, or in both the
remote regions and in the source region:
To unregister a data set in a remote region and disable remote access
to the data set:
On the refreshable clone, unregister the data set.
For example:
BEGIN
DBMS_CLOUD_LINK.UNREGISTER(
namespace => 'TRUSTED_COMPARTMENT',
name => 'SALES');
END;
/
To unregister a data set on the source database and unregister the
data set on remote region refreshable clones:
On the remote refreshable clone if there is only one, or on
multiple refreshable clones in remote regions if there are more than one,
unregister the data set.
For example:
BEGIN
DBMS_CLOUD_LINK.UNREGISTER(
namespace => 'TRUSTED_COMPARTMENT',
name => 'SALES');
END;
/
Notes for Registering or
Unregistering a Data Set in a Remote Region 🔗
Provides
notes for registering a data set in a remote region.
When you register a data set on a refreshable clone in a remote
region, the invocation of DBMS_CLOUD_LINK.REGISTER on the remote region
clone must use the same parameters with the same values as on the source
database, with the exception of the offload_targets
parameter.
For example, when you run DBMS_CLOUD_LINK.REGISTER with scope set to
MY$COMPARTMENT on the source Autonomous Database instance, run the
procedure again on the cross-region refreshable clone with the same scope
parameter value (MY$COMPARTMENT).
If you specify the offload_targets parameter with
DBMS_CLOUD_LINK.REGISTER on source, you should omit
this parameter when you register the data set on the refreshable clone.
After you register an object, users may need to wait up to ten (10)
minutes to access the object with Cloud Links.
The following actions require that you refresh the refreshable clone:
If you add a VPD policy to the data set in the source, you must refresh
the refreshable clone.
If you perform a grant or a revoke for the data set on the source
database, you must refresh the refreshable clone.
Optionally, when you register a data set, in addition to the scope you can
specify that database level authorization is required to access a data set.
Compared to the previous example where you set
auth_required to FALSE, in this example you
set auth_required to TRUE. When
auth_required is TRUE, additional steps are
required to specify one or more databases from which access to the data set is
authorized.
Note
You can only grant authorization, as shown in these steps, if you are authorized.
The ADMIN grants authorization privileges with DBMS_CLOUD_LINK_ADMIN.GRANT_AUTHORIZE.
Use DBMS_CLOUD_LINK.REGISTER to register a data with
authorization required.
Assuming there is a schema CLOUDLINK on your Autonomous Database instance and
you register the object SALES_VIEW_AGG and set
auth_required to TRUE, then in
addition to defining the scope, you must preform additional steps to
determine how the object is accessed.
auth_required: A Boolean value that
specifies if database level authorization is required for the data
set, in addition to the scope access control. When this is set to
TRUE, the data set enforces an additional
authorization step.
data_set_owner: Text value specifies
information about the individual responsible for the data set or a
contact for questions about the data set. For example, you could
supply an email address for the data set owner.
Obtain the database ID for the database you want to grant authorization to (to
allow the database to access to the data set).
On the system you want to grant access the data set:
SELECT DBMS_CLOUD_LINK.GET_DATABASE_ID FROM DUAL:
Use the database ID you obtained to grant authorization to a specified data
set.
You can only grant authorization, and run DBMS_CLOUD_LINK.GRANT_AUTHORIZATION, as shown
in this step, if you are authorized. The ADMIN grants authorization with DBMS_CLOUD_LINK_ADMIN.GRANT_AUTHORIZE.
BEGIN
DBMS_CLOUD_LINK.GRANT_AUTHORIZATION(
database_id => '120xxxxxxx8506029999',
namespace => 'TRUSTED_COMPARTMENT',
name => 'SALES');
END;
/
Perform these steps multiple times if you want to authorize
additional databases.
Register a Data Set with Offload Targets for
Data Set Access 🔗
Optionally, when you register a data set you can offload access to the
data set to one or more Autonomous Database
instances that are refreshable clones.
Use the optional offload_targets
parameter with DBMS_CLOUD_LINK.REGISTER to specify that access is
offloaded to refreshable clones. The source database for each refreshable clone is
the Autonomous Database instance where you
register the data set (data publisher).
The
offload_targets value is a JSON document that defines one or
more CLOUD_LINK_DATABASE_ID and OFFLOAD_TARGET key
value pairs:
CLOUD_LINK_DATABASE_ID is one of:
A database ID: This specifies a database ID for the data
set consumer whose request is offloaded to the corresponding
refreshable clone specified with the OFFLOAD_TARGET
value.
Obtain the database ID by running DBMS_CLOUD_LINK.GET_DATABASE_ID. See
GET_DATABASE_ID Function for more information.
ANY: This specifies that any data set
consumer's request is offloaded to the corresponding offload target.
A consumer's data set request will be routed to the corresponding
offload target.
If you specify ANY without specifying
database IDs, all data set requests from consumers are offloaded to
the refreshable clone specified with the
OFFLOAD_TARGET value.
If you specify both database IDs and
ANY, data set requests from consumers that do not
match a database ID are offloaded to the refreshable clone specified
with the OFFLOAD_TARGET value.
OFFLOAD_TARGET is the OCID for an Autonomous Database instance that
is a refreshable clone.
The following figure illustrates using offload targets.
When a data set consumer requests access to a data set that you
register with offload_targets and the Autonomous Database instance's database ID
matches the value specified in CLOUD_LINK_DATABASE_ID, access is
offloaded to the refreshable clone identified with OFFLOAD_TARGET
in the supplied JSON.
For example, the following shows a JSON
sample with three
OFFLOAD_TARGET/CLOUD_LINK_DATABASE_ID value
pairs:
When a data set consumer requests access to a data set that you
register with offload_targets that includes the
ANY keyword, access is offloaded to the refreshable clone
identified with OFFLOAD_TARGET in the supplied JSON (except for
requests from consumers that have a matching database ID entry in the supplied
JSON).
For example, the following shows a JSON sample with one
explicit OFFLOAD_TARGET/CLOUD_LINK_DATABASE_ID
value pair, and one ANY value with a corresponding
OFFLOAD_TARGET:
To register a data set and specify offload targets, do the
following:
Obtain the OCID for one or more refreshable clones where you want to offload
data set access. Refreshable clone OCIDs are available on the Oracle Cloud
Infrastructure Console on a refreshable clone.
Note
It can take up to 10 minutes after you create a refreshable clone for the
refreshable clone to be visible as an offload target. This means you may
need to wait up to 10 minutes after you create a refreshable clone for the
refreshable clone to be available for Cloud Links offload
registration.
Obtain the database ID for one or more Autonomous Database instances that you want to access the data set
using data provided by the refreshable clone.
On the system you want to access the data set from a refreshable
clone, run the command:
SELECT DBMS_CLOUD_LINK.GET_DATABASE_ID FROM DUAL:
Register a data set and specify the offload_targets
parameter.
For example, assuming there is a schema
CLOUDLINK on your Autonomous Database instance, you can register
SALES_VIEW_AGG and specify the refreshable clones that
provide access to the data set:
auth_required: A Boolean value that
specifies if database level authorization is required for the data
set, in addition to the scope access control. When this is set to
TRUE, the data set enforces an additional
authorization step. See Register a Data Set with Authorization Required for more information.
data_set_owner: Text value specifies
information about the individual responsible for the data set or a
contact for questions about the data set. For example, you could
supply an email address for the data set owner.
offload_targets: Specifies one or more
Autonomous Database OCIDs of refreshable clones where access to data
sets is offloaded, from the Autonomous Database where the data set
is registered.
For each data set consumer, one of the following can
match to offload the request to a refreshable clone:
When there is matching the value of the specified
cloud_link_database_id, that is the
values matches the consumer's database ID, access is
offloaded to the refreshable clone identified by the OCID
specified in offload_target.
When the ANY keyword is specified, if there
is not a matching the value of a specified
cloud_link_database_id, access is
offloaded to the refreshable clone identified with the ANY
entry by the OCID specified in the corresponding
offload_target.
After
you register a data set you can update some data set attributes. You cannot update the
schema name, schema object, namespace, or name attributes.
To update data set attributes:
The user who registered a data set can update its attributes with DBMS_CLOUD_LINK.UPDATE_REGISTRATION.
If you do not have privileges to update data set attributes, you
need to obtain grant register privileges from the ADMIN user.
For example, use DBMS_CLOUD_LINK.UPDATE_REGISTRATION to update
the description, scope, and
auth_required attributes for the data set in the
namespace REGIONAL_SALES with the name
SALES_AGG:
BEGIN
DBMS_CLOUD_LINK.UPDATE_REGISTRATION(
namespace => 'REGIONAL_SALES',
name => 'SALES_AGG',
description => 'Updated description for aggregated regional sales information.',
scope => 'MY$COMPARTMENT',
auth_required => TRUE );
END;
/
The required parameters are:
namespace: is the data set's namespace
you provided when you registered the data set.
name: is the data set's name you
provided when you registered the data set.
Following is a list of the optional parameters. If
NULL is passed in for an optional parameter value, the
attribute is not modified.
description: Specifies the updated text
to describe the data.
scope: Specifies the scope. You can use
one of the variables: MY$REGION,
MY$TENANCY, or MY$COMPARTMENT
or specify one or more OCIDs.
auth_required: A Boolean value that
specifies if database level authorization is required for the data
set, in addition to the scope access control. When this is set to
TRUE, the data set enforces an additional
authorization step. See Register a Data Set with Authorization Required for more information.
data_set_owner: Text value specifies
information about the individual responsible for the data set or a
contact for questions about the data set. For example, you could
supply an email address for the data set owner.
offload_targets: Specifies one or more
Autonomous Database OCIDs of refreshable clones where access to data
sets is offloaded, from the Autonomous Database where the data set
is registered. See Register a Data Set with Offload Targets for Data Set Access for more information.
You cannot update the schema_name or
schema_object attributes.
When a data set is registered in one or more cross-region Refreshable
Clones, any changes to the registration in the source database should be propagated
to the remote regions.
Note the following to propagate changes to cross-region Refreshable Clones:
If a Producer has N cross-region Refreshable Clones in a region, for example
in region A, run DBMS_CLOUD_LINK.UPDATE_REGISTRATION on
exactly one Refreshable Clone in region A.
If the same Producer has M cross-region Refreshable Clones in a different
remote region, for example in region B, run DBMS_CLOUD_LINK.UPDATE_REGISTRATION on
exactly one Refreshable Clone in region B.
To update attributes when a data set is registered in one or more
cross-region Refreshable Clones:
On the source database, update the data set registration.
On a remote Refreshable Clone in the remote region if the there
is only one remote region, or on a remote Refreshable Clone in each remote
region if there are replicated Refreshable Clones in multiple regions,
update the data set registration with the same values you used to update the
source database, with the exception of the offload_targets
parameter.
In any given remote region, you only need to run DBMS_CLOUD_LINK.UPDATE_REGISTRATION on
exactly one Refreshable Clone in that region (if the region has more than
one Refreshable Clone associated with the same data set, you only need to
run the procedure once to propagate the changes to all of the Refreshable
Clones in an individual remote region).
A user
who is granted access to read Cloud Links can search for data sets available to an Autonomous Database instance and can access and
use registered data sets with their queries.
After the ADMIN user runs GRANT_READ, a user can search
for and use Cloud Links.
Find the available data sets on your Autonomous Database instance.
For example, search for all data sets that contain the string,
"TREE":
DECLARE
result CLOB DEFAULT NULL;
BEGIN
DBMS_CLOUD_LINK.FIND('TREE', result);
DBMS_OUTPUT.PUT_LINE(result);
END;
/
[{"name":"TREE_DATA","namespace":"FOREST","description":"Urban tree data including county, species and height"}]
The parameters are:
search_string: The string to search
for. The search string is not case sensitive.
search_result: A JSON document that
includes the namespace, name, and description values for the data
set.
The DBMS_CLOUD_LINK.FIND procedure provides the
FQN you can use with Cloud Links. In this case,
FOREST.TREE_DATA.
Use the view DBA_CLOUD_LINK_ACCESS to list available data
sets:
SELECT * FROM DBA_CLOUD_LINK_ACCESS;
NAMESPACE NAME
--------- --------------
FOREST TREE_DATA
REGIONAL_SALES SALES_AGG
TRUSTED_COMPARTMENT SALES
Use the procedure DBMS_CLOUD_LINK.DESCRIBE to find out more details
about a registered data set.
SELECT DBMS_CLOUD_LINK.DESCRIBE('FOREST','TREE_DATA') FROM DUAL;
DBMS_CLOUD_LINK.DESCRIBE('FOREST','TREE_DATA')
---------------------------------------------------
Urban tree data including county, species and height
Use the registered data set in a query.
SELECT * FROM FOREST.TREE_DATA@cloud$link;
Note
It can take up to 10 minutes after you register a data set with DBMS_CLOUD_LINK.REGISTER for the data set
to be visible and accessible.
Cloud Links support private and public synonyms. For example, you can
define and use a synonym for
FOREST.TREE_DATA@cloud$link:
CREATE SYNONYM S1 for FOREST.TREE_DATA@cloud$link;
CREATE PUBLIC SYNONYM S2 for FOREST.TREE_DATA@cloud$link;
SELECT * FROM S1;
SELECT * FROM S2;
You can set
the service name mapping to use to access data from a consumer database and you can
enable caching on a data set consumer for the results of a query or for a query
fragment that accesses Cloud Link data.
Enable Caching for a Cloud Link Consumer You can enable caching on a data set consumer for the results of a query or for a query fragment that accesses Cloud Link data.
Set Database Service Name Mapping for Cloud
Links Consumers 🔗
You can
set the service name mapping to use when Cloud Links consumers access data from a data set
owner.
Cloud Links rely on database resources in the Autonomous Database instance that is the
data set producer, or the resources of a refreshable clone, to access shared data.
By default remote connectivity for consumers to access Cloud Links data uses the
MEDIUM database service.
Use DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING to set the
database service mapping for a consumer. In this procedure you supply either a
database ID or the keyword ANY to specify the consumer service
mapping. For example, the following figure shows a mapping for Consumer A to the
HIGH service, Consumer B to the MEDIUM service, Consumer C to the LOW service, and a
mapping for ANY to the TP service, meaning all other consumers access Cloud Links
using the TP service.
On the data set owner's Autonomous Database instance, specify a service mapping.
Perform this step on the data set owner's Autonomous Database instance (that
is, on the producer database).
BEGIN
DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING(
database_id => 'database_id',
service_name => 'HIGH');
END;
/
Where the database_id parameter value is the database
ID you obtained in step 1.
Specify the value ANY for the
database_id to use the specified
service_name with all consumer databases that do not
have a service_name associated with their
database_id. That is, any database_id
whose service_name has not been set with DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING.
Service mappings take effect at the time connections are established. If a
particular consumer's service mappings are changed, the new mappings take
effect only for new sessions from the consumer.
Any service mapping configured in a data set owner for a specific Consumer is
honored even if access from the Consumer is offloaded to a Refreshable
Clone. The Refreshable Clone must be refreshed to a point in time after the
time when service mappings were configured in the data set owner. Note that
offload to a Refreshable Clone is configured with the argument
offload_targets during data set registration.
Use the procedure DBMS_CLOUD_LINK_ADMIN.REMOVE_SERVICE_MAPPING
to remove a service mapping for a specified
database_id.
After running DBMS_CLOUD_LINK_ADMIN.REMOVE_SERVICE_MAPPING
a consumer either uses the default MEDIUM database service
or the service_name you specify if you run DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING
with the database_id value ANY. See REMOVE_SERVICE_MAPPING Procedure for more information.
Set Database Service Name Mapping for Cloud
Links Consumers in Remote Region 🔗
A data
set that is registered in a source region can be accessed with Cloud Links from a remote
region when you create cross-region Refreshable Clone in the remote region.
In this case, the service mappings for consumers in the remote region
must be added twice, in the source database, and in the refreshable clone in the
remote region.
Perform the following steps to set the service mappings for Cloud Links
consumers in a remote region.
Create a cross-region Refreshable Clone of the source database (the Refreshable
Clone is a clone of the Cloud Links data set owner in the remote region).
On the remote Refreshable Clone, register the data set using the same arguments
you used to register the data set in the source region (that is, use the same
arguments you used in Step 2).
On the remote Refreshable Clone, add the service mappings using the same
arguments that you used in the source region (that is, use the same arguments
you used in Step 3).
When a consumer in the remote region access Cloud Links data, the access will use the
same service mappings as you added in source region's data set owner database.
You can
enable caching on a data set consumer for the results of a query or for a query fragment
that accesses Cloud Link data.
To enable caching on a data set consumer use the
RESULT_CACHE hint with the SHELFLIFE option.
With the SHELFLIFE option you supply a value that indicates the
duration, in seconds, that a query result is cached. After the
SHELFLIFE interval has passed, the cached result is marked as
invalid. As long as the cached result is valid, a query retrieves the cached data
from the cache on the consumer database, which avoids a round trip to the data set
owner's database.
Use the RESULT_CACHE hint with the
SHELFLIFE option when the data set is static or when the
consumer can tolerate stale results. The value of SHELFLIFE allows
the Cloud Link data set consumer to control the time, in seconds, when the data in
the cache is valid (the acceptable degree of staleness).
If the query result is large and does not fit in memory, you can use the
RESULT_CACHE hint with the SHELFLIFE option
and the TEMP option to indicate that the result should be written
to disk in the temporary tablespace.
To cache Cloud Link data with the RESULT_CACHE hint:
In a query, specify the RESULT_CACHE hint with the
SHELFLIFE option.
For example:
SELECT /*+ RESULT_CACHE (SHELFLIFE=120) */ * FROM FOREST.TREE_DATA@cloud$link;
This RESULT_CACHE specifies a
SHELFLIFE value of 120. This indicates that the result
should be cached in memory for 120 seconds. After 120 seconds, the cached
result is marked as invalid.
The SHELFLIFE value must be a positive integer. The maximum
SHELFLIFE value is 4294967295.
If the query result is large and does not fit in memory, use both the
SHELFLIFE and the TEMP options to indicate
that the result should be written to disk in the temporary tablespace.
SELECT /*+ RESULT_CACHE (TEMP=true SHELFLIFE=360) */ * FROM FOREST.TREE_DATA@cloud$link;
See RESULT_CACHE_MODE for details on using the result cache with Autonomous Database.
See RESULT_CACHE Hint for more
information on RESULT_CACHE with SHELFLIFE.
See DBMS_RESULT_CACHE for information on procedures to
manage the result cache and to invalidate objects in the result cache.
Use to track access to each
registered data set on the Autonomous Database instance. These views track elapsed
time, CPU time, the number of rows retrieved, and
additional information about registered data sets.
You can use the information in these views to
audit Cloud Links data set access and usage.
Provides information about which databases are
authorized to access which data sets. This applies
to data sets where the
auth_required parameter is
TRUE.
Displays details of all service
mappings for Cloud Links consumer databases. Each
service mapping consists of a Cloud Link database
ID and a database service.
Define a Virtual Private Database Policy to
Secure a Registered Data Set 🔗
By defining Virtual Private Database (VPD)
policies for a registered data set, you can provide fine-grained Cloud Link
access control so that only a subset of data (rows) is visible for specific
remote sites.
Oracle Virtual Private Database (VPD) is a security feature
that lets you control data access dynamically at row level for users
and applications by applying filters on the same data set.
Any user who is granted access to read Cloud Links can
access and use registered data sets if they are within the scope
specified when the data set is registered, and if the extra
authorization required parameter is set for the data set, the access
is from an authorized database. Each remote access is done in the
context of the remote Autonomous Database instance accessing the registered
data set (on the database where the data set was registered).
You use the function
DBMS_CLOUD_LINK.GET_DATABASE_ID on the
remote system to get the database's unique ID. By defining a VPD
policy on the database that registered a data set, you now can use
the remote database's identifier as SYS_CONTEXT
rule to provide more fine-grained control. You can define rules for
remote databases accessing your registered data set and limit access
beyond what is possible by specifying a Cloud Link scope.
Consider an example where
REGIONAL_SALES.SALES_AGG is made available
at the tenancy level. If you want to restrict access to all
databases except one specific database, only allowing full access to
the specified database, you can add a VPD policy on the registered
data set.
For example:
Obtain the unique Cloud Link database ID for the Autonomous Database instance where you want to provide
full access.
Create VPD policy on the database that registered the data set
by only allowing full access to the one specific database
whose identifier you got on the database in question in Step
1.
CREATE OR REPLACE FUNCTION vpd_policy_sales(
owner IN VARCHAR2,
object IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF SYS_CONTEXT('USERENV', 'CLOUD_LINK_DATABASE_ID') <> '12121212163948244901' THEN
RETURN 'time_id >= trunc(sysdate,''year'')';
ELSE
RETURN '';
END IF;
END;
/
Provides
notes and restrictions for using Cloud Links.
There is a limit of 4096 on the number of data sets you can register.
Each Autonomous Database
instance can register no more than 4096 data sets. This limit applies to every
Autonomous Database instance,
regardless of the number of ECPUs (OCPUs if your database uses
OCPUs) or the storage size of the instance. The limit is a fixed
value and setting the ECPU count to a larger value does
not allow you to register more data sets.
You can register an object in another schema if you have READ WITH GRANT
OPTION privilege on the object.
To register data sets or see and access remote data sets, you have
to have granted the appropriate privilege for either registering or reading data
sets. This is true for ADMIN as well; however ADMIN can grant this privilege to
themself.
To use either DBMS_CLOUD_LINK.REGISTER or DBMS_CLOUD_LINK.UPDATE_REGISTRATION, you must
have execute privilege on the DBMS_CLOUD_LINK package, in addition to the
register privilege assigned with DBMS_CLOUD_LINK_ADMIN.GRANT_REGISTER. Only the
ADMIN user and schemas with PDB_DBA have this privilege by
default.
If you drop and re-create a table that was registered, you need to re-register
the table for remote access.
Only the ADMIN user and users with the role PDB_DBA
are privileged to access the following views:
Accessing registered, remote data requires the remote database to be open. If the
remote database is closed or in restricted mode, you will not be able to access
the data and an Oracle error will be returned.
There is a limit of a maximum of four open database links per session. Exceeding
this limit can lead to ORA-02020 or
ORA-12545.
If result cache is enabled, like the default behavior in Autonomous Database with
Data Warehouse workload, you need to ensure that
result cache is not used when realtime data is required.
Cloud Links remote connectivity by default uses the
MEDIUM database service. You can change the default with
DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING by
using ANY as the value for DATABASE_ID. You
can change the database service for a consumer with DBMS_CLOUD_LINK_ADMIN.ADD_SERVICE_MAPPING by
specifying the consumer's DATABASE_ID. See Set Database Service Name Mapping for Cloud Links Consumers for more information.
All interfaces are case-sensitive unless otherwise noted, as
follows:
Anything you enter that exists in the database, for example user
names and table names, case is significant and must be entered with
uppercase letters.
Predefined variables, for example the predefined scope values
must be entered in uppercase letters.
Anything that you specify for the setup of Cloud Links, for
example namespaces or names of tables in a namespace, must be specified as
entered. For example, if you define a namespace as trees,
you must enclose the namespace in double quotes, as
"trees", when accessing it with SQL.
It can take up to 10 minutes after you create a refreshable clone for the
refreshable clone to be visible as an offload target. This means you may need to
wait up to 10 minutes after you create a refreshable clone for the refreshable
clone to be available for Cloud Links offload registration.