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 -
https://blogs.sap.com/2013/09/02/upload-data-to-hana-table-with-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.
https://blogs.sap.com/2016/03/21/principal-propagation-between-html5-and-sap-hana-xs-on-sap-hana-clo...
https://blogs.sap.com/2016/02/01/play-it-again-saml-how-to-set-up-saml-authentication-for-your-sap-h...
Pre-requisites
To complete the steps below you will need the following pre-requisites in place:
- 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
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:
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
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
Open the FileUploadView.controller.js file and add the following code
Open neo-app.json and add a new path as follows
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
In the HANA Workbench Catalog open a SQL Console. When setting the certificate use the values generated in certificate.crt and certificate.key.
Examine table SYS.P_PSES_ and you should see the newly created PSE.
Examine table SYS.P_CERTIFICATES_ and you should see the newly created Certificate.
Now take a look at table SYS.PSE_CERTIFICATES to see how the PSE and Certificate is linked
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
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
Verify that the certificate has been created successfully
Now add the certificate to the PSE
Verfiy that it has been added successfully
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.
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
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.
https://blogs.sap.com/2013/09/02/upload-data-to-hana-table-with-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.
https://blogs.sap.com/2016/03/21/principal-propagation-between-html5-and-sap-hana-xs-on-sap-hana-clo...
https://blogs.sap.com/2016/02/01/play-it-again-saml-how-to-set-up-saml-authentication-for-your-sap-h...
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.
- SAP Managed Tags:
10 Comments
