Using SAP HANA XS APIs to create design time roles (*.hdbrole)

HANA is really a great product but from an operations point of view it’s not that handy yet compared to other SAP Solutions. As an HANA Admin you have to run SQL statements, console commands and other manual actions to operate the system and gain information for further analysis or support.

Within the authorization area we often face the problem to generate HANA roles based on dynamic generated views, changing schemata or some other triggers. This is e.g. the case when you transport from dev to qual system and the schema for one particular table is changing (erp_dev_schema.tab_a -> erp_test_schema.tab_a).

As of now, we cover that challenge with HANA procedures and generate sql roles (Create role, GRANT SELECT, EXECUTE…) in each system if it is required.

The recommendation of SAP is to use design time objects whenever possible. This is of course possible with manual effort but there is no chance to generate e.g Design Time roles by an procedure within HANA until now. This was at least my assumption.

I was working the last couple of weeks on HCP topics and used their REST API. Step-wise I became a real fan of that Restful service APIs flying around. Nevertheless, during a debugging session on HANA I ended up on a file in the package “/sap/hana/xs/dt/base/metadata”. I did what I’m always doing when stuck during analysis, i feed Google with “/sap/hana/xs/dt/base/metadata” as keyword. Guess what was the result! Yes, the first results included the word API. Due to my positive experience in the HCP area with the API’s i was sensitive on that key word. it was a bit like surfing through Wikipedia. You start reading a post on some personal vita and you end up hours later on spacemen or something wired.

At the end of the day I created, deleted and moved files on the fly by changing URL and parameter. This will definitively not solve my initial problem with the generation of roles but I have at least an idea how to automate this in the future.

Get your HANA ready

Since HANA SPS09 a new powerful REST API is available for HANA. Find Details on SAP HANA SPS 09: New Developer Features; REST API to get a feeling what is possible with that powerful API.

The HTTP REST API for SAP HANA extends the Orion server and client APIs. For more information visit the Eclipse Orion APIs.

The information provided here describes the Orion REST API in the context of SAP HANA. You can also use the official documentation from the SAP Help,

Lets get the hands dirty do some fancy calls against HANA.

In my last blog I set up an package structure for sfligh which can be downloaded on GitHub. In this blog we wanna create include a new role within the sfligh.auth package granting privilege on the schema SAP_HANA_DA.

Before we start, ensure that you have the relevant developer privileges assigned to your user. You need at least full package privileges for the sfligh package.

For testing the REST API you can use any kind of REST client. Get requests can easily be done by using your internet browser only.

The File API

Let’s navigate through the HANA repository and find the folder where we plan to create the new role. In case you’re wondering what the hell are the curly brackets for, e.g. {xsa_host}, this are simple variables used by my REST client. You can replace them by you hostname.

• Generate X-CSRF-Token (GET)    ->      https://{{xsa_host}}/sap/hana/xs/dt/base/server/csrf.xsjs

• List Package Structure (GET)         ->      https://{{xsa_host}}/sap/hana/xs/dt/base/file/?depth=1

• Navigate through the packages

You can navigate further down by selecting the Name / Value combination from the Jason Object Location or ChildrenLocation. The only difference is that the Location path will guide you to the sflight root package and the childrenLocation will list you all objects within the sflight root location. Just try it an play a bit around. You will see, that navigating through the package is as simple as breathing ????

So let’s move to the sfligh.auth package and list the content insinde. Run the GET request https://{{xsa_host}}/sap/hana/xs/dt/base/file/sfligh/auth/?depth=1 to retrieve the list of all objects in the package.

Btw, once received you can could also go one level deeper by clicking on the Location Value of an artifact to see it’s content. That’s really nice!

Ok, finally we arrived in the sfligh.auth package and can start to create the role.

Lets keep it simple and create a role which provides access the schema SAP_HANA_DA and assign the existing analytic privilege AP_SQL_CARRID

You have to maintain the information on different places before shooting the PUT request back to HANA.

1. maintain the file name in the URL, in our case it is sflight.hdbrole
   1. https://{{xsa_host}}/sap/hana/xs/dt/base/file/sfligh/auth/sflight.hdbrole
2. Pass the Parameter x-csrf-token and the value with the header
3. Maintain the file content in the Request Body

The complete URL should look similar to that one,

     https://{{xsa_host}}/sap/hana/xs/dt/base/file/sfligh/auth/sflight.hdbrole?x-csrf-token={{csrf}}

The body actually includes the hdbrole content. You can copy any existing role definition from HANA or by using the file content retrieved in the steps described earlier.

role sfligh.auth::sflight {

// This role is created by the HANA XS Basis REST API

  catalog schema “SAP_HANA_DA”: SELECT;

  catalog analytic privilege: “sfligh.auth/AP_SQL_CARRID”;

}

The complete call should look like this one.

Let’s check the Repository via the Web IDE.

Yeah, lucky you! Role is created and active. Wasn’t that hard, isn’t it? You can also Delete artifacts by changing from PUT to DELETE.

This was just a simple example how to utilize the File API. You can of course to much more fancy stuff with that. Just play around maybe you’ll find an option how to make use of the API. I’m thinking of an small XS Self Service Application which generates a predefined Package Structure with some template roles. This is often a painful exercise and can be easily automated with that shown approach.

Btw. here you’ll find a list of Design-Time Application Artifacts available for HANA SPS12,

https://help.sap.com/saphelp_hanaplatform/helpdata/en/d9/299101c244465e9431532edf06557d/frameset.htm

Have fun with the API!