Securing Dimension Data - Microsoft SQL Server 2008 Analysis Services - page 742

Dynamic Dimension Security
Analysis Services allows you to use any MDX expression to define dimension security.
Therefore, you can model your security system based on the data stored in Analysis
Services. For example, you can define an MDX expression that allows a user to see informa-
tion about the store where she works, but not other stores. However, such scenarios don't
fit well with the role-based approach because you would have to create an individual role
for each user of the system. You can instead use a data-based approach, called dynamic
dimension security , to address such scenarios.
With dynamic dimension security, you create a single role for all the users and use the
UserName MDX function inside the security definition to tailor the role for each employee.
When you deal with a large number of combinations of security permissions, this method
has a definite advantage because it is easier to maintain than other approaches. When a
new user is added to the system, you don't have to create a new role; the existing role is
automatically adjusted.
The UserName function returns a string that contains the domain\username of the
Windows user that connects to Analysis Services. You can use this string in the MDX
expressions that define dimension security to compare the username of the current
Windows user against the username of employees in your organization.
You can map the result from the UserName function to members of the cube in different
ways. One of the simplest methods (that fits our purpose) is to add a column that contains
the username of the employee and build a dimension attribute based on it. For example,
we added the user_name column to the dbo.employees table in the FoodMart 2008 rela-
tional database. We then created a new attribute, User Name , in the Employee dimension.
The KeyColumns property of the User Name attribute is set to employee.employee_id , and
the NameColumns property of the User Name attribute is set to employee.user_name .
As part of the example, we also created a new role, DynamicRole , and added Everyone as
member of the role. We have also created a cube Sales and Employees . This cube has a
measure group, Sales , which is similar to the Sales measure group contained in the
Warehouse and Sales cube, but it also contains the Employee dimension with granularity
set on the Store attribute.
Now you can use the User Name attribute to map the user who browses the cube to the
member of the Employee dimension. You can also define a set of members that allows you to
see only the store (or stores) where you work. To accomplish both actions, you can specify the
following MDX expression in the AllowedSet property of the Store cube dimension:
Filter(
[Store].[Store].[Store].members,
[Store].[Store].currentmember.properties(“key”) =
Exists(
[Employee].[Store].[Store].members,
StrToMemeber (“Employee.[“+UserName()+”]”, CONSTRAINED)
).Item(0).Properties(“key”) )