question

SoniaSharma-6731 avatar image
0 Votes"
SoniaSharma-6731 asked CarrinWu-MSFT commented

Restricting Base Attribute of the Calculated Measure SSAS

We have a scenario where are looking some help without compromising security. Please let me know if anyone has any pointers

Scenario :
Table1[A] is confidential column. Which user should not be able to access directly.
Calculated Aggregated Column Table1[B] (on Top of Table1[A]), should only return Value if there are more than 2 combination of record present in table else it should Blank. (we have a DAX to control this as it should be done on run time based on slicers).

Requirement :
Table1[A] should not be accessed by user in any scenario. User should be able to access Table1[B] . (Dax will take care of exposing the value or blank)

Problem :
Hiding the column Table1[A], does not guarantee confidentiality as technically there are ways to pull hidden columns.
Security Roles (Column Level) are also not help because if base column is restricted then derived measures are also not accessible.

Tried Approaches :
• Column Level Security : This is restricting the access of Table1[B] as well as it is dependent on Table1[A].
• Access control via Perspectives.

Looking forward for some suggestions to control it via inbuilt capability of SSAS or any custom design suggestions.

Please reply me if you have any pointers.

sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SoniaSharma-6731, we have not get a reply from you. Could you please do "Accept Answer" if below anwsers helped you? By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @SoniaSharma-6731,

Welcome to Microsoft Q&A!

Is it a tabular model? If yes, please take a look for Column-level security:
119371-column.png


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



column.png (63.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SoniaSharma-6731 avatar image
0 Votes"
SoniaSharma-6731 answered SoniaSharma-6731 commented

Hi @CarrinWu-MSFT , Thanks for your response. Object level security doesnot help because Securing Column Table1[A] will also make derived measure Table1[B] inaccessible ...

We want to expose measure still making sure base attribute is secure ..

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SoniaSharma-6731, sorry for the late reply. May I ask which model do you used, Tabular model or Multidimensional model?

0 Votes 0 ·

Hi @CarrinWu-MSFT , ITs Tabular model.

Thank you.

Regards,
Sonia

0 Votes 0 ·
SoniaSharma-6731 avatar image
0 Votes"
SoniaSharma-6731 answered

Hi @CarrinWu-MSFT , Any other option that can be tried? Any Masking / Encryption option that can be tried?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered SoniaSharma-6731 commented

Hi @SoniaSharma-6731, have you try to consider that create a bridge table? Please take a look for DYNAMIC COLUMN LEVEL SECURITY WITH POWER BI AND SSAS.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @CarrinWu-MSFT , Thank you so much for sharing the link, I really appreciate your help!!!
While above is an interesting problem, It doesnot help in our current approach.
The reason is that column level security on base attribute will also make other measures inaccessible built on top of it.
In our scenario we want to secure base column Table1[A] but still want to expose measure Table1[B] to user.

The above highlighted scenario is consistent across all the roles.
Any other lead?

Thank you once again :-)


0 Votes 0 ·
AlexeiStoyanovsky avatar image
1 Vote"
AlexeiStoyanovsky answered

The Tabular security model doesn't work like you want, unfortunately. Measures are simply formulas for manipulating data accessible to users, and they don't have any security functionality. The objects that can be secured are tables, columns, and rows. After all your security rules are applied, an authenticated user has access to all the data from the combination of these securables allowed by these rules. Then, a simple EVALUATE is all it takes to obtain the data.
Thus with Tabular for your application tier, you have to leverage your presentation tier to satisfy these requirements.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SoniaSharma-6731 avatar image
0 Votes"
SoniaSharma-6731 answered AlexeiStoyanovsky commented

Hi @AlexeiStoyanovsky , I understand there is no straight forward way to achieve it with SSAS out of the box functionality.
Looking for any workaround.

Couple of questions running in my mind are :

  1. Is there any masking / Encryption technique that can help here? ex. encrypt the base column but show the measure?

  2. Is there any way we can restrict user to query DAX from Client tools. Our Reports are supposed to be generated via live connection only.

  3. This requirement is very easily achievable in SQL like Share a subset of columns via views to user still keeping underlying table secure. Why cant something same be achieved from perspectives in SSAS?

Really appreciate any pointer.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

  1. This could actually work if you manage to keep the definitions of measures from end-users, since that's where you'd have to store encryption keys as essentially plain text variables. Off the top of my head I'd say this metadata is not securable from users who can connect to the model. And, I fear for performance.
    By the way, is the model supposed to ever return any of the actual data for [A]? Or do you only care for the number of unique values for a set of filters a user selects? In the latter case, you could simply hash the data at the source making sure distinct counts remain the same. No decryption would then be necessary, problem solved.

  2. That's what I was referring to with 'presentation tier', and that'll depend on how and with what client tools the users will be accessing the model.

  3. Perspectives are not security tools by design, their purpose is to let users who want to view only a part of the bigger model to do so.




1 Vote 1 ·