Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Ian_Henry
Product and Topic Expert
Product and Topic Expert
11,967
When developing with XSA and the WebIDE you will likely need to access existing database objects, schemas, tables, remote sources or other objects from an HDI Container.  This configuration has been captured before by Christophe Gilde, but the process has evolved with the latest feature release of the WebIDE (4.3.63 for HANA 2 SPS3).

Tenant Database Objects


1. Role & User



XSA Artificats


2. User-Provided Service
3. mta.yaml
4. .hdbgrants
5. .hdbsynonym




 

1. Role & User


For Simplicity we have combined the classic database privileges into a single role "GRANT_REMOTE_SOURCES".  We could also grant access to specific privileges as described here Enable Access to Objects in a Classic Schema
CREATE ROLE GRANT_REMOTE_SOURCES;

GRANT SELECT, EXECUTE ON SCHEMA FAKENEWS TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT ROLE ADMIN TO GRANT_REMOTE_SOURCES;

DROP USER GRANTOR_SERVICE;
CREATE USER GRANTOR_SERVICE PASSWORD NotMyPassword123 NO FORCE_FIRST_PASSWORD_CHANGE;
ALTER USER GRANTOR_SERVICE DISABLE PASSWORD LIFETIME;

GRANT GRANT_REMOTE_SOURCES TO GRANTOR_SERVICE WITH ADMIN OPTION;

We can check in HANA Studio that these permission are as expected.







Now that we have a user with the role assigned we can switch to our XSA developement

XSA Artificats


2. User-Defined Service


We can now create the user defined service with either WebIDE, XSA Cockpit or XS command line.

In the WebIDE we need a project



We need associate the project with the correct space can then build the db unit of this.
Now we can add/create our User-Defined Service



If we haven't already created the service we can do this here.

Beware, the port is that of your tenant database, the default would be 30015, but I have multiple tenants so my port is 30041.


3. mta.yaml


By adding this service in the WebIDE it will automatically update the mta.yaml file, which is a good thing.  The mta.yaml hold the resources that our project requires.  This now references our user-provided service.



An alternative way to create the user-provided service is with the xs command line.  Make sure you are in the correct xs SPACE, here mine is PROD
xs t -s PROD
xs cups grantor-service -p '{"host":"mo-3fda111e5.mo.sap.corp","port":"30015","user":"GRANTOR_SERVICE","password":"NotMyPassword123","driver":"com.sap.db.jdbc.Driver", "tags":["hana"]}'
xs service grantor-service

You can still use the WebIDE, but now you would tick the box "use existing service" and you would only need to enter the service name.

Now when I build the db module again it will create a binding for this service to the di-builder

We can see (and create/edit) this in the XSA Cockpit


 

4. .hdbgrants


We now need to pass on the role "GRANT_REMOTE_SOURCES" that we defined above to our HDI Container.  This is done by creating an .hdbgrants file within your project src directory.
{
"grantor-service": {
"object_owner": {
"roles": [
"GRANT_REMOTE_SOURCES"
]
},
"application_user": {
"roles": [
"GRANT_REMOTE_SOURCES"
]
}
}
}

We should now build the db module of the project, all being well we will now have access to our existing database objects, in my case Remote Source and the FAKENEWS schema and tables.

5. .hdbsynonym


If we create a Calc View and search for a table from existing the schema we need to click the "External Services" drop down and then our grantor-service. This will then automatically create the required synonyms for us.



 
43 Comments