Purpose
I found accessing tables in HDI container using SAP BAS is a bit of tricky and have been juggling of how to use the form editor for synonym. Here is a tip for someone who might have been in the same dilemma as I have, or for who don’t want to spend time on investigating and heading around in trying to find the reason as I did.
Environment
A table (“city”) & the data have been generated in an external data source – Data Lake whose connection is already established on HANA Data Explore (Please refer to other blog if you need to link external data sources to HANA ). I created a schema ‘booking’ in my HANA cloud.
Note : You need to login HANA Data Explore as an admin user with the privilege of DB admin to manage DB objects. The way of connecting external resources on SAP Web IDE might be a bit different and eventually SAP Web IDE would fade out and to be replaced with BAS.
A table ‘city’ in remote data source ‘BLLAKE’ created as
Then mounted the table ‘city’ onto my HANA DB BLHANA as a virtual table under the schema ‘booking’
I also created two tables called ‘AIRPLANE’ & ‘FLINFO’ as below in my HANA DB directly, under the same schema ‘booking’
You should be able to notice the virtual table ‘city’ referring to the external Data Lake has a green triangle icon on the bottom.
I imported data into them by using import function on HANA Database Explorer… which are like
Okay ….. now go to BTP Cockpit
In my BTP cockpit, I have 2 spaces defined under my subaccount, one for development, the other for test.
note: Database links for HANA & Data Lake are setup in space ‘dev’ but ‘test’ shares the link which makes developers able to access DBs from space ‘test’. intended to separated this project from my other projects
Defining Access and role
This is very important part in order to access classic schemas from the HDI container in BAS. go to SAP Database Explorer again
Create an end user ‘BKUSER’
CREATE USER BKUSR PASSWORD “Password1” NO FORCE_FIRST_PASSWORD_CHANGE ;
Then need to create a role which you can do it in SAP DE, alternatively, you can also make the role in DB admin cockpit. Assign the privileges which required to manage and to access data in the tables under schema ‘BOOKING’
CREATE ROLE BOOKING_CROLE;
grant SELECT, UPDATE, INSERT, DELETE, EXECUTE, SELECT METADATA ON SCHEMA “BOOKING” TO BOOKING_CROLE with grant option;
Now grant the role to the end user with admin option,
grant BOOKING_CROLE to BKUSR with admin option;
Now technical users need to get the role granted as well.
Go to your BTP cockpit and define a User-Provided HDI instance. It is “booking_external_access” on the space “test” in my case.
Of course, you need to add a piece of Jason code to identify the end user (actually, application user) ‘BKUSER’. The password is ‘Password1’ as many guys used it for test purpose. This password was set for user BKUSR when creating the user.
Also don’t forget to add some snip in your yaml file in your project folder, I created a module ‘fl’ under project ‘booking’ as below.
Goto BAS and create a project and application, add some code snips (of “booking_external_access” in my case ) in your yaml file to get your project to access your User-Provided HDI instance.
Create a .hdbgrants file as below. “booking crole” is the role that I created and assigned it to the end user “BKUSR” at SAP Database explorer. So object owners, that is, technical users will have the same privilege for the schema “BOOKING” as “BKUSR”
{
“booking_external_access”: {
“object_owner” : {
“roles” : [“BOOKING_CROLE” ]
},
“application_user” : {
“roles” : [“BOOKING_CROLE” ]
}
}
}
Deploy it
Finally! Let’s Define Synonyms
Here is the tricky part, SAP BAS provides the auto generated form editor for most of DB artifacts in according to the plugins registered in the config file. right? I figured that the form editor for some artifacts is not working as it should be. Unfortunately, hdbsynonym is one of them.
As you can see there is a table “TICKETINFO” under the schema “BOOKING” exists in HANA
However, the searching function on the form editor couldn’t find the table “until you define a synonym for the table in your HDI container” like below
So it seems we need to stick to the old-fashion code editor in creating synonyms for now even after you move from WEB IDE to BAS.
The reason why I explain it with the long details from about creating tables, assigning privileges, and defining synonyms is because I had been confused and misled by the thought that I did something wrong. such as granting my privilege, role assignment, sort of things.
No comments:
Post a Comment