Oracle Integration Cloud Service (ICS), an industry-leading iPaaS offering, provides rich orchestration and mapping capabilities over FTP connections. ICS FTP adapter supports connections over secure or non-secure channels. Files can be transferred and processed by ICS without validating content upon transfer, which is the default behavior. Files transferred over secured FTP benefit from inherent integrity checks built into the SFTP protocol. For more information on ICS FTP adapter, see the link to a guide in references section.
Some users might have advanced requirements to validate content of the files during transfer or check for integrity of file explicitly. In this article, let’s take a look at steps to enable content validation against a schema during transfer, to perform integrity checks using MD5 checksum and to implement PGP encryption to secure files over secure or plain FTP connections.
Note that code archives provided with this article do not come with warranty from Oracle. Use discretion and additional testing to reuse code for production. Instructions in this article are applicable to release 17.1.3 of ICS.
This article is split into three sections, in order to keep them easier to follow. Click on one of the titles below to jump to a section.
Some integration use cases might require that the data file’s integrity be verified after transfer and before further processing. This could be achieved by storing MD5 checksum of the original content, transfer it along with the file, compute checksum after transfer and compare with the checksum provided along with the file. If they match, then content has not been altered or corrupt during or after transfer.
In the sample orchestration, a data file and a file containing its MD5 checksum are in a .zip file. ICS computes checksum upon unpacking files into ICS. The checksum computed at source is compared with the checksum of the data file provided with file listing. If they match, orchestration proceeds with further processing.
FTP connections in ICS allow definition of a schema for content being transferred. The content could be plain text and comma separated. By default, FTP adapter reads the content and parses the records into structure defined by the schema, but does not validate content. For example, a column in the file could be specified as date, but the value could contain an invalid date string, something like ‘2017-12-32’. This condition could be handled within the orchestration, to reject the record with invalid date.
Optionally, a tweak to the integration archive could apply validation upon transfer and reject the file with invalid content. Note that such validation will be resource intensive for large files and might not be suitable for all scenarios. Instructions below are applicable to ICS orchestrations.
In order to enable schema validation, XML schema needs to be tweaked to add validation constructs. For XML payload, edit the existing schema to add these constructs as attributes to the "schema element"
nxsd:validateNxsd="true" nxsd:validation="true" nxsd:fieldValidation="true"
.Set the namespace nxsd to "http://xmlns.oracle.com/pcbpel/nxsd". A sample XSD schema element looks like this:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="https://www.w3schools.com" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns="https://www.w3schools.com" elementFormDefault="qualified" nxsd:validateNxsd="true" nxsd:validation="true" nxsd:fieldValidation="true" >
For non-XML content, there are couple of steps.
Create a temporary "MAP DATA" integration. We'll use to generate schema and download it, so this integration will not be saved.
Add a FTP connection as trigger. Select settings as shown.
Select a csv file with sample data and make sure to set appropriate data type for each field, as shown below. By default, the data type i set to "string".
In the next page, a link to schema is shown. Click on the limk to open schema, copy the contents and save it in a .xsd file. We're done with generating desired schema, so cancel the activity and discard the integration.
Now modify the generated schema to include nxsd constructs and use it in any orchestration that reads files with conforming content over FTP.
PGP encryption is the best way to secure content transferred via FTP, if higher level of security than SFTP protocol is desired. Let’s look at enabling PGP encryption via ICS FTP adapter using a sample orchestration.
First, let’s quickly cover the basics of PGP encryption, which is performed using key pairs from the sending and receiving parties. Each party shares the public key to the other. Content encrypted using the public key can only be decrypted by the associated private key, which is a secret known only to the possessing party. Each key pair is associated with an email address name or both, of a recipient. Files are encrypted by specifying the recipient, whose public key is already imported.
There are plenty of free utilities that help generate key pairs and to encrypt or decrypt content, OpenPGP and GPG are the most popular. For the sample orchestration, GPG was used to generate key pair. Here are some useful commands for Linux.
To generate key pair:
To export public and private keys of a key pair:
gpg --export -a "Test.User@invaliddomain.com" > public.key gpg --export-secret-key -a "Test.User@invaliddomain.com" > private.key
To encrypt a file:
gpg --verbose -e -r "Test.User@invaliddomain.com" datafile.zip
In the sample orchestration, PGP-related settings are configured in the connection page. Public key , Private key along with password, cipher (typically AES256) are relevant inputs for configuration. PGP configuration used for sample orchestration is shown here.
The sample orchestration decrypts a file, named datafile.encrypted, upon transfer of the file to a local ICS folder and lists contents of ICS folder. Orchestration fails if the decryption fails. Here is the orchestration.
This article explored ways to check the integrity of files transferred over FTP connections and to enable validation of content being transferred. The solutions implemented in the 3 sample orchestrations can be used individually or combined for a more robust solution. Note that the instructions are specific to release 17.1.3.
Download artifacts at this link Artifacts
ICS FTP adapter: https://docs.oracle.com/en/cloud/paas/integration-cloud-service/icsft/using-ftp-adapter.pdf
PGP tools: http://openpgp.org/ , https://gnupg.org/