SAP HANA Cloud

How to share tables across different CAP projects

webadmin

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!

Scenarios

I’ve come up with the following scenarios.

SAP Marketing Cloud Implementation Certification Preparation Guide

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": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS",
            "schema": "_PLACEHOLDER_"
        }
    }
}

In this case, you need to specify schema, because the target object resides in a different schema. But where does “_PLACEHOLDER_” come from?

To supply schema name, we need one more file: .hdbsynonymconfig. This files 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/schema"
        }
    }
}

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

  • Create .hdbgrants file to assign roles to HDI container users.
{
    "master-db": {
        "object_owner": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS_G#"
            ]
        },
        "application_user": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS"
            ]
        }
    }
}

“master-db” at the top is 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
    - name: master-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
   properties:
     TARGET_CONTAINER: sales-db   
   requires:
    # 'hana' and 'xsuaa' resources extracted from CAP configuration
    - name: sales-db
    - name: master-db 


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

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.

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. The image below shows the required user and role assignments to the user.

  • Go to Database Explorer with DBADMIN user and open SQL console.
  • Check role names and corresponding schema name. To do so, execute the following statement. ‘MASTER_PARTNERS%’ is part of the role name we created in the scenario 3.
select * from roles where role_name like 'MASTER_PARTNERS%';

As a result, you’ll get role names together with their schema name.

  • Create a new database user and assign above roles with admin option. With this, this user can grant ‘MASTER_PARTNERS_EXTERNAL_ACESS_xx’ roles to other container users.
create user UPS_GRANTOR password "UpsGrntr01" no FORCE_FIRST_PASSWORD_CHANGE;

grant "<schema_name>"."MASTER_PARTNERS_EXTERNAL_ACCESS_G#" to UPS_GRANTOR with admin option;
grant "<schema_name>"."MASTER_PARTNERS_EXTERNAL_ACCESS" to UPS_GRANTOR with admin option;
  • Now we’ve got a grantor user, create a user provided service in dev2 space. Execute the following cf command in your terminal.
cf create-user-provided-service cross-schema-master-partners -p '{ \"user\": \"UPS_GRANTOR\", \"password\": \"UpsGrntr01\", \"driver\": \"com.sap.db.jdbc.Driver\", \"tags\": [\"hana\"], \"schema\": \"<schema_name>\" }'

* How you supply parameters differs depending on your machine. To check it, execute cf create-user-provided-service -h. Here I’m using Windows PowerShell pattern.

Linux/Mac:
      cf.exe create-user-provided-service my-db-mine -p '{"username":"admin","password":"pa55woRD"}'

   Windows Command Line:
      cf.exe create-user-provided-service my-db-mine -p "{\"username\":\"admin\",\"password\":\"pa55woRD\"}"

   Windows PowerShell:
      cf.exe create-user-provided-service my-db-mine -p '{\"username\":\"admin\",\"password\":\"pa55woRD\"}'

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": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS",
            "schema": "_PLACEHOLDER_"
        }
    }
}
  • 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-master-partners/schema"
        }
    }
}

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

  • Create .hdbgrants file to assign roles to HDI container users.
{
    "cross-schema-master-partners": {
        "object_owner": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS_G#"
            ]
        },
        "application_user": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS"
            ]
        }
    }
}
  • Add the user provided service instance (cross-container-master-partners) to mta.yaml.
# --------------------- SERVER MODULE ------------------------
 - name: accounting-srv
# ------------------------------------------------------------
   type: nodejs
   path: gen/srv
   parameters:
     buildpack: nodejs_buildpack
   requires:
    # Resources extracted from CAP configuration
    - name: accounting-db
    - name: cross-schema-master-partners #add
   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
    - name: cross-schema-master-partners #add


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: cross-schema-master-partners
   type: org.cloudfoundry.existing-service
   parameters:
      service-name: cross-schema-master-partners

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.

Leave a Reply

Your email address will not be published. Required fields are marked *