XSA Accessing Remote Sources & External Objects (Schemas, etc)

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

Role & User

For Simplicity we have combined the classic database privileges into a single role “GRANT_REMOTE_SOURCES”.

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

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.

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

.hdbgrants

We now need to pass on the role “GRANT_REMOTE_SOURCES” that we defined above to our HDI Container. This is done using 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.

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.