How to Create Analytic Privileges in SAP HANA – An Outstanding Guide

If you plan to work on SAP HANA and master it, then you can’t afford to miss SAP HANA Analytic Privileges. We will learn about this concept in detail here as it plays a crucial role in SAP HANA functioning. With the help of this tutorial, you will learn to create both types of analytic privileges i.e. Classical and SQL in easy steps.Analytic Privileges in SAP HANA

Hope you have completed the Joins in SAP HANA tutorial

What are Analytic Privileges in SAP HANA?

To start with understanding analytic privileges in SAP HANA, let us imagine a scenario. Suppose you work in an MNC. The company is based in the USA but has offices in other countries like India, Japan, Germany, China, etc. They have a central repository where sales data for all the countries is stored together. But they don’t want all the report developers sitting across the globe to access entire data regarding sales.

Then, what do they do? They restrict each report developer/user to access the sales data of their concerning region/country only. So, if you are working in India, you will only get to see and work with the sales data of India.

Thus, analytic privileges have the same job to do in SAP HANA, that is, restrict users to access only that portion of data that is relevant to them and their business roles from the whole. That is why they are named analytic privileges as they permit users to access a decided data set as their privilege.

Following the similar concept, analytic privileges in SAP HANA are data access restrictions defined and assigned to selected users. They work as data security tools for SAP HANA users and clients. Analytic privileges are created over information modeling objects and restrict users to see just a decided part from the information views present in that package or modeling objects. You can create an analytic privilege for calculation view, attribute view or analytic view.

Must learn – Steps to Create Analytic View in SAP HANA

Analytic privileges provide row-level data security to SAP HANA users as opposed to object-level security assigned by object privileges. Object-level security can only allow or restrict a user from opening an object, but row-level security allows or restricts users from accessing specific columns within an object.

A user to which an analytic privilege is assigned can only see the attributes and data related to it according to the condition defined while creating the privilege. For instance, if a user named Aron_UK has an analytical privilege where he can only see employee data for the year 2018, then, he cannot access data from any other year. Also, you can only select attributes to define analytic privilege and no measures can be selected.

Types of Analytic Privileges in SAP HANA

Now, we will learn two types of analytic privileges that you can create in SAP HANA.

i. Classical Analytic Privilege

The classical analytic privilege is the simple XML-based privilege which use SAP HANA Studio UI to create and assign basic privileges to users. Some features of classical analytic privileges in SAP HANA are:

  • Applicable on attribute views, analytic views, and calculation view.
  • It does not give the user, control over read-only access to SQL views and database tables.
  • Enables design-time modeling on SAP HANA Information Modeler and SAP HANA Workbench.
  • It does not allow design-time modeling on SAP web IDE for SAP HANA.
  • It is transportable.
  • Do not offer HDI support.
  • Do not offer complex filtering.

ii. SQL Analytic Privilege

SQL analytic privilege is SQL-based and allows users to create more complex restriction conditions than classic privileges. Users can apply complex filtering and restriction conditions based on information models and procedures. These privileges also differ slightly in terms of flexibility and options in creating privileges. Some important features of SQL analytic privilege are:

  • Applicable on attribute views, analytic views, and calculation view.
  • It enables a user to control read-only access to SQL views and but not database tables.
  • Enables design-time modeling on SAP HANA Information Modeler and SAP HANA Workbench.
  • It allows design-time modeling on SAP web IDE for SAP HANA.
  • It is transportable.
  • Offers HDI support.
  • Offers complex filtering.

Creating Analytic Privileges in SAP HANA

Moving further in SAP HANA Analytic Privileges Tutorial, we will learn the steps to create classical analytic privilege and SQL analytic privilege. The procedures for creating analytic privilege is nearly same for both classical and SQL analytic privilege. The SQL analytic privilege just differs when you need to write an SQL script to define complex formulations and restriction conditions which cannot otherwise be done using the XML-based user interface method.

Steps to Create Classical Analytic Privilege

Step 1: Open SAP HANA Studio and login to the system. All the folders within a system will be visible once you log in.

Step 2: Go to the Content node and right-click on the package under which you wish to create an analytic privilege. Click on New and Select Analytic Privilege from the options.

Step 3: Create an Analytic Privilege

Enter the name of the privilege, add a label/description. Also, you can change the package from here.

Select the type of privilege, create new or copy the format of existing privilege. Click on Next to continue.

Step 4: Select Information Models

From the next dialog, select the information views upon which you wish to assign the analytic privilege.

You can select any calculation, analytic or attribute view from the available list. To add a view into your privilege, select the view and click on Add.

Step 5: Next, the main analytic privilege will open to creating an interface. It divides into five sections. Let us discuss them one by one.

The first section is the General section. It shows the Name of the analytic privilege, Label, and Type. You can check the “Applicable to all information models” if you wish this privilege to apply on all the views containing the selected attribute.

  • Below, is the Secured Models section containing the list of secured models or information views that are available and on which you can apply analytic privileges.
  • The “Associated Attributes Restriction” section is where you select attributes from the selected view. To select an attribute, click on Add. Similarly, to remove an existing attribute, click on Remove.
  • In the “Assign Restriction” section, you can select the Type of restriction (procedural or fixed), Operator and Value based on which you want to put the data access restriction.

There are several operator types from which you can select such as equal, greater than, greater equal, less than, less equal, etc. For instance, we have set the restriction value as 2001 i.e. the user can only see data for the year 2001.

Explore all types of SAP HANA SQL Operators

  • Another section is the “Privilege Validity” section from where you set the validity period of the analytic privilege that you are creating. From the Inclusion option, include the From and To dates.

You can select the appropriate operator under the Operator column and enter a date or period (from and to) of validity.

After entering all the details, select the year 2001 from the attribute YEAR of the calculation view. Finally, click on the green execute button present on the upper bar.

Step 6: Next, we will assign the created privilege to a user. You do this by selecting a user from the Security node (suppose we have selected user DTF1).

This will open the user details pane. At the bottom, select Analytic Privilege tab and add the analytic privilege by clicking on the green plus + icon.

Step 7: Select Analytic Privileges

Search for the analytic privilege from the list using the search option. Then, click on OK.

The analytic privilege, you select will be added and applied for that user.

Step 8: You can also confirm the successful creation of the analytic privilege by looking in the Analytic Privileges folder under the relevant package (in your HANA system folder).

Step 9: If the user DTF1 opens the calculation view DTF_UNION, only the data for the year 2001 will be visible. Thus, it shows that the analytic privilege is successfully applied to the user DTF1.

Steps to Create SQL Analytic Privilege

To create SQL Analytic Privilege, select the privilege type as SQL Analytic Privilege when entering the name of the privilege in initial stages.

On the window of privilege creator, there are three options are available while creating SQL analytic privilege; Attribute, SQL Editor and Dynamic. All the three modes are for the users to create analytic privilege according to the level of complexity of restriction logic.

If you wish to create a simple privilege, then go with attribute option. If you wish to apply a filter condition through a SQL statement to define the privilege, then go for SQL Editor method. You can also apply complex filter conditions having sub-queries. And if your privilege has a complex formulation logic which involves procedures and state of the user, then select the dynamic option.

Don’t forget to check SAP HANA SQL Statements

We will discuss how to create a privilege from each.

Step 1: Attribute option – It is the same as creating the classic analytic privilege. Select an attribute from the view you have selected. Then enter a value for restriction for instance, “United States”.

Step 2: SQL Editor option – You can simply enter the restriction conditions as a SQL statement. You can make the logic as complex as possible by creating the script as per the requirement.

For instance, in our case, the restriction condition SQL statement would be:

((“SALES_COUNTRY” = ‘United States’))

Step 3: Dynamic option – Using this option, you can define a complex procedure and base data access on the outcome of more than one events or procedures.

For instance, we have created a procedure where only the user named DTF_USER has access to only the data related to the USA in the calculation view named CA_DEALER_SALES_SQL.

The complex procedure used is:

SELECT * from “PUBLIC”. "STRUCTURED_PRIVILEGES”
where ROOT_SCHENA_NAME = '_SYS_DTF'

AND ROOT_OBJECT NAME = ‘hana-modeling/COMP_SALES_SQL’
AND USER_NAME = 'DTF_USER'

Now, when the user DTF_USER open the calculation view CA_DEALER_SALES_SQL, all the data related to the United States will be visible.

Please note that no other SAP HANA user can access this particular set of data (related to sales only in the USA)  because they don’t have that analytic privilege assigned to them. For them to access the data, you need to add that analytic privilege to that particular user.

Summary

We hope this explanation on SAP HANA Analytic Privileges was useful for you. We know that this topic seems a little confusing at first because of the many steps and procedures it involves but it is a very important process from the data security point of view.

Any queries or feedback for us? Do let us know in the comment section below.

Take your career to new heights! – Explore the SAP HANA Career Opportunities

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.