Same user to multiple Roles in Multidimensional Model Role Security

Hi,

I want to apply dynamic dimension level security on my cube. I created two roles and assigned users to them. Everything worked fine till the time users did not overlap between roles. But as  soon as I added the same user in both the roles ,all security was nullified and the user was able to see the entire data.

I read that SSAS has additive role security. Both the roles are based on Different Dimensions. I am trying to find a workaround so that I can maintain the security even if the same user is assigned to two different roles. Any suggestions as to how this could be achieved would be highly appreciated.

July 20th, 2015 5:13am

Hi,

please see this,

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ee249cbf-afe5-41c5-ae91-a28d1917f5bb/security-implementation-failure-missing-feature-or-a-bug?forum=sqlanalysisservices#19576f88-62f6-42ce-bbbb-3b82dfe51669

Regards,

Manish

Free Windows Admin Tool Kit Click here and download it now
July 20th, 2015 5:25am

hi,

if want to implement Dynamic security

http://www.bidn.com/Blogs/userid/23609/analysis-services-dynamic-security

Regards,

Manish

July 20th, 2015 5:26am

My suggestion to have multiple cube. 

so when user user fall into two different roles, you can have two cube for them to chose. 

Like User is admin and manager ..then  1 cube for admin and 1 for manager. 

Else one role user will see only one cube.

Free Windows Admin Tool Kit Click here and download it now
July 20th, 2015 5:26am

I am already implementing dynamic security. The problem I am facing is when the same user is part of two roles that are adding security to two different dimensions. No ,I cannot create multiple cubes.
July 20th, 2015 6:38am

Hi,

you need to create two hierarchy for both user, see in this

example

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ee249cbf-afe5-41c5-ae91-a28d1917f5bb/security-implementation-failure-missing-feature-or-a-bug?forum=sqlanalysisservices#19576f88-62f6-42ce-bbbb-3b82dfe51669

in both role i have same user.

Regards,

Manish

Free Windows Admin Tool Kit Click here and download it now
July 20th, 2015 7:39am

Here's how additive security works in your case.

Role X limits access to dimension A, role Y limits dimension B. However, X doesn't limit B, nor does Y limit A. A user in both X and Y gets the sum of accesses from both roles, which means full B access from X and full A access from Y.

You can probably work around this with one role and unions in allowed set definitions.

July 20th, 2015 12:18pm

Hi Alexei, Thanks a lot for the detailed explanation.

I have created two roles one for account access and other for Contract access. The tables for these roles are as follows

Account Id   Username

ABC

Free Windows Admin Tool Kit Click here and download it now
July 21st, 2015 3:12am

Hi Nimo,

As a broad guess, you have some fact table to which your dimensions are both linked, and also some way of linking usernames with each of the dimensions, say user table and one bridge table for each of your roles.
Now we know that two roles do not work. So you make one, and its allowed set for accounts is (not exact syntax)

Union
(
nonempty ( accounts, { user_from_username, measure_off_user_account_bridge } ),
nonempty ( accounts, { user_from_username, measure_off_main_fact_table } )
),

which is a way of saying 'give me all accounts I see either directly or through contracts and main fact table'. For contracts, replace account references with contract.

July 21st, 2015 3:45am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics