In the previous post I introduced synonyms in XS Advanced (XSA) and created a very simple synonym. Now I will create synonyms pointing to objects I defined by myself.

Accessing Objects in a Classical Schema

I assume, that the external schema I want to access is already existing. The example schema EPM_DEV can be created by using repo https://github.com/CGilde/syn-prov-classic. I also assume, there is already a project with a db module existing in the XSA Web IDE. We will insert the synonyms into this project. A git repo for the project with the complete coding can be found at https://github.com/CGilde/syn-hdi-classic-1 .

To provide access  to objects in this schema via synonyms, the following steps have to be performed:

1. Create roles in external schema

The roles are used to control access to the objects we want to expose to the HDI container.
Technically we could also use object privileges, or allow access to the whole external schema. Both have disadvantages over using roles and I will not use them here.

In schema EPM_DEV, created by using the repo mentioned above, the roles are created using plain SQL. The roles could also be created using design-time role definitions.

Typically there will be two roles created for synonym access:

1. A role with grant option. This role is used to allow the HDI object owner access to the external objects and to grant access to other users, e.g. via roles for accessing procedures/functions/calcviews/views in definer mode. In my example this is the role “EPM_XXX::external_access_g”.

2. A role without grant option. This role is used to allow the HDI application user direct access to the synonyms or via procedures/functions/calcviews in invoker mode. In my example this is the role “EPM_XXX::external_access”.

-- Create role to be granted for external access via synonym,
-- we leave out access to SNWD_EMPLOYEES on purpose, just imagine this table contains sensitive data
create role "EPM_XXX::external_access";
--grant select on schema EPM_DEV to "EPM_XXX::external_access"; -- use this for allow access to the whole schema
grant select on SNWD_AD to "EPM_XXX::external_access";
grant select on SNWD_BPA to "EPM_XXX::external_access";
...

create role "EPM_XXX::external_access_g";
--grant select on schema EPM_DEV to "EPM_XXX::external_access_g" with grant option; -- use this for allow access to the whole schema
grant select on SNWD_AD to "EPM_XXX::external_access_g" with grant option;
grant select on SNWD_BPA to "EPM_XXX::external_access_g" with grant option;
...

2. Create a User Provided Service

The roles created above have to be granted to the generated users of the HDI container. To achieve this, we first have to create a so called “user provided service”. It connects with a given user to a given DB and executes grant statements during deployment. The grant statements are generated out of the content of .hdbgrants files, which I define later.

Logon to the XS CLI (Command Line Interface) with administrator privileges:

xs-admin-login

Change the target organization and space to those you are developing in (here space DEV, no change of organization):

xs t -s DEV

Create the user provided service. Replace host, port user, password and schema with your values (xs cups is the alias for xs create-user-provided-service):

xs cups EPM_XXX-table-grantor -p "{\"host\":\"my_host\",\"port\":\"30015\",\"user\":\"EPM_DEV\",\"password\":\"Grant_123\",\"driver\":\"com.sap.db.jdbc.Driver\",\"tags\":[\"hana\"] , \"schema\" : \"EPM_DEV\" }"

The output should look like this:

Created environment:
"EPM_XXX-table-grantor": [
  {
    "schema": "EPM_DEV",
    "password": "Grant_123",
    "driver": "com.sap.db.jdbc.Driver",
    "port": "30015",
    "host": "my_host",
    "user": "EPM_DEV",
    "tags": [ "hana" ]
  }
]

Now every service (e.g. project in XSA Web IDE) within the same organization and space can use the user provided service to obtain access to the external objects. You might think: “well, if every service can access the external objects, what about security?” The answer is simple. Services in XSA have only access to other services in their own organization and space. XSA organizations and spaces, and assignment of developers to them have to be structured in a way that supports the security requirements of your system landscape.

3. Create .hdbgrants files

To grant the privileges to the generated users, I include a file SNWD-table.hdbgrants into the db folder of my project in the Web IDE. The .hdbgrants file can be seen as the HDI equivalent of the “GRANT” statement in plain SQL. In the background, SQL “GRANT” statements are generated out of the .hdbgrants file content and executed using the user provided service  “EPM_XXX-table-grantor”  created above.

{
	"EPM_XXX-table-grantor": {
		"object_owner": { 
			"roles": [
				"EPM_XXX::external_access_g"
			]
		},
		"application_user": {
			"roles": [
				"EPM_XXX::external_access"
			]
		}
	}
}

In this hdbgrants file, “EPM_XXX-table-grantor” corresponds to the user defined service, which grants the privilege (grantor). “object_owner” and “application_user” refer to the users, to which the privileges are granted (grantees). The privileges/roles listed after “object_owner” will be granted directly to the HDI container’s object owner user. The privileges/roles listed after “application_user” will be granted to the HDI application user (aka runtime user) via a specific role (role with suffix ::access_role).

For backwards compatibility, instead of .hdbgrants also the suffix .hdbsynonymgrantor is supported.

4. Define dependencies

The dependencies of our project are defined in the development descriptor file mta.yaml. The db module requires not only a hdi-container, where the synonyms will be created in, but also the user defined service to create the grant statements.  I put some comments into the following mta.yaml file to explain, which symbols/properties refer to which development artifacts.

_schema-version: '2.0'
ID: syn-hdi-classic-1
version: 0.0.1

modules:
  - name: db
    type: hdb
    path: db
    requires:                                        # db module needs:
      - name: hdi-container                          # ...where synonyms are created
        properties:
          TARGET_CONTAINER: ~{hdi-container-service} # defined at (d1)
          
      - name: EPM_XXX-table-grantor                  #...for executing grant statement
          
resources:
  - name: hdi-container
    type: com.sap.xs.hdi-container
    properties:
      hdi-container-service: ${service-name}        # (d1) get service into variable

  - name: EPM_XXX-table-grantor
    type: org.cloudfoundry.existing-service         # service created with xs cups

5. Create Synonyms

Before creating the first synonym, I build the almost empty db module of the project. Currently (2.0 SPS0) this is necessary to use the object search dialog in the graphical synonym editor. When only the text editor is used, the empty project does not have to be build.

I include a file SNWD.hdbsynonym into the db folder of my project. Finally, I can create the synonyms, either using the graphical synonym editor or the text editor. In the graphical synonym editor I can select objects from the external services I have bound to the db module via the mta.yaml file, in our case from “EPM_XXX-table-grantor”. The search dialog offers all objects, that can be read by the external service and also some system objects that are accessible via some HDI default privileges/roles. The hdbgrants are not considered here.

SNWD.hdbsynonym in graphical editor with search dialog:

SNWD.hdbsynonym in text editor:

{
  "SNWD_AD": {
    "target": {
      "object": "SNWD_AD",
      "schema": "EPM_DEV"
    }
  } ...

The .hdbsynonym file defines the synonyms, and references a target object and schema for each synonym. Several synonyms can be defined in the same file.

I build the db module again. After successfully building, I use the HANA database explorer (or HANA runtime tools) to check the definition and content of the synonyms just created. The synonyms point to the right objects in schema EPM_DEV and show the right data:

6. Consume Synonyms

The synonyms can now be consumed in all places, where their base objects could be consumed. I created a very simple calculation view to verify, that the synonyms can be used instead of tables. Also a role for consumption of the calculation view by external users (e.g. from BI client tools) was created to verify the end-to-end use case. I will not go into further detail here, because this is not specific to synonyms. The calculation view and the role are contained in repo syn-hdi-classic-1.

Typical problems that occur when consuming synonyms are authorization problems, caused by insufficient privileges assigned to the generated HDI users or external users. In this case, your roles and hdbgrants were probably not defined as explained above (see 1. Create roles …, 3. Create .hdbgrants…). For table-synonyms, the object owner typically needs select with grant option, the application user typically needs only select privilege. You should also avoid granting more privileges than needed (e.g. giving the application user the grant option) to avoid security risks.

7. Deployment

Promoting to production can be done using some software life-cycle management tool, or via the XS CLI (Command Line Interface). I will use the XS CLI, since deployment is not the main topic of this post. After building the whole project in the Web IDE, I download the mtar file from the mta_archives folder. The mtar file has to be downloaded to a place, that is accessible from the XS CLI.
The only thing that is different to deployment of a “stand-alone-application” is, that you have to make sure that the user provided service is available in your deployment target before you deploy the application containing the synonyms. Create it if needed using “xs cups…” as described above.
Trivial deployment:

xs deploy syn-hdi-classic-1_0.0.1.mtar 

If I want or have to change the service and/or schema name, to which I deploy, I can optionally use an mta-extension. I include this example, because it is often a requirement to have a defined, readable schema name.

mta-extension file syn-hdi-classic-1.mtaext, must be accessible from XS CLI:

_schema-version: '2.0'
ID: syn-hdi-classic-1-mtaext
extends: syn-hdi-classic-1

resources:
  - name: hdi-container
    parameters:
      service-name: syn-hdi-classic-1-deploy
      config:   
        schema: SYN_HDI_CLASSIC_1

Deployment with mta extension:

xs deploy syn-hdi-classic-1_0.0.1.mtar -e syn-hdi-classic-1.mtaext

Accessing objects in another HDI Container

Again, I assume, that the HDI Container which I want to access is already existing. The example HDI container can be created by using repo https://github.com/CGilde/syn-prov-hdi. I created it using “xs deploy” with schema name EPM_DEV_HDI and service name EPM_DEV_HDI_DB.
I also assume, there is already a project with a db module existing in the XSA Web IDE. I will insert the synonyms into this project. The repo for this project can be found at https://github.com/CGilde/syn-hdi-hdi-0.

There are three typical use cases that I will consider when accessing another HDI Container:

1. Accessing an HDI Container generated from Web IDE during development (same organization/space)
   During development we sometimes want to access an HDI Container which is also still “being developed”. Then we do not want to deploy the other HDI Container first, but want to consume the one that is generated from Web IDE.
2. Accessing an HDI Container generated during deployment of an MTA archive (same organization/space)
   During deployment and sometimes during development we want to access and HDI Container which was generated during deployment.
3. Accessing an HDI Container in a different organization/space
   Same as 2., but for security or other reasons the HDI Container we want to access was deployed in a different organization/space. We will look at the differences compared to 1. and 2. at the end.

Let’s see, what the differences are between accessing objects in an HDI Container vs. accessing a classical schema. The following steps have to be performed:

1. Create roles in “external” HDI Container

The roles are defined using .hdbrole files. Again, I create two roles, one with and one without grant option, named “EPM_XXX::external_access” and “EPM_XXX::external_access_g#“.
Roles containing privileges/roles with grant option have to use “#” as their last character. Those roles can only be granted to the object owner user.
Example role EPM_XXX::external_access:

2. Getting the Service name of target HDI Container

Creating a user defined service is not necessary when accessing a container in the same organization and space. Every HDI Container can just reference the services from its organization and space. The referenced service could be either a deployed service or – during development – a generated service from Web IDE.

In the following example EPM_DEV_HDI_DB is a service create by “xs deploy…”, the other one was generated by Web IDE and both using repo https://github.com/CGilde/syn-prov-hdi:

> xs s | grep -E "EPM_DEV|syn-prov-hdi-"
GILDE-lf10figdtim2i93w-syn-prov-hdi-hdi-container       hana       hdi-shared
EPM_DEV_HDI_DB                                          hana       hdi-shared   DB

3. Create .hdbgrants files

The .hdbgrants files refer to the HDI services instead of the “user provided service”. And we have to use “container_roles” instead of “roles” within the .hdbgrants file. Container roles refer to HDI Container specific roles instead of global roles. Everything else is identical.

Example with deployed service:

{
	"EPM_DEV_HDI_DB": {
		"object_owner": { 
			"container_roles": [
				"EPM_XXX::external_access_g#"
			]...

Example generated Service from Web IDE:

4. Define dependencies

The dependencies in the mta.yaml are defined identically compared to accessing a classical schema. Just use the service names of the HDI Container you want to access.

5. Create Synonyms

This step is also identical compared to accessing a classical schema, when you know the schema names.
Use the schema name you find in the build-log of the Web IDE:

… or from a deployed application. Here you either know the schema name already (defined via mta extension), or you can get the generated schema name via XS CLI command “xs env <app_name>”, or by executing “SELECT current_schema from sys.dummy” from the DB explorer:

Example synonym pointing to Web IDE generated HDI Container:

At this point it becomes quite obvious, that we need a more flexible solution when accessing generated schema names. I will show in the next post how flexibility can be achieved.

6. Consume Synonyms

This is identical compared with accessing a classical DB schema.

7. Deployment

Deployment with hard coded schema names is identical compared with accessing a classical DB schema – but only when the schema and service names are known in advance. I will show in the next post how more flexibility can be achieved.

8. Accessing HDI Container in different organization/space

Accessing a HDI container that is running in a different organization/space is almost identical to accessing a classical schema.
The following steps have to be executed:

• create a DB user
• create global wrapper roles for the HDI generated roles (like EPM_XXX::external_access from above)
• grant the DB user those wrapper roles with grant option
• create a user provided service using the DB user
• assign the wrapper roles in the .hdbgrants files

The use of a global additional wrapper role is a workaround, since schema roles are currently not supported in .hdbgrants files and container roles can only be used when accessing HDI containers in the same organization/space.

Summary

The complete end-to-end scenario of using synonyms, pointing to an external classical schema or to another HDI container has been shown. However, schemas and services were hard coded.

A usecase could be accessing an external schema, that has always the same name.
It is obvious, that a similar functionality like schema mapping in XS classic is needed. It would also be nice to be able to change the target of synonyms at a central place, e.g. in migration scenarios.

Flexibility options will be shown in the next post.