Data Level Authorization in SAP HANA without assigning Analytical Privilege

Introduction

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.

Problem

In this blog we will see how to assign data level authorization to users without assigning analytical privilege.

Solution

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 ************/

 BEGIN 

   T_LogonInfo  = select   SESSION_USER,

     count(*) as COUNT 

     from Dummy ;

   T_ZUSER_ROLE = select COMPANYCODE,USERS

    from “KAARBI”.”Users_Table”;

var_out =  select T1.”SESSION_USER”,

sum(T1.”COUNT”) as “COUNT”,

T2.”USERS”,

T2.”COMPANYCODE”

FROM :T_LogonInfo as T1 INNER JOIN

:T_ZUSER_ROLE  as T2 on

T1.”SESSION_USER” = T2.”USERS”

GROUP by T1.”SESSION_USER”,

T2.”USERS”,

T2.”COMPANYCODE”;

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.

Output

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.

Conclusion

Using the above method we can achieve data level authorization without assigning analytical privilege to user.