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

• We need to restrict access by Reseller. So if a user from the "Roadway Bicycle

Supply" reseller queries the cube, they will only be able to see their sales and
will have no access to other resellers' information. We will demonstrate two
different ways to handle this requirement.

• We need to restrict access to certain Measures, both real and calculated.

In a real-world project, the security requirements must be agreed with the customer
and they should be specified before we start work on building the cube. The reason for
this is that the cube needs to contain the right information to apply the security rules. If
we leave the security analysis until after the cube implementation has finished, we may
end up in a situation where the cube doesn't contain some information that is needed
for security purposes.

Analysis Services security features
Analysis Services provides a rich set of security features that allow us to control
every aspect of how a user can interact with a cube. In this section, we'll describe
how Analysis Services security actually works, and the different types of permission
you can grant to a user.

Roles and role membership
Analysis Services uses roles to manage security. A role is a security group, and users
have to be members of one or more roles in order to access the cube. We can grant a
role the right to access any cube object or perform any administrative task.

Given our requirements, we will need to create several roles. We will need to
create roles to secure countries, so we'll have one role for French users which only
allows access to data from France, one for Canadian users that only grants access
to Canadian data, and so on. Similarly, we will also need to have one role for each
Reseller who is to access the cube. This may mean, of course, that we end up having
a large number of different roles; later on in this chapter, we'll show how you can use
a single role to handle many different sets of permissions.

Analysis Services uses roles to define security but a role, from the point of view
of the security model, is just a container. Security only comes into play when we
add users to a role. Therefore, we need to know what kind of users we can add to
a role. The answer is that the only users allowed to access a cube through a role are
Windows users.