In the article SAP HANA Modeling Introduction
and SAP HANA Calculation View
we explained the basics of SAP HANA data modeling. We also learnt how to create modeling views in Build Your First SAP HANA Model in 10 Minutes
In this article we will explain what is Analytic privilege in HANA and how does it work.
What is Analytic Privileges?
Analytic privileges control access to SAP HANA data models.
Analytic privileges are used to grant different users access to different portions of data in the same view
depending on their business role.
It allows us to maintain row-level access
Why do we need Analytic Privilege?
SQL privileges implement authorization at object level only. Users either have access to an object, such as a table, view or procedure, or they do not.
While this is often sufficient, there are cases when access to data in an object depends on certain values or combinations of values. Analytic privileges are used in the SAP HANA database to provide such fine-grained control of which data individual users can see within the same view.
Suppose there is a calculation view which contains the sales data of all the regions like Asia, Europe and America.
The regional managers must have access to the calculation view to see the data. However, managers should only see the data for their region. The manager of America region should not be able to see data of other region.
In this case, an analytic privilege could be modeled so that they can all query the view, but only the data that each user is authorized to see is returned.
Important Facts about Analytic Privileges:
- Analytic privileges are intended to control read-only access to SAP HANA information models, that is
- Attribute views
- Analytic views
- Calculation views
- Analytic privileges do not apply to database tables or views modeled on row-store tables.
Create and Manage Analytic Privilege.
Here is the sequence of steps to achieve this
- Create Analytic Privilege and assign restriction for region “Asia”.
- Assign the Analytic Privilege to User to restrict the data on HANA Views.
SAP HANA System Privileges Required to Create/Manage Analytic Privilege:
To create analytic privileges, the system privilege CREATE STRUCTURED PRIVILEGE is required.
To drop analytic privileges, the system privilege STRUCTUREDPRIVILEGE ADMIN is required.
In the SAP HANA modeler, repository objects are technically created by the technical user _SYS_REPO, which by default has the system privileges for both creating and dropping analytic privileges.
The database user requires the package privileges REPO.EDIT_NATIVE_OBJECTS and REPO.ACTIVATE_NATIVE_OBJECTS to activate and redeploy analytic privileges in the Modeler.
Steps to Create an Analytic Privilege:
We need to create the modeling view first which will be used in the Analytic Privilege.
Create a calculation view by following the article Create a calculation view in 10 minutes.
The output of the calculation view is
Let us see how we can restrict the output only for "Asia" region.
Right click on the package and select “Analytic Privilege”
Specify Name and label for the Analytic Privilege
Select the calculation view and click on Add button. Then click on “Finish”.
Click on Add button as shown below and select the column REGION_NAME.
Now we need to assign the restriction. Click on the add button as shown below and select the value “Asia”.
Save and activate the analytic privilege.
The analytic privilege is ready. Now we can assign this analytic privilege to any user.
Assign Analytic Privilege to a User:
: You must have authorization to create/assign privileges to a user.
Go to Security -> Users. Right click and create a new user. Specify user name and password.
Click on the “Analytic Privileges” tab and add the analytic privilege created in previous step.
You also need to assign following privileges required to get access to modeling views.
- Execute & Select access on _SYS_BI
- Execute & Select access on _SYS_BIC
- Execute on REPOSITORY_REST
Done!! We have created an analytic privilege and assign that to a user.
Now add the same HANA system using new user. Open the data preview of the same calculation view. It will show only the data for region “Asia”.