OData Service in SAPUI5 Application on HANA Cloud Platform

[stefan_heitzer]

Hi everybody,

I'm pretty new to SAP HANA Cloud Platform and have a big problem with my application. I wrote a SAPUI5 application which is currently running on HANA Cloud Platform. The app has a normal database schema. I created via a database-tunnel a connection to the schema and created a database and filled it with data. What I wanna do now is to connect my SAPUI5 application with this database table. I want to receive the data as a odata service but this doen't work. I read something about creating a .xsodata file which has the following structure:

service namespace "myNamespace" {

  "SCHEMANAME"."TABLENAME"

      as "TABLE_ALIAS";

}

The SCHEMANAME is exactly the name of the schema how it is named on SAP HANA Cloud Platform. The table name is the name how the table is named in the schema.

Just for your information... the .xsodata file is inside a folder in my SAPUI5 application. When I create the oData Model I call the url of the application -> .xsodata.

Doesn't work at all... Can somebody help me solving this issue?

Looking forward to your answers!

Greetings

[stefan_heitzer · Accepted solution]

Hi,

what have you exactly done? I have my custom schema "NEO_XXXXX..." and in this is my table. Afterwards I created a repository and checked this out. Then I created a normal XS-Project. In this XS-Project there are the following files:

- .xsapp --> no content

- .xsaccess

{
"exposed" : true
}

- .xsprivileges

{ "privileges" :
       [ { "name" : "Basic", "description" : "Basic usage privilege" } ]}

- .odata.xsodata

service{
    "NEO_XXXXX..."."TABLE_NAME";
}

- model_access.hdbrole

role pMYUSERtriall.MYPACKAGE::model_access {
    catalog schema "NEO_XXXX...": SELECT, UPDATE, INSERT, DELETE;
}

- application_access.hdbrole

role pMYUSERtrial.MYPACKAGE::application_access {
    application privilege: pMYUSERtrial.MYPACKAGE::Basic;
}

After activating these objects, calling the SQL Statement you wrote and calling my odata.xsodata service I get again

Service exception: insufficient privilege.

What have I made wrong? ...

Looking forward to your help.

Greetings

Stef

[stefan_heitzer · Accepted solution]

Hi everybody,

here is a small status update. This afternoon I tried the followin sql calls:

call "HCP"."HCP_GRANT_ROLE_TO_USER"('pMYUSERtrial.MYPACKAGE::model_access', 'pMYUSER')

and

call "HCP"."HCP_GRANT_ACTIVATED_ROLES"

Both were not successful ... I also tried to use a schema which is a standard schema in SAP HANA XS (EPMSTANDARD....) also not successfull ... I really hope that there is a solution 😕

Greetings

[Former Member · Accepted solution]

Hi Stefan,

I had this same problem and the solution for me was to add a line to the hdbrole that grants access to the tables that the odata service is exposing. You'll need something like this:

role pYourUser.package.sub-package::model_access {

     sql object pYourUser.package.sub-package.folder::yourtablename: INSERT, SELECT;

}

For example,

role p12345trial.myproject.helloworld::model_access {

     sql object p12345trial.myproject.helloworld.data::mytable: INSERT, SELECT;

}

Assuming you follow the practice of defining your hdbtables in a separate data folder.

Hope this helps,

Tom

[stefan_heitzer · Accepted solution]

Hi Tom

thanks for your reply but your solution doesen't work either ... When I create this line and say afterwards

call "HCP"."HCP_GRANT_ACTIVATED_ROLES"

I get the following error:

insufficient privilege exception: insufficient privilege: Can only grant privileges for objects owned by _SYS_REPO

Again as information: I work with the SAP HANA Cloud Platform TRIAL

Greetings

Stef

[Former Member · Accepted solution]

Hi Stef,

Sorry that didn't help - I do understand you're using the Cloud trial, I am also. I'll write down here all the steps I went through to get this kind of thing working and hopefully something will provide the missing clue!

So, firstly I have a HANA Package created under my user called "helloworld". In HANA Studio I created a project called "sample". I'll add a couple of pics so you can see the structure. One thing I learned here is that you need to make sure that when you add the project to the repository workspace, you add it as a new sub-package - I found that if I didn't then my roles didn't work, no matter what I did.

The .xsaccess file contains the following content:

{

          "exposed": true

}

The model_access.hdbrole contains the following content:

role p1940235347trial.helloworld.sample::model_access {

          sql object p1940235347trial.helloworld.sample.data::mytable: SELECT,INSERT;

}

The mytable.hdbtable contains the following content:

table.schemaName = "NEO_DJZV7AXQ43AJUP6QXT8O87BVH";

table.tableType = COLUMNSTORE;

table.description = "My Table";

table.columns = [

          {name = "id"; sqlType = NVARCHAR; nullable = false; length=10;},

          {name = "name"; sqlType = NVARCHAR; nullable = false; length=30;}

];

table.primaryKey.pkcolumns = ["id"];

The samples.xsodata contains the following content:

service {

          "NEO_DJZV7AXQ43AJUP6QXT8O87BVH"."p1940235347trial.helloworld.sample.data::mytable" as "Samples";

}

By way of proof, if I just create this and activate it and then go to the HCP cockpit to try to run I get the following error... which is what you originally reported.

I then use the SQL console in hdbstudio and run the following statement...

call "HCP"."HCP_GRANT_ROLE_TO_USER"('p1940235347trial.helloworld.sample::model_access','P1940235347')

Then when I run it again it runs successfully. (albeit with no data because i didn't add any to the table).

Maybe if you can reproduce these steps it will work for you. I did this on a brand-new Could trial account with a separate email address from my primary S-User so it should work if you follow the same steps.

Let me know.

Kind Regards,

Tom

[stefan_heitzer · Accepted solution]

Hi Tom,

IT WORKS!!!

You even helped me finding the error ...

One thing I learned here is that you need to make sure that when you add the project to the repository workspace, you add it as a new sub-package - I found that if I didn't then my roles didn't work, no matter what I did.

This was exactly the thing which I haven't done ...

I'm so happy right now!!! Thanks a lot!

Greetings

[Former Member · Accepted solution]

Hi Stef,

I'm glad that worked. Good luck with your developments. 🙂

Regards,

Tom

[Former Member · Accepted solution]

I'm not able to locate model_access

[mark_hwang · Accepted solution]

Hi Stef and Tom,

I am having the same problem and I tried your approach but am still getting the same error.  I created a sub-package named testodata and I have the files created there as you suggested.  I was able to grant the role eventually as shown in the attached screenshot.  But the error still shows in the second screenshot.  Any idea?

[Former Member · Accepted solution]

I tried different things and it is working now.  I think the reason was I did not have a privileges file defined.  Once I did that and added the application privilege to the role file it worked!

[Former Member]

This message was moderated.

[Former Member]

HI,

I have followed all steps mentioned above to create odata service on hcp. i have also executed both sql commands which executed successfully but if i try to run odata file then it gives page not found error on hcp,

please help me to resolve this issue.

[Former Member]

Hi Kelly,

Have you executed call "HCP"."HCP_GRANT_SELECT_ON_ACTIVATED_OBJECTS"; query?

Also we have an online web based tutorial to guide with simple application creation on hana xs trial on HCP

regards,

Ankur

[Former Member]

HI,

I have followed all steps mentioned above to create odata service on hcp. i have also executed both sql commands which executed successfully but if i try to run odata file then it gives page not found error on hcp,

please help me to resolve this issue.

[stefan_heitzer]

Hi,

here is a small status update! I made it now to call the .xsodata file. I don't have a problem with calling the .xsodata/ and also not with calling .xsodata/$metadata

But when I try the following: .xsodata/DETAIL I receive the following message:

Service exception: insufficient privilege.

I also tried then to build up a .hdbrole file with the following content:

role pMYUSERtrial.MYPACKAGE::schema_role {
    catalog sql object "NEO_MYSCHEMA"."MYTABLE" :           SELECT,UPDATE,INSERT,DELETE;
}

After activating all the files I try to call the following:

call "HCP"."HCP_GRANT_ROLE_TO_USER"('pMYUSERtrial.MYPACKAGE::schema_role', 'MYUSER');

But I can't because I get an error saying "insufficient privileges" ... By the way ... I work with the SAP HANA Cloud Platform Trial

I really hope that somebody can help me solving this problem...

Greetings

[kostyah]

I have the same issues here. It seems like our users are not allowed to see exposed OData services they created, or am I wrong? I have tried dozens of combinations to grand privileges, but no luck.

Even though it is trial, I find it pretty much sad.

Hope to hear any advices!

Thanks, Kostia

[kostyah]

Works now!

So, my odata service is using a table from NEO_ schema(given by HCP), my service description is pretty much like yours and then I create a role with the following content:

role p123456789trial.demo::model_access {
          catalog schema "NEO_ANY38XP9ZA52GR83Z7R8OMZKD": SELECT, UPDATE, INSERT, DELETE;
}

then I activate this role and run the following SQL query:

call "HCP"."HCP_GRANT_ACTIVATED_ROLES"

now it should expose your service by address like this:

services/businessPartners.xsodata/BusinessPartners?$format=json

really hope it helps.

[fralarsen]

This message was moderated.