Oracle Cloud Infrastructure Documentation
Try Free Tier

Encrypt Data While Exporting to Object Storage

You can encrypt table data while exporting to Object Storage.

Use the format parameter and the encryption option with DBMS_CLOUD.EXPORT_DATA to encrypt data when you export from Autonomous Database to Object Storage.

Note the following when you export encrypted data to Object Storage:

  • The encryption option is only supported when exporting data from Autonomous Database to Object Storage as CSV, JSON, or XML.

  • When the export includes both encryption and compression, the order of operations is: first the data is compressed, next the data is encrypted, and then it is uploaded to Object Storage.

  • There are two supported encryption methods:

    • Using a user-defined function.

    • Using a DBMS_CRYPTO specified encryption algorithm.

      See DBMS_CRYPTO for information on the cryptographic functions and procedures for encryption and decryption.

Topics

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

Encrypt Data Using DBMS_CRYPTO Encryption Algorithms

Shows the steps to encrypt data using DBMS_CRYPTO encryption algorithms while exporting to Cloud Object Storage.

Perform the following steps to encrypt data while exporting to Cloud Object Storage (this example exports table data to a CSV file):

  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 encryption key (the encryption key to be used for encrypting data).

    When you encrypt data using DBMS_CRYPTO encryption algorithms you store the encryption key in a credential. The key is specified in the password field in a credential you create with 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 a credential 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 Oracle Cloud Infrastructure Vault.

    See CREATE_CREDENTIAL Procedure for more information.

  4. Run DBMS_CLOUD.EXPORT_DATA.

    Use the format parameter with the encryption option. The encryption type specifies the DBMS_CRYPTO encryption algorithm to use to encrypt the table data and the credential_name value is credential that specifies the secret (encryption key).

    For example:

    BEGIN
    DBMS_CLOUD.EXPORT_DATA (
        credential_name => 'OBJ_STORE_CRED',
        file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/encrypted.csv',
        query           => 'SELECT * FROM ADMIN.employees',
        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 encrypts and exports the data from the EMPLOYEES table into a CSV file.

    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.

    See EXPORT_DATA Procedure and DBMS_CLOUD Package Format Options for EXPORT_DATA for more information.

After you encrypt files with DBMS_CLOUD.EXPORT_DATA, when you use DBMS_CRYPTO encryption algorithms to encrypt the files, you have these options for using or importing the files you exported:

  • You can use DBMS_CLOUD.COPY_DATA or DBMS_CLOUD.COPY_COLLECTION with the same encryption algorithm options and the key to decrypt the files.

    See Decrypt and Load Data Using DBMS_CRYPTO Algorithms for more information.

  • You can query the data in an external table by supplying the same encryption algorithm options and the key to decrypt the files, with any of the following procedures:

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

    See Decrypt and Load Data Using DBMS_CRYPTO Algorithms for more information.

  • On a system that is not an Autonomous Database you can use the DBMS_CRYPTO package with the same algorithm options and the key to decrypt the files.

    Note that the key is stored as a VARCHAR2 in the credential in Autonomous Database but DBMS_CRYPTO uses RAW type for the key parameter.

    See DBMS_CRYPTO Algorithms for more information on encryption algorithms.

Encrypt Data with a User Defined Encryption Function

Shows the steps to encrypt data using a user-defined encryption function while exporting to Cloud Object Storage.

Perform the following steps to encrypt data while exporting to Cloud Object Storage (this example exports table data to a CSV file):

  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 callback function to encrypt data.

    For example:

    CREATE OR REPLACE FUNCTION encryption_func (data IN BLOB)
  RETURN BLOB
  IS
      l_encrypted_data BLOB;
       BEGIN   
     DBMS_LOB.CREATETEMPORARY (l_encrypted_data, TRUE, DBMS_LOB.CALL);
     DBMS_CRYPTO.ENCRYPT (
         dst => l_encrypted_data,
         src => data,
         typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
         key => 'encryption key'
      );
     RETURN l_encrypted_data;
END encryption_func;
/

    This creates the ENCRYPTION_FUNC encryption function. This function encrypts data using a stream or block cipher with a user supplied key.

    Note

    You must create an encryption key to be used as a value in the KEY parameter. See DBMS_CRYPTO Operational Notes for more information on generating the encryption key.
  4. Run DBMS_CLOUD.EXPORT_DATA with the format parameter, include the encryption option and specify a user_defined_function.

    For example:

    BEGIN
      DBMS_CLOUD.EXPORT_DATA (
        credential_name => 'OBJ_STORE_CRED',
        file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/encrypted.csv',
        query           => 'SELECT * FROM ADMIN.emp',
        format          => json_object( 
                              'type' value 'csv', 
                              'encryption' value json_object('user_defined_function' value 'admin.encryption_func'))
      );
END;
/

    This encrypts the data from the specified query the on EMP table and exports the data as a CSV file on Cloud Object Storage. The format parameter with the encryption value specifies the user-defined encryption function to use to encrypt the data.

    Note

    You must have EXECUTE privilege on the encryption 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.

    See EXPORT_DATA Procedure and DBMS_CLOUD Package Format Options for EXPORT_DATA for more information.

Was this article helpful?