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

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 AI 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 AI Database from Object Storage:

1. Connect to your Autonomous AI Database instance.

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        => '[e-mail]',
        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.

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/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 AI Database from Object Storage:

1. Connect to your Autonomous AI Database instance.

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        => '[e-mail]',
        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.

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;
    BEGIN
      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 => 'encryption key'
        );
      RETURN l_decrypted_data;
    END decryption_callback;
    /
   

   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.