cancel
Showing results for 
Search instead for 
Did you mean: 

Column level restriction using graphical calculation view in SAP HANA

0 Kudos
1,417

Hi All,

There is a business requirement to create a graphical calculation view in SAP HANA to fulfill below criteria:

  • Only those columns should be visible to user to which the user has access to (Using predefined access table)
  • Eg. If User A is not authorized to access column B, D, F from CV_XYZ then those columns should not appear in output select query for CV. However on other hand if user B is authorized to see full data of CV then he should be able to read whole data of that CV.

In this scenario, predefined access table is explained below.

  • "POC"."T_ATTRIBUTE_ACCESS" is the table where the attributes that are accessible to the user A and B are set beforehand, where access table data contain below rows.

The expected output with above access table should be as below:

  • If CV_XYZ is queried by DB User A then at run time data of PERSON_ID, CUSTOMER_NAME, GEOGRAPHY, COUNTRY only should be visible.
  • However if the same CV, CV_XYZ is queried by DB User B then at runtime PERSON_ID, CUSTOMER_NAME, EMP_Joining_Date, EMP_SAL, GEOGRAPHY, COUNTRY, all these columns should be visible.

We would like to implement this in our project with graphical calculation views being called from user of same tenant or cross tenant.

Kindly guide us..

Thanks & Regards,

Krina

View Entire Topic
Abhishek_Hazra
Active Contributor
0 Kudos

Hi Krina,
What you are tyring to achieve, is not possible completely based on graphical way of modelling, or at least I am not aware of any such supporting ideas. If you are not having the possibility to use table function, I assume you can use a scripted calculation view instead. But I am afraid you need to create a new calculated column with respect to each column to use a reference to your authorization table. The calculated column can be defined as following : for example the one for COUNTRY :

CASE WHEN "VIEW_ATTRIBUTE_NAME" = 'COUNTRY' THEN "COUNTRY" ELSE '' END;

However, prior to this you need to make two things :

1. inner join with between the authorization table & the scripted calculation view/table function returning session_user on ID = SESSION_USER.

2. Referential join between the actual data with the above said join node.

On the referential join node you can define these calculated columns & add an aggregation node on top of it & add these calculated columns as aggregated columns as MAX since you are joining between two mutually transposed nodes.

Make sure you set the keep flag value set to 'True' on necessary fields to get the correct output.

You don't need a column for each user, but definitely need an extra column to be created for each existing column to apply the above said logic of showing actual value or blank.

The model would look somewhat like below :


Best Regards,
Abhi

0 Kudos

Hi Abhishek,

Thanks for the alternative solution shared here.

I tried creating the simple SQL scripted view in one tenant. the data preview is working for that tenant. However while calling the same view from different tenant (the connection is already established and working for all other cross tenant) it is throwing below error.

feature not supported: Cross database access of procedure/function is not supported
Abhishek_Hazra
Active Contributor
0 Kudos

Hello Krina,

Do you have cross database access configured as required?

It should be set to True & the target database should be configured correctly with necessary privileges.

Best Regards,
Abhi