Enabling Access to External Data

The SAP HANA Deployment Infrastructure (HDI) relies on a strong isolation of containers and a schema-free definition of all the modeling artifacts.

By default, a container has full access to all of the database objects it contains (tables, calculation views, and so on), but has no access at all to other database schemas, whether it is another container's schema or a classic database schema.

But it is possible to enable access if it is required.

Defining Access to an External Schema – End-to-End Scenario

Let’s consider the end-to-end setup of an access to an external schema.

Create the User Provided Service

To enable access to external schema you need a user-provided service. This can be created in the Business Application Studio.

You simply choose the name of the service and a user id and password that has privileges to all external objects that you wish to access through the service. These privileges in turn will be granted by this service user, to the container's technical user and application users using a .hdbgrants file.

Hint

After you create the service, open the mta.yaml file and notice how the user-provided service has been automatically added as a resource assigned to your HDB module.

Watch this video to learn how to prepare to setup a User-Provided Service.

Watch this video to learn how to define the User-Provided Service.

Create the HDBGRANTS file

Before we describe the .hdbgrants file, we need to describe two types of user that are referenced in the file.

Object Owner - when a database artifact is deployed to a database container, it is owned by the technical user of the container and not the developer who created the source file. Only the object owner can alter and drop the object. During deployment, the object owner must have privileges to access any external objects that have been referencing in a calculation view.
Application User - Any 'real' (not technical) user (for example, a reporting user) who accesses the calculation view must have privileges to also access the external objects that are referenced. These privileges might be different (probably more restricted) than the object owner privileges.

The user-provided service's user has all privileges required to access all external objects. Technically, it would be possible to grant all the database privileges of the service user to the object owner and application user. But this gives away too much access. Instead, we should grant only the required privileges that are needed - in other words, a subset of the service user's privileges. For example, if the service users has access to an entire schema, the object owner and application user might only need access to a few tables or views in that schema. Remember, the service user might be shared with other development projects with different privilege requirements.

We define the privileges required by the object owner and application user using a special file with the suffix .hdbgrants. In that file we specify the name of the user provided service and the privileges that should be granted to both the object owner and application user. When this file is deployed, the privileges are granted.

Watch this video to learn how to create the HDBGRANTS file.

Creating Synonyms

The final step in the setup of external schema access, is to create synonyms that point to the target objects of the external schema. The synonyms declaration is done in a .hdbsynonym file. This file type can be edited either with the text editor, as in the example below, or a dedicated synonym editor.

Example of a Synonym Source File

Code snippet

 {
  "HA300::SALES_DATA": {
    "target": {
      "object": "SALES_DATA",
      "schema": "TRAINING"
    }
  },
  "HA300::SALES_DATA_NEW": {
    "target": {
      "object": "SALES_DATA_NEW",
      "schema": "TRAINING"
    }
  },
  "HA300::HANA_SEARCH_DATA": {
    "target": {
      "object": "HANA_SEARCH_DATA",
      "schema": "TRAINING"
    }
  },
  "HA300::CUSTOMER": {
    "target": {
      "object": "CUSTOMER",
      "schema": "TRAINING"
    }
  }
}

Copy code

When defining a synonym there are three key parameters:

Name of synonym - you will refer to this name whenever you need to access the target object
Object - the actual object in the target schema, such as a table name
Schema - where the target object is found.

Watch this video to learn how to define Synonyms and Test Access.