Make External Calls Using a Customer-Managed Wallet

When your Autonomous Database instance is on a private endpoint you can use a customer-managed wallet with procedures in UTL_HTTP, DBMS_LDAP, UTL_SMTP, or UTL_TCP. You can also use a customer-managed wallet when the scheduler sends SMTP email notifications for various events related to scheduler jobs.

About Using a Customer-Managed Wallet with External Calls

When your Autonomous 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 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 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 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 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:

About Using a Customer-Managed Wallet with Scheduler Email Server

The scheduler email server is available to send email notifications for various events related to the scheduler, such as notification of started, failed, or completed jobs. By default, the scheduler SMTP email server uses the wallet defined in the SSL_WALLET property for SSL/TLS communications. Optionally, you can use a customer-managed wallet with the scheduler SMTP email server.

The following global attributes support using a customer-managed wallet:

  • EMAIL_SERVER_WALLET_DIRECTORY: is set to a directory object that specifies the path where the SSL wallet resides.

  • EMAIL_SERVER_WALLET_CREDENTIAL: is set to a credential object with a username/password pair, where the username is any value and the password is the SSL wallet password.

You set values for these attributes using DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE.

See the following for more information:

Prerequisites to Use a Customer-Managed Wallet with External Calls

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

Perform the prerequisite steps:

  1. Verify that the Autonomous Database instance is configured with a private endpoint.
  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.
  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: ********
  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.

Use a Customer-Managed Wallet for External Calls with UTL_HTTP

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

These steps describe using a customer-managed wallet with UTL_HTTP. The steps are the same for the other supported packages, including: DMBS_LDAP, UTL_SMTP, and UTL_TCP.

Perform the prerequisite steps to use a customer-managed wallet. See Prerequisites to Use a Customer-Managed Wallet with External Calls for more information.

To configure an Autonomous Database to use a customer-managed wallet:

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

Use a Customer-Managed Wallet with Scheduler Email Notifications

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

Perform the prerequisite steps to use a customer-managed wallet. See Prerequisites to Use a Customer-Managed Wallet with External Calls for more information.

To use a customer-managed wallet with the scheduler email server:

  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 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"

Notes for Using a Customer-Managed Wallet with External Calls

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.

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