Even as an SAP instructor, I was confused about some of the basics of security in SAP HANA. In part 1 of this series of articles, I discussed the first area of confusion—the difference between design-time (repository) and run-time (catalog) roles. In this article I address the confusion caused by the name change, for example, from XML to classical. I also address the differences between the types of analytic privileges, and why you would use one versus another in the ultimate goal of securing row-level data (for example, who can see what country in an SAP HANA information view). Originally, analytic privileges were modeled with an easy-to-use user interface (UI) tool in the content repository under the covers, in XML, using SAP HANA studio and now, more recently, using the SAP HANA Web Integrated Development Environment (SAP HANA Web IDE) tool. Although these techniques were easy to use, they were not flexible when it came to crafting more complex rules—for example, when you want users to be able to view sales data for company X, but only for country Y. It was not until the most recent Support Package Stack 10 (SPS10) was released that it became possible to create a newer type of SQL-based analytic privilege that solved this flexibility constraint, either created via the modeling perspective of SAP HANA studio or via web-based SAP HANA Web IDE. Also as of the release of SPS10, the same convenient creation UI options that existed for XML-based analytical privileges can be used to deploy both XML- and SQL-based analytic privileges. There are, however, significant differences in features and settings for each. In this article I explore, in more detail, the differences between these two types of analytic privileges, why one might be better than the other in certain scenarios, and the UI options for building each. This is now possible because there are two tools (SAP HANA studio and SAP Web IDE). Both tools can build both types of privileges.

A High-Level Overview of Security in SAP HANA

As discussed in part 1 of this series, [insert link when posted] SAP HANA operates not only as a database to support applications running in their own servers; it also behaves as an application itself with users getting information directly from SAP HANA. Because of this, SAP HANA has a complex mixture of security components, and each use scenario comes with its own unique security demands. Security in SAP HANA is provided via privileges, and these privileges are grouped into categories. These include system privileges to do database-type tasks, object privileges for accessing database objects (such as tables and schemas), package privileges for modeling tasks, and application privileges for accessing web applications. Finally, analytic privileges control access to read data from SAP HANA information views. Although it is possible to assign these privileges directly to users, normally they are assigned to roles and, subsequently, then these roles are assigned to users. Figure 1 illustrates the access that users have to these privileges. The upper-right corner shows the direct access that end users ultimately have to SAP HANA information views. This direct-access type of scenario is the same even if the users access SAP HANA via a reporting tool such as BusinessObjects, as long as the authentication from BusinessObjects is user specific. In order for users to access the data in information views, they need a combination of object privileges (to be able to access the data in the appropriate schemas), package privileges (to access the information views), and (the focus of this article) analytic privileges (to read the data in these information views). There are two types of analytic privileges and a couple of variations within the two. On top of this, add the complexity of the concepts of run time and design time (discussed in part 1), but, in this case, applied to analytic privileges, not roles.

A Deeper Dive into XML Analytic Privileges Versus SQL Analytic Privileges

Analytic privileges are the privileges that allow access to users to view data contained in information views, such as analytic and calculation views. They also facilitate row-level security. For example, user X can see rows that have US as their country, but cannot see any rows that have other countries. There are two types of analytical privileges. The original type (on SAP HANA, many moons ago), used the term analytic privilege in its menu path options, with no prefix. As there was only one, this name was used for the default type of analytic privilege. In some places in the documentation these were also called XML analytic privileges. The reason for using XML in the name was because the format that the graphical tool generated was XML. In SPS10, this has officially been changed to classical analytic privilege. The second type of analytic privilege is SQL-based analytic privileges. In this type, the system allows the definition of the privilege to be entered directly in SQL format. For example (as the name would suggest) you code directly into this type the SQL coding, for example,  what countries a privilege allows a user to see, via SQL coding (e.g., COUNTRY = ‘US’). Although it might be possible to code these SQL-based analytic privileges and execute the code as a run-time object, SAP recommends that design-time (repository) objects be used instead (for the same reasons discussed in my part 1 article). However, unlike with roles, where the run-time option (not recommended) looked to be the one presented front and center, with analytic privileges, SAP nearly pushes you to use the recommended design-time option, using either UI. As of SAP HANA SPS10, it is now possible to use SAP HANA studio or the SAP HANA Web IDE editor to build both classic (XML)- and SQL-based analytic privileges. Therefore, here I focus on these two techniques for building these two types of analytic privileges. In addition, I also briefly discuss the concept for both classical and SQL-based analytic privileges, where code (a stored procedure), external to the privilege itself, is used accomplish the logic of what can be viewed. When this call to an external procedure is configured in the privilege, the privilege is called a dynamic analytic privilege. When deciding which kind of analytic privilege to use, you are making a trade-off decision between ease of creation versus having flexibility. The classical analytic privileges (XML) are 100 percent graphical user interface (GUI)-based. No coding is required by the creator; you just use drop-down boxes to make your selections. Therefore, the classical analytic privileges are the easiest type to create. SQL-based analytic privileges, on the other hand, offer two options. One uses a UI that basically makes them behave to the user as if they were the classic type, and another uses a UI that converts the UI to a SQL editor where code is required. Thus, they are more flexible to use, but when the coding option is chosen, they become much more complicated. In light of the fact that the SQL privilege option gives you the ability to use more complex coded logic, but does not require you to, my hunch is that, in the future, SQL analytic privileges will dominate—not  the classical (XML) type.

A Basic Overview of Implementing Analytic Privileges

The user running a reporting application that accesses SAP HANA information views must have access to the schema that contains the database objects (tables). In addition, the objects’ privileges on the schema views created via the modeler are secured with an extra layer, called analytic privileges, which are invoked by default. It is possible as of SPS6 to not have this extra layer of security invoked on specific views, but this requires leaving the Apply Privileges field blank in the properties of the view prior to activation (Figure 2). This would only make sense if every user is allowed to see all the data contained in the view as long as they had access to the underlying tables. If this is not the case, you must select one of the analytic privilege options (e.g., classical or SQL in SPS10) that you want to deploy to hide access to the view in general or, as an option, which rows of data will be viewable. In Figure 2, the SQL option is chosen, but the Classical Option is another choice in the Apply Privileges: field. Once you’ve finished setting the Apply Privileges property for your view (and you did not leave it blank), the next step—to create the appropriate type of privilege—is critical. You have to follow through and create the corresponding privilege type (SQL or classical) and assign it to users—either directly or indirectly—via roles. If you skip this step, your users (except the ones with privilege _SYS_BI_CP_ALL, as noted above) will not have the ability to see any data from these views. However, this creates the questions which type and why. In addition, you also must decide if you want to make the privileges dynamic and in which user interface (UI) to perform your work. I answer those questions below.

Classical Privileges Versus SQL Privileges

At the end of the day, all privileges to see one row of data or another are in the database catalog as code: select WHERE some field is = to some value or set of values. The difference is in the process for how the code is created. In the first article in this series I discuss how to create objects in the content repository or directly in the database catalog. In the case of analytic privileges, excluding the dynamic type (which I address later in this article), SAP pushes you to create analytic privileges as transportable repository or design-time objects that must be deployed or activated. That is a good thing, as privileges should be tested in a dedicated test system before being moved to and deployed in production. So what is the difference between classical and XML privileges? Originally, the modeling of these analytic privileges was targeted only to the average security professional (one who did not know how to code). As such, an easy-to-use UI was developed that allowed analytic privileges to be easily created in the repository using SAP HANA studio. The details of the privilege were then stored in XML format. Since it was not necessary for the security pro know how to code in any language, the XML code was behind the scenes. This article is not a detailed how-to for creating privileges—for that you need to take the HA240 class or read the documentation. That said, the first UI I use to show how to create the privilege is in SAP HANA studio. To access the screen where you can create either type of privilege (classical or SQL), expand the Content folder in either the SAP HANA Administration Console or the Modeling Perspective (as shown on the left of Figure 3). Then select the content package you previously created (in this example, training) and right-click. In the context menu that opens, click New and then Analytic Privilege, and the New Analytic Privilege pop-up window in Figure 3 opens. What’s new here is that both SQL and classical privileges can be created in the Content folders of the repository. Make sure the Create new radio button is selected, and then select which type simply by choosing from the drop-down options shown in the figure. In this case, Classical Analytic Privilege.

Classical Analytic Privilege Details

After choosing the classical option (Figure 3), the modeling screen for the privilege opens (Figure 4). I have filled in just a few options for clarity. Using the intuitive buttons in each section (Add, Remove) I have created a privilege that allows users named Cody to have access to the SECURITY_TEST view. The XML part is not normally encoded manually, but rather is generated as you build the privilege using the UI in Figure 4. To see the settings in XML format, click the XML icon in the top right corner of the UI (boxed in the figure). This opens the View XML pop-up window in Figure 5 with the code. Although, as I mentioned previously, this older type of analytic privilege got the job done, creating complex logic (for example, country US and customer X, but not country US and customer Y) was not possible or not easily done without the need for a stored procedure.  As a result, SAP decided to create a new type of privilege, the so-called SQL privilege, to make it easier to incorporate complex logic.

SQL Privilege Details

Although is still possible to code SQL privileges directly in the database catalog as run-time objects using SQL code to define them, again SAP is not pushing that. Design-time (repository) objects that are transportable are the way to go. (For more details on this topic, see my part 1 article.) To that end, with SAP HANA SPS10, SAP has enabled SQL analytic privileges to be easily coded as design-time objects in a content repository package, just as the XML ones are. First, set the view to use SQL privileges (using the options in Figure 2). Your task is then to model the allowed values. For this, follow the same menu path in Figure 3, but this time choose the SQL Analytic Privilege drop-down option. This opens the screen shown in Figure 6. Although the screens look very similar (compare Figure 4 to Figure 6), there is a significant difference. In the SQL analytic privilege screen (Figure 6) there are three radio buttons: Attributes, SQL Editor, and Dynamic. Although both screens (Figures 4 and 6) encode the design-time definition in XML code, you can see that even when the SQL privilege UI is set to Attributes (in Figure 6), and you look at the underlying code in the same way you did previously, the highlighted code (Figure 7) shows the SQL analytic privilege type. The three radio buttons of the SQL privilege UI (Figure 6) provide a lot of choices, as detailed below. The Attributes radio button: The default Attributes radio button setting in the SQL analytic privilege (Figure 6) works exactly like the classical analytic privilege type; even the XML encoding looks very similar. The Dynamic radio button: Dynamic Privileges are coded privileges, but with the code in a stored procedure. Although it looks like only the SQL privilege type has a dynamic option when comparing the classical type (Figure 4) to the SQL type (Figure 6), this is not true. It is just harder to find this option in the older classical privilege UI. (I discuss this in more detail later in the article.) The SQL Editor radio button: The main difference comes when you select the SQL Editor radio-button option. When this option is selected in Figure 6, the pop-up in Figure 8 opens. Click the Yes button to acknowledge the warning, and the UI changes to allow you to define the restrictions using standard SQL syntax (Figure 9). Now, instead of using the drop-down options to model the selected attributes and values to secure, you have the SQL Editor where you can directly code SQL to provide the required restrictions. The obvious advantage is you have the ability to code it all in a flexible, well-known language. The drawback is that now you need to know how to code. Remember, you don’t have to know how to code when you use the Attributes radio-button option of the SQL analytic privilege, but you can if you want to. This, in my mind, makes this newer SQL privilege creation process the preferred way to go. However, there is yet another option for both: dynamic privileges.

About Dynamic Privileges

For both SQL and classical analytic privileges, you have the option to use dynamic privileges. The difference between dynamic and non-dynamic privileges is all about where the logic is defined for the restriction values. Unlike both options shown above, where the logic was either coded or set using attributes and assigned values directly in the privilege, with dynamic privileges you link the privilege to code that is in a SQL-stored procedure outside of the privilege itself. The idea is illustrated by the image in Figure 10. Now that you see the idea of a dynamic privilege, you can see its power (and its flaws). The power is that the code used in the stored procedure can be as complex as needed. You can make changes to it (and the resulting output of the allowed values) outside of the realm of the actual privilege UI once the privilege has been set to call the procedure. This advantage is also the flaw, however, as now the security professional needs to worry about the security of who can run the procedure (if it does not run it will break the output completely) and the security of who can change it. Full disclosure: I should also mention it is possible to call procedures that are repository (transportable or testable) or catalog (run-time editable), but in this case, I just want to show how to alter the two types of privileges to set them to be dynamic. In the first case (classical privileges), the option to set the privilege to be dynamic lies in changing the Fixed default option for the attribute type to either a Catalog Procedure or a Repository Procedure (Figure 11). The process for making the SQL analytic privilege dynamic is much more straightforward. For these SQL privileges (in the screen in Figure 9), you merely select the Dynamic radio button. After that option is chosen, the system provides a pop-up window (not shown) that warns you that any other settings made with the other radio buttons will be lost, and asks if you want to continue. Click the Yes button, and a screen like the one in Figure 12 opens. The option shown here is, again, nearly identical to the prior one (Figure 11).

Two GUI Options for Creating Repository Analytic Privileges

Now you know that there are two ways to easily create repository (transportable) analytic privileges, but there is even more to learn—there are two GUIs to do it in. SAP is moving more and more of the SAP HANA studio functions to the web. A web application (delivered with SAP HANA running on the SAP HANA Extended Application Services [XS] engine), called SAP HANA Web IDE, allows the creation of either classical or SQL privileges using the web. The UI looks very similar to that of SAP HANA studio, but for clarity and full disclosure, I have included a screenprint of the classical privilege maintenance screen in SAP HANA Web IDE and a (redacted) URL for getting there (Figure 13). You should now have a good overview of the options and maintenance UI choices you have for creating row-level analytic privileges in SAP HANA studio. I hope to see you in an HA240 class to teach you all the nitty-gritty details. Or learn them on your own by reading this guide on security: https://help.sap.com/hana/sap_hana_security_guide_en.pdf.