How to share tables across different CAP projects

Introduction

The purpose of this blog is to demonstrate several patterns of table sharing across different CAP projects. I’ve been using CAP for about a year, but I’m still new to HANA db, so comments and suggestions are appreciated!

Updates (2021/10/15)

• Scenario 3 and 4
  • .hdbsynonym requires only synonym name, because the actual configuration is taken from .hdbsynonymconfig.
  • use SERVICE_REPLACEMENTS in mta.yaml so that we don’t have to specify the actual service name in .hdbsynonymconfig and .hdbgrants files. – thanks to the advice by Dinu PAVITHRAN
  • it is not required to bind the reused HDI container (or user provided service) to srv module.
• Scenario4
  • use the credentials of reused HDI container for the user provided service, so no need to create a database user. – again, thanks to Dinu PAVITHRAN

Scenarios

I’ve come up with the following scenarios.
Each pale blue box represents a CAP project, and “Customers” in the left most box is the entity to be reused.

* Keep in mind that these scenarios are purely for learning purpose. There are cases where consuming OData service is more appropriate than accessing database directly.

1. A project sharing the same HDI container and namespace with the reused entity
2. A project sharing the same HDI container but using different namespace from the reused entity
3. A project using a different HDI container from the reused entity
4. A project in a different space from the reused entity

Summary

The matrix below shows an overview of required artifacts per each scenario.

In the following sections, I’ll show the settings of each project. These projects are developed using HANA Cloud trial. The entire code is available at GitHub repository below.

https://github.com/miyasuta/cap-cross-container

After you’ve completed the settings, you can access reused entity from your OData service, either with /Customers or /<mainenity>?$expand=customer endpoints.

Scenario 1

When your project is sharing the same HDI container and namespace with the reused entity, you need to:

• Annotate the reused entity with @cds.persistence.exists.

namespace master.partners;

entity Vendors {
    key ID: Integer;
    name: String;
    customer: Association to Customers
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String
}

• Use the same HDI container service instance as the reused entity. In the mta.yaml, specify resource type as org.cloudfoundry.existing-service.

resources:
 - name: master-db
   type: org.cloudfoundry.existing-service
   parameters:
     service-name: master-db   

Scenario 2

When your project is sharing the same HDI container but using different namespace from the reused entity, you need to:

• Annotate the reused entity with @cds.persistence.exists.

namespace master.orgs;

entity Plants {
    key ID: Integer;
    name: String;
    customer: Association to Customers
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String
}

• Create .hdbsynonym file to fit the reused entity’s name to your namespace.

{
    "MASTER_ORGS_CUSTOMERS": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS"
        }
    }
}

• use the same HDI container service instance as the reused entity (same as the scenario 1).

Scenario 3

When your project is using a different HDI container from the reused entity, you need to:

• First in the reused project, create roles to allow access from external containers.

*File name can be any name, only the extension matters.

MASTER_PARTNERS_EXTERNAL_ACCESS.hdbrole

{
    "role": {
        "name": "MASTER_PARTNERS_EXTERNAL_ACCESS",
        "object_privileges": [
            { 
                "name":"MASTER_PARTNERS_CUSTOMERS", 
                "type":"TABLE", 
                "privileges":[ "SELECT" ], 
                "privileges_with_grant_option":[] 
            }                          
        ]
    }
}

MASTER_PARTNERS_EXTERNAL_ACCESS_G.hdbrole

{
    "role": {
        "name": "MASTER_PARTNERS_EXTERNAL_ACCESS_G#",
        "object_privileges": [
            { 
                "name":"MASTER_PARTNERS_CUSTOMERS", 
                "type":"TABLE", 
                "privileges":[], 
                "privileges_with_grant_option":["SELECT"]
            }                          
        ]
    }
}

These files look similar, only difference being that the first one has the “privileges” for “SELECT”, and the second one has “privileges_with_grant_option” for “SELECT”. Later, the first role will be assigned to an application user of a newly created HDI container and the second role to an object owner of the same. Once you’ve made above changes, deploy the project to the Cloud Foundry.

Next steps will be performed in the “sales” project.

• Annotate the reused entity with @cds.persistence.exists.

namespace sales;

entity Orders {
    key ID: Integer;
    amount: Integer;
    customer: Association to Customers;
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String;
}

• Create .hdbsynonym file to fit the reused entity’s name to your namespace.

{
    "SALES_CUSTOMERS": {}
}

This file is almost empty, as the actual configuration is coming from .hdbsynonymconfig. This file needs to be placed under db/cfg as below. The artifacts in db/cfg are processed first in deployment time.

• Create .hdbsynonymconfig file to supply schema name.

{
    "SALES_CUSTOMERS": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS",
            "schema.configure": "master-db-hdi/schema"
        }
    }
}

Schema name will be taken from “schema” property of “master-db-hdi” service instance.

* “master-db-hdi” is an alias for the HDI container “master-db”, which will be defined in SERVICE_REPLACEMENTS in mta.yaml. Thanks to this SERVICE_REPLACEMENTS concept, we don’t have to specify actual service instance names in our development artifacts.

• Create .hdbgrants file to assign roles to HDI container users.

{
    "master-db-hdi": {
        "object_owner": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS_G#"
            ]
        },
        "application_user": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS"
            ]
        }
    }
}

“master-db-hdi” at the top points to the reused HDI container service instance, which is the grantor of these privileges.

• Add the HDI container service instance of the reused entity (master-db) to mta.yaml.

 # --------------------- SERVER MODULE ------------------------
 - name: sales-srv
 # ------------------------------------------------------------
   type: nodejs
   path: gen/srv
   parameters:
     buildpack: nodejs_buildpack
   requires:
    # Resources extracted from CAP configuration
    - name: sales-db 
   provides:
    - name: srv-api      # required by consumers of CAP services (e.g. approuter)
      properties:
        srv-url: ${default-url}

 # -------------------- SIDECAR MODULE ------------------------
 - name: sales-db-deployer
 # ------------------------------------------------------------
   type: hdb
   path: gen/db  
   parameters:
     buildpack: nodejs_buildpack
   requires:
    # 'hana' and 'xsuaa' resources extracted from CAP configuration
    - name: sales-db
      properties: 
        TARGET_CONTAINER: ~{hdi-service-name}       
    - name: master-db
      group: SERVICE_REPLACEMENTS
      properties:
        key: master-db-hdi
        service: ~{master-db-hdi}   


resources:
 # services extracted from CAP configuration
 # 'service-plan' can be configured via 'cds.requires.<name>.vcap.plan'
# ------------------------------------------------------------
 - name: sales-db
# ------------------------------------------------------------
   type: com.sap.xs.hdi-container
   parameters:
     service: hana  # or 'hanatrial' on trial landscapes
     service-plan: hdi-shared
   properties:
     hdi-service-name: ${service-name}

 - name: master-db
   type: org.cloudfoundry.existing-service
   parameters:
     service-name: master-db   
   properties:
      master-db-hdi: ${service-name}          

Here, two HDI container are used by srv module and db deployer module each. To let the deployer know which container to deploy the project’s own artifacts, property TARGET_CONTAINER is specified for sales-db.

Also, note that the alias for “master-db” is defined in the SERVICE _REPLACEMENTS section as below.

    - name: master-db
      group: SERVICE_REPLACEMENTS
      properties:
        key: master-db-hdi
        service: ~{master-db-hdi}   

Scenario 4

When your project is in a different space from the HDI container of the reused entity, you need to create a user provided service which contains a database user who has the privileges to grant access to reused entities.

For this, we will first create a service key for master-db service instance, and based on the service key we will create a user provided service.

• Create a service key for master-db.

cf create-service-key master-db grantor-key

• In the accounting project directory, dump the key into a file.

cf service-key master-db grantor-key > grantor-key.json

• Remove a few lines before the first curly bracket and make it a valid JSON file
• Remove the outmost {“credentials”: …}
• Tag the service as hana by adding the following property to JSON
  “tags”:  “hana” 

{
	"certificate": "-----BEGIN CERTIFICATE-----certificate-----END CERTIFICATE-----",
	"driver": "com.sap.db.jdbc.Driver",
	"hdi_password": "password",
	"hdi_user": "8C0524334BA3449487CF430CCCF279F4_2CPDC6Q1YEW5CA32U4OXI923P_DT",
	"host": "6f4ade47-353e-4a67-bbb0-431cba244981.hana.trial-eu10.hanacloud.ondemand.com",
	"password": "password",
	"port": "443",
	"schema": "349472F3D2FB4B6BBBF430B47451A7B2",
	"url": "jdbc:sap://6f4ade47-353e-4a67-bbb0-431cba244981.hana.trial-eu10.hanacloud.ondemand.com:443?encrypt=true\u0026validateCertificate=true\u0026currentschema=349472F3D2FB4B6BBBF430B47451A7B2",
	"user": "8C0524334BA3449487CF430CCCF279F4_2CPDC6Q1YEW5CA32U4OXI923P_RT",
	"tags": "hana"
}

• Switch to dev2 space and create a user provided service.

cf create-user-provided-service ups-master-db -p grantor-key.json

The remaining steps are similar to the scenario 3.

• Annotate the reused entity with @cds.persistence.exists

namespace accounting;

entity Invoices {
    key ID: Integer;
    amount: Integer;
    customer: Association to Customers;    
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String;
}

• Create .hdbsynonym file to fit the reused entity’s name to your namespace.

{
    "ACCOUNTING_CUSTOMERS": {}
}

• Create .hdbsynonymconfig file to supply schema name. This file should to be placed under db/cfg.

{
    "ACCOUNTING_CUSTOMERS": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS",
            "schema.configure": "cross-schema-ups/schema"
        }
    }
}

Schema name will be taken from “schema” property of “cross-schema-ups” service instance, which is the user provided service we created in the previous step.

* “cross-schema-ups” is an alias for the actual ups name.

• Create .hdbgrants file to assign roles to HDI container users.

{
    "cross-schema-ups": {
        "object_owner": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS_G#"
            ]
        },
        "application_user": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS"
            ]
        }
    }
}

• Add the user provided service instance (ups-master-db) to mta.yaml.

# --------------------- SERVER MODULE ------------------------
 - name: accounting-srv
# ------------------------------------------------------------
   type: nodejs
   path: gen/srv
   parameters:
     buildpack: nodejs_buildpack
     memory: 256M
     disk-quota: 1024M      
   requires:
    # Resources extracted from CAP configuration
    - name: accounting-db
   provides:
    - name: srv-api      # required by consumers of CAP services (e.g. approuter)
      properties:
        srv-url: ${default-url}

 # -------------------- SIDECAR MODULE ------------------------
 - name: accounting-db-deployer
 # ------------------------------------------------------------
   type: hdb
   path: gen/db  
   parameters:
     buildpack: nodejs_buildpack     
   requires:
    # 'hana' and 'xsuaa' resources extracted from CAP configuration
    - name: accounting-db
      properties: 
        TARGET_CONTAINER: ~{hdi-service-name}     
    - name: ups-master-db
      group: SERVICE_REPLACEMENTS
      properties:
        key: cross-schema-ups
        service: ~{cross-schema-ups}

resources:
 # services extracted from CAP configuration
 # 'service-plan' can be configured via 'cds.requires.<name>.vcap.plan'
# ------------------------------------------------------------
 - name: accounting-db
# ------------------------------------------------------------
   type: com.sap.xs.hdi-container
   parameters:
     service: hana  # or 'hanatrial' on trial landscapes
     service-plan: hdi-shared
   properties:
     hdi-service-name: ${service-name}      

 - name: ups-master-db
   type: org.cloudfoundry.existing-service
   parameters:
      service-name: ups-master-db   
   properties:
      cross-schema-ups: ${service-name}

Conclusion

In this blog, I explained four patterns of table sharing across different CAP projects.

• A project sharing the same HDI container and namespace with the reused entity
  • No special setting is required. Just use the same HDI container service instance as the reused entity.

• A project sharing the same HDI container but using different namespace from the reused entity
  • .hdbsynonym is required to fit the reused entity’s namespace to your namespace.

• A project using a different HDI container from the reused entity
  • .hdbsynonym is required to fit the reused entity’s namespace to your namespace.
  • .hdbsynonymconfig is required to supply schema name to the synonym.
  • .hdbgrants is required to grant HDI container users privileges to access reused entities.

• A project in a different space from the reused entity
  • A user provided service is required to enable cross-space access.
  • .hdbsynonym is required to fit the reused entity’s namespace to your namespace.
  • .hdbsynonymconfig is required to supply schema name to the synonym.
  • .hdbgrants is required to assign HDI container users privileges to access reused entities.

References

YouTube

• HANA Academy: BTP Extension Generators: CAP – Intra-HDI Container Access
• SAP Developers: Building hana-opensap-cloud-2020 Part 6: Cross Container

CAP document

• Using Native SAP HANA Artifacts

SAP Help

• Enable Access to Objects in Another HDI Container