DBMS_CLOUD for Access Management

This section covers the DBMS_CLOUD subprograms used for access management.

Prerequisites

As a developer, you can use DBMS_CLOUD procedures with Autonomous AI Databases deployed on Oracle Public Cloud, Multicloud 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 AI 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 AI 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.

Summary of 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 AI Database.
DROP_CREDENTIAL Procedure	This procedure removes an existing credential from Autonomous AI Database.
UPDATE_CREDENTIAL Procedure	This procedure updates cloud service credential attributes in Autonomous AI Database.

CREATE_CREDENTIAL Procedure

This procedure stores cloud service credentials in Autonomous AI 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-CompatibleCredentials

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.

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.

Manage DBMS_CLOUD Credential to authenticate to C3 Object Storage

You can use the DBMS_CLOUD.CREATE_CREDENTIAL procedure to provide the Autonomous AI Database with the authentication information required to connect to the C3 Object Storage Bucket. 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 => 'OBJECT_STORAGE_CREDENTIAL',
    user_ocid       => 'ocid1.user.oc1..abcdedfxxxxx',
    tenancy_ocid    => 'ocid1.tenancy.oc1..abcdefxyyyyyy',
    private_key     => 'MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQDku_Rest_of_PrivateKey_no_spaces_no_newlines',
    fingerprint     => '3d:ba:f9:aa:bb:cc:dd:ee:ff:gg:hh:ii:jj:xa:xb:xc'
  );
END;
/

DROP_CREDENTIAL Procedure

This procedure removes an existing credential from Autonomous AI 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.

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.

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;
/

DBMS_CLOUD for Access Management

Prerequisites

Summary of DBMS_CLOUD Subprograms for Access Management

CREATE_CREDENTIAL Procedure

Syntax

Parameters

Usage Notes

Oracle Cloud Infrastructure Credentials (Auth Tokens)

Oracle Cloud Infrastructure Signing Key Based Credentials

Amazon Web Services (AWS) Credentials

Microsoft Azure Credentials

Amazon S3-CompatibleCredentials

Manage DBMS_CLOUD Credential to authenticate to C3 Object Storage

DROP_CREDENTIAL Procedure

Syntax

Parameters

UPDATE_CREDENTIAL Procedure

Syntax

Parameters

Usage Notes

Examples

Related Content