Requirement

Use Oracle Apex to download files and play audio files on a NetApp Storagegrid that is secured by credentials in private subnets not available to Apex.

Why did they engage Corvus IT

This was part of a larger build of an application to access legacy data from an obsolete system that was no longer used, but its data had not been converted to the replacement system.

The client met with Corvus IT, stated that this would likely be a challenge, but as we had been delivering other improvements elsewhere, there was faith that we could find a solution. We did.

The Challenge

1. The files were not in the database, they were on a NetApp Storagegrid (an on-premises object storage appliance) that has an AWS S3 compatible API layer. There was in excess of 120TB of files/objects.
2. The Apex Oracle database did not have access to the NetApp Server and this was not allowed to change for security isolation reasons.
3. All access to files had to be audited and access was determined by the user's role in their Corporate AD.
4. There was no data linking all the records back to the database records.
5. Access to the NetApp was secured with different credentials per storage bucket. There were 2 buckets: Test & Production.
6. Users were to be able to choose a file and download it through the Apex application.
7. Some of the files were audio files, they were to be playable within the application if the user has permission to access them.

The Solution

1. Building References to the Files. We used Python with the AWS boto3 library to build csv files of the file references including the file name, file type, full folder reference and its size and directory path segments that would help associating with the data in the database. As there were in excess of 30M records we split this up by logical partitions. We then used Oracle SQL Loader to load into the database and build the various links so that users could find references to the files.
2. Secure Access of Files. We built a custom application server that received authorised requests from Apex using encrypted web credentials. If the credentials were correct, then the NetApp credentials which were encrypted in memory is passed to the boto library for generation of a pre-signed URL that had a special address that was allowed by the network team. In addition, we built a timer-based cache that automatically cleansed any item older than a parameterised 'keep-alive' time so that multiple requests for the same file did not result in multiple pre-signed URL calls and was significantly faster.
3. Apex downloading files. When a user found the file they wanted, they had all the metadata already about the file, if they wanted to download it, they clicked a link that opened a new modal. That modal obtained a pre-signed URL for the file and dynamically added that as a link for a download that can be accessed by clicking a button on the new modal. This initiated the download and closed the modal.
4. Apex playing files. In a similar fashion, the user clicks a 'Play' link which opens a modal containing a HTML5 audio control that has been dynamically updated with the pre-signed URL. The user can play, pause, rewind the file as would be done for a normal file including having visibility of the audio file length before commencement of play.

As well as making files available to play or download, an audit record is also generated capturing all details. The custom Application server was accessed from Apex using web services with encrypted web credentials and other measures were added to ensure the webservice call could not be spoofed, thereby creating a very secure solution.

The Outcomes

The client is able to access files in an isolated and secured object store without having to bloat a database or an expensive file system. It allows complete separation of Apex to the files it is interacting with. To meet other requirements of this project, we were able to extend this concept by zipping files on the NetApp as well as deleting them once authorised and validated.

What started as an intimidating requirement resulted in a robust solution that is rewarding to see in action. We used Apex native features to help meet this requirement and it is a testament to the flexibility of Apex to be able to cope with such an unusual requirement.  

?