DBMS_CLOUD_ADMIN Package

This section covers the DBMS_CLOUD_ADMIN subprograms provided with Autonomous Database.

Subprogram Description

ATTACH_FILE_SYSTEM Procedure

This procedure attaches a file system in a directory on your database.

DETACH_FILE_SYSTEM Procedure

This procedure detaches a file system from a directory on your database.

DISABLE_EXTERNAL_AUTHENTICATION Procedure

This procedure disables external authentication for the Autonomous Database instance.

ENABLE_EXTERNAL_AUTHENTICATION Procedure

This procedure enables a user to logon to Autonomous Database using the specified external authentication scheme.

ATTACH_FILE_SYSTEM Procedure

This procedure attaches a file system in the database.

The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure attaches a file system in your database and stores information about the file system in the DBA_CLOUD_FILE_SYSTEMS view.

Syntax

DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (
    file_system_name         IN VARCHAR2,
    file_system_location     IN VARCHAR2,
    directory_name           IN VARCHAR2,
    description              IN VARCHAR2
);

Parameters

Parameter Description

file_system_name

Specifies the name of the file system.

This parameter is mandatory.

file_system_location

Specifies the location of the file system.

The value you supply with file_system_location consists of a Fully Qualified Domain Name (FQDN) and a file path in the form: FQDN:file_path.

For example:

  • FQDN: myhost.sub000445.myvcn.oraclevcn.com

    For Oracle Cloud Infrastructure File Storage set the FQDN in Show Advanced Options when you create a file system. See Creating File Systems for more information.

  • File Path: /results

This parameter is mandatory.

directory_name

Specifies the directory name for the attached file system. The directory must exist.

This parameter is mandatory.

description

Provides a description of the task.

This parameter is mandatory.

Example: 

BEGIN
   DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (
    file_system_name      => 'FSS',
    file_system_location  => 'myhost.sub000445.myvcn.oraclevcn.com:/results',
    directory_name        => 'FSS_DIR',
    description           => 'Source NFS for sales data'
  );
END;
/

Usage Notes

  • To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

  • You must have WRITE privilege on the directory object in the database to attach a file system using DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM.

  • The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure can only attach a private File Storage Service in databases with Private Endpoints enabled.

    See OCI File Storage Service and Configure a Development System to Access the Database for more information.

  • The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure looks up the Network File System hostname on the customer's virtual cloud network (VCN). The error "ORA-20000: Mounting NFS fails" is returned if the hostname specified in the location cannot be located.

  • Oracle Cloud Infrastructure File Storage uses NFS Version 3 to share

  • If you attach to non-Oracle Cloud Infrastructure File Storage systems, the procedure supports NFS Version 2 and NFS Version 3

  • DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM does not support NFS Version 4

DETACH_FILE_SYSTEM Procedure

This procedure detaches a file system from the database.

The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure detaches a file system from your database. In addition to that, the DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure also removes the information about the file system from the DBA_CLOUD_FILE_SYSTEMS view.

Syntax

DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM(
    file_system_name         IN VARCHAR2
);

Parameters

Parameter Description

file_system_name

Specifies the name of the file system.

This parameter is mandatory.

Example: 

BEGIN
   DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM (
    file_system_name      => 'FSS'                                       
  );                                                                          
END;                                                                          
/

Usage Notes

  • To run this procedure, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

  • You must have the WRITE privilege on the directory object in the database, to detach a file system from a directory using the DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure.

  • The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure can only detach a private File Storage Service in databases with Private Endpoints enabled.

    See OCI File Storage Service and Configure a Development System to Access the Database for more information.

  • The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure looks up the Network File System hostname on the customer's virtual cloud network (VCN). The error "ORA-20000: Mounting NFS fails" is returned if the hostname specified in the location cannot be located.

DISABLE_EXTERNAL_AUTHENTICATION Procedure

Disables user authentication with external authentication schemes for the database.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION;

Exceptions

Exception Error Description
invalid_ext_auth ORA-20004

See the accompanying message for a detailed explanation.

Example

BEGIN 
   DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION;
END;
/

PL/SQL procedure successfully completed.

ENABLE_EXTERNAL_AUTHENTICATION Procedure

Enable users to login to the database with external authentication schemes.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
   type         IN VARCHAR2,
   force        IN BOOLEAN DEFAULT FALSE,
   params       IN CLOB DEFAULT NULL
);

Parameter

Parameter Description

type

Specifies the external authentication type. Valid values: or .

  • 'OCI_IAM'
  • 'AZURE_AD'
  • 'CMU'
  • 'KERBEROS'

force

(Optional) Override a currently enabled external authentication scheme. Valid values are TRUE or FALSE.

The default value is FALSE.

params

A JSON string that provides additional parameters for the external authentication.

CMU parameters:

  • location_uri: specifies the cloud storage URI for the bucket where files required for CMU are stored.

    If you specify location_uri there is a fixed name directory object CMU_WALLET_DIR created in the database at the path 'cmu_wallet' to save the CMU configuration files. In this case, you do not need to supply the directory_name parameter.

  • credential_name: specifies the credentials that are used to download the CMU configuration files from the Object Store to the directory object.

    Default value is NULL which allows you to provide a Public, Preauthenticated, or pre-signed URL for Object Store bucket or subfolder.

  • directory_name: specifies the directory name where configuration files required for CMU are stored. If directory_name is supplied, you are expected to copy the CMU configuration files dsi.ora and cwallet.sso to this directory object.

KERBEROS parameters:

  • location_uri: specifies the cloud storage URI for the the bucket where files required for Kerberos are stored.

    If you specify location_uri there is a fixed name directory object KERBEROS_DIR created in the database to save the Kerberos configuration files. In this case, you do not need to supply the directory_name parameter.

  • credential_name: specifies the credential that are used to download Kerberos configuration files from the Object Store location to the directory object.

    Default value is NULL which allows you to provide a Public, Preauthenticated, or pre-signed URL for Object Store bucket or subfolder.

  • directory_name: specifies the directory name where files required for Kerberos are stored. If directory_name is supplied, you are expected to copy the Kerberos configuration files to this directory object.

AZURE_AD parameters:

  • tenant_id: Tenant ID of the Azure Account. Tenant Id specifies the Autonomous Database instance's Azure AD application registration.
  • application_id: Azure Application ID created in Azure AD to assign roles/schema mappings for external authentication in the Autonomous Database instance.
  • application_id_uri: Unique URI assigned to the Azure Application.

    This it the identifier for the Autonomous Database instance. The name must be domain qualified (this supports cross tenancy resource access).

    The maximum length for this parameter is 256 characters.

Exceptions

Exception Error Description
invalid_ext_auth ORA-20004

See the accompanying message for a detailed explanation.

Usage Notes

  • With type OCI_IAM, if the resource principal is not enabled on the Autonomous Database instance, this routine enables resource principal with DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL.

  • This procedure sets the system parameters IDENTITY_PROVIDER_TYPE and IDENTITY_PROVIDER_CONFIG to required users to access the instance with Oracle Cloud Infrastructure Identity and Access Management authentication and authorization.

Examples

Enable OCI_IAM Authentication 

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'OCI_IAM',
     force=> TRUE );
END;
/

PL/SQL procedure successfully completed.

Enable CMU Authentication for Microsoft Active Directory

You pass in a directory name that contains the CMU configuration files through params JSON argument. 

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'CMU',
     force => TRUE,
     params => JSON_OBJECT('directory_name' value 'CMU_DIR'); // CMU_DIR directory object already exists
END;
/

PL/SQL procedure successfully completed.

You pass in a location URI pointing to an Object Storage location that contains CMU configuration files through params JSON argument. 

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
       type     => 'CMU',
       params   => JSON_OBJECT('location_uri' value 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
                               'credential_name' value 'my_credential_name')
   );
END;
/

PL/SQL procedure successfully completed.

Enable Azure AD Authentication

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'AZURE_AD',
     force => TRUE,
     params   => JSON_OBJECT( 'tenant_id' VALUE '....',
                              'application_id' VALUE '...',
                              'application_id_uri' VALUE '.....' ));
END;
/

PL/SQL procedure successfully completed.

Enable Kerberos Authentication

You pass in a directory name that contains Kerberos configuration files through params JSON argument. 

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'KERBEROS',
     force => TRUE,
     params => JSON_OBJECT('directory_name' value 'KERBEROS_DIR'); // KERBEROS_DIR directory object already exists
END;
/

PL/SQL procedure successfully completed.

You pass in a location URI pointing to an Object Storage location that contains Kerberos configuration files through params JSON argument. 

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'KERBEROS',
     force => TRUE,
     params => JSON_OBJECT('location_uri' value 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
                           'credential_name' value 'my_credential_name');
END;
/

PL/SQL procedure successfully completed.