Defining Roles

Objective

After completing this lesson, you will be able to create a design-time role to secure data access

Design-Time Roles

Security Concepts

Before we cover roles, there are some important concepts that you must understand.

A database user can be the owner of database objects. A role is a collection of privileges and can be granted to a user or another role. A privilege enables specific operations on one or several objects.

In SAP HANA Cloud, we define roles and users, and assign privileges. Privileges can be assigned to users directly. They can also be assigned to users indirectly by using roles.

Roles help you to structure access using reusable business-related roles. Roles can be nested, enabling the implementation of a hierarchy of roles.

It's highly recommended that you manage authorizations for users by using roles. Assigning a privilege directly to a user isn't a good practice and creates a lot of maintenance.

Some key point regarding security concepts of SAP HANA Cloud:

  • All the privileges granted directly or indirectly to a user are combined.

    Whenever a user tries to access an object, the system performs an authorization check based on the user's roles and directly allocated privileges (if any).

  • It isn't possible to explicitly deny privileges. All privileges grant access.

    The system doesn't need to check all the users roles. As soon as all the privileges required for a specific operation on a specific object have been found, the system ends the check and allows the operation without checking if the same privileges appear again in another role.

  • Several predefined roles exist in the SAP HANA Cloud database.

    Some of them are templates (and must be customized), and others can be used as they are.

Defining Roles

In the SAP HANA Cloud database, there are two ways to create roles:

  • As pure runtime objects (with no source file) that are created using SQL or SAP HANA cockpit. These objects are called Catalog Roles. You assign privileges to these roles using SQL grant statements.

  • By means of source files that you create in the HDB module of a project. These are called Design-Time Roles, and the source file describes the privileges that are immediately granted when the role is deployed.

The design-time files used to create roles must have the extension .hdbrole in order to be recognized as design-time role files.

Note

Each role must be defined in its own .hdbrole design-time file.

It isn't possible to create several roles within the same .hdbrole file.

The role ID (including a valid namespace if applicable) must be unique in the HDB module, as for any other object (calculation view, synonym, and so on).

Types of Privileges in a Design-Time Role

The .hdbroleconfig File

The .hdbrole file can't contain references to real schema names, but only logical references to schemas that are resolved in another type of design-time file: the .hdbroleconfig file.

The purpose of the.hdbroleconfig file is to maintain the actual name of the external schemas in a dedicated file, instead of having many occurrences of the schema names in the .hdbrole files themselves. It makes the maintenance of a project easier when you're able to maintain the references to external schemas in just one place.

An .hdbrole file can contain a reference, such as Ref1. A .hdbroleconfig file defines the corresponding schema for Ref1. For purpose and details, refer to the surrounding text.

You can create the .hdbroleconfig file manually and then specify this file when you create your .hdbrole file. Or you can generate the .hdbroleconfig file automatically from within the .hdbrole editor and then optionally adjust the generated file if necessary.

Using the Role Editor

The .hdbrole and .hdbroleconfig files can be created as text files or you can use the Role Editor.

By default, when you create a new .hdbrole file, the Role Editor opens.

The following video shows how the Role Editor can be used to generate the corresponding files.

Enabling Access to an External Schema

When your application requires access to an external schema, an administrator must define a dedicated user-provided service. A technical user is assigned to this service, and brings its own authorizations to the database objects.

As part of the security implementation in your project, it's necessary to define which of these authorizations will be granted to the different roles. For example, some users might need insert/update/delete privileges on a particular set of tables, when other users only need select privileges.

Note

When creating calculation views, the main authorization you need is a SELECT privilege on the data sources.

The .hdbgrants File

A dedicated file, with extension .hdbgrants, is used to define the set of authorizations that will be given to two specific users, the Object Owner and the Application User.

In a project, only one .hdbgrants file is allowed. The object_owner section lists privileges granted to the Object Owner. The application_user section lists privileges granted to the application user. For details, refer to the surrounding text.

The <filename>.hdbgrants file is structured into three levels:

  • The name of the user-provided service

  • The users to whom the privileges are granted

    There are two possible values for users:

    • object_owner is the technical user that owns all the objects of the container schema.

    • application_user represent the users who are bound to the application modules.

  • The set of privileges granted

    The syntax of this third level is very similar to the syntax of what you find in a .hdbrole file.

Note

A single .hdbgrants file can list authorizations from more than one user-provided services.

It's essential to give the application_user a correct set of authorizations. For example, if a SELECT privilege on an external table is granted to the object_owner, this will allow the creation of a synonym for this table. But if the same SELECT privilege isn't granted to the application_user, you won’t be able to display the content of the target table.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn