SAP HANA: Workaround for implementing Column level security with SAP AO on HANA

[former_member182302]

Hi SCN,

This document is a result of this thread: Can we have column level security i.e desired column list using HANA models?

We do not have a straight forward approach for this. However have found a work around ( which has both Pros and Cons) , which will be explained in this document  with a hope that helps in directing some one who is also currently struck with a similar problem.

Also would be happy if experts here can correct my approach and hence creating this as a document which can be collaborated.

Problem Description:

In this View, we have 3 measures columns i.e Invoice Qty, Order Qty and Revenue.

And depending on the user logged in,we should determine whether the user can see the measure or see only 0.

If he is not authorized he will see the column in the output, but the values will be always 0.

Solution:

1) Mapping Table between USERs and Columns list:

Create a Mapping Table as mentioned in the below format

Column List in the table:

1) USER_ID

2) ORDERED_QTY ( Measure 1)

3) INVOICED_QTY ( Measure 2)

4) NETREVENUE ( Measure 3)

You need to keep the all the measures accessible for that application in this table.

If the Measure is accesible, then mark the field as 'X' else 0

So as per the data above,

KT: Can access ORDERED_QTY, INVOICED_QTY

TEST_USER: Can access only NETREVENUE

**Note: Apart from these 2 Users, if any one else tries to access he will not see any data ( 0 records)

2) Calculation View to know the user logged in:

Create a calculation view (CV_AUTH) which will give the output of USER ID along with column list he is accessible for.

If you see the code above, I am using SESSION_USER to know the User who logged in and am also including a dummy column which would help me to join this model to our reporting model.

3) Joining CV_AUTH to the Reporting Model:

Have created a similar dummy column in the Reporting model and then joined it with CV_AUTH on column dummy as shown below:

Now we have to hide the actual measures and create a calculated measures with these formulas:

1) Ordered_Qty:

2) Invoiced_Qty:

3) Net_Revenue:

RESULT:

Output (KT):

Output ( TEST_USER)

Output (SYSTEM)

Thus we are able to achieve what we intended.

Advantages of using this approach:

1) Number of models to be created to handle security will be only 1

2) Maintenance of the users and security will be easy with the mapping table.

Thanks for reading this blog and would like your inputs on further improving this document. Please do share your comments

Yours

Krishna Tangudu