Make External Calls Using a Customer-Managed Wallet

When your Autonomous AI Database instance is on a private endpoint you can use a customer-managed wallet to handle external calls, or with the scheduler when the scheduler sends email for various events related to scheduler jobs.

On Autonomous AI Database you can make external calls for any of the of the following purposes:

• To use web services with UTL_HTTP.

• To access data from LDAP servers using DBMS_LDAP.

• To send email with UTL_SMTP.

• To communicate with external TCP/IP-based servers using TCP/IP with UTL_TCP.

• For Oracle Scheduler job email notifications.

By default when you use procedures in these packages Autonomous AI Database maintains an internal wallet and always uses secure connections (the Oracle managed wallet contains more than 90 of the most common trusted root and intermediate SSL certificates). When your Autonomous AI Database resides on a private endpoint you have the option to use the default Oracle managed wallet with the trusted and intermediate SLL certifications, or you can supply a customer-managed wallet.

When your Autonomous AI Database resides on a private endpoint you can specify a customer-managed wallet for UTL_HTTP, UTL_SMTP, DBMS_LDAP, and DBMS_NETWORK_ACL_ADMIN using the following PL/SQL procedures:

UTL_HTTP.SET_WALLET (
   path                 IN VARCHAR2,
   password             IN VARCHAR2 DEFAULT NULL);
UTL_HTTP.REQUEST (
   wallet_path          IN VARCHAR2 DEFAULT NULL,
   wallet_password      IN VARCHAR2 DEFAULT NULL)
UTL_HTTP.REQUEST_PIECES (
   wallet_p ath         IN VARCHAR2 DEFAULT NULL,
   wallet_password      IN VARCHAR2 DEFAULT NULL,
UTL_HTTP.CREATE_REQUEST_CONTEXT (
 wallet_path            IN VARCHAR2 DEFAULT NULL,
 wallet_password        IN VARCHAR2 DEFAULT NULL)
UTL_TCP.OPEN_CONNECTION
   wallet_path          IN  VARCHAR2 DEFAULT NULL,
   wallet_password      IN  VARCHAR2 DEFAULT NULL);
UTL_SMTP.OPEN_CONNECTION
   wallet_path          IN  VARCHAR2 DEFAULT NULL,
   wallet_password      IN  VARCHAR2 DEFAULT NULL)
DBMS_LDAP.OPEN_SSL(
  sslwrl                IN VARCHAR2,
  sslwalletpasswd       IN VARCHAR2)
DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE
  wallet_path IN VARCHAR2
DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACL(
  wallet_path IN VARCHAR2

In these calls, depending on the procedure, use either the path or wallet_path parameter to specify a customer-managed wallet. You set the wallet directory with the DIR: prefix and include a wallet path. For example:

UTL_HTTP.set_wallet('DIR:WALLET_DIR', 'password');

The current user invoking the API must have READ privileges on the directory object or be granted the CREATE ANY DIRECTORY system privilege.

The DIR: prefix is the preferred form to use to specify a customer-managed wallet. In addition, the file: prefix is supported. For example:

UTL_HTTP.set_wallet('file:WALLET_DIR/wallet.sso', 'password');

See Use a Customer-Managed Wallet for External Calls with UTL_HTTP for more information:

Shows the prerequisite steps to use a customer-managed wallet with external calls or with scheduler SMTP email notifications.

1. Verify that the Autonomous AI Database instance is configured with a private endpoint.

   See Configure Network Access with Private Endpoints for more information.

2. Set the private_target parameter to value TRUE when you grant the required ACLs with dbms_network_acl_admin.append_wallet_ace, or set ROUTE_OUTBOUND_CONNECTIONS to the value PRIVATE_ENDPOINT.

   See Use a Customer-Managed Wallet for External Calls with UTL_HTTP and Enhanced Security for Outbound Connections with Private Endpoints and for more information.

3. Obtain or create a customer-managed wallet.

   For example, to create a wallet with orapki:

   -- Create an SSL Wallet and load the Root CERTs using orapki utility
   $ORACLE_HOME/bin/orapki wallet create -wallet /u01/web/wallet -pwd ********
   $ORACLE_HOME/bin/orapki wallet add -wallet /u01/web/wallet -trusted_cert -cert MyWebServer.cer -pwd ********
   -- Store the credentials in the SSL Wallet using mkstore utility
   $ORACLE_HOME/bin/mkstore -wrl /u01/web/wallet -createCredential secret-from-the-wallet 'example@oracle.com' ********
   Enter wallet password: ********

   See Managing Public Key Infrastructure (PKI) Elements for more information.

4. Store the wallet to a bucket on Cloud Object Storage.

   After you obtain or create the customer-managed wallet containing self-signed certificates, store the wallet to a location on your Cloud Object Storage.

When your Autonomous AI Database instance is on a private endpoint you can use a customer-managed wallet to handle external calls.

1. Create a credential using DBMS_CLOUD.CREATE_CREDENTIAL to access Cloud Object Storage.

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

   The values you provide for username and password depend on the Cloud Object Storage service you are using.

   This creates the credential you use to access the Cloud Object Storage where the customer-managed wallet resides.

   Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

2. Use an existing directory or create a new a directory for the wallet file.

   For example:

   CREATE DIRECTORY wallet_dir AS 'directory_path_of_your_choice';
               

   See Create Directory in Autonomous AI Database for information on creating directories.

3. Use DBMS_CLOUD.GET_OBJECT to upload the customer-managed wallet to the directory you created in the previous step, WALLET_DIR.

   For example:

   BEGIN 
       DBMS_CLOUD.GET_OBJECT(
           credential_name => 'DEF_CRED_NAME',
           object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
           directory_name => 'WALLET_DIR'); 
   END;
   /

   In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

4. Grant the required ACLs to allow you to read the credentials from the specified directory.

   BEGIN
     dbms_network_acl_admin.append_wallet_ace(
         wallet_path => 'dir:WALLET_DIR',
         ace         => xs$ace_type(
              privilege_list => xs$name_list('use_client_certificates', 'use_passwords'),
              principal_name => 'USER_NAME',
              principal_type => xs_acl.ptype_db)
       );
   END;
   /

5. Set the wallet path for use with UTL_HTTP procedures.

   BEGIN
       UTL_HTTP.set_wallet('DIR:WALLET_DIR', 'password');
   END;
   /

   To specify the wallet directory with UTL_HTTP.set_wallet you can use either the dir: prefix or the file: prefix.

   The dir: prefix is only available on Autonomous AI Database. See UTL_HTTP for information on the file: prefix.

6. Now you can run UTL_HTTP procedures to access an endpoint using the customer-managed wallet with a self-signed certificate.

   For example:

   SELECT UTL_HTTP.REQUEST('https://example.com') from dual;

Describes the steps to use the scheduler SMTP email server with a customer-managed wallet.

1. Create a credential using DBMS_CLOUD.CREATE_CREDENTIAL to access your Cloud Object Storage.

   For example:

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

   The values you provide for username and password depend on the Cloud Object Storage service you are using.

   This creates the credential you use to access Cloud Object Storage where the customer-managed wallet resides.

   Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

2. Use an existing directory or create a new a directory for the wallet file.

   For example:

   CREATE DIRECTORY wallet_dir AS 'directory_path_of_your_choice';
               

   See Create Directory in Autonomous AI Database for information on creating directories.

3. Use DBMS_CLOUD.GET_OBJECT to upload the wallet to the directory you created in the previous step, WALLET_DIR.

   For example:

   BEGIN 
       DBMS_CLOUD.GET_OBJECT(
           credential_name => 'DEF_CRED_NAME',
           object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
           directory_name => 'WALLET_DIR'); 
   END;
   /

   In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

4. Run the commands to set up the scheduler to send SMTP email for scheduler job notifications.

   For example:

   BEGIN
       DBMS_SCHEDULER.set_scheduler_attribute('EMAIL_SERVER','smtp.email.us-ashburn-1.oci.oraclecloud.com:587');
       DBMS_CLOUD.create_credential('EMAIL_CRED', 'ocid1.user.oc1..username', 'password');
       DBMS_SCHEDULER.set_scheduler_attribute('EMAIL_SERVER_CREDENTIAL','ADMIN.EMAIL_CRED');
       DBMS_SCHEDULER.set_scheduler_attribute('EMAIL_SERVER_ENCRYPTION','STARTTLS');
   END;
   /

   These commands set the scheduler email SMTP server, create the credential object containing the SMTP credentials and set the scheduler attribute for the SMTP credentials, and specify to use TLS for email sent for scheduler job notification.

   See SET_SCHEDULER_ATTRIBUTE Procedure for more information.

5. Create a credential to store the password for the customer-managed wallet.

   BEGIN
        DBMS_CLOUD.CREATE_CREDENTIAL(
            credential_name  => 'WALLET_CRED',
            username         =>  'any_user', 
            password         => 'password');
   END;
   /

   This creates the credential you use in the next step to provide the password for the customer-managed wallet.

6. Set the scheduler wallet directory and the wallet credential.

   BEGIN
        DBMS_SCHEDULER.set_scheduler_attribute('EMAIL_SERVER_WALLET_DIRECTORY','WALLET_DIR'); 
        DBMS_SCHEDULER.set_scheduler_attribute('EMAIL_SERVER_WALLET_CREDENTIAL', 'ADMIN.WALLET_CRED');
   END;
   /

7. Query the DBA_SCHEDULER_GLOBAL_ATTRIBUTE view to verify the values you set in the previous steps.

   SELECT attribute_name, value
          FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE
          WHERE attribute_name LIKE 'EMAIL_SERVER%' ORDER BY 1, 2;

   ATTRIBUTE_NAME                 VALUE
   ------------------------------ -----------------------------------------------
   EMAIL_SERVER                   smtp.email.us-ashburn-1.oci.oraclecloud.com:587
   EMAIL_SERVER_CREDENTIAL        "ADMIN"."EMAIL_CRED"
   EMAIL_SERVER_ENCRYPTION        STARTTLS
   EMAIL_SERVER_WALLET_CREDENTIAL "ADMIN"."WALLET_CRED"
   EMAIL_SERVER_WALLET_DIRECTORY  "WALLET_DIR"

Provides notes for using a customer-managed wallet with external calls.

• DBMS_CLOUD requests do not honor the custom wallet you set with UTL_HTTP.set_wallet. This includes DBMS_CLOUD.SEND_REQUEST and all object storage access for DBMS_CLOUD external tables that you define with DBMS_CLOUD.CREATE_EXTERNAL_TABLE, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE, or DBMS_CLOUD.CREATE_HYBRID_PART_TABLE. When you perform a query on an external table that you created with a DBMS_CLOUD procedure, the queries do not honor the custom wallet you set with UTL_HTTP.set_wallet.

• APEX_WEB_SERVICE requests do not honor the custom wallet you set with UTL_HTTP.set_wallet.

  To use a customer-managed wallet with APEX, specify the p_wallet_path parameter in APEX_WEB_SERVICE API calls or set the Wallet Path instance setting in APEX Administration Services.

  See Access Oracle APEX Administration Services for more information.

• Both auto-login wallets and password protected wallets are supported. When you use an auto-login wallet, specify NULL for the wallet_password parameter.

• The current user invoking the UTL_HTTP.set_wallet API must have READ privileges on the directory object or be granted the CREATE ANY DIRECTORY system privilege.

• The UTL_HTTP.SET_AUTHENTICATION_FROM_WALLET API is allowed. See SET_AUTHENTICATION_FROM_WALLET Procedure for more information.

• The file: prefix is supported with UTL_HTTP.set_wallet as long as the specified file path is PATH_PREFIX compliant.

  You can determine the PATH_PREFIX compliance for a path provided as input with the DBMS_PDB_IS_VALID_PATH procedure (granted to PUBLIC including the ADMIN user).

  For example:

  with function check_path_prefix_compliance(file_path varchar2)
    return varchar2 as
  BEGIN
    if dbms_pdb_is_valid_path(file_path) then
      return 'YES';
    else
      return 'NO';
    end if;
  END;
  SELECT
    check_path_prefix_compliance('/u03/dbfs/1276CDexample/data/dpdump') as PATH_PREFIX_COMPLIANT,
    check_path_prefix_compliance('/u01/app/oracle/diag') as PATH_PREFIX_COMPLIANT
  FROM
    dual;
  /

• To ensure backward compatibility when you use UTL_HTTP.set_wallet, in cases where the wallet path is ignored, input values such as file:, NULL, and so on, are accepted. These values are ignored and specify to use the default SSL wallet path with UTL_HTTP.set_wallet.

• The DBMS_NETWORK_ACL_ADMIN wallet ACL APIs, such as APPEND_WALLET_ACL are supported. These procedures allow you to grant/revoke wallet ACL privileges. See DBMS_NETWORK_ACL_ADMIN for more information.

• To support using the password credentials in an SSL wallet for authentication, the current user invoking UTL_HTTP APIs must have the "use-passwords" ACL privilege on the wallet path.

• Notes for setting EMAIL_SERVER_WALLET_DIRECTORY and EMAIL_SERVER_WALLET_CREDENTIAL with DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE:

  • To set the attribute values with DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE you must be an administrative user or a user with MANAGE SCHEDULER privileges (the ADMIN user has these privileges).

  • In addition to MANAGE SCHEDULER privilege, the user invoking DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE must have of READ privilege on the directory object you set with EMAIL_SERVER_WALLET_DIRECTORY, and EXECUTE privilege on the credential object you set with EMAIL_SERVER_WALLET_CREDENTIAL.