SSAS Dynamic Security

Imagine the scenario where there are hundreds or thousands of cube users, most of which are only permitted to see "their" data. And there is a dimension with an attribute containing their Windows user id. 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.

http://3.bp.blogspot.com/_SHRRgijB18E/TLDqZj9eE6I/AAAAAAAAAbM/vaMZaYK2qSY/s400/DySecurity.JPG

A technique 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, 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 user id.

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. 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. Let's combine 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 most experiences 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.