DBMS_CLOUD for Access Management

This section covers the DBMS_CLOUD subprograms used for access management.

Related Topics

Prerequisites

As a developer, you can use DBMS_CLOUD procedures with Autonomous Databases deployed on Oracle Public Cloud or Exadata Cloud@Customer.

Depending on the deployment choice, the following prerequisites must be met to use the DBMS_CLOUD procedures with Amazon S3, Azure Blob Storage, and Google Cloud Storage service providers.

An outbound connectivity must have been configured using a NAT gateway, by your fleet administrator as described below:
  • Create a NAT gateway in the Virtual Cloud Network (VCN) where your Autonomous Database resources reside by following the instructions in Create a NAT Gateway in Oracle Cloud Infrastructure Documentation.
  • After creating the NAT gateway, add a route rule and an egress security rule to each subnet (in the VCN) where Autonomous Database resources reside so that these resources can use the gateway to obtain a public key from your Azure AD instance:
    1. Go to the Subnet Details page for the subnet.
    2. In the Subnet Information tab, click the name of the subnet's Route Table to display its Route Table Details page.
    3. In the table of existing Route Rules, check whether there is already a rule with the following characteristics:
      • Destination: 0.0.0.0/0
      • Target Type: NAT Gateway
      • Target: The name of the NAT gateway you just created in the VCN

      If such a rule does not exist, click Add Route Rules and add a route rule with these characteristics.

    4. Return to the Subnet Details page for the subnet.
    5. In the subnet's Security Lists table, click the name of the subnet's security list to display its Security List Details page.
    6. In the side menu, under Resources, click Egress Rules.
    7. In the table of existing Egress Rules, check whether there is already a rule with the following characteristics:
      • Destination Type: CIDR
      • Destination: 0.0.0.0/0
      • IP Protocol: TCP
      • Source Port Range: 443
      • Destination Port Range: All

      If such a rule does not exist, click Add Egress Rules and add an egress rule with these characteristics.

The HTTP Proxy settings in your environment must allow the database to access the cloud service provider.

These settings are defined by your fleet administrator while creating the Exadata Cloud@Customer infrastructure as described in Using the Console to Provision Exadata Database Service on Cloud@Customer .

Note:

The network configuration including the HTTP Proxy can only be edited until the Exadata Infrastructure is in Requires Activation state. Once it is activated, you cannot edit those settings.

Setting up an HTTP Proxy for an already provisioned Exadata Infrastructure needs a Service Request (SR) in My Oracle Support. See Create a Service Request in My Oracle Support for details.

DBMS_CLOUD Subprograms for Access Management

The subprograms for credential management within the DBMS_CLOUD package, including creating, deleting, and updating credentials.

Subprogram Description

CREATE_CREDENTIAL Procedure

 This procedure stores cloud service credentials in Autonomous Database.

DROP_CREDENTIAL Procedure

 This procedure removes an existing credential from Autonomous Database.

UPDATE_CREDENTIAL Procedure

 This procedure updates cloud service credential attributes in Autonomous Database.

CREATE_CREDENTIAL Procedure

This procedure stores cloud service credentials in Autonomous Database.

Use stored cloud service credentials to access the cloud service for data loading, for querying external data residing in the cloud, or for other cases when you use DBMS_CLOUD procedures with a credential_name parameter.

Syntax

DBMS_CLOUD.CREATE_CREDENTIAL (
      credential_name   IN VARCHAR2,
      username          IN VARCHAR2,
      password          IN VARCHAR2 DEFAULT NULL);


DBMS_CLOUD.CREATE_CREDENTIAL (
      credential_name IN VARCHAR2,
      user_ocid       IN VARCHAR2,
      tenancy_ocid    IN VARCHAR2,
      private_key     IN VARCHAR2,
      fingerprint     IN VARCHAR2);

Parameters

Parameter Description

credential_name

The name of the credential to be stored. The credential_name parameter must conform to Oracle object naming conventions, which do not allow spaces or hyphens.

username

The username and password arguments together specify your cloud service credentials. See the usage notes for what to specify for the username and password for different cloud services.

password

The username and password arguments together specify your cloud service credentials.

user_ocid

Specifies the user's OCID. See Where to Get the Tenancy's OCID and User's OCID for details on obtaining the User's OCID.

tenancy_ocid

Specifies the tenancy's OCID. See Where to Get the Tenancy's OCID and User's OCID for details on obtaining the Tenancy's OCID.

private_key

Specifies the generated private key. Private keys generated with a passphrase are not supported. You need to generate the private key without a passphrase. See How to Generate an API Signing Key for details on generating a key pair in PEM format.

fingerprint

Specifies a fingerprint. After a generated public key is uploaded to the user's account the fingerprint is displayed in the console. Use the displayed fingerprint for this argument. See How to Get the Key's Fingerprint and How to Generate an API Signing Key for more details.

Usage Notes

  • This operation stores the credentials in the database in an encrypted format.

  • You can see the credentials in your schema by querying the user_credentials table.

  • The ADMIN user can see all the credentials by querying the dba_credentials table.

  • You only need to create credentials once unless your cloud service credentials change. Once you store the credentials you can then use the same credential name for DBMS_CLOUD procedures that require a credential_name parameter.

  • This procedure is overloaded. If you provide one of the key based authentication attributes, user_ocid, tenancy_ocid, private_key, or fingerprint, the call is assumed to be an Oracle Cloud Infrastructure Signing Key based credential.

  • You can list credentials from the view ALL_CREDENTIALS. For example, run the following command to list credentials: 

    SELECT credential_name, username, comments FROM all_credentials;

Oracle Cloud Infrastructure Credentials (Auth Tokens)

For Oracle Cloud Infrastructure the username is your Oracle Cloud Infrastructure user name. The password is your Oracle Cloud Infrastructure auth token. See Working with Auth Tokens.

For example:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@example.com',
    password => 'password' );
END;
/

Use Auth Token based credentials when you are authenticating calls to OCI Object Storage. For calls to any other type of Oracle Cloud Infrastructure cloud service, use Oracle Cloud Infrastructure Signing Key Based Credentials.

Oracle Cloud Infrastructure Signing Key Based Credentials

Use the Oracle Cloud Infrastructure signing key related parameters, including: user_ocid, tenancy_ocid, private_key, and fingerprint with Oracle Cloud Infrastructure Signing Keys authentication.

For example:

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => ‘OCI_KEY_CRED’,
       user_ocid       => ‘ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa’,
       tenancy_ocid    => ‘ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a’,
       private_key     => ‘MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=’,
       fingerprint     => ‘f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27’);
END;
/

Private keys generated with a passphrase are not supported. You need to generate the private key without a passphrase. See How to Generate an API Signing Key for more information.

Amazon Web Services (AWS) Credentials

If your source files reside in Amazon S3 or you are calling an AWS API, the username is your AWS access key ID and the password is your AWS secret access key. See AWS Identity and Access Management.

Microsoft Azure Credentials

If your source files reside in Azure Blob Storage or you are calling an Azure API, the username is your Azure storage account name and the password is an Azure storage account access key. See About Azure storage accounts.

Amazon S3-Compatible Credentials

Service Credentials Information

Google Cloud Storage

If your source files reside in Google Cloud Storage or you are calling Google Cloud Storage APIs, then you need to set a default Google project and obtain an HMAC key to create credentials to supply with Google Cloud Storage S3-compatible URLs. Use the HMAC key id as the username, and the HMAC secret as the password.

See Projects and HMAC Keys for more information.

DROP_CREDENTIAL Procedure

This procedure removes an existing credential from Autonomous Database.

Syntax

DBMS_CLOUD.DROP_CREDENTIAL (
   credential_name     IN VARCHAR2);

Parameters

Parameter Description

credential_name

The name of the credential to be removed.

UPDATE_CREDENTIAL Procedure

This procedure updates an attribute with a new value for a specified credential_name.

Use stored credentials for data loading, for querying external data residing in the Cloud, or wherever you use DBMS_CLOUD procedures with a credential_name parameter.

Syntax

DBMS_CLOUD.UPDATE_CREDENTIAL (
    credential_name   IN VARCHAR2,
    attribute         IN VARCHAR2,
    value             IN VARCHAR2);

Parameters

Parameter Description

credential_name

The name of the credential to be updated.

attribute

Name of attribute to update.

For a username/password type credential, the valid attribute values are: USERNAME and PASSWORD.

See CREATE_CREDENTIAL Procedure for more information.

value

New value for the specified attribute.

Usage Notes

  • The username value is case sensitive. It cannot contain double quotes or spaces.

  • The ADMIN user can see all the credentials by querying dba_credentials.

  • You only need to create credentials once unless your cloud service credentials change. Once you store the credentials you can then use the same credential name for DBMS_CLOUD procedures that require a credential_name parameter.

  • You can list credentials from the view ALL_CREDENTIALS. For example, run the following command to list credentials: 

    SELECT credential_name, username, comments FROM all_credentials;

Examples

BEGIN
  DBMS_CLOUD.UPDATE_CREDENTIAL(
     credential_name => 'OBJ_STORE_CRED',
     attribute => 'PASSWORD',
     value => 'password'); 
END;
/
BEGIN
  DBMS_CLOUD.UPDATE_CREDENTIAL(
     credential_name => 'ARN_CRED',
     attribute => 'aws_role_arn',
     value => 'NEW_AWS_ARN'); 
END;
/