The basics about SAP HANA security can be a somewhat confusing topic, even for experienced users. However, there are some terms and concepts that users must understand in order to properly set up security in SAP HANA. In the first of this two-part series about SAP HANA security, gain an overview of two ways for building security roles: run-time and design-time roles. There are significant differences between these two techniques, each with its own advantages and disadvantages.

Key Concept

In the early days of SAP HANA, there was only one way to build roles that enabled some users to see data X and others to see data Y: it had to be coded manually. Now there are SAP HANA graphical and automated tools that allow security roles to be built that can be assigned to users to do this task. When choosing which graphical option to use, the SAP HANA SQL-compliant database codes the required data control language (DCL) syntax behind the scenes. There are options on which user interface (UI) to use, however, and even though either UI creates code, which one is chosen affects the ultimate goal of achieving an easy-to-administer yet still secure SAP HANA system. SAP HANA is a SQL-compliant database that comes with many extra features that are not standard in most databases.  These capabilities include text searching, a built-in web server, geo-spatial data handling, and more. As I understand it, a SQL-compliant database has to have the SQL basics implemented in order to secure the data. With SAP HANA, SAP has done this: implemented the basic requirements of standard SQL. However, although the basics of SAP HANA’s security features are adequate for complying with SQL standards, the bare minimum security features are not enough for a complex corporate security environment. To meet these needs, SAP HANA offers multiple user interfaces (UIs) that allow for more than one way to deploy security schemes and various features over and above the basics needed for SQL compliance. In this article I address one major area where options exist—the area of deploying security roles (run-time [catalog] or design-time [repository] roles). First, I give a high-level overview of the security options that come with the SAP HANA database, its features for deploying reporting security, and when reporting security is needed in SAP HANA. Then I examine in more detail the differences between run-time and design-time roles, and clear up some confusion that exists about these two options for deploying roles.

A High-Level Overview of Security in SAP HANA

Many different components make up SAP HANA security. This is because not only does SAP HANA operate in most scenarios as a database to support applications running in their own servers; it also behaves as an application itself with users getting information directly from the SAP HANA system. Both these scenarios have different security demands. In all these cases you need security for database administrators to manage and back up the SAP HANA system (Figure 1). However, when SAP HANA is used solely as a database with all access going through an application layer (such as SAP ERP Central Component [ECC] or SAP BW), only one super user (the technical user in Figure 1) is granted access to almost all the data in SAP HANA. The super user then determines which users (who have access to the application layer) can access which data. What complicates SAP HANA is when it is used as an application that serves up web pages or as a data warehouse that serves up data directly to end users (the direct access to end users in Figure 1). In these cases, you need security to be set up not only for end users but also for developers and modelers. Security in SAP HANA is provided via privileges. These privileges are grouped into four major categories:

1. System privileges
2. Object privileges
3. Package privileges
4. Application privileges

System privileges secure database administration-type tasks, object privileges control access to database objects (such as tables and schemas), package privileges are to perform modeling tasks, and application privileges control access to web applications. Although it is possible to assign these privileges directly to users, normally they are assigned to roles and then, subsequently, the roles are assigned to users. In Figure 1, if you are only focusing on the database administrators and users to connect to an application running on SAP HANA, the roles and associated privileges are much simpler. When the task is to secure data and applications accessed directly from SAP HANA, however, the privilege list is expanded. If you then add more privileges to the users and, for example, restrict what uses can see data X but not data Y, the complexity increases even more. In this last scenario, you need to have roles created that contain object privileges (to select data from a schema), package privileges (to read views that were modeled with the SAP HANA modeling tools), and analytic privileges (to read data from these views). This complexity is due in part because SAP HANA is promoted to be a SQL-compliant database, but also one that needs features to support direct deployment as an application platform at the same time. This article focuses on these areas specifically—not on the how, but rather more on the where and why. There are two methods to build up roles that contain these privileges: run-time roles (sometimes called catalog) and design-time roles (also called repository roles). On top of that, there are two graphical user interfaces (GUIs) for building them: SAP HANA studio and SAP HANA Web Integrated Development Environment (IDE).

Confusing Issue #1: Run-Time (Catalog) Roles Versus Design-Time (Repository) Roles

In order to be considered a SQL-compliant database it has to be able to provide a way to secure access to data. To do this, you need to support the subset of SQL called data control language (DCL) commands. According to Wikipedia, the definition of DCL is:

A data control language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database. In particular, it is a component of Structured Query Language (SQL). Examples of DCL commands include: •    GRANT to allow specified users to perform specified tasks. •    REVOKE to cancel previously granted or denied permissions. The operations for which privileges may be granted to or revoked from a user or role may include CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE, or USAGE.

From its early days as a SQL-compliant database, the SAP HANA system was able to grant these privileges. In the beginning, however, this was only possible by executing SQL code in the SQL Editor in SAP HANA studio. Fast-forward a bit and SAP created a Security folder within SAP HANA studio that allowed the creation of users and roles without having to use code (Figure 2).

Run-Time Roles Defined

The roles created via code directly in the SQL Editor or those created via the security folder in Figure 2 are considered run-time roles. These run-time roles were created directly in the database via SQL commands run by a real user. For role creation done this way (as with other SQL-compliant databases), the privileges and roles and the granting of such were owned by the user who executed them. The issue that arises with this scenario is when, in this pure form of a SQL-compliant database, the user who executed them leaves. When this happens, all the roles and granting of these roles are lost along with the employee. In addition, with either method, there was no way to capture the objects if this was done in a development environment and then were subsequently transported to a test or production environment. This test-then-transport life cycle is a critical feature, one on which SAP security administrators rely in these SAP ECC/ERP suite scenarios, and one that they expect to continue to be available. Obviously, roles created this way (run time/catalog) do not work in a production environment as employees leave and change positions, and transporting is a standard practice. For these reasons SAP does not recommend using them for this purpose.

Design-Time Roles Defined

SAP recommends using design-time (repository) roles instead of the run-time roles discussed above, as they do not have these fundamental flaws. However, before diving into the details for why this is the case, you first need to have a full understanding of the concept of the SAP HANA repository. In order to make the database an application platform complete with the missing transporting features that were identified above, SAP needed to create a repository of inactive database objects that, once activated, would become real objects. The activation of these objects (e.g., the execution of the SQL code) would actually be performed by a special user that never leaves: _SYS_REPO. Since all the inactive objects must still be shared and kept track of, they are managed in a special schema, again called _SYS_REPO. This schema also stores the information about which objects are organized into what packages, and which objects have been activated. To the end user, the content contained in the _SYS_REPO schema is shown in packages under the Content folder (Figure 2). This so-called content repository is a critical feature of SAP HANA. Now that you understand the idea of a repository of content that is managed and activated by a special _SYS_REPO user (Figure 3), let’s discuss the reasons for having design-time/repository roles. Nearly from the start, modelers could create design-time information views in content packages. Then, via activation, the _SYS_REPO user can generate the database view (behind the scenes). This was all accomplished in the main area of SAP HANA studio, not under the more technical development perspective used mostly by code developers. No such straightforward way to create design-time roles was available. Like design-time information views, repository roles are created in the content packages of the repository (unlike run-time roles). However, unlike modeling views, until SAP HANA Support Package Stack 9 (SPS9), it was tedious to create these recommended repository roles. The only way to do it was with a wizard that simply provided a shell around creating a bunch of code, done in the developer’s perspective. The old way, prior to SPS9, is explained in this SAP help link: https://help.sap.com/saphelp_hanaplatform/helpdata/en/f3/73e4db9a104749812b7ce11ee964d4/content.htm. As shown, creating a repository role required you to use the development perspective of SAP HANA studio, and as such, was not something the typical security professional would feel comfortable doing as most security pros are used to using UIs that hide many technical details. Now with SPS9 and above, there is a graphical editor to create these repository roles. It is accessed as part of the SAP HANA Web IDE application (a web-based development workbench). Although there are ways to code applications using this web-based toolset, now with SPS9 and above there are 100 percent graphical ways to create repository roles.

Confusing Issue #2: The Web Run-Time and Design-Time UIs

If you thought (as I did) that web-based roles meant design-time (repository) roles and that, if you did role maintenance in the web UI you were always doing it in repository packages, you are wrong. But don’t be confused, there is a right way and a wrong way to use SAP HANA Web IDE in a production environment. Previously you could create these not-recommended run-time roles via the web but now, with the release of SPS9, you can create the recommended repository roles in the web as well. The URL you use is one key to tell which type of role you are creating. Another way is to know that, when you create design-time roles, you always do so under a package; when you create run-time roles, you do not use packages. Figure 4 shows the URL and menu path to build the not-recommended run-time roles. As you can see from the URL and navigation tree under Roles, you are not under a repository package. Figure 5, on the other hand, shows a different URL and path to create the recommended design-time roles. This is available in SPS 9 and above. Take note of the URL with editor in the address. Editor here refers to the design-time repository editor. Also note that the menu path to create a role is under the Content package. To allow the proper testing and transporting of roles from one environment to another in a development, test, or production environment, SAP recommends using the content repository and putting roles in content packages. SAP does not (except maybe in a playground environment) recommend using the Security folder path, either in a web GUI or on SAP HANA studio, to directly build roles’ catalog roles in run time. SAP recommends building roles in content packages either with the older and harder SAP HANA studio technique in a developer’s perspective, or with the newer and easier SPS9 SAP HANA Web IDE as shown in this article (for SAP HANA SPS9 and up). When you use a package, you are building a design-time (repository) role. As such, the user SYS_REPO is the owner and you can easily transport the roles from development to test and then on to production using SAP HANA delivery units. For more information on this topic, follow this link: https://help.sap.com/hana/sap_hana_security_guide_en.pdf or take the SAP HA240 class about security in SAP HANA: https://training.sap.com/shop/course/ha240.