Oracle Cloud Infrastructure Documentation

DBMS_CLOUD_LINK Package

The DBMS_CLOUD_LINK package allows a user to register a table or a view as a data set for read only access with Cloud Links.

Parent topic: Autonomous Database Supplied Package Reference

DBMS_CLOUD_LINK Overview

Describes the use of the DBMS_CLOUD_LINK package.

The DBMS_CLOUD_LINK package provides the REGISTER procedure that allows you to register a table or a view as a data set for use with Cloud Links. Before you can register a data set, the ADMIN user must grant a user permission to register a data set using the DBMS_CLOUD_LINK_ADMIN.GRANT_REGISTER procedure. After the ADMIN runs GRANT_REGISTER, a user can register a table or a view they own as a registered data set (or register an object in another schema if the user has READ WITH GRANT OPTION privilege on the object). Registered data sets provide remote access to the registered object with Cloud Links, subject to the scope specified with the REGISTER procedure.

To run DBMS_CLOUD_LINK.REGISTER you have to have execute privilege on DBMS_CLOUD_LINK.REGISTER, in addition to having previously run DBMS_CLOUD_LINK_ADMIN.GRANT_REGISTER. Only the ADMIN user and schemas with PDB_DBA role have execute privilege on DBMS_CLOUD_LINK.REGISTER by default.

Parent topic: DBMS_CLOUD_LINK Package

Summary of DBMS_CLOUD_LINK Subprograms

Shows a table with a summary of the subprograms included in the DBMS_CLOUD_LINK package.

Subprogram Description

DESCRIBE Function

This function retrieves the description for a data set. The description is provided when a data set is registered with DBMS_CLOUD_LINK.REGISTER.

FIND Procedure

Retrieves the namespace, name, and description for data sets that match the search string. Matching data sets are only shown if they are accessible to the user, based on access restrictions.

GET_DATABASE_ID Function

Returns a unique identifier for the Autonomous Database instance. Repeated calls to DBMS_CLOUD_LINK.GET_DATABASE_ID on the same instance always return the same value.

GRANT_AUTHORIZATION Procedure

Grants authorization to a specified database to access the specified data set.

REGISTER Procedure

Registers a table or view as data set.

REVOKE_AUTHORIZATION Procedure

Revokes authorization for a specified database to access the specified data set.

UNREGISTER Procedure

Removes a registered data set.
  • DESCRIBE Function
    This function retrieves the description for a data set. The description is provided when a data set is registered with DBMS_CLOUD_LINK.REGISTER.
  • FIND Procedure
    This procedure retrieves the namespace, name, and description for data sets that match the search string. Matching data sets are only shown if they are accessible to the user, based on access restrictions.
  • GET_DATABASE_ID Function
    The function returns a unique identifier for the Autonomous Database instance. Repeated calls to DBMS_CLOUD_LINK.GET_DATABASE_ID on the same instance always return the same value.
  • GRANT_AUTHORIZATION Procedure
    This procedure grants authorization to a specified database to access the specified data set.
  • REGISTER Procedure
    The procedure registers a table or a view as a data set to allow remote read only access, subject to restrictions imposed by the scope parameter.
  • REVOKE_AUTHORIZATION Procedure
    This procedure revokes authorization for a specified database to access the specified data set.
  • UNREGISTER Procedure
    The procedure allows a user who previously registered a table or a view with the REGISTER procedure to unregister the table or view so that it no longer is available for remote access.

Parent topic: DBMS_CLOUD_LINK Package

DESCRIBE Function

This function retrieves the description for a data set. The description is provided when a data set is registered with DBMS_CLOUD_LINK.REGISTER.

Syntax

DBMS_CLOUD_LINK.DESCRIBE(
      namespace        IN   VARCHAR2,
      name             IN   VARCHAR2
) return CLOB;

Parameters

Parameter Description

namespace

Specifies the namespace of registered data set.

name

Specifies the name of a registered data set.

Usage Note

You can use this function subject to the access restrictions imposed at registration time with DBMS_CLOUD_LINK.REGISTER. If a data set is not accessible to a database, then its description will not be retrieved.

FIND Procedure

This procedure retrieves the namespace, name, and description for data sets that match the search string. Matching data sets are only shown if they are accessible to the user, based on access restrictions.

Syntax

DBMS_CLOUD_LINK.FIND(
      search_string        IN   VARCHAR2,
      search_result        OUT   CLOB
);

Parameters

Parameter Description

search_string

Specifies the search string. The search string is not case sensitive.

search_result

A JSON document that includes the namespace, name, and description values for the data set.

Usage Note

The search string is not case sensitive and the package leverages free text search using Oracle Text.

GET_DATABASE_ID Function

The function returns a unique identifier for the Autonomous Database instance. Repeated calls to DBMS_CLOUD_LINK.GET_DATABASE_ID on the same instance always return the same value.

You can call this function on a database that is accessing a registered data set remotely to obtain the database ID. This allows you to provide the database iD so that a data set owner can leverage a more fine-grained data access control, for example with VPD, based on a specified database IDs from remote sites.

A database ID identifies each remote database that accesses a registered data set, to track and audit access in the V$CLOUD_LINK_ACCESS_STATS and GV$CLOUD_LINK_ACCESS_STATS Views on the database that owns a registered data set.

Syntax

DBMS_CLOUD_LINK.GET_DATABASE_ID()
  RETURN VARCHAR2;

Usage Notes

Cloud links use the unique identifier that DBMS_CLOUD_LINK.GET_DATABASE_ID returns to identify individual databases that are accessing a data set remotely. The database owning the registered data set tracks and audits the database ID as a record of the origin for data set access in the V$CLOUD_LINK_ACCESS_STATS and GV$CLOUD_LINK_ACCESS_STATS Views.

The DBMS_CLOUD_LINK.GET_DATABASE_ID identifier is available as a SYS_CONTEXT value so that you can programmatically obtain this information about a connecting remote session using SYS_CONTEXT, to further restrict and control what specific data can be accessed remotely by individual Autonomous Database instances with Virtual Private Databases (VPD)s.

Return Values

A unique identifier for the Autonomous Database instance of VARCHAR2.

GRANT_AUTHORIZATION Procedure

This procedure grants authorization to a specified database to access the specified data set.

Syntax

DBMS_CLOUD_LINK.GRANT_AUTHORIZATION(
      database_id        IN   VARCHAR2,
      namespace          IN   VARCHAR2 DEFAULT,
      name               IN   VARCHAR2
);

Parameters

Parameter Description

database_id

Specifies the database ID for an Autonomous Database instance. Use DBMS_CLOUD_LINK.GET_DATABASE_ID to obtain the database ID.

namespace

Specifies the data set namespace to grant access authorization for the specified database_id.

name

Specifies the data set name to grant access authorization for the specified database_id.

REGISTER Procedure

The procedure registers a table or a view as a data set to allow remote read only access, subject to restrictions imposed by the scope parameter.

Syntax

DBMS_CLOUD_LINK.REGISTER(
     schema_name      IN VARCHAR2,
     schema_object    IN VARCHAR2,
     namespace        IN VARCHAR2,
     name             IN VARCHAR2,
     description      IN CLOB,
     scope            IN CLOB,
     auth_required    IN BOOLEAN DEFAULT,
     data_set_owner   IN VARCHAR2 DEFAULT,
     offload_targets  IN CLOB DEFAULT

);

Parameters

Parameter Description

schema_name

Specifies the owner of the table or view specified with the schema_object parameter.

schema_object

Specifies the name of a table or a view. Valid objects are:

  • Tables (including Heap, External or Hybrid)
  • Views
  • Materialized Views
  • Cloud Tables

Other objects such as analytic views or synonyms are not supported.
namespace

Specifies the namespace for the data set.

A NULL value specifies a system-generated namespace value, unique to the Autonomous Database instance.

name

Specifies the name for the data set.
description

Specifies text to describe the data.
scope

Describes who is allowed to access the data set. The value 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. By scope, Cloud Links access is limited to within a single region and is not cross-region. A consumer in a different region can access a data set only when a cross-region Refreshable Clone of the database that is the data set owner exists in the consumer database's region.

    See Register or Unregister a Data Set in a Different Region for more information.

  • 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.

  • List of regions: Access to the data set is allowed for databases in the specified regions

    For example: 

    scope   =>  'us-phoenix-1,us-ashburn-1',

    A consumer in a different region can access a data set only when a cross-region Refreshable Clone of the database that is the data set owner exists in the consumer database's region.

The scope values, MY$REGION, MY$TENANCY, and MY$COMPARTMENT are variables that act as convenience macros and resolve to OCIDs.

auth_required

Specifies that an additional authorization is required for databases to read from the data set. The following cases are possible:

  • 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.

data_set_owner

Specifies the data set owner. This is indicates who the data set belongs to or who is responsible for updating and maintaining the data set. For example, you can set the data_set_owner to the email address of the person who registered the data set.

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.

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.

  • The OFFLOAD_TARGET is the OCID for an Autonomous Database instance that is a refreshable clone.

For example, the following shows a JSON sample with three OFFLOAD_TARGET/CLOUD_LINK_DATABASE_ID value pairs: 

{
  "OFFLOAD_TARGETS": [
    {
      "CLOUD_LINK_DATABASE_ID": "34xxxxx69708978",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfabc"
    },
    {
      "CLOUD_LINK_DATABASE_ID": "34xxxxx89898978",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfdef"
    },
    {
      "CLOUD_LINK_DATABASE_ID": "34xxxxx4755680",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfghi"
    }
  ]
}

When a data set consumer requests access to a data set that you register with offload_targets with the ANY keyword, then any request for access is offloaded to the refreshable clone identified with OFFLOAD_TARGET in the supplied JSON (except requests that have an explicit 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 entry: 

{
  "OFFLOAD_TARGETS": [
    {
      "CLOUD_LINK_DATABASE_ID": "ANY",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfdef"
    },
    {
      "CLOUD_LINK_DATABASE_ID": "34xxxxx4755680",
      "OFFLOAD_TARGET":
"ocid1.autonomousdatabase.oc1..xxxxx3pv6wkcr4jqae5f44n2b2m2yt2j6rx32uzr4h25vqstifsfghi"
    }
  ]
}

DBMS_CLOUD_LINK.REGISTER reports an error if the OCID supplied as an OFFLOAD_TARGET value is not an OCID of a refreshable clone in the same region.

See Use Refreshable Clones with Autonomous Database for information on using refreshable clones.

Usage Notes

  • You can register an object in another schema if you have READ WITH GRANT OPTION privilege on the object.

  • After you register an object, users may need to wait up to ten (10) minutes to access the object with Cloud Links.

  • To change the scope for an existing data set, you must first unregister the data set with DBMS_CLOUD_LINK.UNREGISTER. Then you need to register the data set with the new scope with DBMS_CLOUD_LINK.REGISTER. The total wait time for these two operations can be up to 20 minutes, including 10 minutes for the unregister and another 10 minutes for the register to be propagated and accessible through Cloud Links.

    This delay can also impact the visibility of the data set 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 READ WITH GRANT OPTION privileges for the table or view.

  • 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

  • Certain hierarchical validity checks for registration cannot happen at registration time. Invalid registrations will not be visible, discoverable, or even accessible to anyone.

  • To use DBMS_CLOUD_LINK.REGISTER, 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.

  • 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 a cross-region refreshable clone.

REVOKE_AUTHORIZATION Procedure

This procedure revokes authorization for a specified database to access the specified data set.

Syntax

DBMS_CLOUD_LINK.REVOKE_AUTHORIZATION(
      database_id        IN   VARCHAR2,
      namespace          IN   VARCHAR2 DEFAULT,
      name               IN   VARCHAR2
);

Parameters

Parameter Description

database_id

Specifies the database ID for an Autonomous Database instance. Use DBMS_CLOUD_LINK.GET_DATABASE_ID to obtain the database ID.

namespace

Specifies the data set namespace to revoke access authorization for the specified database_id.

name

Specifies the data set name to revoke access authorization for the specified database_id.

UNREGISTER Procedure

The procedure allows a user who previously registered a table or a view with the REGISTER procedure to unregister the table or view so that it no longer is available for remote access.

Syntax

DBMS_CLOUD_LINK.UNREGISTER(
      namespace        IN   VARCHAR2,
      name             IN   VARCHAR2
);

Parameters

Parameter Description

namespace

Specifies a username.
name

Specifies the name for the data set.

Usage Note

DBMS_CLOUD_LINK.UNREGISTER may also take up to ten (10) minutes to fully propagate, after which the data can longer be accessed remotely.