Enabling Access to External Data

Objective

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, 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 referenced in any calculation view.

  • Application User:

    The application user is used to bind the HDI container to a consuming application. In the context of calculation view modeling, it is typically the user <container_schema_name>...RT who performs a number of modeling operations, including data preview on top of the HDI container connection, for example.

    The privileges granted to these two roles are generally different. In particular, the object owner always needs the authorization to grant the privileges he has (for example, to include them in a role you define inside the HDI container or a default one), whereas the application user generally does not need the right to grant the privilege he is given.

The user-provided service's user may have a lot of privileges on external objects. Technically, it would be possible to grant all the database privileges of that user to the object owner and application user. But this gives away too much access. Instead, we should grant only the 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.

Revoking Privileges and Roles

The .hdbgrants design-time files are special compared to other files types (such as calculation views, functions, or roles) because they do not materialize as database objects when you build the database module.

For this reason, if a certain privilege or role has been granted to the object owner and/or application user when you last built the .hdbgrants file, this will not be automatically reverted if you remove the privilege/role from the .hdbgrants file and build the file again.

Instead, you need to create a .hdbrevokes file, with the same structure, listing (only) the privileges and/or roles that you want to revoke. You must also remove these privileges and/or roles from the .hdbgrants. This will be effective after you build the .hdbrevokes file.

Caution

During deployment, the .hdbrevokes are processed before the .hdbgrants files, so a privilege or role listed in a .hdbgrants file will always be granted, whatever a .hdbrevokes specifies.

To avoid this complexity, a common practice is to define fine-grained authorizations to external objects in two roles (one for the object owner, another for the application user), out of the HDI container, and to grant these two roles to the user-provided service's user. Then, the .hdbgrants file only needs to reference these roles. If needed, additional privileges and/or nested roles can be granted to (or revoked from) these two roles and are then automatically granted to (or revoked from) the object owner and/or application user, without even building the local HDB module.

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
Copy code
Switch to dark mode
1234567891011121314151617181920212223242526
 {
  "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"
    }
  }
}

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 from the HDI container
  • Object: The actual object name 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