Skip to Content
Technical Articles
Author's profile photo Milen Ann Abraham

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.

 

Assigned tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo srikanth v
      srikanth v

      Hi   Milen Ann Abraham,

       

      Thanks for sharing .I was waiting for someone to come up workaround solution .

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks Srikanth

      Author's profile photo Anu Mathey
      Anu Mathey

      Well written blog post.

      Thanks for sharing this alternative method for analytical privilege assignment.

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks Anu

      Author's profile photo Adam Evans
      Adam Evans

      Hi Milen,

      I tried this solution with some dummy data and it is working fine.

      Thanks for this solution.

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks Adam

       

      Author's profile photo Elandra Sirvoski
      Elandra Sirvoski

      Hi,

      Came across this blog and seems to be interesting. I am gonna give a try.

      Good one.

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks Elandra

      Author's profile photo Sandra Dsouza
      Sandra Dsouza

      Appreciate for this workaround. Was looking for such a solution

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks Sandra

      Author's profile photo Nicholas Andrew
      Nicholas Andrew

      Hi,

      Good work. Will try this one.

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks Nicholas

      Author's profile photo Varun Mahadev
      Varun Mahadev

      Nice blog

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks Varun

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks Anu

      Author's profile photo Jyane Jerome
      Jyane Jerome

      Thanks for this workaround

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Welcome

      Author's profile photo Michael Neil
      Michael Neil

      Good blog

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks

      Author's profile photo Yousifina Muhammed
      Yousifina Muhammed

      Nice try

      Author's profile photo Milen Ann Abraham
      Milen Ann Abraham
      Blog Post Author

      Thanks