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.