Analysis Services Dynamic Security

Who is online?  0 guests and 0 members
Home  »  Articles  »  Analysis Services Dynamic Security

Analysis Services Dynamic Security

change text size: A A A
Published: 6/17/2010 by  DevinKnight  - Views:  [7053]  

                Imagine you have a requirement to capture the user accounts that log into your cube and based on their username they will only be permitted to see specific data.  This can be accomplished by creating dynamic or table based security in Analysis Services which is much less difficult to setup than it sounds like.  In this article I will walk you through the setup needed on the relational database side as well as Analysis Services side to get this working in your environment. 

                You will start by identifying what attributes you want to base your security off of.  For example, maybe I want User1 to only be able to see the sales for the states Florida, Georgia and Louisiana, while User2 can see California, Oregon and Washington.   Typically, if I were to create this security I would create a role for each grouping of states and add the appropriate users.  An easier way to maintain security like this example would be to store all this information in relational tables that way a DBA that may not be as familiar with Analysis Services can easily handle adding new users or modifying existing ones.  This table structure would look something like this:

FactSales is storing all the company sales, while DimState has all the possible distinct states that sales can occur in.  This is a very small scale example, but here’s in detail what DimUser and UserStateBridge tables store:

DimUser

                This table simply has a key which is an identity column in this case and an account column which is the user account that you wish to grant access.  So example records would look like this:

UserID

Account

1

Domain\User1

2

Domain\User2


UserStateBridge

This is a table that has a many-to-many relationship between the DimUser table and DimState.  This many-to-many relationship allows for one user to see many states and one state to accept many users.  Here’s a sample of what this table may look like:

UserID

StateID

1

5

1

27

1

8

1

14

2

42

2

27

2

6


I’m not going to show you what the State dimension would look like because this is just a typical dimension you see day to day.  The StateID here is the identity column from the state table.

Once this table structure is created your next step is to bring it into Analysis Services.  Here are the steps to follow inside BIDS to leverage these tables (I’ll assume you’ve already created a basic cube first):

·         Add these new tables (DimUser and UserStateBridge) to you Data Source View.

·         Create a new dimension based off the DimUser table.  Hide all the columns by changing AttributeHierarchyVisible to False.  You don’t want users to be able to view this dimension.  Process this dimension.

·         Create a new Measure Group in the cube based off the UserStateBridge.  Delete all measure it creates except for the automatically generated Count measure.  Select the single measure left to be hidden by selecting it and changing the Visible property to False.  This will hide the entire Measure Group from your users but you can still use it when writing MDX.

·         Ensure these two have a Regular relationship setup in the Dimension Usage table of the cube browser.  Often this relationship is created automatically.

·         In the Solution Explorer, right-click on Roles and select New Role.  Give Read access in the General, Data Sources and Cubes tabs.  On the Membership tab add any groups or user accounts this Role will effect.

·         On the Dimension Data tab select the State dimension from the dropdown list.  Then select the StateID(you would likely have this column hidden in the dimension) in the Attribute Hierarchy dropdown list.

·         Inside the Dimension Data tab select the Advanced tab.  In the Allowed member set area enter this MDX:

Template

Sample

NonEmpty (

[Dimension To Secure Name].[Dimension Key].Members,

(

StrToMember ("[User Dimension].[User Key].[" + UserName () + "]"),

[Measures].[Bridge Table Count Measure]

)

)

NonEmpty (

[State].[State ID].Members,

(

StrToMember ("[User].[User ID].[" + UserName () + "]"),

[Measures].[User State Bridge Count]

)

)


The UserName() function will grab the user account that attempts to access the cube.  StrToMember converts a literal string to a dimension member.

·         Last in the bottom left of your current screen check the box called Enable Visual Totals.

·         Reprocess the entire SSAS database now.  When you navigate to the cube browser now you can test and make sure that your security is working properly by emulating one of the users you’ve added to the role.

After verifying you security in the browser if you still find problems with the results then your next step is to check the user and bridge table.  Again this is a very basic example of creating dynamic security so if you did something more complicated like based a role off of two bridge tables then you may run into conflicts between the two.  For example, say you not only want to base your security off of state but also on the store the items was sold at.  In that case you would create two bridge tables.  One for state, which we’ve already done, and a second for store built the same way the first one was.  So image your fact table had some results similar below:

StateID

StoreID

SalesAmount

3

4546

1000

23

6434

1200

14

1554

900

43

7664

500

3

4578

1100

35

6789

3000


                When using multiple bridge tables in a single role you need to make sure that if User1 has rights to StateID 3 in the UserStateBridge table then he also needs rights to StoreID 4546 and 4578 in the store bridge table to be able to see each record when browsing.  This means the bridge tables can actually over rule each other if they do not have permission for both keys.  This is especially important if you’re using inferred members in you data warehouse using some kind of indicator like -1.  If there is a store in an unknown store and you assign unknowns to a -1 key value when you will want to make sure your users have access to that -1 store.

                Adding new accounts is very simple now.  Add them to the DimUser table and then reprocess.  If you need additional states associated with users simply add them to the bridge table then reprocess the cube. 

After reading this article if you still need more information on this topic I highly recommend the book Expert Cube Development with Microsoft SQL Server 2008 Analysis Services.  This book shows many different types of dynamic security including security based off of a stored procedure.  Also feel free to email me with any questions about this or any other topic at dknight@pragmaticworks.com.  You can also post on our forums at www.bidn.com/forums.

 
4.16
/5
Avg: 4.16/5: (1 votes)

Comments (20)

ClarkJeff
ClarkJeff said:

Devin,

If there are other higher levels, like Continent and Country, above State, will the user automatically be secured in those as well?  Or do you have to add security attributes to those levels?  Obviously, assuming that the State-Country-Continent relationships have to exist in the source tables already.  Thanks.

6/24/2010
 · 
 
by
DevinKnight
DevinKnight said:

If you pick the lowest level you should be good.  Anything above will be included.

6/24/2010
 · 
 
by
CurrentMember
CurrentMember said:

Devin,

Great article!  We had implemented something similiar in our cube.  This method works great. For others that may try it, just wanted to mention the added benefits one gets in front-end tools like SSRS with this model.   If you were to create a parameter list, for instance, populated with states, it would only be populated with the dimension members that are accessible to the current user.  This makes for reports that can be used by a variety of users without any special coding in the reports.  Visual Totals are cool in ensuring that the grand totals make sense for the current user while at the same time not divulging the whole pie (the "All" value for all states).  Thanks again for sharing.

Thanks Luke

6/29/2010
 · 
 
by
ClarkJeff
ClarkJeff said:

Devin,

Me again.  I'm trying to implement something like this, but am not getting it to work as expected.  Can you try to add a Country level above the State to see if the Country is automatically secured based on the lower State? 

My logic isn't exactly the same as yours, as I'm using IIF, Count, Exists, and Except expressions in the MDX to select specific members.  Even if I have the lowest dimension level Allowed Member Set defined, the parent levels are visible and are only secured when I add an appropriate Allowed Member Set in the parent level, too.

Ideally, I want to secure the lowest level and have Analysis Services secure the levels above it automatically without having to define the Allowed Member Sets in those levels, too.

Let me know if that doesn't make sense.  Thanks.

6/29/2010
 · 
 
by
DevinKnight
DevinKnight said:

Is it showing all of the higher level or just sections of it that you have not locked down on the lower level.  Sounds like this may be a case for multiple bridge tables if you need to secure both levels.  I do give some warnings about multiple bridge tables in this article though.

7/1/2010
 · 
 
by
ClarkJeff
ClarkJeff said:

Devin,

I got it working!  :)  As I mentioned earlier, I have custom MDX in the Allowed Member Set, which contains the EXCEPT function.  That function was returning the [All] member as part of the result set, which made it look like the security wasn't working.  As soon as I included .DefaultMember as a parameter in the EXCEPT function (to exclude the [All] member), everything started working as expected.  Thanks!

7/2/2010
 · 
 
by
MarkGStacey
MarkGStacey said:

Another use of having the security in relational tables, one that I often use, is that when creating parameters in SSRS, you can use the relational tables and join to the dimension tables, filtering by the current user.

 

The advantage of this over

A) not doing it - is that it doesn't give the user options he can't use

B) doing it in MDX - is that the query is simpler (a join rather than an EXISTS on the measure) and most likely more performant 

7/7/2010
 · 
 
by
mikedavis
mikedavis said:

Nice Article

7/12/2010
 · 
 
by
briankmcdonald

Nice article indeed! We are doing something similar for a client right now. And like Mark Stacey stated, it does make it easier to filter the results by the current user in SSRS.

7/29/2010
 · 
 
by
Vwylenzek
Vwylenzek said:

Devin,

Thanks for writing and sharing this article. We have implemented our dynamic security model exactly the same and it has been working for almost two years now.

It works perfectly in combination with Reporting Services 2005/2008. In Excel we are experiencing some vague problems. The most mysterious problem is in the next scenario:

User X connects to SSAS via Excel, with rights to see department A and B. At the beginning (=after SSAS restart or full cache refresh) everything works fine. After some slicing and dicing in a pivottable a black art magician comes into play and user X loses his rights to the cube and the strangest of all is that sometimes user X loses all his rights and sometimes it loses only a small part of his rights. Only a SSAS reset or a full cache refresh in SSMS will solve this problem. I already traced all the MDX queries of the users, but could not find something wrong. There was an issue with the SID, but correcting the SID did not help.

Have you ever bumped into a similiar problem?

Group (AD-group) security

Another limitation on this dynamic security thing, is that it only works on authorising Users and not directly on authorising groups.

I made an custom part in TSQL that connects to the AD and resolves the groupnames, but I do not think that this is the best solution.

Has someone made something similiar? I think the most 'beautiful' solution is to solve this in custom code, that can ben referenced in the MDX-filter part (other function o.s.)

but I really do not know how to implement this.

 

Thanks for your reply in advance.

 

Regards,

 

Vincent Wylenzek

 

 

7/29/2010
 · 
 
by
Vwylenzek
Vwylenzek said:

Devin,

Thanks for writing and sharing this article. We have implemented our dynamic security model exactly the same and it has been working for almost two years now.

It works perfectly in combination with Reporting Services 2005/2008. In Excel we are experiencing some vague problems. The most mysterious problem is in the next scenario:

User X connects to SSAS via Excel, with rights to see department A and B. At the beginning (=after SSAS restart or full cache refresh) everything works fine. After some slicing and dicing in a pivottable a black art magician comes into play and user X loses his rights to the cube and the strangest of all is that sometimes user X loses all his rights and sometimes it loses only a small part of his rights. Only a SSAS reset or a full cache refresh in SSMS will solve this problem. I already traced all the MDX queries of the users, but could not find something wrong. There was an issue with the SID, but correcting the SID did not help.

Have you ever bumped into a similiar problem?

Group (AD-group) security

Another limitation on this dynamic security thing, is that it only works on authorising Users and not directly on authorising groups.

I made an custom part in TSQL that connects to the AD and resolves the groupnames, but I do not think that this is the best solution.

Has someone made something similiar? I think the most 'beautiful' solution is to solve this in custom code, that can ben referenced in the MDX-filter part (other function o.s.)

but I really do not know how to implement this.

 

Thanks for your reply in advance.

 

Regards,

 

Vincent Wylenzek

 

 

7/29/2010
 · 
 
by
Tanya86
Tanya86 said:

Hi,

We have recently implemented this solution, and the security works, however we are experiencing serious performance issues, where if you log in as an administrator so no security is applied, the cube runs fine without any performance hit. Whereas if I sign in as a user who is part of the user dimension, it can take up to 10minutes to run a report or browse the cube in management studio. However once this first load has happened then subsequent queries run in no time.

It seems that when first running a query it evaluates the whole cube for security and creates a cached cube just for that user. Now if we only had a couple of users we could cache this for each user at the start of each day, but we have 200+ users who will access the reports.

Has anyone else run into performance issues?

Thanks,

8/12/2010
 · 
 
by
Maxui
Maxui said:
Hi Thanks for this it really helped us out. Just one thing that I would like to mention. The MDX should be applied to the State Dimension of the CUBE and not the state Dimension of the DB. I spent a few frustrating hours trying to work out why I got a Missing Member error. Thanks again
8/22/2011
 · 
 
by
Maxui
Maxui said:
Hi Just one other thing ~ make sure to check that you're not an admin on the SSAS instance when you're testing!
8/23/2011
 · 
 
by
DevinKnight
DevinKnight said:
Good point!
8/23/2011
 · 
 
by
sivavenkat4u
sivavenkat4u said:
Hi Devin , Thanks for the nice post . Can you help us in extending the functionality you discussed . Here is the detailed info on that . we have to implement security for the cube . We also the security info in relational database ,and we also made a dimension in cube out of it .when we implemented the security for a dimension like "Geography" as like suppose a user "A" has access only to country "US" then he should see only "US" when viewing the data. This scenario is working fine only when the user drags "Country" attribute of "Geography dimension" in to the row lables of EXCEL( as we report the data in EXCEL) , But this restriction has to be extended irrespective of whether a user drags the "Geography" dimension in to excel or not . So the User "A" has to see the data only for "US" when the "Geography" dimension is dragged or not(i.e he has dragged dimensions other than "Geography") in EXCEL .
4/3/2012
 · 
 
by
atrapp
atrapp said:
Hi Devin, I just read the latter part of this article and I have a question about multiple bridge tables. My fact table only has the key of one of the bridge tables (Users to Students) and then I join it to another bridge table (Users to Schools) through a related dimension (School Enrollment [Student/School relationship]). So there's a many-to-many relationship between the two bridge tables (Users-Students-Schools) through a dimension (Student Enrollment). The only place that security is needed however is on one of the bridge tables (Users to Students) but I still have to make sure that those in the Users to School bridge table only see the students in that school without having to add the Cartesian product to the Users to Students bridge table. Does this make sense? If so, can you comment? My struggle seems to be the appropriate set expression MDX to put in the role (Principals, in this case).
8/9/2012
 · 
 
by
marvo09
marvo09 said:
Hello Devin, I have gone through your nice post on dynamic security here but I have a question regarding the requirement that I have on dynamic security to see what you think about it. Here goes: I have a requirement to show the sum of values of a particular dimension member (manufacturer)in an attribute hierarchy to a user who is permitted to see it, then also the sum of the all the other members in the attribute hierachy without showing their names but replacing the names of all the members that contribute to that total as "ALL Others". I have implemented this using the Dimension data tab and visual totals of the role designer in the solution explorer of BIDS. Illustration: Manufacturer SalesAmount Hiets Ltd 1,545,000 Grand Total 3,545,000 So this shows only the contribution of Hiets Ltd for 1,545,000 and the Grand Total of 3,545,000, but the Grand Total shows the contribution of other dimension members not shown(Manufacturers). But what I want is Illustration: Manufacturer SalesAmount Hiets Ltd 1,545,000 All Others 2,000,000 Grand Total 3,545,000 So this shows the sum of the contribution of the other members without showing the names but rather "All others".Please how can I implement this using the Dimension Data Tab and visual Totals or however. The requirement does not want to see the details of other manufactures but only their contribution encapsulated as "ALL Others" Thanks
9/14/2012
 · 
 
by
Laxmi
Laxmi said:
Hi Devin, Thank you for nice and detail post on security. I need a help on below cube security : 1. I have 4 fact tables (GeoKey, A ); (GeoKey, B ); (GeoKey, C ); (GeoKey, D ); 2. GeoDimension 3. UserArea( UserAlias, AreaName) --its kind of bridge table to show which user has access to which Area 4. join GeoDimension with UserArea on AreaName Question: We have to apply Geosecurity to A and B measure only. AS all fact table have GeoKey.. when I do step 4 it applies security by default to all measures whereas I need only on A and B measure and for C and D it should show data for all area. If I skip step 4 and do dynamic cell security on A and B measures how do I specify through MDX to look for Area permission in userArea table for that user who is running the report and show data for only specific area instead of all. Could you please suggest. Thanks.
8/7/2013
 · 
 
by
asif
asif said:
Hey Sir...your article is great...i am following your article and stucked at the follwoing point... i have added two users in the db i.e. (1)Asif that can access departments a, b, c (2)Kasif that can access departments d, e, f and a bridge table between users and departments when i browse the cube the with the admin user then i can see the userid of asif with the respective department (a, b, c) and the user id of kashif with the department (d,e,f)... however when i change the user from the browser window the following error teases me..."The name provided is not a properly formed account name. ." i have changed my name in the db then change the user from the browser window every time the same error peeps me...what can i do? help me please...
12/17/2013
 · 
 
by
MarkFeltham
MarkFeltham said:
HI Devin, Great article, thanks I am having challenges with the dimension Data allowed member set step. I cannot get my business Unit dimension (the DimState equivalent) to expose it's key [Level ID] in the dialogue and I am wondering if it is because this is a Parent-child hierarchy. will this method still work with a parent-child Hierarchy? If so, any tips on how to expose the ID - I have already set the "attribute hierarchy visible" to true. Thanks Mark
12/23/2013
 · 
 
by

Most Recent Articles