End to End Securing Data in Oracle Integration Cloud (OIC) with OCI Vault: AES for Encryption & Decryption & RSA for Signing and verification | Implement message-level encryption in Oracle Integration using OCI Vault

Scenario:

A company is integrating Oracle Integration Cloud (OIC) with an external financial system to exchange sensitive payroll data securely. The external system encrypts and signs the data before sending it to OIC. OIC must decrypt and verify the data, process it, then encrypt and sign the response before sending it back.

Solution Approach

1. Incoming Data: Decrypt & Verify in OIC

1. The source system encrypts the data using AES-256 (symmetric encryption) and signs it using an RSA private key.
2. OIC receives the encrypted and signed payload.
3. OIC retrieves the AES decryption key and RSA public key from OCI Vault.
4. OIC decrypts the data using AES and verifies the signature using RSA.

2. Outgoing Data: Encrypt & Sign in OIC

1. After processing the data, OIC encrypts the response using AES-256.
2. OIC signs the encrypted data using an RSA private key.
3. The response is sent back to the source system.
4. The source system retrieves the AES key to decrypt the data and verifies the RSA signature using OIC’s public key.

Implementation in OIC with OCI Vault

1. Key Management in OCI Vault

• Store the AES encryption/decryption key for data security.
• Store RSA key pairs (private for signing, public for verification).
• Use OCI Vault’s Key Management APIs to securely fetch keys.

2. OIC Integration Implementation

• Configure REST connection to OCI Vault for key retrieval.
• Use AES encryption/decryption logic in OIC.
• Implement RSA signing and verification in OIC using the retrieved keys.

Detailed steps with screenshots:

Here we will create two common service which will perform the following:

1. Encryption service: It will perform encrypt and signing.
2. Decryption service: It will perform verify and decrypt.

Rest APIs used:

Encrypt: post /20180608/encrypt

Decrypt: post /20180608/decrypt

Sign: post /20180608/sign

Verify: post /20180608/verify

Rest Connection:

Encrypt and signing:

Integration flow:

Rest trigger:

Encrypt:

Signing:

Decrypt and Verify:

Integration flow

Rest Trigger

Verify:

Decrypt:

Reference:

https://docs.oracle.com/en/solutions/oic-message-level-encryption/index.html

https://docs.oracle.com/en-us/iaas/api/#/en/key/release/EncryptedData/Encrypt

Signing vs Verification keys

When it comes to signing and verifying, the distinction lies in how public and private keys are used in asymmetric cryptography (like RSA or ECC). Here's the breakdown:

Signing

Purpose: To prove the authenticity of the data and the identity of the signer.

 Key Used: Private Key.

Explanation: When you "sign" something (like a document or message), you use your private key to create a signature. This ensures that only you, the holder of the private key, could have signed it, guaranteeing the data's integrity and the signer's identity.

Verifying

Purpose: To confirm that the data hasn't been altered and was indeed signed by the entity claiming to have signed it.

Key Used: Public Key.

Explanation: When you "verify" a signature, you use the public key of the signer to check the signature's validity. The public key allows anyone to verify that the signature matches the signed data, but it doesn't let anyone create a sig nature themselves.

Where to Use Each Key:

 Private Key: Used when signing. It should be kept secure and never shared, as anyone with access to the private key could sign data as though they were you.

Public Key: Used when verifying a signature. This key is shared publicly, allowing others to confirm the authenticity of the signed data without compromising security.

In summary:

1. Sign with your private key.
2. Verify with the public key.

Mutual Signing and Verification Between Two Parties (A & B)

1. A → B (Signed Message)

A signs the message using A’s private key.

B verifies the message using A’s public key.

2. B → A (Signed Response)

B signs the response using B’s private key.

A verifies the response using B’s public key.

OIC - Extract Microsoft 365 Outlook Email Attachments and upload them to OCI Object storage

Use Case:

A client has a requirement to automate the processing of email attachments received in their Microsoft 365 Outlook inbox. Currently, they manually download the attachments from emails and upload them to Oracle Cloud Infrastructure (OCI) Object Storage for archival and further processing. This process is time-consuming and prone to errors.

The client needs a solution where the attachments are automatically extracted from specific emails (based on criteria like sender, subject, or date) and uploaded to a designated OCI Object Storage bucket. This ensures seamless and timely processing of files while reducing manual effort and improving efficiency.

Design steps:

We need to follow these steps to implement the solution:

1. Configure the Mailbox: Set up the mailbox and create a connection in OIC using the Microsoft Office 365 Outlook adapter.

2. Set Up Object Storage Connection: Establish a connection to OCI Object Storage in OIC.
3. Design a Scheduled Integration: Create a scheduled integration in OIC and implement the following steps:
1. Fetch emails from the configured mailbox.
2. Read the email messages.
3. Extract the attachments from the emails.
4. Upload the extracted attachments to OCI Object Storage.

Integration flow:

Steps involved in integration flow:

Step1: Fetch the email messages from the configured mailbox

Select method: Get Messages

Step2: Loop over the fetched Email messages and check for email messages having attachments

Step3: Fetch the email attachments using the method: "Get an Attachment Collection" of the MS outlook adapter.

Map the message id template parameter to fetch the attachments of the email message.

Step4: Loop over the fetched attachments and upload each attachment to object storage bucket using the PUT operation of the Rest API.

Step5: Map the input content file and object name. Decode to stream reference using decodeBase64ToReference() function. Bucket name, namespace name can be passed from a lookup. 

Configure Microsoft office 365 outlook adapter connection in OIC:

Create a connection to the Object Storage

Either use the REST endpoint using the Object Storage Service API — https://docs.oracle.com/en-us/iaas/api/#/en/objectstorage/20160918/ 

you can follow my blog

https://soalicious.blogspot.com/2022/08/oic-how-to-use-oci-object-storage-from.html

or configure your Oracle Integration instance using the steps in this link — https://docs.oracle.com/en/cloud/paas/application-integration/integrations-user/add-actions-app-driven-orchestration-integration.html#GUID-822226B0-B8EB-42E0-B053-8D844D2F45DB to access Object Storage using OCI Object Storage Action.

For mailbox setup follow this below Medium post: It has involved two steps:

1. Create a microsoft office 365 outlook account with custom domain
2. Register the application in Azure to create client Id and client secret.

https://lavanya-siliveri.medium.com/oracle-integration-gen3-configure-microsoft-office-365-outlook-adapter-624ca6a63a24

OIC - how can I use XSLT functions to remove leading zeros from numeric and alphanumeric fields?

To remove leading zeros from an numeric field in Oracle Integration Cloud (OIC) using XSLT, you can Use number() Function

The number() function automatically converts a string with leading zeros into a numeric value, effectively removing the leading zeros.

<xsl:value-of select="number(input_element)" />

Example:

Input: "000123"

Output: 123

To remove leading zeros from an alphanumeric string in Oracle Integration Cloud (OIC) using XSLT, you can use the replace() function in combination with regular expressions. Here's how you can achieve it:

Explanation:

1. Input Element: Replace your_input_element with the XPath to the input value.

2. Regular Expression:

^0+ matches one or more zeros (0) at the start (^) of the string.

3. Replace Function: The replace() function removes the matched zeros by replacing them with an empty string ('').

Input Example:

If the input is 00123ABC, the result will be 123ABC.

Xslt code:

<xsl:template match="/">

    <result>

        <xsl:value-of select="replace(your_input_element, '^0+', '')"/>

    </result>

</xsl:template>

OIC - Splitting Fixed-Length File Based on batch header Terminal Numbers into 2 separate files using xslt mapping.

Use Case: 

OIC - Splitting Fixed-Length File Based on batch header Terminal Numbers into 2 separate files using xslt mapping.

In integration workflows, processing fixed-length files is a common requirement. A typical fixed-length file might contain hierarchical data structured as:

• 1. File Header: Represents metadata for the file.
• 2. Batch Header: Denotes the start of a batch, including terminal-specific identifiers (e.g., 001 or 002).
• 3. Detail Records: Contains individual transaction or data entries for each batch.
• 4. Batch Trailer: Marks the end of a batch.
• 5. File Trailer: Marks the end of the entire file.

Problem Statement:

Given a fixed-length file structured as above, the requirement is:

Identify Batch Headers containing specific terminal numbers (e.g., 001, 002).

Split the file into separate outputs based on these terminal numbers.

Transform each split batch into a target file format for further processing.

Example Input File:

File Header  

Batch Header (001 Terminal)  

Detail  

Detail  

Batch Trailer  

Batch Header (002 Terminal)  

Detail  

Detail  

Batch Trailer  

File Trailer

Expected Output:

File 1: Contains data related to 001 terminal.

Batch Header (001 Terminal)  

Detail  

Detail  

Batch Trailer  

File 2: Contains data related to 002 terminal.

Batch Header (002 Terminal)  

Detail  

Detail  

Batch Trailer  

Solution Overview:

1. File Parsing: Read the the fixed-length file as csv sample file. 

2. Get batch header position: identify the positions of Batch Headers with terminal numbers 001 and 002.

3. Splitting Logic: Extract data between Batch Header and Batch Trailer for each terminal number 001 and 002 respectively using the positions fetched in step2.

4. Read splited fixed length files: using nxsd, read the files.

3. Transformation: Convert the split content into the desired target file format (e.g., XML or JSON).

4. Output Generation: Write the transformed content into separate output files.

This solution ensures modular processing of hierarchical data, enabling seamless integration into downstream systems.

Xslt code Used for getting the batch header position for 001 and 002:

<xsl:template match="/" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xml:id="id_11">

    <nstrgmpr:Write xml:id="id_12">

        <ns28:RecordSet>

            <xsl:variable name="CircleKPosition">

                <xsl:for-each select="$ReadSourceFile/nsmpr2:ReadResponse/ns26:RecordSet/ns26:Record" xml:id="id_48">

                    <xsl:choose>

                        <xsl:when test="contains(ns26:Data, &quot;RH&quot;) and (substring(ns26:Data, 23, 3) = &quot;001&quot;)">

                            <xsl:value-of select="position()" />

                        </xsl:when>

                    </xsl:choose>

                </xsl:for-each>

            </xsl:variable>

            <xsl:variable name="VangoPosition">

                <xsl:for-each select="$ReadSourceFile/nsmpr2:ReadResponse/ns26:RecordSet/ns26:Record" xml:id="id_48">

                    <xsl:choose>

                        <xsl:when test="contains(ns26:Data, &quot;RH&quot;) and (substring(ns26:Data, 23, 3) = &quot;002&quot;)">

                            <xsl:value-of select="position()" />

                        </xsl:when>

                    </xsl:choose>

                </xsl:for-each>

            </xsl:variable>

            <ns28:Record>

                <ns28:CircleK>

                    <xsl:value-of select="$CircleKPosition" />

                </ns28:CircleK>

                <ns28:Vango>

                    <xsl:value-of select="$VangoPosition" />

                </ns28:Vango>

            </ns28:Record>

        </ns28:RecordSet>

    </nstrgmpr:Write>

</xsl:template>

Xslt code for spliting for 001 file : same way we have to do for 002.

<xsl:template match="/" xml:id="id_175">

    <nstrgmpr:Write xml:id="id_17">

        <ns31:RecordSet xml:id="id_56">

            <xsl:choose xml:id="id_59">

                <xsl:when test="number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:CircleK) &lt; number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:Vango)" xml:id="id_60">

                    <xsl:for-each select="$ReadSourceFile/nsmpr2:ReadResponse/ns28:RecordSet/ns28:Record[position() &lt; number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:Vango)]" xml:id="id_61">

                        <ns31:Data>

                            <xsl:value-of select="ns28:Data" />

                        </ns31:Data>

                    </xsl:for-each>

                </xsl:when>

                <xsl:when test="number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:CircleK) &gt; number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:Vango)" xml:id="id_62">

                    <xsl:for-each select="$ReadSourceFile/nsmpr2:ReadResponse/ns28:RecordSet/ns28:Record[position() &gt;= number($WriteBatchHeaderPositions_REQUEST/nsmpr3:Write/ns32:RecordSet/ns32:Record/ns32:CircleK)]" xml:id="id_63">

                        <ns31:Data>

                            <xsl:value-of select="ns28:Data" />

                        </ns31:Data>

                    </xsl:for-each>

                </xsl:when>

            </xsl:choose>

        </ns31:RecordSet>

    </nstrgmpr:Write>

</xsl:template>

Screenhots:

For getting batch header positions

For splitting the content.

Eee