SAP Learning Journey

Enabling Access to External Data

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Set up access to external data

External Data Access Setup

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’s another container's schema or a classic database schema.

But it's possible to enable access if it's 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 SAP 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"
 }
 }
}
Expand

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.

Cross-Container Access

When you need to access data from another HDI container, the setup is relatively similar to what you've just learned for an external (classic) database schema. Let’s point out the main differences:

  • There's no need for a user-provided service if the external container service is running in the same space as the one your project is assigned to.

    You can add the external HDI container service to your project (which automatically adds it to the mta.yaml file).

  • You must create roles inside the external container that contain the relevant privileges to all objects that could be accessed by the service.

  • The .hdbgrants file doesn't refer to database object privileges of the technical user assigned to the user-provided service, but to the dedicated roles created inside the external container (see the previous point).

Log in to track your progress & complete quizzes