Oracle Cloud Infrastructure Documentation

Decrypt Data While Importing from Object Storage

You can decrypt and load data from encrypted files stored in Object Storage. You can also decrypt encrypted data on Object Storage that you use in an external table.

This option is useful when migrating from an on-premises database to an Autonomous Database if the data in your source files is encrypted.
Note

This option is only supported for Object Storage files less than 4 GB.

This option is applicable for the following procedures:

  • DBMS_CLOUD.COPY_DATA

  • DBMS_CLOUD.CREATE_EXTERNAL_TABLE

  • DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE

  • DBMS_CLOUD.CREATE_HYBRID_PART_TABLE

    For DBMS_CLOUD.CREATE_HYBRID_PART_TABLE this option is only applicable to the Object Storage files.

  • DBMS_CLOUD.COPY_COLLECTION

Topics

Parent topic: Encrypt Data While Exporting or Decrypt Data While Importing

Decrypt and Load Data Using DBMS_CRYPTO Algorithms

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

As a prerequisite you must have encrypted files and uploaded the files into Object Storage. This example uses a CSV file and it is assumed that the file is encrypted using DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5 algorithm and uploaded to your Cloud Object Storage.

See ENCRYPT Function for more information on the ENCRYPT function.

See DBMS_CRYPTO Operational Notes for more information on generating an encryption key.

To decrypt and load data into an existing table on Autonomous Database from Object Storage:

  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  => 'OCI$RESOURCE_PRINCIPAL',
   file_uri_list    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namepace-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.

Decrypt and Load Data with a User Defined Function

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

As a prerequisite for these steps you must have encrypted files and uploaded the files into Object Storage. This example uses a CSV file and it is assumed that the file is encrypted using DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5 algorithm and uploaded to your Cloud Object Storage.

See ENCRYPT Function for more information on the ENCRYPT function.

See DBMS_CRYPTO Operational Notes for more information on generating an encryption key.

To decrypt and load data into an existing table on Autonomous Database from Object Storage:

  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/namepace-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/namepace-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.