Describing SAP HANA Privileges and Roles

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.

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)

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.