Data Level Authorization in SAP HANA without assigning Analytical Privilege
In SAP HANA, data level authorization is usually done by assigning analytical privileges to users either statically or dynamically. So whenever a new user is created we will have to assign analytical privilege to that user. We have done a workaround for data level authorization to users without assigning analytical privilege by getting the logged in session user.
In this blog we will see how to assign data level authorization to users without assigning analytical privilege.
I have a calculation view ZCV_CC_SALES with fields Company_Code and Sales_Value.
I have a table: Users_Table with fields COMPANYCODE and USERS where company code for each users is allocated.
Now I have to restrict the data in calculation view according to the logged in session user. I have logged in with KAARBI user.
For that follow the below steps :
Step1: Create a calculation view ZCV_SALES_USER in HANA with SQL Script type.
Create output fields : SESSION_USER, COUNT, USERS,COMPANYCODE.
In the Script view write the below SQL code and activate:
/********* Begin Procedure Script ************/
T_LogonInfo = select SESSION_USER,
count(*) as COUNT
from Dummy ;
T_ZUSER_ROLE = select COMPANYCODE,USERS
var_out = select T1.”SESSION_USER”,
sum(T1.”COUNT”) as “COUNT”,
FROM :T_LogonInfo as T1 INNER JOIN
:T_ZUSER_ROLE as T2 on
T1.”SESSION_USER” = T2.”USERS”
GROUP by T1.”SESSION_USER”,
END /********* End Procedure Script ************/
This view will give the below result in data preview:
Where KAARBI is the logged in session user and we have got the company code allocated for this user.
Step2: Now we will join this calculation view with the initial view to restrict the data.
Join ZCV_SALES_USER – COMPANYCODE to ZCV_CC_SALES – Company_Code.
Now on the data preview of the this view ZCV_CC_SALES, we can see that the Sales_value per Company_Code is restricted according to logged session user’s assigned company codes.
So we have now achieved data level authorization without assigning analytical privilege. Below is the final data preview where the data is restricted according to the logged in user.
Using the above method we can achieve data level authorization without assigning analytical privilege to user.
Hi Milen Ann Abraham,
Thanks for sharing .I was waiting for someone to come up workaround solution .
Well written blog post.
Thanks for sharing this alternative method for analytical privilege assignment.
I tried this solution with some dummy data and it is working fine.
Thanks for this solution.
Came across this blog and seems to be interesting. I am gonna give a try.
Appreciate for this workaround. Was looking for such a solution
Good work. Will try this one.
Thanks for this workaround