Securing the Cube - Expert Cube Development with SSAS Multidimensional Models - page 262

Even if it is very powerful, there are some issues to watch out for with Dynamic
security that need to be well understood. Over the next few pages, we will cover
the most important ones, as well as showing some concrete examples of how to
implement Dynamic security.

Dynamic dimension security
Let's take a look at how we'd go about implementing Dynamic dimension security
for resellers. If you recall, we want to define a single role that will be used by all of
our resellers. In this role, we want to apply dimension security so that each reseller
sees only their own sales.

First, we need to model the relationship between resellers and the users who should
have access to those resellers in our dimensional model. We might be tempted to
add a new column to the reseller dimension table that contains a username, but
this only allows us to associate one username with one reseller. It's very likely
though, that many different users will need to have access to the same reseller,
and probably, that a single user will need to have access to more than one reseller.
Modeling this relationship with a bridge table will give us much more flexibility than
adding another column to the dimension table will do. Our bridge table might look
something like this:

UserName Reseller

SQLBOOK\Alberto Rewarding Activities Company

SQLBOOK\Chris Roadway Bicycle Supply

SQLBOOK\Chris Requisite Part Supply

SQLBOOK\Marco Roadway Bicycle Supply

This table allows us to model the many-to-many relationship between users and the
resellers they have access to. Since we need to write an MDX expression to return the
set of allowed members, we will need to build a new measure group from this bridge
table. Here's what we need to do to add the objects we need to our cube:

• Create a new table, Security.Users , in our relational data source that

contains all the users needed to model reseller security

• Create a new bridge table, Security.Bridge_ResellerUsers , that models

the many-to-many relationship between users and resellers, as shown earlier

• Create a new Analysis Services dimension, Users , based on Security.Users

• Create a new measure group based on Security.Bridge_ResellerUsers

• Create regular dimension relationships between the two dimensions Users

and Reseller , and this new measure group