SAP HANA: Workaround for implementing Column level security with SAP AO on HANA
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
Hi Krishna,
I think it's an option yes but it depends on the usage of it.
There are some hidden points on this, specially because you named as "Column level security" and this bring to my mind auditing, and from auditing point of view you don't provide standard support. To answer who saw an information you will need create additional reporting to cross a table change log of this customizing to report call.
About performance it's a price to pay for calculated columns as it's being checked as "visible / not visible".
At least the usability. I'm wondering a user that doesn't have too much data looking for zeros or empty columns if he will immediatelly know that don't have access or if it's really zero. They will achieve the conclusion alone or will open a support ticket?
With these points in mind I'd use it only in specific scenarios, in fact currently I can't imagine one that I could go for it.
Key points:
- security : it's necessary enhance the solution
- performance : depends on filtering, how many entries need to be summed up.... need to test in fact
- usability : depends on how many columns will be provided, how many will be hidden, which kind of users will be affected
For this situation I'd go for create one information view with all data with reporting disabled and create one new calculation (wrapper) to be used and apply the security on this one. The cons is not so flexible / customizable than yours.
My 2 cents
Best regards, Fernando Da Rós
Interesting solution. I would echo some of Fernando's points.
- Since the column is still technically present, just populated with zeros, it could be confusing to an end user who is not really aware of what this means. The latter suggestion given by Fernando to control the view of columns with a wrapper is the best of both worlds for reuse and customization.
- Calculated columns that have string expressions/case statements do carry a cost that would need to be evaluated on larger data sets.
Good thought process, thinking out of the box a bit.
Happy HANA,
Justin
Thanks Fernando Da Ros and Justin Molenaur for your valuable inputs.
Usability -- As we are using Analysis Office as the reporting tool here being a adhoc reporting tool. The column will be present in the "Selections" but not shown in the report by default. And even if the user tries to select the column in the report , he will still see 0's ( thereby enforcing the security)
Loading -- Though I should have used case statements on Integer flags, but I'd agree with you guys on the performance cost due to calculated columns.
Auditing -- This is one point which has to be taken care.
With being no exact information on when the column level security feature will be added to the HANA. At this time we can propose both the solutions along with their pros and cons d leaving the decision to the client himself 🙂
Regards,
Krishna Tangudu
Nice approach ,thanks for sharing 🙂
Hey Krishna Tangudu - Great blog!
I attempted this approach and we are getting an error "SAP DBTech JDBC: [258]: insufficient privilege: [2950] user is not authorized : " unless we provide CONTENT_ADMIN role to the end user. Do we need some special privileges to capture SESSION_USER?
Thanks in advance for your help.
Abhi