File Uploader on HANA Cloud Platform

Recently I’ve started dipping my toes into the Hana Cloud Platform and as an exercise I thought I’d recreate a File Upload application on which I wrote a blog a number of years ago –

Upload data to HANA table with SAPUI5 FileLoader and HANA Extended Services

In that initial demo the UI and server-side javascript code were both deployed on the HANA XS engine of an on-premise Hana platform.

In this new demo the UI will be developed and deployed as an HTML5 application on HCP and call an xsjs service on a HANA instance running on HCP. The purpose is to upload a csv file with data to a table in the HANA database.

It will use Principal Propogation to pass the logged-in cloud user to the HANA XS application. The authentication mechanism used to propogate the logged in user will be Application-to-Application SSO. This has the benefit of the HANA XS dynamically creating a new DB user based on the forwarded details.

Much of the details of Principal Propogation and the setting up of the service provider certificate can be found in the following two excellent blogs by Martin Raepple and Oliver Goetz. I will be repeating many of those steps with some slight variations e.g. the HANA Studio will not be used and everything will be done with the web-based dev tools. Also it seems the limitation in using the “Generating Key Pair” button for the HTML5 Local Service Provider certificate has been fixed in the latest revisions of HANA.

Principal Propagation between HTML5- or Java-based applications and SAP HANA XS on SAP HANA Cloud Platform

“Play It Again, SAML” – How to Set Up SAML Authentication For Your SAP HANA Cloud Platform Trial Instance

Pre-requisites

To complete the steps below you will need the following pre-requisites in place:

• A Hana Cloud Platform trial account and have created a HANA MDC database instance (see below for version of the database I am on).
• OpenSSL installed to generate the key pair for SAML Service Provider.
• Following roles assigned to the SYSTEM user

– sap.hana.security.base.roles::HANACertificateAdmin
– sap.hana.security.base.roles::HANACertificateView
– sap.hana.security.cockpit.roles::DisplayCertificateStore
– sap.hana.xs.lm.roles::Developer
– sap.hana.ide.roles::Developer

• Also add all roles containing “xs.admin”

1. Create schema and table to upload data to.

First up we’ll create a schema and table in the database to upload our data to.

Open up the catalog in the HANA Web based Development Workbench. Right click on Catalog and do New Schema.

Open SQL Console and execute the following to create the table:

create table TEST_UPLOAD.MY_TABLE
(
id int primary key,
name nvarchar(20),
description nvarchar(50)
);

2. Create the xsjs fileupload service.

Now we create our service which will take in the uploaded file, read the content and write the content to the database table.

Open the Editor in the HANA Web based Development Workbench. Right click on Content and do New -> Package.

Right click on fileupload package and do Create Application.

Right click on fileupload package and do New -> File to create FileUpload.xsjs

Add the following code to FileUpload.xsjs

function escape(v1)
{
          var v2 = v1.replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;');
          return v2;
}

$.response.contentType = "text/html";
try
{
   
    var conn = $.db.getConnection();

    var pstmt = conn.prepareStatement( "insert into TEST_UPLOAD.\"MY_TABLE\" (id, name, description) values(?,?,?)" );
    if($.request.entities.length>0){
        var file_body = $.request.entities[0].body.asString();
        var allTextLines = file_body.split(/\r\n|\n/);
                    
        var lines;
        var entries;
        var col;
           
        pstmt.setBatchSize(allTextLines.length-1);
        for (lines=0; lines<allTextLines.length-1; lines++)
        {
            entries = allTextLines[lines].split(',');
            col = entries.splice(0,allTextLines.length);
                            
            if ( col[0].length > 0 )
            {
                col[0] = escape(col[0]);
                pstmt.setString(1,col[0]);
            }
            if ( col[1].length > 0 )
            {
                col[1] = escape(col[1]);
                pstmt.setString(2,col[1]);
            }
            if ( col[2].length > 0 )
            {
                col[2] = escape(col[2]);
                pstmt.setString(3,col[2]);
            }
                              
            pstmt.addBatch();
                            
        }
        pstmt.executeBatch();
    }
    else
    {
        $.response.setBody("No Entries");
    }
    pstmt.close();
    conn.commit();
    conn.close();
    $.response.setBody("Upload successful!");
}
catch(err)
{
    $.trace.error("Error upload: "+err.message);
    
    if (pstmt !== null)
    {
        pstmt.close();
    }
    if (conn !== null)
    {
        conn.close();
    }
    $.response.setBody(err.message);
}

Open .xsaccess file and set the value for prevent_xsrf to false.

Create New -> File .xsprivileges and add the text per below

Create New -> Role and add the application privilege created in the previous step

Activate all the files.

The folder structure should look like this

3. Create the SAPUI5 application.

Now create our SAPUI5 frontend application.

Open the SAP Web IDE.

File -> New -> Project from Template

Open the FileUploadView.xml file and add the following code

<mvc:View controllerName="pm.com.demo.fileupload.ui.controller.FileUploadView" xmlns:html="http://www.w3.org/1999/xhtml"
	xmlns:mvc="sap.ui.core.mvc" displayBlock="true" xmlns="sap.m" xmlns:unified="sap.ui.unified">
	<App>
		<pages><Page title="{i18n>title}">
				<content>
				    <unified:FileUploader id="FileUploader" uploadUrl="/pm/com/demo/fileupload" uploadComplete="onUploadComplete">
				    </unified:FileUploader>
				    <Button text="Upload" width="100px" id="__button1" press="onUploadPress"/>	
				</content>
			</Page>
		</pages>
	</App>
</mvc:View>

Open the FileUploadView.controller.js file and add the following code

sap.ui.define([
	"sap/ui/core/mvc/Controller"
], function(Controller) {
	"use strict";

	return Controller.extend("pm.com.demo.fileupload.ui.controller.FileUploadView", {

		/**
		*@memberOf pm.controller.Upload
		*/
		onUploadPress: function () {
			//This code was generated by the layout editor.
			var fileLoader = sap.ui.getCore().byId("__xmlview0--FileUploader");
			fileLoader.upload();
		},
		/**
		*@memberOf pm.controller.Upload
		*/
		onUploadComplete: function (oEvent) {
			//This code was generated by the layout editor.
			jQuery.sap.require("sap.ui.commons.MessageBox");
		    var sResponse = oEvent.getParameter("response");
		    sap.ui.commons.MessageBox.show(sResponse, sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
		}
		
	});
});

Open neo-app.json and add a new path as follows

{
      "path": "/pm/com/demo/fileupload",
      "target": {
        "type": "destination",
        "name": "xsfileupload",
        "entryPath": "/pm/com/demo/"
      },
      "description": "HANA XS FileUpload backend service"
    }

The project structure should look like the below

Deploy the application to the HANA Cloud Platform. Right click on the FileUploadUI folder and do Deploy -> Deploy to SAP HANA Cloud Platform.

4. Create PSE, assign for SAML and register the service provider Certificate in the HANA MDC instance.

In OpenSSL create a new certificate and key

openssl req -x509 -sha256 -newkey rsa:2048 -keyout certificate.key -out certificate.crt -days          1024 -nodes   -subj ‘/CN=pm.trust.no.one’

In the HANA Workbench Catalog open a SQL Console. When setting the certificate use the values generated in certificate.crt and certificate.key.

CREATE PSE TrustMe;

SET PSE TrustMe PURPOSE SAML;

ALTER PSE TrustMe SET OWN CERTIFICATE '-----BEGIN CERTIFICATE-----
MIIDMjCCAhqgAwIBAgIJAJLRWfjKJO52MA0GCSqGSIb3DQEBCwUAMBoxGDAWBgNV
..............................................
QRsu8wtTvDbo83ijcydy560WUDVG6aQkEXAEXaVKGXA8wKn4BuXk1J7gRYNSaR+v
5o/qL4Ju
-----END CERTIFICATE-----
-----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEAsmFE98RhnpoqluG79+TiiYveFZI696K8y+JYAc7hBahXf7VG
..........................................
+p7T2swIFjO75pn7zHRd72kVo6BEwXNSxuq/M8Ui/HlgKlxkV7pjTg==
-----END RSA PRIVATE KEY-----'

Examine table SYS.P_PSES_ and you should see the newly created PSE.

SELECT * FROM SYS.P_PSES_;

Examine table SYS.P_CERTIFICATES_ and you should see the newly created Certificate.

SELECT * FROM SYS.P_CERTIFICATES_;

Now take a look at table SYS.PSE_CERTIFICATES to see how the PSE and Certificate is linked

SELECT * FROM SYS.PSE_CERTIFICATES;

5. Update the Service Provider Configuration

Go to the XS admin tool at the following path –

https://<mdcname><account name>.hanatrial.ondemand.com/sap/hana/xs/admin/#/samlSP

Click Edit and update the org info (as appropriate) for your Service Provider

Ensure the Default Role is set to PUBLIC

6. Configure the Local Service Provider for HTML5 apps

In the HANA Cloud Platform Cockpit, under Security click on Trust to go to the Trust Management screen.

Click on Edit and change Configuration Type to Custom. Click Generate Key Pair to generate key and certificate.

After generating the key and certificate click on “Get Metadata”. Save the generated Metadata XML file.

Save and then change the Configuration Type back to Default and Save again.

Click on the Trusted identify Provider. The SAP ID Service is the identity provider when the Default configuration type is selected.

7. Set up trust in HANA XS to the HTML5 Local Service Provider

Go to the XS admin tool at the following path –

https://<mdcname><account name>.hanatrial.ondemand.com/sap/hana/xs/admin/#/samlSP

Go to SAML Identity Provider and click the add (+) button. In the Metadata section paste the Metadata XML generated from step 6. Click Save. Make sure the Dynamic User Creation checkbox is checked and enter dummy values /saml2/sso for the SingleSignOn URLs.

Open the SQL Console in HANA and you should see an entry in _SYS_XS.HTTP_DESTINATIONS for the above

SELECT * FROM _SYS_XS.HTTP_DESTINATIONS;

8. Create certificate in HANA for the generated HCP Local Provider certificate

Open SQL Console in HANA and execute the following statement taking the certificate value from above

CREATE CERTIFICATE FROM 
'-----BEGIN CERTIFICATE-----
MIIDdTCCAl2gAwIBAgIEcXOI9DANBgkqhkiG9w0BAQUFADBrMQswCQYDVQQGEwJERTEPMA0GA1UEChMGU0FQI
...............................................
52qCvIyjRgCP3ZzQQFDizgooS5qo48spH41HoIg==
-----END CERTIFICATE-----'

Verify that the certificate has been created successfully

SELECT * FROM SYS.P_CERTIFICATES_;

Now add the certificate to the PSE

ALTER PSE TrustMe ADD CERTIFICATE 160675;

Verfiy that it has been added successfully

SELECT * FROM SYS.PSE_CERTIFICATES;

9. Setup Destination for the xsfileupload service in HCP

In HCP, under Connectivity, select Destinations and click New Destination.

The URL should be the full url of the file upload xsjs service e.g.
https://<mdc_name><account_name>.hanatrial.ondemand.com/pm/com/demo/fileupload/FileUpload.xsjs

Add additional property “saml2_audience” with value as per the name identifier in the SAML Service Provider.
Specify AppToAppSSO authentication.

10. Setup the default role for dynamically created users in XS.

In the HANA Web-based Development Workbench open the Security window. Right click on Roles and do New Role. Set name to DEFAULT_ROLE_FOR_FILE_UPLOAD and add the previously created role pm.com.demo.fileupload::FileUpload.

Also add the TEST_UPLOAD.MY_TABLE table to the role.

Now open SQL Console again and execute the following statement to update the “defaultrole” configuration property.

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') 
SET ('saml', 'defaultrole') = 'DEFAULT_ROLE_FOR_FILE_UPLOAD';

11. Configure SAML for the XS Service

Go to
https://<mdcname><account name>.hanatrial.ondemand.com/sap/hana/xs/admin/#/package/pm.com.demo.fileupload

Select SAML and choose the HANA identity provider from the list

12. Test the application

I created some test data and saved in a file called uploadtest.csv. We will upload this data to the database using our new upload tool.

In the HCP Cockpit go to the HTML5 applications and select the fileuploadui. You should see a link to the application in the Active Version section.

Copy the link and open in a private browser so that you will be prompted to enter your user credentials.
You should be redirected to the HANA Cloud log on page

https://accounts.sap.com/saml2/idp/sso/accounts.sap.com

Enter credentials and click Log On. You should now be brought to the File Upload UI.

Click Browse and select the data file that you want to upload.

Click Upload

Now open a SQL Console and verify the data

select * from test_upload.my_table;

Success!

Now open the Security section of the workbench and expand the Users list. You should see a new user has been created with the same ID as your HCP ID and the default role assigned.

Done.