10.2    What Are Analytic Privileges?

Analytic privileges govern access to data made available through SAP HANA’s information views. Two types of analytic privileges exist: classic XML-based and SQL-based. XML-based analytic privileges are a legacy type of analytic privilege. These privileges manage access to information views and provide both simple and dynamic filtering capabilities. SQL-based analytic privileges offer the ability to govern access to both information views and catalog database views. These privileges also offer the ability to implement more complex filter logic using subquery statements, which overcomes some of the limitations associated with the filter logic available with XML-based analytic privileges.

10.2.1    XML-Based Analytic Privileges

Like all analytic privileges, XML-based analytic privileges provide a twofold level of access. First, XML-based analytic privileges grant read access to the information views associated with the analytic privilege. Within the SAP HANA, we refer to them as secured models. In most security models, every information view will be associated with at least one analytic privilege, which will then be assigned to a role. When an analytic privilege is assigned to a role, grantees of that role will have read access to the information views.

Second, data restrictions can be applied within analytic privileges to statically or dynamically filter the data based on specified filter criteria. In the world of business intelligence, we call this row-level security. Rows of data are strategically filtered to restrict a user’s access to specific rows of data. For example, if the security administrator needs to restrict data so that regional managers can only see records associated with their region, he’d use analytic privileges to do so.

XML-based analytic privileges provide two different types of restriction to filter data. You can define static analytic privileges using static filter conditions or dynamic analytic privileges that will dynamically look up filter conditions on a user-by-user basis. Let’s look at these options in more detail.

Static Analytic Privileges

The methodology behind static analytic privileges dictates that an analytic privilege object be created for each unique filter scenario. For example, if your organization wanted to filter its information views for 10 different regions, you would need to define 10 different analytic privilege objects within the repository. You would probably define 10 different roles for each analytic privilege and assign user accounts one or more corresponding roles for matching filter conditions. Figure 10.1 shows how the mapping between static analytic privileges and roles/users typically would be set up.

Figure 10.1    Static Analytic Privileges Mapped to Roles and Users

Let’s assume that each analytic privilege filters the sales region attribute column within each assigned information view. For example, the attribute column SALES_REGION = 'NORTH' would be applied to the analytic privilege AP_STATIC_REGION_NORTH, and the SALES_REGION = 'SOUTH EAST' condition would be applied to the analytic privilege AP_STATIC_REGION_SOUTH_EAST. As shown in Figure 10.1, each filter condition would require an analytic privilege, and each analytic privilege would require a role. Each user can be granted one or more roles.

Each analytic privilege can be assigned one or more filter conditions or restrictions. When multiple conditions are configured within the same analytic privilege, they’re executed using an AND condition, which means that all filter conditions or restrictions must be true. For example, if you configured an analytic privilege with the conditions SALES_REGION = 'NORTH' AND SELLING_PLANT = 'Chicago North', only data records that fulfill both conditions would be returned when queried.

When a user is assigned one or more analytic privileges, the overall filter condition is executed using an OR condition, which means that either of the restrictions in each individual analytic privilege can be true. For example, a user is granted two roles, each with its own unique analytic privileges. The first role’s analytic privilege requires the SALES_REGION = 'NORTH' restriction, and the second role’s analytic privilege requires the SALES_REGION = 'SOUTH_EAST' restriction. When the user queries the assigned information view, the query would result in records with SALES_REGION of either NORTH or SOUTH_EAST.

Static analytic privileges are effective at providing data-level security. However, as shown in Figure 10.1, each unique filter condition requires an analytic privilege object and role. If your organization requires a large quantity of restrictions on a variety of different attribute columns, you’ll need to create and maintain a significant number of objects. This proliferation of objects can produce management issues in most SAP HANA systems. Let’s look at how dynamic analytic privileges can help to solve this problem.

Dynamic Analytic Privileges

The methodology behind dynamic analytic privileges allows a single analytic privilege object to dynamically look up restrictions based on one or more data elements. Dynamic analytic privileges significantly reduce the number of objects required to support a large and diverse set of restrictions. Figure 10.2 shows an overview of the objects required to implement dynamic analytic privileges.

Dynamic analytic privileges work by retrieving the filter criteria from a catalog table within the SAP HANA system. The catalog table, at a minimum, must map each SAP HANA user account to a specific data element. For example, each user account is mapped to one or more regions within a table. Users with multiple data element assignments will have multiple rows defined in the table, and each row will map the user account name to a unique data element. These data elements will be dynamically generated and applied to the analytic privilege when an associated information view is queried.

The major benefit of dynamic analytic privileges is that they reduce the number of analytic privilege and role objects required to facilitate data restrictions. For example, in the previous region-based example, using dynamic analytic privileges would require only a single analytic privilege and role. You wouldn’t need to create an analytic privilege and role for each filter condition. However, the catalog table containing the user-to-region mapping still must be maintained. In an ideal scenario, this mapping could be programmatically derived and refreshed from an external source.

Figure 10.2    Dynamic Analytic Privilege Mapping to Roles and Users

10.2.2    SQL-Based Analytic Privileges

SQL-based analytic privileges grant access to either information views or catalog views. In this chapter, we’ll only discuss the options for restricting information views using the analytic privilege objects stored in the SAP HANA repository. Note that SQL-based analytic privileges are a type of structured privilege. Typically, when we refer to SQL-based analytic privileges, we’re referring to the graphical analytic privilege objects created in the SAP HANA repository. When working with catalog views, you’ll typically create structured privileges or refer to the restrictions as structured privileges.

Before an information view can use SQL-based analytic privileges, the view must be registered for an authorization check. When defining an information view, the option to support an analytic privilege check must be selected. This option is found under the View Properties tab of the semantics node, in the Apply Privileges dropdown list. Figure 10.3 shows this option within an information view. Notice the View Properties tab and the Apply Privileges dropdown list with three options: SQL Analytic Privileges, Analytic Privilege, and <blank> options. You’ll find the same options when working with all calculation view types. Note that the Analytic Privilege option only applies to XML-based analytic privileges, and the blank option means that an analytic privilege check will not be performed on the information view.

Figure 10.3    Setting SQL Analytic Privileges Option in Information View

SQL analytic privileges also offer you the ability to define both simple and complex data restrictions using complex SQL filter logic. They support three options for defining restrictions: static, dynamic, and SQL expression.

Static Analytic Privileges

Static SQL-based analytic privileges are like their XML counterparts, and they too can lead to scalability issues if your organization requires a diverse allotment of filter conditions. You can assume that all the benefits and issues associated with static XML-based privileges also apply to static SQL-based analytic privileges.

Dynamic Analytic Privileges

Dynamic SQL-based analytic privileges are also like their XML-based counterparts. However, SQL-based analytic privileges look up filter conditions as opposed to filter values. For example, dynamic XML-based analytic privileges will look up and return an array of values such as 10, 100, 300, 400, and 900. Dynamic SQL-based analytic privileges will look up and return a single string value, such as (CUSTOMER IN (10, 100, 300, 400, 900)). This string value can become the syntax for a condition used to filter an information view. Dynamic SQL-based analytic privileges also support more complex conditions, such as (( CUSTOMER = 900 ) or ( PLANT = 1000)). We’ll explore dynamic analytic privileges further in Section 10.5.2.

SQL Expression Analytic Privileges

Dynamic expression-based SQL analytic privileges are unique and don’t have an XML-based counterpart. These types of analytic privileges are dynamic, but they don’t require the use of stored procedures to look up filter conditions; the filter conditions are manually crafted into a filter expression. Filter expressions support the use of subqueries and other complex operators in their definition. For example, you can use a complex subquery expression to dynamically filter data for a given user, for example, in the following example code:

"CUSTOMERKEY" IN     
(SELECT DISTINCT CUSTOMERKEY     
FROM "SECURITY"."Security.ap::RLS_CUSTOMER_ACCESS"    
WHERE USERNAME = SESSION_USER)

Expressions can be even more complex than this example, and they support a wide variety of solutions. We’ll explore expressions further in Section 10.6.