Defining Analytic Privileges

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Define analytic privileges

Analytic Privileges

Analytic privileges are used to enable data access in calculation views, by filtering the data based on the values of one or more attributes.

The rationale for analytic privileges is to allow the use of calculation views by different users who might not be allowed to see the same data. This approach means we do not have to create copies of the same calculation views with different filters defined.

For example, different regional sales managers who are only allowed to see sales data for their regions could use the same calculation view, but they would have different analytic privileges assigned to their user id that specify the regions they are supposed to see.

An analytic privilege can be assigned to multiple users who have the same data access requirements.

Analytic Privileges — The End-to-End Scenario

To secure a calculation view with analytic privilege, the main steps are as follows:

To Create and Assign an Analytic Privilege

  1. Create a source file with the extension .hdbanalyticprivilege.

  2. Assign the calculation view(s) that you want to secure with this analytic privilege.

  3. Choose the type of restrictions you want to use and define the restrictions.

  4. Set the secured calculation views to check analytic privileges.

  5. Deploy the analytic privilege.

  6. Assign the analytic privilege to a role.

  7. Assign the role to a user.

The next section breaks down the steps in detail:

1. Create the Source File

  • Select a folder in your project.

  • Choose New File.

  • Provide a name and be sure to use the extension .hdbanalyticprivilege.

  • Click Rename and enter a label.

2. Select Calculation Views

An analytic privilege definition contains the list of calculation views to which it will apply. You can include any type of calculation view.

3. Define the Restrictions

There are three methods available to define the restrictions in an analytic privilege. These methods are exclusive; that is, in one analytic privilege, you use only one of these methods.

Restriction Types in Analytic Privileges

Restriction typeHow to UseRestriction Example
AttributeWith the restriction editor, select one or several attributes from the secured views. For each of them, define restrictions.
  • REGION: EMEA

  • YEAR: Between 2015 and 2017

SQL ExpressionCreate a valid static SQL expression that refers to the attributes and the authorized values. This is useful when the Attribute restriction type does not fulfill the requirement.("REGION"=’EMEA’ AND "YEAR"=’2015’) (valid SQL expression)
DynamicUse a procedure to derive a dynamic SQL expression to restrict the data set. This expression must be similar to a WHERE clause in a select statement.P_DYNAMIC_AP_FOR_REGION (name of the procedure)

Defining a Restriction of Type Attribute

Defining Values in the Restriction Filter

To define values in the restriction filter, you can use the following operators:

  • Between <scalar_value_1> <scalar_value_2>

  • ContainsPattern <pattern with *>

  • Comparison operators: =, <=, <, >, >= with <scalar value>

  • IsNull and Is Not Null

Note

All filter operators, except IsNull and Is Not Null, accept empty strings (" ") as filter operands. For example:

  • In (" ", "A", "B")

  • Between (" ", "XYZ") (as lower limit in comparison operators)

Only columns of type Attribute (NOT Measure) can be specified in dimension restrictions.

If a DIMENSION calculation view contains a parent-child hierarchy and the hierarchy is enabled for SQL access, it is also possible to define the restriction on a hierarchy node.

Combining Several Attribute Restrictions

Several restriction filters within an analytic privilege are combined in the following way:

  • Within one attribute column, several restrictions are combined with a logical OR.

  • Within one analytic privilege, all dimension restrictions are combined with a logical AND.

For example, if an analytic privilege includes two restrictions on two different attributes (YEAR=2017, COUNTRY=US), the user is allowed to see only data fulfilling the compound condition YEAR=2017 AND COUNTRY=US.

Several Analytic Privileges Applying to the Same View

If two analytic privileges (or more) are defined to apply to the same view, SAP HANA combines the corresponding conditions with a logical "OR".

Caution

If the two restrictions from the previous example were defined in two different analytic privileges applying to this user and this view, the user would see more data. Namely all the rows for which YEAR=2017 OR COUNTRY=US (that is, any year for COUNTRY=US and any country for YEAR=2017).

Defining a Validity Period

You can decide to make an analytic privilege of the type Attribute valid for only a certain period of time. This restriction applies to the date when users are querying the secured view (CURRENT_DATE).

Caution

This is not related to the time attributes (year, month, date, and so on) defined in your views. To limit the access to the data based on such attributes, you use a classic attribute-based restriction, for example on a DATE or YEAR column.

Restricting Value with an SQL Expression

This second type of restriction allows you to define a filtering expression that cannot be obtained with restrictions of type Attribute. For example, when the precedence of OR and AND logical operators does not correspond to what you want to define.

Suppose you want to allow a user to view all the data from the country he’s responsible for (US), but also all the historical data for any country. You could create a restriction of type SQL Expression as follows:

Code snippet
("COUNTRY" = 'US' OR "YEAR" <= '2016')
Copy code
Hint
Instead of writing your SQL expression from a blank page, you can start defining a restriction of the type Attribute, and then convert the restriction type to SQL expression. The corresponding SQL code will be already generated for you, and you will just need to adjust it to your requirements.

Defining a Validity Period

Restrictions of the type SQL Expression also support a validity period, by adding to the SQL expression the relevant SQL code to filter the CURRENT_DATE.

For example:

Code snippet
(CURRENT_DATE BETWEEN '2017-07-28 00:00:00.000' AND '2017-07-28 23:59:59.999')
AND < SQL expression to define the restriction >
Copy code

Switching Restriction Type

Note

A restriction of type Attribute can be automatically converted into an SQL Expression restriction, but the other way round is NOT possible.

To define a restriction of type Attribute, you can select among the list of shared and private attributes from the secured calculation views. You can define as many restrictions as needed to define the correct data set.

Creating a Dynamic Restriction Type

In an analytic privilege, in addition to static values filtering conditions, it is possible to determine the filtering conditions in a dynamic way, which means that the filtering condition is not defined once for all, but is evaluated dynamically when the view is executed. This is called Dynamic Analytic Privilege.

With a dynamic restriction, the filtering conditions that apply for a specific user are determined at runtime. This allows a more scalable approach, where the same analytic privilege can be granted to several users who have different authorization requirements.

For example, the COUNTRY attribute in one or several calculation views can be filtered dynamically based on the actual list of countries that each is allowed to access, depending on his position in the geographical organizational structure.

Technically, to implement a dynamic restriction, you assign to the analytic privilege one procedure, which returns a SQLScript expression to filter data, like in the WHERE clause of an SQL statement.

For example, a procedure could return ("COUNTRY"='US') for User1 and ("COUNTRY"='UK' OR "COUNTRY"='FR') for User2.

This procedure must have the following properties:

Dynamic Restriction - Procedure Properties

  • Procedure must be read-only

  • Security mode must be DEFINER

  • No input parameters

  • Only one scalar output parameter of type VARCHAR(256) or NVARCHAR(256)

4. Set the Secured Calculation Views to Check SQL Analytic Privileges

To actually secure a calculation view with an analytic privilege, you must set the Apply Privileges property of the calculation view to SQL Analytic Privileges.

5. Deploy the Analytic Privilege

During the deployment of calculation views and analytic privileges, a specific dependency check is triggered to avoid errors that would lead to unsecured calculation views. You get a deployment error in the following cases:

  • If you try to deploy a calculation view after activating the check for SQL analytic privileges but NO analytic privilege has this view in its Secured Models list.

  • If you try to deploy an analytic privilege but some of the (runtime) calculation views it secures do NOT have the property Apply Privileges set to SQL Analytic Privileges.

  • If you try to deploy a calculation view after deactivating the check for SQL analytic privileges but there is still one or several (runtime) analytic privileges that have this view in their Secured Models list.

Note
The best approach is to build in parallel analytic privileges and the calculation views that they secure.

6. Assign the Analytic Privilege to a Role

Once an analytic privilege is deployed, the calculation views it applies to cannot be viewed until the privilege is granted to the end user.

To do so, in your project, you create a design-time role and grants the new analytic privilege to this role.

7. Assign the Role to a User

The last step is to grant the role to the end user.

Definition of Restrictions on Hierarchy Nodes in Analytic Privileges

SAP HANA supports attribute restrictions based on a hierarchy node (rather than a list of values, and interval) in analytic privileges.

This is useful in scenarios where a user who is enabled to a certain node of the hierarchy must also be enabled to all the descendants of this node.

With a geographical hierarchy, for example, the manager of the North America area will see all the countries in his area if the analytic privilege sets the attribute restriction to the NA node. You do not have to list all the countries from this area.

To Use a Hierarchy Node in an Attribute Restriction

  1. Enable the calculation view for analytic privilege check and also for SQL access to hierarchies.

  2. Create an analytic privilege:

    • Add the calculation view to the list of secured models.

    • Create an Attribute restriction type.

    • From the section Hierarchical Privilege select one of the available hierarchies..

    • Choose a hierarchy node value.

Caution

This feature is supported:

  • With calculation views of the type CUBE With Star Join that are designed to check SQL analytic privilege and enable SQL access to hierarchy.

  • Only for parent-child Hierarchies.

Defining Data Access Security with Nested Calculation Views

There are many business cases where calculation views contain references to one another.

The figure, Nested calculation views - Data Access Security Principles, explains how data access is handled when calculation views are nested.

For each calculation view, the following criteria are considered:

  • Does the user have SELECT privilege on the column view (this is the actual database object generated from a calculation view) in the container schema?

  • Does the calculation view check analytic privileges?

  • Is the user granted analytic privileges for the view?

Nested Calculation Views - Data Access Security Principles

The key rules that govern the access to data are as follows:

  • Object privileges

    There is no need to grant SELECT privileges on the underlying views or tables. The end user only needs to be granted SELECT privileges on the top column view of the view hierarchy.

  • Analytic Privileges

    The analytic privileges logic is applied through all the view hierarchy.

    Whenever the view hierarchy contains at least one view that is checked for analytic privileges but for which the end user has no analytic privilege, no data is retrieved (not authorized).

    Note

    In the figure, Nested Calculation Views - Data Access Security Principles, this is the case for View 5, so View 4 will not retrieve any data.

    This is also the case for View 6, and in addition, the end user is not granted a SELECT privilege on View 6. So View 6 will retrieve no data (not authorized).

Note that the end user always needs an explicit SELECT privilege on a calculation view to be able to query its data. That is, granting an Analytic Privilege to this user does not also grant an implicit SELECT authorization on the views that this Analytic Privilege secures.

Secure Calculation Views

Enable Calculation View for Privilege Check

Watch this video to learn how to enable Calculation View for privilege check.

Define Analytic Privilege

Watch this video to learn how to define Analytic Privilege.

Define Role

Watch this video to learn how to define role.

Assign Role to User and Test

Watch this video to learn how to assign role to User and Test.

Save progress to your learning plan by logging in or creating an account