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.
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,'&').replace(/</g,'<').replace(/>/g,'>');
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.
Nicely done! Thanks for taking the time to write this up. Would love to find the time to try this.
Thanks a lot for the content. I see some of the images are not showing up. Appreciate a fix?
Hi Sridhar, they show up fine for me. Maybe a browser issue on your end?
Hi Peter,
I've followed this blog upto end, but I'm getting "
" message while run the application. Please help me out from this issue.
Thanks
Raja B
Hi Raja, not sure on this. Can you check your config for the service provider - step 5?
Hi Murphy,
I have created the same with Different SAP ID, on this I’m getting Information like
After this message I checked in trace, there the message shown like
Check above images, and please help me…..
And we are in urgent to get through this issue, it would be grateful if you guide us to solve this issue by your valuable guidance.
It would be great if you personally help us. braja2593@gmail.com
Looking forward for your guidance.
Thanks & Regards,
Raja. B
Hello,
my application "does not want to open" in Fiori Lauchpad or it is completely empty in Web (below printscreen). I have done everything as you have. Can you help me with that problem?
Best regards,
Emilia Olszak
Hi Peter,
Thanks for this detailed post. After completing all the steps, the fileuploader page is not showing anything(Browse/upload option page). Can you please let me know what I might have done wrong. Thanks.
Hello Mate,
Thanks for the timely post, at least for me. I have encore time wondered about the transition, but somehow did not dare, because the information I came across very controversial.
But today, after reading your article I’m very confident to take up any challenges.
I have Hana 2.0 Platform and Lumira 1.31 Desktop and Server. I want to create a restricted user on <a href="https://asha24.com/blog/sap-hana-certification-guide">SAP S/4 Hana</a>
database which can use lumira Desktop to access limited calculation views via analytical privileges. My success is that the user can access the Lumira view (through BI Platform Lumira Server) and access the data for view which were made by developers. But the user cannot create their own Live Hana Data Acquisition Lumira Documents using Lumira Desktop and their restricted user login.
My question is then, what role or privileges do i need to assign to allow them this access?
By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
Please keep providing such valuable information.
Thank you,
Kevin
Hi Peter,
Thanks a trillion mate!
We have a problem migration our client’s date to SAP S/4 Hana system.
We are trying to upload around 50.000 BP data from legacy system to SAP with external batch input software(same as LSMW) .So we create flat files, intermediate files and upload it to batch input software. We have two problems:
① Process is very slow (it took us 3 days to upload 50.000 data)
②Each BP data contains a lot of bank details, but the system is only migrating maximum 4 banks ( Bank details section has 4 spots and for one to register more than 4, they have to scroll down, and we are not being able to stimulate that process in our software)
As a solution, we are thinking of using BAPI. Is there anyone of who had similar problems during data migration, if yes, how did you resolve them. Also if there is anyone who has experience with BAPI for BP, please let me know how you did.
It works like charm, saved a lot of energy & time.
By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
Please keep providing such valuable information.
</a>
Thank you,
Irene Hynes