SAPUI5, ODATA and anonymous database access in the HCP: How to do it

The HANA Cloud Platform trial account is free of charge. This is possible because multiple developers work on the same databases in parallel. For that reason, additional safety measures have been implemented. This makes the application development quite tricky sometimes. A couple of weeks ago I developed an SAPUI5 application using my HANA Cloud Platform trial account. When I wanted to receive data from the database, it was not working. The process of figuring out the problem and solving it took quite some time but I came up with a solution. Jamie Wiseman from the SAP HANA Academy team encouraged me to explain my solution in the SCN, so that’s what I am going to do

The issue

My SAPUI5 Application accesses a database table containing Geodata. The standard way of doing this in SAP HANA is creating and using an ODATA service. Creating an ODATA service in SAP HANA is very easy: You simply need to create another file (file extension .odata) in your XS or SAPUI5 package and insert something like:

service {
“SCHEMA”.”TABLE” as “Geodata” keys generate local “GENERATED_ID”;
}

At first you of course should test the ODATA interface. You can do this by just calling it in your browser. The URL is like:

https://s1hanaxs.hanatrial.ondemand.com/path/to/your/package/odataservice.xsodata/ servicename?json&$select=COLUMN,COLUMN,…

Be careful when you test your service. When I did it on my laptop, I had no problems. My SAPUI5 app however seemed to be unable to access data. Using a different browser, I finally understood the issue: Normally, I have to logon with my S-User first.

    ODATA access will trigger S-USER Logon process

The other browser had the SAP Certificates for automated logon installed, so testing with it had always worked. This by the way also applies to the SAPUI5 application itself. If you want to be able to access it without logging on with your S-User, it does not work out of the box.

First problems

At first, I looked at the settings for authentication in the SAP HANA Cloud Platform Cockpit (Trust). Unfortunately, your only alternative to the authentication via S-User is setting up a connection to another trusted identity provider. I am no expert on this, but I guess it would be a lot of work and at the end also require some sort of authentication which is not supported by a standard SAPUI5 application. So this approach is a dead end.

My second try was using SQLCC which is described very well in a video from the SAP HANA Academy. For this approach, your database user needs certain privileges (role sap.hana.xs.admin.roles::SQLCCAdministrator). These privileges could be used to basically become administrator if you use them right. As the trial instances (just the trial instances) of the HANA Cloud Platform are used by multiple developers in parallel, they don’t have these privileges. Another dead end.

Anonymous access to SAPUI5 application

My first step to success was a thread on saphana.com with a solution for anonymous access very similar to the full SQLCC approach. It uses the .xsaccess file in the project. HANA development projects including ODATA services or SAPUI5 applications always need two files to work: “.xsapp” and “.xsaccess”

.xsaccess – as the name says – can contain options for the accessing the application. To enable anonymous access, you have to adjust your file like this:

{
“anonymous_connection”: “path.to.your.package::anonuser”,
“authentication” : null,
“exposed”: true
}

Then you have to create a database user using XS development. You can this by creating a file with the name “username.xssqlcc”. I used the name “anonuser” in .xsaccess, so the filename has to be “anonuser.xssqlcc”. There is not a lot of content, but you can insert a description. Just remember To have brackets in the file:

{
“description” : “Anon SQL connection”
}

Now you can access your development projects in this package without having to authenticate with your S-User. It works fine for the SAPUI5 applications. However, access to the database is still not possible. If you try to access the ODATA service like I described, you will no longer see a logon screen. Instead you won’t see anything. By using the development tools of your browser, you can examine the response from the server and see something like this:

   Accessing with an anonymous database user does not work for ODATA

Error 500 makes it clear: Using ODATA services anonymously does not work in the HANA Cloud Platform. So I found a different solution. (However: If someone knows a solution for ODATA, please tell me in the comments )

Anonymous access to SAP HANA

My next idea was to develop a database interface like odata on my own. As I was already using an XS application project in the HANA Cloud Platform, I tried to do it with XS. Remember that you have to enable anonymous access to the XS application itself before an SAPUI5 application would be able to use it. If you want to access the database out of XS, you do something like:

var conn = $.db.getConnection();
var pstmt = conn.prepareStatement( “select * from NEO_ET72UJMG1CL4IUODE1NJVKJ1O.ADACALL” );
var rs = pstmt.executeQuery();

The problem with that is the first statement. It will not work if you call it using anonymous access. Instead you will get an error code 500 like you did with ODATA. The only way around this is using SQLCC, which is not possible with an SAP HANA Cloud Platform trial account (see “First problems”).

The key to success is the JAVA development in the SAP HANA Cloud Platform. If you access the database out of a JAVA application, it does not require any authentication. This is even the default setting. Using JAVA, you can easily write a little webservice that transfers the data you need to the SAPUI5 application.

A little issue remains: I wanted to be able to create HANA Views on top of my tables. It is important to understand that the standard database schemas of JAVA and XS applications are fundamentally different. The first sign for that are different database versions:

   XS and JAVA Schemas are located on different databases

That means you not only have different schemas but actually different databases. Therefore it is not possible to access a table in a JAVA schema out of an XS application using a XS schema. Also, you have far more privileges in the XS schema. The most important one is the privilege to create packages and develop XS and SAPUI5 applications as well as HANA Views in them.

To combine the benefits of anonymous database access and HANA Views as well as XS development, I simply changed the schema binding of my JAVA webservice to the XS schema.

   Changing the schema binding combines the benefit of JAVA and XS in the HCP

You can change the schema binding of your JAVA application in the HANA Cloud Platform Cockpit. In the menu on the left side, select “JAVA Applications” and then select your application. On the left side, click on “Schmema Bindings”. Now delete the old schema binding and create a new binding to your XS schema.

As far as I can tell, there is no benefit from using the JAVA schema over the XS schema (again, please correct me if I’m, wrong). Of course you can also establish multiple bindings for one application if you like.

I hope I could help some of you to understand how anonymous access works. Please keep in mind that this issue only exists in the trial account. And please give me some feedback if you like