Shows the steps to decrypt encrypted files from Object Storage and load the data into a table on Autonomous Database (the decrypt step uses DBMS_CRYPTO algorithms).

1. Connect to your Autonomous Database instance.

   See Connect to Autonomous Database for more information.

2. Store your Cloud Object Storage credential using DBMS_CLOUD.CREATE_CREDENTIAL.

   For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'OBJ_STORE_CRED',
       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.

   See CREATE_CREDENTIAL Procedure for more information.

   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.

3. Create a credential to store the key using DBMS_CLOUD.CREATE_CREDENTIAL. For example:

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

   As an alternative you can create credentials to store the key in a vault. For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name  => 'ENC_CRED_NAME',
       params           => JSON_OBJECT ('username' value 'Any_username',
                                        'region'    value 'Region',
                                        'secret_id' value 'Secret_id_value'));
   END;
   /

   Note
   
   The username parameter you specify in the credential that stores the key can be any string.

   This creates the ENC_CRED_NAME credential which is a vault secret credential, where the secret (decryption/encryption key) is stored as a secret in the Oracle Cloud Infrastructure Vault.

   See CREATE_CREDENTIAL Procedure for more information.

4. Run DBMS_CLOUD.COPY_DATA and specify DBMS_CRYPTO encryption algorithm as decryption method.

   BEGIN
    DBMS_CLOUD.COPY_DATA (
      table_name       => 'CSV_COPY_DATA',
      credential_name  => 'OBJ_STORE_CRED',
      file_uri_list    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/encrypted.csv',
      format           => json_object( 'type' value 'csv', 'encryption' value json_object('type' value DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,'credential_name' value 'ENC_CRED_NAME'))
     );
   END;
   /

   This decrypts the ENCRYPTED.CSV file in the Object Storage. The data is then loaded into the CSV_COPY_DATA table. The format parameter encryption option value specifies a DBMS_CRYPTO encryption algorithm to be used to decrypt data.

   See DBMS_CRYPTO Algorithms for more information on encryption algorithms.

   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.

   For detailed information about the parameters, see COPY_DATA Procedure.

   For detailed information about the available format parameters, you can use with DBMS_CLOUD.COPY_DATA, see DBMS_CLOUD Package Format Options.

Shows the steps to decrypt files in Object Storage and load the data into tables using a user-defined decryption function.

1. Connect to your Autonomous Database instance.

   See Connect to Autonomous Database for more information.

2. Store your Cloud Object Storage credential using DBMS_CLOUD.CREATE_CREDENTIAL.

   For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'OBJ_STORE_CRED',
       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.

   See CREATE_CREDENTIAL Procedure for more information.

   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.

3. Create a user-defined function decryption callback function.

   For example:

   CREATE OR REPLACE FUNCTION decryption_func (data IN BLOB)
   RETURN BLOB
   IS
     l_decrypted_data     BLOB;
     response             DBMS_CLOUD_TYPES.resp;
     l_response_json      JSON_ARRAY_T;
     l_response_json_obj  JSON_OBJECT_T;
     l_secret_id          VARCHAR2(4000) := '<secret_ocid>';
     l_key                VARCHAR2(4000);
   BEGIN
      response := DBMS_CLOUD.send_request (
                   credential_name => 'OCI$RESOURCE_PRINCIPAL',
                   uri             => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/' || l_secret_id,
                   method          => 'GET');
     l_response_json_obj := JSON_OBJECT_T.parse (DBMS_CLOUD.get_response_text(response));
     l_key := UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw (l_response_json_obj.get_object('secretBundleContent').get_string('content'))));
    
     DBMS_LOB.createtemporary (l_decrypted_data, TRUE, DBMS_LOB.CALL);
     DBMS_CRYPTO.decrypt (
         dst => l_decrypted_data,
         src => data,
         typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
         key => l_key
       );
     RETURN l_decrypted_data;
   END decryption_func;
   /

   This creates the DECRYPTION_FUNC decryption function. This function decrypts data using a stream or block cipher with a user supplied key. The user supplied key in the example is stored in Oracle Cloud Infrastructure Vault and is retrieved dynamically by making a REST call to Oracle Cloud Infrastructure Vault service.

4. Run DBMS_CLOUD.COPY_DATA and specify the format option encryption and specify the user-defined function you created to decrypt the data.

   BEGIN
    DBMS_CLOUD.COPY_DATA (
       table_name      => 'CSV_COPY_DATA',
       credential_name => 'OBJ_STORE_CRED',
       file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/encrypted.csv',
       format          => json_object(
                            'type' value 'csv',
                            'encryption' value json_object('user_defined_function' value 'admin.decryption_func'))
     );
   end;
   /

   This decrypts the ENCRYPTED.CSV file in the Object Storage. The data is then loaded into the CSV_COPY_DATA table. The format parameter encryption option value specifies a user-defined function name to use to decrypt data.

   Note
   
   You must have the EXECUTE privilege on the user-defined function.

   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.

   For detailed information about the parameters, see COPY_DATA Procedure.

   For detailed information about the available format parameters, you can use with DBMS_CLOUD.COPY_DATA, see DBMS_CLOUD Package Format Options.