Describing SAP HANA Privileges and Roles

Objectives

After completing this lesson, you will be able to:

  • Identify the different privileges types
  • Describe roles

Privileges

When a user accesses the SAP HANA database using a client interface (for example, ODBC, JDBC, or HTTP), their ability to perform database operations on database objects is determined by the privileges that they have been granted.

Object Privileges

Object privileges allow access to and modification of database objects, such as tables and views. Depending on the object type, different actions can be authorized (for example, SELECT, CREATE ANY, ALTER, DROP, and so on).

For each SQL statement type (for example, SELECT, UPDATE, or CALL), a corresponding object privilege exists. If a user wants to execute a particular statement on a simple database object (for example, a table), they must have the corresponding object privilege for either the actual object itself, or the schema in which the object is located. This is because the schema is an object type that contains other objects. A user who has object privileges for a schema automatically has the same privileges for all objects currently in the schema and for any objects created there in the future.

You can grant object privileges for database catalog objects such as tables, views, and procedures. You can also grant object privileges for non-catalog objects such as development objects in the repository of the SAP HANA database.

Object Privilege Activities

Object privilege activities also include the following:

  • CREATE ANY

    This privilege allows the creation of all kinds of objects, in particular, tables, views, sequences, synonyms, SQL script functions, or database procedures in a schema. You can only grant this privilege on a schema.

  • ALL PRIVILEGES

    This is a collection of all Data Definition Language (DDL) and Data Manipulation Language (DML) privileges that the grantor currently has and is allowed to grant, and that can be granted on this particular object. This collection is evaluated dynamically for the given grantor and object. ALL PRIVILEGES is applicable to a schema, a table, a view, or a table type.

  • DROP and ALTER

    These are DDL privileges and they authorize the DROP and ALTER SQL commands. While the DROP privilege is valid for various objects, the ALTER privilege is not valid for sequences and synonyms. This is because their definitions cannot be changed after creation.

  • SELECT, INSERT, UPDATE, and DELETE

    These are DML privileges and they authorize the respective SQL commands. SELECT is valid for all kinds of objects, except for functions and procedures. However, INSERT, UPDATE, and DELETE are only valid for schemas, tables, table types, and table views.

  • INDEX

    This special DDL privilege authorizes the creation, alteration, or revocation of indexes for an object using the CREATE INDEX, ALTER INDEX, and DROP INDEX commands. This privilege can only be applied to a schema, table, and table type.

  • EXECUTE

    This special DML privilege authorizes the execution of an SQL script function or a database procedure using the CALLS or CALL command, respectively.

Note

For more information about the object privileges available in SAP HANA and for which objects they are relevant, see the SAP HANA Security Guide - Object Privileges.

System Privilege

System privileges control general system activities. They are mainly used for administrative purposes, such as creating schemas, creating and changing users and roles, performing data backups, managing licenses, and so on.

System privileges are mainly used to authorize users to perform administrative actions, including the following:

  • Creating and deleting schemas

  • Managing users and roles

  • Performing data backups

  • Monitoring and tracing

  • Managing licenses

System privileges are also used to authorize basic repository operations. This includes the following examples:

  • Importing and exporting content

  • Maintaining Delivery Units (DUs)

Types of System Privilege

The following list gives an example of system privileges that are available on the SAP HANA Database:

  • Users and Roles, which include the following:
    • USER ADMIN

      This privilege authorizes the creation and changing of users with the CREATE USER, ALTER USER, and DROP USER SQL commands.

    • ROLE ADMIN

      This privilege authorizes the creation and deletion of roles with the CREATE ROLE and DROP ROLE SQL commands. It also authorizes the granting and cancellation of roles with the GRANT and REVOKE SQL commands.

  • Catalog and Schema Management, which include the following:
    • CREATE SCHEMA

      This privilege authorizes the creation of database schemas with the CREATE SCHEMA SQL command.

    • DATA ADMIN

      This privilege authorizes all users to have unfiltered read-only access to the full content of all system and monitoring views. It also authorizes users to execute all DDL – and only DDL – commands in the SAP HANA database. The content of those views is filtered based on the privileges of the user.

    • CATALOG READ

      This privilege authorizes all users to have unfiltered read-only access to the full content of all system and monitoring views. The content of those views is filtered based on the privileges of the accessing user.

  • System Management

    These privileges authorize the various system activities that can be performed using the ALTER SYSTEM SQL commands. Because of the high level of impact on the system, these privileges are not designed for a normal database user. Proceed with caution when granting these privileges (for example, only grant them to a support user or role.)

  • Data Import and Export

    The following system privileges are available for the authorization of the data import and export in the database:

    • IMPORT

      This privilege authorizes the import activity in the database using the IMPORT or LOAD TABLE SQL commands. Note that, besides this privilege, the user needs the INSERT privilege on the target tables to be imported.

    • EXPORT

      This privilege authorizes the export activity in the database via the EXPORT or LOAD TABLE SQL commands. Note that, besides this privilege, the user needs the SELECT privilege on the source tables to be exported.

Note

For more information about the system privileges, see the SAP HANA Security Guide - System Privileges

Package Privilege

Package privileges authorize actions on individual packages in the classic SAP HANA repository. The privileges can grant you read access to the repository, or give you full access (read/write) to the classic SAP HANA repository.

The SAP HANA database repository is structured hierarchically with packages assigned to other packages as subpackages.

Packages contain design-time versions of various objects, such as calculation views, as well as analytic privileges and functions. To work with packages, the respective package privileges must be granted.

Privileges granted on a repository package are assigned implicitly to the design-time objects in the package, as well as to all subpackages. Users are only allowed to maintain objects in a repository package if they have the necessary privileges for the package in which they want to perform an operation, for example to read or write to an object in that package. To perform operations in all packages, a user must have privileges on the root package REPO_PACKAGE_ROOT.

For a requested operation in a specific package, the authorization check is repeated on the parent package and recursively up the package hierarchy to the root level of the repository. If the user does not have the necessary privileges for any of the packages in the hierarchy chain, the authorization check fails, and the user is not permitted to perform the requested operation.

In the context of repository package authorizations, there is a distinction between native packages and imported packages.

If you grant privileges to a user for a package, the user is also authorized automatically for all corresponding subpackages.

Note

For more information about the package privileges, see the SAP HANA Security Guide - Package Privileges

Analytic Privilege

Analytic privileges allow read access to data in SAP HANA information models such as calculation views depending on certain values or combinations of values. Analytic privileges are evaluated during query processing.

Analytic privileges grant different users access to different portions of data in the same view based on their business role. Within the definition of an analytic privilege, the conditions that control which data users see is either contained in an XML document or is defined using SQL.

Standard object privileges (SELECT, ALTER, DROP, and so on) implement coarse-grained authorization at object level only. Users either have access to an object, such as a table, view or procedure, or they don't. While this is often sufficient, there are cases when access to data in an object depends on certain values or combinations of values. Analytic privileges in the SAP HANA database provide fine-grained control at row level of the type of data that individual users can see within the same view.

All calculation views that have been designed in the modeler and have been activated from the modeler of the SAP Web IDE for SAP HANA are supported automatically by the analytic privilege mechanism.

If you are already familiar with the authorization model of SAP Business Warehouse (SAP BW), you can see that there are many similarities between the two models.

The overall idea behind analytic privileges is the reuse of analytic views by different users. However, the different users may not be allowed to see the same data. For example, different regional sales managers, who are only allowed to see sales data for their regions, can reuse the same analytic view. They obtain the analytic privilege to see only data for their region, and their queries on the same view return the corresponding data. This is a major difference compared with the SAP BW model. While the concept is very similar, if you execute a query that returns values that you are not authorized to see, SAP BW forwards an error message. With the SAP HANA database, the query is executed and, corresponding to your authorization, only the values that you are entitled to see are returned.

Note

For more information about the analytic privileges, see the SAP HANA Security Guide - Analytic Privileges

Application Privilege

Developers of applications for SAP HANA extended application services can create application privileges to authorize user and client access to their application.

These privileges authorize user and client access to the application. For example, they authorize users and clients to start the application or to perform administrative actions in the application.

You can grant and revoke application privileges through the GRANT_APPLICATION_PRIVILEGE procedures and through the REVOKE_APPLICATION_PRIVILEGE procedure in the _SYS_REPO schema.

You can grant application privileges to users or roles in runtime. However, we recommend that you grant application privileges to roles created in the repository.

Note

For more information about the application privileges, see the SAP HANA Security Guide - Application Privileges

Privileges on Users

Privileges on users are SQL privileges that users can grant to other users. ATTACH DEBUGGER is the only privilege that can be granted on a user.

For example, User A can grant User B the privilege ATTACH DEBUGGER. This allows User B to debug SQLScript code in the session of User A. User A is the only user who can grant this privilege.

Note

User B also needs the object privilege DEBUG on the relevant SQLScript procedure.

You cannot grant the ATTACH DEBUGGER privilege on behalf of other users.

Overview of Roles

A role is a collection of privileges that can be granted to either a user or to another role in runtime.

A role typically contains the privileges required for a particular function or task, such as the following:

  • Business end users reading reports using client tools

  • Modelers creating models and reports in the modeler of the SAP Web IDE for SAP HANA

  • Database administrators operating and maintaining the database and the users

You can grant privileges directly to users of the SAP HANA database; however, roles are the standard mechanism of granting privileges. This is because they allow you to implement complex, reusable authorization concepts that can be modeled on business roles. Several standard roles are delivered with the SAP HANA database (for example, MODELING, MONITORING). You can use these as templates for creating your own roles.

Role Structure

A role can contain any number of the following privileges:

  • System privileges for administrative tasks (for example, AUDIT ADMIN, BACKUP ADMIN, CATALOG READ)

  • Object privileges on database objects (for example, SELECT, INSERT, UPDATE)

  • Analytic privileges on SAP HANA information models

  • Package privileges on repository packages (for example, REPO.READ, REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS)

  • Application privileges for enabling access to applications for SAP HANA extended application services

A role can also contain other roles.

Note

There are no HDI or XS advanced equivalents in the SAP HANA authorization concept for package privileges on repository packages and applications privileges on SAP HANA XS classic applications. For more information about the authorization concept of XS advanced, see the SAP HANA Security Guide.

Types of Roles

Roles in the SAP HANA database can exist as runtime objects only (catalog roles), or as design-time objects (repository roles) that become catalog objects on deployment (database artifact with the file suffix .hdbrole).

Before the arrival of SAP HANA extended application services, advanced model (XSA) there were two types of roles in HANA: catalog roles, which are created directly in the catalog, and repository roles, which are created using the SAP HANA repository.

With the introduction of SAP HANA XS advanced, new types of roles were also introduced, known as HDI-based roles. Like repository roles, HDI-based roles provide role versioning and can be transported between systems.

In an SAP HANA XS classic environment, database roles are created in the built-in repository of the SAP HANA database using either the SAP HANA Web IDE or the SAP HANA cockpit. These are also referred to as repository roles. In an SAP HANA XS advanced environment, design-time roles are created using the SAP Web IDE and deployed using SAP HANA Deployment Infrastructure (SAP HANA DI, or HDI).

SAP HANA XS advanced has the additional concept of application roles and role collections. These are independent of database roles in SAP HANA itself. In the XS advanced context, SAP HANA database roles are used only to control access to database objects (for example, tables, views, and procedures) for XS advanced applications. For more information about the authorization concept of XS advanced, see the SAP HANA Security Guide.

It is possible to create roles as pure runtime objects that follow classic SQL principles or as design-time objects in either an SAP HANA XS advanced or classic environment.

Catalog Roles

You can create catalog roles in the SAP HANA system. A role administrator creates the role in the runtime of the SAP HANA system. The database user grants catalog roles directly, and they can only be revoked by the same user.

To create a catalog role, open the catalog role editor in the SAP HANA cockpit. Then select the roles and privileges that you want to include, and save the role.

You can create a new role directly in runtime and grant it the privileges and roles necessary for the task or function that it represents on the role page of the SAP HANA cockpit. It is also possible to map roles to LDAP groups if you are implementing user authorization based on LDAP group membership.

Design-Time HDI Roles

SAP HANA XS, advanced model provides a comprehensive platform for the development and execution of native data-intensive applications that run efficiently in SAP HANA. Design-time objects are created within a project stored in the GIT repository. When the developer deploys the project, a runtime version of the objects is created in an HDI container in the SAP HANA database.

In SAP HANA XS, advanced model, runtime versions of the design-time roles are created at schema-level due to containerization. These roles are owned by the container-specific technical user and can be compound by privileges granted with the admin/grantable option to the container-specific technical owner.

An HDI container can be seen as a database schema and there can be multiple HDI containers within the SAP HANA database. Due to this containerization, it is possible to separate role development from application development because you can organize this within different spaces in XSA and only assign the relevant privileges to the respective container-specific technical user.

For the development of roles, no real application is created. Only database artifacts – the roles – are used. For this, developers use the SAP Web IDE to work on a project within a space in XSA where they will create the design-time objects.

The initial step is to set up and prepare the XSA + SAP Web IDE application. Once the setup is ready, developers need to set up their organization and spaces in a way that suits the needs of their organization.

You can find more information about how to create spaces, set up users, and call the SAP Web IDE in the Post-Installation Administration Tasks chapter in the SAP Web IDE for SAP HANA Installation and Upgrade Guide.

Our recommendation is to set up at least a dedicated space for role developing. You can find more details in the document Best Practices and Recommendations for Developing Roles in SAP HANA.

Design-Time Repository Roles (Deprecated)

You can create repository roles in the development system. A developer or role designer creates the role in the repository of the development system and tests it. Therefore, the following prerequisites have to be fulfilled:

  • Authorization assigned: sap.hana.xs.ide.roles::EditorDeveloper role

  • A shared project must exist with a suitable package for storing roles

  • Package privileges on the required packages

The technical user _SYS_REPO is the owner of all objects in the repository, as well as the runtime objects that are created on activation.

The role is transported to the production system, for example, using HALM or CTS+.

In the production system, a user administrator grants the role to end users.

Because the SAP HANA repository and the SAP HANA extended application services, classic model (XSC) have been deprecated and are planned to be removed from the next major SAP HANA release, HDI-based roles are now the recommended type of roles to be used, and thus, the successors of repository roles.

Note

SAP HANA XS classic and the SAP HANA repository are deprecated as of SAP HANA 2.0 SPS 02. For more information, see SAP Note 2465027 - Deprecation of SAP HANA extended application services, classic model and SAP HANA Repository.

The HDI-based roles and repository roles have fundamental differences in the way that they are developed or are assigned. To explain these differences, we have prepared the Best Practices and Recommendations for Developing Roles in SAP HANA document, which describes the most important changes related to the role development process. This document also provides you with additional information and recommendations on how to address common challenges when developing HDI-based roles.

Also, in the document, we deliver and describe HDI-based role templates for administrative tasks in the SAP HANA database. You can use these role templates as a starting point for the creation of your own HDI-based roles.

Usage Scenarios for Different Role Types

Catalog roles are created in runtime and have typical SQL object behavior (for example, ownership). They are mainly used in conjunction with high-level user/identity management tools where roles are managed by a technical user in SAP HANA.

Catalog roles make sense in scenarios where user and role provisioning is carried out solely using a higher-level application that connects to SAP HANA through a technical user, such as SAP Identity Management.

Design-time roles are recommended for other scenarios where roles are managed by users directly in SAP HANA.

In general, it is recommended that you model roles as design-time objects for the following reasons:

  • Unlike roles created in runtime, roles created as design-time objects can be transported between systems. This is important for application development because it means that developers can model roles as part of their application's security concept and then ship these roles or role templates with the application. Being able to transport roles is also advantageous for modelers implementing complex access control on analytic content. They can model roles in a test system and then transport them into a production system. This avoids unnecessary duplication of effort.

  • Roles created as design-time objects are not directly associated with a database user. They are created by technical users and granted through the execution of stored procedures. Any user with access to these procedures can grant and revoke a role. Roles created in runtime are granted directly by the database user and can be revoked only by the granting user or a user with system privilege ROLE ADMIN. Also, if the database user is deleted, all roles that they granted are revoked. Because database users correspond to real people, this could impact the implementation of your authorization concept, for example, if an employee leaves the organization or is on vacation.

Make sure that the design-time version of a role in the repository and its activated runtime version always contain the same privileges. In particular, do not grant additional privileges to the activated runtime version of a role created in the repository. Although there is no method for preventing a user from doing this, the next time the role is activated in the repository, any changes made to the role in runtime are reverted. Therefore, do not change the activated runtime version of a role in runtime.

Predelivered Catalog Roles

Several catalog roles are available by default in the SAP HANA database.

PUBLIC Role

PUBLIC
This role contains privileges for filtered read-only access to the system views. Only objects for which the users have access rights are visible. By default, this role is granted to every user, except restricted users.

Support Role

This role contains system privileges (for example, CATALOG READ) and object privileges (for example, SELECT on SYS schema) that allow access to certain low-level internal system views needed by SAP HANA development support in support situations. All access is read only. This role does not allow access to any customer data.

The definition of the low-level internal system views to which this role allows access is not part of the stable end-user interface. It might change from revision to revision. To avoid administrators and end users accidentally accessing these internal system views in applications or scripts, this role has several usage restrictions. Only grant this role to SAP HANA development support users for their support activities.

Roles Restrictions

This role contains privileges for read-only access to all metadata, the current system status, and the data of the statistics server. Additionally, it contains the privileges for accessing low-level internal system views. Without the SAP_INTERNAL_HANA_SUPPORT role, this information can be selected only by the SYSTEM user.

To avoid accidental use of this role in day-to-day activities, the following restrictions apply to the SAP_INTERNAL_HANA_SUPPORT role:

  • It cannot be granted to the SYSTEM user.

  • It can only be granted to a limited number of users at the same time.

    The maximum number of users to which the role can be granted can be configured with the parameter internal_support_user_limit in the authorization section of the indexserver.ini configuration file. The default value is 1.

  • It cannot be granted to another role.

  • It cannot be granted another role.

  • It cannot be granted further object privileges.

  • It can be granted only further system privileges.

  • With every upgrade of the SAP HANA database, it is reset to its default privileges.

Log in to track your progress & complete quizzes