Data import and export is one of the most critical aspects of a modern data management system enabling seamless data transfer between different platforms, applications, and organizations. Whether you are migrating your data or integrating it with third-party systems, moving data around is quite common. However, the ingress and egress of data from your sources requires utmost care and attention since mishandling this process can lead to various security risks, including data breaches, unauthorized access, and compliance violations. One of the best practices, from a security standpoint, implemented by many of our customers is to encrypt their data at the source right after an export before they move it to Object Storage (or another external source). This, in return, means that the same data needs to be decrypted before it’s loaded into a database or integrated with an application. Today, I’m pleased to announce the built-in encryption and decryption in Autonomous Database to make this process much simpler and more efficient.

Here are the steps that we’ll follow to see this feature in action:

  • Encrypt and export data into OCI Object Storage
  • Decrypt and load data from OCI Object Storage

Encrypt and export data into OCI Object Storage

For this example, I have a table called SALES that I want export to an Object Storage bucket as an encrypted CSV file. I already configured my IAM dynamic group and policies, and enabled resource principal authentication for my Autonomous Database (ADB) to be able to access my Object Storage bucket (see my earlier blog post to learn more about how to complete these preliminary steps).

The first step is to store my encryption key as a credential object in my ADB (note that you can also store it as a secret in OCI Vault as described in our documentation and showcased in another blog post of mine):

BEGIN
  DBMS_CLOUD.create_credential(
    credential_name => 'ENCRYPTION_CRED',
    username => 'ctuzlaKey',
    password => UTL_RAW.cast_to_varchar2('********************'));
END;
/

In case you are wondering how to create your own key using DBMS_CRYPTO package, see the following example from Oracle documentation.

Now that I have my key readily available in my ADB, I can go ahead and export the SALES table to my Object Storage bucket:

BEGIN
  DBMS_CLOUD.EXPORT_DATA(
    credential_name => 'OCI$RESOURCE_PRINCIPAL',
    file_uri_list   => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/adb/b/ctuzla/o/sales_export',
    query           => 'SELECT * FROM SALES',
    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 'ENCRYPTION_CRED')));
END;
/

Let’s pause here for a moment and observe the changes that we introduced for the DBMS_CLOUD.EXPORT_DATA procedure. As part of the format options, we now have encryption which lets you encryp or decrypt your data via a user defined function or built-in DBMS_CRYPTO algorithms (i.e. block cipher algorithms, block cipher chaining modifiers, and block cipher padding modifiers) along with an encryption key stored as a credential object as shown in this example. Running this procedure will generate an encrypted CSV file in my Object Storage bucket.

Decrypt and load data from OCI Object Storage

To show how easy it is to do the reverse of what we just did, I will load the encrypted CSV file back into my ADB using the DBMS_CLOUD.COPY_DATA procedure:

BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name =>'SALES2',
    credential_name =>'OCI$RESOURCE_PRINCIPAL',
    file_uri_list =>'https://objectstorage.us-ashburn-1.oraclecloud.com/n/adb/b/ctuzla/o/sales_export_1_20240312T221908487272Z.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 'ENCRYPTION_CRED'))
  );
END;
/

As you can see, DBMS_CLOUD.COPY_DATA procedure has also been enhanced to allow the similar format options that we covered earlier. Even though this example uses the COPY_DATA procedure, the same decrypt functionality is also available for CREATE_EXTERNAL_TABLE, CREATE_EXTERNAL__PART_TABLE, CREATE_HYBRID__PART_TABLE, and COPY_COLLECTION procedures of DBMS_CLOUD.

To summarize, moving data in and out of various data sources is part of our everyday lives which demands careful handling due to the potential security risks. The introduction of built-in encryption and decryption support in the Autonomous Database for data on the move is a significant step toward simplifying and enhancing the security of this process, benefiting users with improved efficiency and data protection. Make sure to check out our documentation to learn more about this feature.