rs, most of which are only permitted to see "their" data. And there is a dimension with an attribute containing their Windows userid. It would be very tedious to set up a security role for every individual. Not to mention the maintenance in adding and deleting roles. Although this could be somewhat automated with AMO scripting.A technique I like to employ is to set up just one SSAS role, which essentially contains everyone that might have some access to the cube (integrated security only). So, in the Dimension Data security tab, I restrict the "Allowed member set" to just those members where the member name is equal to the username(). Username() is an mdx function that returns the Windows userid.
The Allowed member set might look something like
{StrToMember("Branch.BranchManager.&[" + UserName() + "]")}
The default member could be
StrToMember("Branch.BranchManager.&[" + UserName() + "]")
You can add other roles, for example, one with read access to the entire cube. So the users that can only see their Branch, would be in the first role (they would only be able to see their own branch) and head office users in the second role would be able to see the entire cube.
This is a very elegant solution. As you can see, it requires no maintenance, as new/old users will automatically be able to see their own data. If the branch manager changes, the old user will have no read access and the new user will have read access to that branch.
There are many ways to extend this security. I have combined it with application tables that list users' access. This can be a many-to-one or many-to many relationship. No problem, just create a dimension (possibly many-to-many) , and grant read access to the dimension in the same way. Also, it might be that you only want part of the username, or that you want/don't want the domain. Again, that is no problem, you can use VBA functions in the MDX expression to get the substring that you want. You could also use this technique to manage a black list (Denied member set) instead of a white list (Allowed member set).
A classic use of this technique would be to support sales persons to see information relating to their customers. This technique might be combined with other restrictions. For example the dynamic security role might let browsers see a subset of their data, such as quantities, but not dollar values. And only for their customers.
Note, there is an overhead in Dimension Data security, but in my experience it is almost undetectable. Of more concern, if you needed to use it, would be the many-to-many dimension. Be wary of performance if your m2m dimension and/or m2m fact table is large.
Also note, you probably want to check the "Enable Visual Totals" check box, so that users can only see the total for members they are allowed to see.
The use of this dynamic security technique can be used in conjunction with conventional roles in the same cube.
12 comments:
Nice post Richard. I've implemented a variation of this with PerformancePoint using the CustomData() function instead of UserName().
Cheers,
Rod Colledge.
Yes, that's interesting, CustomData can be useful. However, since it is part of the connection string, how would you prevent someone from connecting directly to AS, specifying their own CustomData string?
Yep, that's true, and well understood. It's a temporary workaround until Keberos issues are revolved in the client's environment :-)
I understand. It is about time that the Windows team made it easier to enable Kerberos. It seems that even with Windows 2010, you still need a really good Windows engineer to enable Kerberos.
I have a problem related to this. The allowed set I'm using is
case when [Role Req Level].item(0) is [Resource].[Req Level].&[Manager]
then [Measures].Allmembers
else {[Measures].[Total Hours]}
end
[Role Req Level] is a named set which returns the Req Level based on username. This works but only with the first user that opens the cube. E.g. if the first user that opens the cube is a manager he will get all the measures. After that all the users will get all measures even though they are not manager level. If the first user that opens the cube is not a manager he will get only total hours measure. After that all the users will get only total hours measure even though they are manager level.
Do you have any suggestions to this problem? I have tried cell data also but it is very slow in Excel. In BIDS it works ok.
Tomi, sets are only created once. You should take the set out of the expression and replace it with the set expression. Another option, might be to use the new Dynamic keyword in the set declaration.
Thank you Richard. I moved the named set from calculations to the dimension data security. It works well.
Richard,
Have you ran across an issue where the dimensional data attribute permission you set stops allowing everything (including what you've defined to be allowed)? Neither that I have defined are numeric fields.
Curiously,
Brian Weber
No, I can't say that I have had that issue. However, I tend to run the MDX expressions in a query window to ensure that I am getting the correct result/set etc.
What suggestions would you have for this issue? Both of these have made the SSAS 2008 system very cumbersome to the success of the project.
Here are this and some other articles on Analysis Services Custom Security:
http://ssas-wiki.com/w/Articles#Custom_Security
I found the answer to my issue. It is a bug within SQL Server Enterprise 2008 SP1. I updated it to SP2 and it is now smooth sailing.
Thank you,
Brian Weber
Post a Comment