Expert Cube Development with SSAS Multidimensional Models (2014)

Chapter 9. Securing the Cube

Security, for an Analysis Services cube is a very important issue and one that needs to be properly understood and implemented. It's almost always the case that some form of security needs to be in place: the data in the cube may be extremely sensitive. For example, if a cube contains sales data for a whole company, we may need to make sure that each sales person can only look at his or her own sales and no one else's; equally, if a cube contains data regarding the profitability of a company, it's likely we'll need to ensure that only the finance department and the board have access to it.

Ignoring security during the initial analysis phase will inevitably lead to problems during the deployment of the solution. We can't stress this enough: security must be planned for in advance; it is such a complex issue that it cannot simply be left until the cube is in production.

In this chapter, we will discuss the Analysis Services security model, exploring the different ways to use it and highlighting best practices. As with the rest of the book, this chapter is not about where to click to add a role to a cube. Instead, we'll focus on how to solve the most common security problems, and how we can make the best use of the different security features of Analysis Services.

Sample security requirements

Before diving into technical details, let's outline the sample set of security requirements that we will use in this chapter to illustrate how security can be implemented.

Imagine we are building a BI solution for a multinational company, and we want to give employees and partner companies all over the world access to our cube. Here's what we will need to do:

· We need to restrict access by Country: Canadian users will be able to see only Canadian data, French users will only be able to data for France, and so on.

· 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.

If you are familiar with other Microsoft products, such as Internet Information Services (IIS) or SQL Server itself, you will know that they provide several different ways to authenticate users. Windows integrated security is always one option, but these products often implement some other forms of authentication. This is not the case for Analysis Services—it only supports Windows integrated security and no other form of authentication.

By "Windows users", we are referring to both domain users and local users from the server where Analysis Services is installed. Local users are often used when the cube is accessible over the Internet. In this scenario, there will probably be an Analysis Services server in a DMZ, with a copy of a cube that has been processed on another Analysis Services server from somewhere inside the corporate network and, since the server in the DMZ does not belong to a domain, we will need to use local users in order to access it.

Since we do not want to tie security to specific Windows users who will come and go over time; it's a good idea to restrict membership of Analysis Services roles to Windows user groups (and preferably domain user groups). We can then control who has access to the cube by adding and removing Windows users from these groups. This level of indirection frees us from the need to make changes in Analysis Services each time a new user needs access to the cube.

So in order to let users access the cube and enforce security, we will need to:

· Define Analysis Services roles, one for each set of permissions our users will have

· Define domain groups and add them to the roles. For the sake of clarity, we will use the same name for the role and the domain group

· Add domain users to these groups

This will only work if users are able to access the cube using Windows integrated security. There are, however, a lot of situations where integrated security cannot be used as a result of the presence of firewalls protecting the server or other types of network infrastructure that prevent integrated security from working. In these cases, we will need to use Internet Information Services to let the users connect to the cube via HTTP; we'll describe how to do this later on in this chapter.

Securable objects

Here is a list of the objects in an Analysis Services database that can be secured:

· Data Sources: This object lets us define whether a role has read access to a data source or to its definition.

· Cubes: This object lets us define which cubes can be queried by each role.

· Dimensions: This object lets us define which members on a hierarchy can be queried by a role. We can also change the default member of a hierarchy for a specific role. This applies to all hierarchies on all dimensions, including the Measures dimension.

· Cells: This object lets us apply security at the cell level, defining which cells in the cube can be queried by each role. When we refer to a cell, we are referring to a point in the multidimensional space of the cube that contains a value.

Obviously, different securable objects will have different permissions we can set. For example, we can grant permission to execute Drillthrough queries at the cube level, while this would make no sense for a specific dimension.

There are two basic types of permission we can grant a user in Analysis Services: permission to perform administrative tasks such as process a cube, and permission to access data within a cube. Here's a brief overview of what type of permission can be granted on each securable object:

Administrative rights are relatively straightforward and allow users to perform tasks such as processing a cube. Data security is much more complex and controls what access a role has to data in the cube; as such, we'll spend much more time discussing data security and how it can be implemented.

Creating roles

A new role can be created by right-clicking on the Role node in the Solution Explorer pane in SQL Server Data Tools. The new role will get a default name, but can subsequently be renamed. Upon creation, we can define the role's permissions in each of the following tabs in the Role Editor:

· General: This tab lets us to provide a text description what the role does, and define administrative permissions for the entire Analysis Services database. These permissions are not usually granted to regular users, only to administrators.

· Membership: This tab lets us add local and domain users and groups to the role. As stated previously, we will only use domain user groups so as not to tie the security to specific users. Local users and local groups are useful only when users need to connect to the cube via HTTP through IIS.

· Data Source: This tab lets us to grant access to data sources. This is only necessary when using the data mining functionality in Analysis Services; as data mining is outside the scope of this book, we can ignore this tab.

· Cubes: This tab lets us grant the right to access cubes and set various cube-level administrative permissions.

· Cell Data: This tab lets us to define security at the cell level, allowing the role to access data in individual cells within the cube.

· Dimensions: This tab lets us set various administrative permissions on our dimensions.

· Dimension Data: This tab lets us configure dimension security, allowing the role to access data for specific members on dimension hierarchies.

Membership of multiple roles

It's possible that a user can belong to more than one role, so as a result we need to understand what happens when a user is a member of multiple roles that have different permissions.

If you have worked with security in Windows, you will probably know about the "deny wins over grant" rule. This means that, if a user belongs to a group that grants access to a resource and at the same time belongs to another group where access to that resource is explicitly denied, the user will not have access to that resource because the deny in the second group wins over the grant in the first one.

In Analysis Services, there is no such rule. When a user is a member of multiple roles, that user will have all of the permissions granted to them in each of the roles—the permissions of each role are combined together. For example, if a user belongs to a role that can only see data for Canada and no other country, and another role that can only see data for France, that user will be able to see data for both Canada and France. Similarly, if a developer is a member of a role that gives them full administrative permissions, this will guarantee that they can perform all administrative tasks and see all data in all cubes, even if they are also members of roles with more limited permissions. This might seem strange but, like it or not, this is how Analysis Services security works.

Testing roles

If we as developers have full administrative permissions, then we need a way to simulate membership of more restrictive roles so that we can test they work properly. There are three ways of doing this:

· We can set the following two connection string properties when connecting to the cube. Connection string properties can be set in SQL Management Studio in the Additional Connection Properties tab of the connection dialog.

· Roles: This property takes a comma delimited set of role names; when this property is set, the user will then connect to the server as if they were a member of these roles. In order to use this option, the user needs to be either an administrator or belong to the roles in question.

· EffectiveUserName: This property takes a Windows username in the form DomainName\Username; when this property is set the user will then connect to the server impersonating this user. To use this property, the user must be an administrator.

· In SQL Server Data Tools, in the Browser tab of the Cube Editor, we can set the previous connection string properties by clicking on the Change User button in the toolbar. This is the easiest way to test security.

· Running the client tool (for example, Excel) as a different user, by using the Run As option on the right-click menu in Windows. Note that in different versions of the Windows operating system, this option is activated in different ways.

Administrative security

Administrative permissions can be granted at two levels in Analysis Services: at the server level and at the database level. To become an Analysis Services server administrator, a user must be a member of the special, server-wide administrators role. Users can be added to this role when Analysis Services is installed, or by right-clicking on an instance in the Object Explorer pane in SQL Management Studio, selecting Properties on the menu, and going to the Security page in the Analysis Services Properties dialog. Server administrators have no restrictions on what they can do; also, certain tasks, such as creating databases, can only be performed by server administrators. As a result, it's likely that we'll have to be a member of this role while we're developing our cube.

Database-level administrative permissions are granted in the database roles we've just been introduced to in the preceding sections. By checking the Full Control box on the General tab, we can grant role-full administrative permissions to the database. This means members of the role can create new objects, edit existing objects, process objects, and so on. In the unlikely event that we need finer grain control over administrative permissions, you can grant the following two permissions on either the entire database or individual objects:

· Read Definition: This allows members of a role to read the XMLA definition of an object, without being able to modify it or read data associated with it. If a user needs to be able to view an object in SQL Management Studio or BI Development Studio (while connected to the database in Online mode), they need to be a member of a role which has this permission.

· Process: This allows members of a role to process an object.

Data security

There are three different types of data security that we can implement: we can grant members of a role permission to access entire cubes; and we can control access to data within a cube by granting or denying access to individual cells (Cell security) or individual members on dimension hierarchies (Dimension security).

Granting Read Access to Cubes

Before a user can access any data in a cube, they need to be a member of a role that has Read permissions on that cube. A role can be granted Read permission on a cube by selecting Read on the Access drop-down box next to the cube name on the Cubes tab of the Role Editor as shown in the following screenshot:

If we have set up Writeback on our cube, we can also control whether the Role can write data back to the cube by granting the Read/Write permission instead. Last of all, if we want members of the role to be able to run Drillthrough queries and create local cubes, we can grant permission to do this by choosing the appropriate option from the Local Cube/Drillthrough Access drop-down box.

Cell security

Cell security is configured using three MDX expressions returning Boolean values. They are Enable read permissions, Enable read-contingent permissions, and Enable read/write permissions, as you can see in the following screenshot:

These three expressions let us define which cells in the cube can be read or written back to: they are evaluated for every cell in the cube and if they return true, we can read or write to the cell, if they return false, we cannot.

Let's look at an example. If, for example, we check the Enable read permissions box in the Cell Data tab in the Role Editor, and enter the following expression into the Allow reading of cube content box:

[Sales Territory].[Sales Territory Country].CurrentMember IS [Sales Territory].[Sales Territory Country].&[Canada]

Then we are stating that the user will be able to see only the cells where CurrentMember on the Sales Territory Country hierarchy of the Sales Territory dimension is Canada. If we query the cube, under this role, we will see this:

There are several interesting things to point out in the preceding screenshot:

· Even if the role is able to access data only for Canada, users will see that data for other existing countries. For all the cells the role can't read, the value returned is #N/A; this, however, can be changed by setting the Secured Cell Value connection string property.

· Granting access to a Country also grants access to all of the Regions in that Country. This is because there is an attribute relationship defined between Country and Region, so when a Region is selected in a query, the CurrentMember on Country changes to be the Country that Region is in. Granting access to members on an attribute therefore grants access to lower granularity attributes that have a direct or indirect relationship with that attribute. This does not happen on higher granularity attributes though, so values for the North America member and the Grand Total (which is the All Member on the hierarchy) cannot be accessed.

This last point raises another question: what will happen if a user queries the cube through this role without selecting anything from the Sales Territory hierarchy? We can see the answer in the left-hand side of the following screenshot—the CurrentMember on Sales Territory is the default member, which is the All Member, which of course the role cannot access data for. It's only when the user specifically selects Canada in the query, as shown in the right-hand side of the following screenshot, that any data is visible:

Cell level security can be used to control access to Measures too. For example, using this expression for the Enable read permissions MDX formula:

([Measures].CurrentMember IS [Measures].[Sales Amount])

OR

([Measures].CurrentMember IS [Measures].[Tax Amount])

We get this result:

Let's now take a look at Read Contingent permissions, and see how they are different from Read permissions. If we want our users to be able to see the Sales Amount for all Countries but Total Product Cost only for Canada, we could use the following expression in the Enable read permissions MDX box:

[Measures].CurrentMember IS [Measures].[Sales Amount]

OR (

[Measures].CurrentMember IS [Measures].[Total Product Cost]

AND

[Sales Territory].[Sales Territory Country].CurrentMember

IS [Sales Territory].[Sales Territory Country].&[Canada]

) OR

[Measures].CurrentMember IS [Measures].[Gross Profit]

Notice that we added the Gross Profit measure too, since we want our users to be able to see the Gross Profit for wherever he can access the Total Product Cost. Remember that Gross Profit is a calculated measure equal to Sales Amount minus Total Product Cost.

However, querying the cube, we get this result:

We can see that the Read expression worked correctly, but the users can see Sales Amount and Gross Profit in places where they cannot see the Total Product Cost. Clearly, by subtracting Sales Amount from Gross Profit, they will be able to determine theTotal Product Cost.

We can solve this problem by making the Read expression more complex, making sure that Gross Profit is only accessible when both Sales Amount and Total Product Cost are accessible, but doing this for all of the calculated measures in the cube would involve a lot of work. What we really want to do is apply a more general rule: we want to let our users' access values for a calculated member only when they can access all of the values that the calculated member is derived from. This is what Read Contingent security allows us to do.

If we remove the expression controlling access to Gross Profit from the Read security and add it to the Read Contingent textbox, then we will get the following result:

We can now see that users can only access values for Gross Profit when they can access values for both Sales Amount and Total Product Cost.

Dimension security

Dimension security allows us to control access to members on a hierarchy. It's less flexible than Cell security, but it's much easier to configure and manage, and in most cases, security requirements can and should be implemented with Dimension security rather than Cell security.

Let's go back to our first requirement: we want our users to only be able to see sales for Canada, and no other country. We showed how to use cell security to do this and we have seen that, when browsing the cube, while users do see other countries, they only see#N/A in the cells containing non-Canadian data. We can achieve the same result rather more elegantly using Dimension security, so let's describe how.

Here's the Dimension Data tab from the Role Editor, for the Sales Territory Country attribute from the Sales Territory dimension:

First of all, notice that there are two options to choose from on the left: Select all members and Deselect all members. They might seem to be two shortcuts to avoid having to click on each box in the tree view on the right-hand side, but there is more to them than that.

· Select all members: This means that all the members in the hierarchy will be accessible through the role by default, and we have to specifically uncheck the members that we do not want to be accessible

· Deselect all members: This means that no members will be accessible by default, and we have to specifically check any members we do want to be accessible

The difference between the two becomes clear when during the life of the dimension; new members appear in the hierarchy, for example, because new countries appear in the underlying dimension table. If we used the Select all members option, these new members would automatically be accessible to the role until we edited the role and unchecked them; if, however we used the Deselect all members option, these new members would not be accessible until we edited the role and specifically checked them. We can think at these two options as setting the default behavior for how to handle new members in the hierarchy. In most cases, it makes sense to use the Deselect all members option.

Armed with this knowledge, we can now choose Deselect all members and then check Canada. Here's what now happens when we query the cube through the role:

The Sales Territory hierarchy now appears as if Canada was the only country on it. However, even if we don't filter on Country in the Excel worksheet we will still see data because all of the members on the hierarchy above Canada are still there.

Visual Totals

When you secure dimension members, you need to pay attention to how the totals are computed. In fact, if we look at the preceding screenshot carefully, we can see something wrong. The Sales Amount for Canada is $16,355,770.46 USD but the Sales Amountfor North America is $79,353,361.17 and the Grand Total is $109,819,515.28. We've managed to deny access to the other countries in the hierarchy but their values are still contributing to the totals at the Group level and Grand Totals. This is a potential security loophole because, once again, a user might be able to derive a value they should not have access to from values they can see.

To stop this happening, go to the Advanced tab in the Dimension Data security window:

Look at the checkbox at the bottom of the page: Enable Visual Totals. This small checkbox controls one of the most powerful features of the Analysis Services security engine. By enabling Visual Totals, we can force Analysis Services to aggregate up data using only the members that are accessible through this role. This will mean that the North America Region and the Grand Totals in our example will now return the same values as Canada, as shown in the following screenshot:

We now have the exact result we wanted: members of the role can only see data for Canada.

What is the price of checking this box? It is potentially a high one. As we know, Analysis Services makes extensive use of aggregations during query processing to improve performance. If we use the Enable Visual Totals option, aggregations at granularities above the hierarchy we set it on can no longer be used because they contain values calculated using the entire hierarchy and not the subset of it that the role exposes. This means that when Analysis Services receives a query that contains the All Member on the Sales Territory Country attribute or any member above it, it will not be able to use certain aggregations and so performance may be worse. It's worth pointing out that there are situations where the Enable Visual Totals box can remain unchecked, for example, because the need to display unfiltered values at higher granularities is greater than any potential security concerns.

Let's now take a look at some of the other options on the Advanced tab of the Dimension Data security window. We can see in the preceding screenshot that there are three panes. The first two let us define an MDX set of allowed and denied members; this is simply the MDX representation of the selections made on the Basic tab that we discussed earlier: the role will have access to all of the members in the allowed set except for any members specified in the denied set. The third pane lets us define a default member on the hierarchy specifically for the role, which may be necessary when the role denies access to the original default member.

Restricting access to Dimension Members

A common requirement is to be able to deny access to an entire level in a hierarchy. In our example, we might want to give a role access to the Group and Country levels on the Sales Territory user hierarchy but not the Region level; this would mean that users could not drill down below the Country level at all. To do this, all we need to do is to deny access to all of the members on the Sales Territory Region attribute hierarchy except the All Member: select the Deselect all members option and then select the All Member. TheAllowed Member Set pane on the Advanced tab would therefore contain the expression:

{[Sales Territory].[Sales Territory Region].[All]}

The Basic tab will look like this:

Although, it's also an another fairly common requirement, it isn't actually possible to deny access to an entire dimension in a role. The only thing we can do is deny access to all of the members on the hierarchy, either by setting the Allowed Member set to an empty set in the following way:

{}

Or by only allowing access to the All Member on every hierarchy. Once we've done this, the dimension itself will remain visible, but there will be no members available on any of the hierarchies and so user will not be able to select anything on it. It gives us the result we want, even if the end result isn't very elegant.

Applying security to Measures

The Dimension Data security tab can be used to secure the Measures dimension in the same way as any other dimension. However, when we look at the Measures dimension, we might be a little surprised to see that only real, not calculated measures can be secured:

So, if we deny access to the Total Product Cost measure what will happen to any calculated members that depend on it? Here's a screenshot to show us:

Since one of the measures needed to perform the calculation is not available, the result of the calculation will not be accessible through the role. Nevertheless, the value displayed is very different from what we saw with Cell security.

The cube returns #VALUE, which indicates that there is an error in the definition of the calculated measure, which is strictly speaking, true – the Total Product Cost measure does not exist as far as this role is concerned.

One possible approach for avoiding this problem is outlined here: http://tinyurl.com/ChrisDimSecurity, but this is rather complex. An alternative would be to adapt the Date Tool technique from Chapter 5, Handling Transactional-Level Data, and create a new, empty physical measure group containing real measures whose value we can overwrite with MDX Script assignments, as described at http://tinyurl.com/SSAS-MeasureTool.

Tip

When is security evaluated?

More details on when security is evaluated during the cube initialization process can be found at http://tinyurl.com/moshascriptinit. The basic rule is that dimension security is evaluated before the MDX Script is evaluated, and cell security is evaluated after the MDX Script. This means that it is safe to reference named sets and calculated members in cell security expressions, but not in dimension security expressions. This is a simplification of what actually happens, however: in some cases it does appear to be possible to reference MDX Script objects from dimension security expressions but it can lead to some unexplained behavior so we do not recommend doing this.

Dynamic security

Armed with the knowledge we've learned up to now, we might think we're ready to create the roles that we need to handle our reseller-related requirements. If you remember, we need to grant each reseller access to the cube and allow them to see their own sales data, but not that of any other reseller. It sounds very much like what we've just done for countries, and indeed we can take the same approach and set up Dimension security on the Reseller dimension with one role for each reseller.

This solution works fine if we only have a small number of resellers that need access to the cube. However, a quick look at the Reseller dimension will show that we have 702 resellers, so potentially we would need to create 702 roles in order to let all these resellers have access to our cube. Moreover, if we needed to change something in the role definition, for example, because a new measure has been added and we don't want resellers to have access to it, we will need to update 702 different roles. The obvious solution, as simple as it is, is not powerful enough.

In order to solve this problem, we will need to use Dynamic security. The security roles we have seen so far have been static. This means that we have defined the set of accessible cells and members using MDX expressions that will return the same results for all the users that belong to the role. If we can write a single MDX expression that will return different results for each user, we will be able to use only one role for all the reseller users. This is exactly what we'll do when we implement Dynamic security.

Dynamic security uses one very important function: UserName. This function returns the name of the user (not the role) that is currently querying the cube. The username will be preceded by the domain name, separated with a slash, as is standard in Windows logon names. Thus, a possible return value for UserName is SQLBOOK\Alberto. You can use the UserName function in any MDX expression used to define security, and it is the key to writing MDX expressions that return different results for each user.

There is another function, which we will not show in our examples, which you can use to pass user information into MDX from the outside: CustomData. The CustomData MDX function returns a string that is the value assigned to the CustomData connection string property. Strictly speaking, CustomData is not a security-related function because any user can set this connection string property to any value and there is no validation at all carried on by Analysis Services. However, if the cube we are building will be queried only by other applications, in a multitier environment, it might be convenient to run all the cube queries under the same user account. In this case, UserName will always return the same value. The particular application that is querying the cube could set the CustomData connection string property to simulate impersonation of a different user for each query. Remember that you should not use CustomData in security if the cube is directly accessible by users because it would represent a potentially serious vulnerability.

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:

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

The relational data source for the security section will look like this:

Both the new dimension that contains reseller users and the new measure group should not be made visible to end users; they cannot be used in users' queries, and their presence might be confusing to them. However, as it's useful to be able to see them for debugging purposes, we might be tempted to create them as visible and then use security to deny end users access to them. Unfortunately, as we found earlier, we can't deny access to an entire dimension using dimension security, so setting their visibility is the only thing we can do.

Note

Setting an object's Visible property to False to hide it from end users is not the same thing as using security to deny access to it. Even if an object is not visible, it can still be used in a query and results returned for it.

After we've completed these steps, the Dimension Usage tab will look like this:

Now that we've added these structures to the cube, it will be very easy for us to write an MDX expression that returns the set of resellers each user has access to. The UserName function will return a string containing the username, and what we need is a way to transform it into a member on the User dimension to use it as a filter on the measure group we built from the bridge table. We'll be able to use the StrToMember function for this purpose: it takes a string containing the unique name of a member and returns the corresponding member object.

Note

Where possible, always specify the Constrained flag in the second parameter of the StrToMember function. Even though it is optional, using it has two benefits: it improves the performance of the function and more importantly, it prevents MDX injection attacks. However, in this case, we can't use it because we are dynamically generating the string we're passing into StrToMember, which violates the restrictions that the Constrained flag imposes.

The set definition we'll use is this:

NonEmpty (

Reseller.Reseller.Members,

(

StrToMember ("[Users].[User].[" + UserName () + "]"),

[Measures].[Bridge Reseller Users Count]

)

)

This expression filters the set of members from the Reseller hierarchy, returning only those members where the Bridge Reseller User measure group contains a value for the member on the User hierarchy of the User dimension that has a name that's the same as the string returned by UserName. By checking Enable Visual Totals, we end up with this role definition:

Note that users have access to the All Member too: enabling the Enable Visual Totals option will make sure the user only sees totals for the members that the user has access to. Here's what the users Chris and Marco will see when they connect to the cube through the role:

We can see that the two different users see different members on the Reseller dimension. Both users can see the reseller Roadway Bicycle Supply but Marco can't see Requisite Part Supply.

Dynamic security with stored procedures

The example we've just looked at uses data-driven security: it stores data on access rights inside the cube itself. Sometimes though, we will be faced with more complex requirements: we might, for example, have to call external authentication procedures through web services. This is definitely something that cannot be handled using MDX expressions alone.

In such cases, we can create a .NET assembly to handle the security logic and then call the assembly from an MDX expression. A .NET function, when called from inside Analysis Services, is called a Stored Procedure. Note that Analysis Services stored procedures should not be confused with SQL Server stored procedures—they're really just a way of creating custom MDX functions. Having the ability to call .NET code from inside MDX will, though, let us use the full power of .NET programming to define very complex security models.

In this section, we are going to perform the following actions:

· Create a new class library containing security authentication code in C#

· Upload this library to Analysis Services

· Call our stored procedure from MDX to return the Allowed Members set for Dimension security

The first step thing we need to do in our new C# project is to add a reference to the msmgdsrv assembly, which gives us access to the Microsoft.AnalysisServices.AdomdServer namespace. We are not going to explain what everything in this assembly does; it's enough to say that it allows us to interact with the Analysis Services server object model in .NET.

In this example, we're going to secure the Sales Territory Country attribute hierarchy on the Sales Territory dimension. The full code is available with the rest of the samples for this book so we won't repeat it here, but the most important part is the function that returns the set of countries accessible to a user:

public static Set SecuritySet () {

try {

string userName = (new Expression ("UserName ()")

.Calculate (null))

.ToString ();

MemberCollection members = Context.CurrentCube

.Dimensions["Sales Territory"]

.AttributeHierarchies["Sales Territory Country"]

.Levels[1]

.GetMembers ();

SetBuilder sb = new SetBuilder ();

foreach (Member m in members) {

if (RegionsForUsers

.isRegionEnabled (m.Caption, userName)) {

TupleBuilder tb = new TupleBuilder ();

tb.Add (m);

sb.Add (tb.ToTuple ());

}

}

return sb.ToSet ();

} catch (Exception) {

return null;

}

}

Let us comment this code:

· The return value is a Set, an MDX set object, from Microsoft.AnalysisServices.AdomdServer. This means that the return value of the function can be used directly in our MDX expression; it is much more efficient than returning a string from the function and then casting that string to a set using the StrToSet function.

· The local variable UserName is calculated using the AdomdServer Expression object. Expression takes any valid MDX expression and, when evaluated, will return the value of the MDX expression. We use it to return the value of the UserName MDX function.

· The local variable members is assigned the set of all members from Sales Territory Country. We use this variable to iterate over all members of the hierarchy and decide whether they will be visible or not.

· Context: This is an AdomdServer object that exposes the context of the current query; among other things, it lets us interact with the cube currently being queried. Context.CurrentCube exposes dimensions, measure groups and other metadata.

· Dimensions: This is a collection of all the dimensions on the cube. We use it to select the Sales Territory dimension.

· AttributeHierarchies: This is a collection containing all the hierarchies of a dimension, from which we select the Sales Territory Country hierarchy.

· Levels: This contains the levels in the hierarchy. For user hierarchies it might contain several levels, for attribute hierarchies it normally contains two levels: one is the All level, the second one is the level of the other members on the hierarchy. We are interested in the second level to get the different countries.

· GetMembers: This returns the list of all the members at the specified level.

· Since we want to return a set, we use the AdomdServer helper object SetBuilder, which lets us create a set and add items to it.

· We then iterate over each country in the Members collection and for each one, we call a simple function called RegionsForUsers.isRegionEnabled that will return true or false, indicating whether the user is allowed to see that country or not. In our example, the code is very simple as it simply looks up values from a static array. In your implementation, the code will almost certainly be more complex, perhaps querying a table in a relational database to determine which countries users can see.

· Since a set is made up of tuples, for every country we need to use the AdomdServer helper object TupleBuilder to create a simple tuple containing only that country member, and then pass that to the SetBuilder.

· At the end, SetBuilder.ToSet will create the set of all allowed countries and return it to the caller.

Having written the stored procedure, we can compile it and add it to the assemblies referenced by the Analysis Services project in SQL Server Data Tools. This can be done by right-clicking on the Assemblies node in the Solution Explorer and choosing New Assembly Reference. SSDT will copy the necessary files to the server during the next cube deployment. Once we have done this, we still need to call the function from the Dimension Security pane. In order to call a function from a stored procedure in MDX, we need to reference it in the form, as follows:

AssemblyName.FunctionName

In our case, the expression is as follows:

SecurityUDF.SecuritySet()

All we then need to do is insert this expression inside the Allowed Members set of the Sales Territory Country hierarchy. When the expression is evaluated, Analysis Services will recognize it as a stored procedure and call the function from the assembly. The function will return the set of countries accessible by the current user, and that is the set that will be used for Dimension security.

Note

We recommend that you only use stored procedures for Dynamic security in situations where it is not possible to store security information inside the cube as stored procedures are hard to write, test, and debug. The following blog entry contains some best practices for writing them: http://tinyurl.com/moshasproc, you can also find a lot of code examples in the Analysis Services Stored Procedure Project: http://tinyurl.com/assproc. For information on how to go about debugging a stored procedure, seehttp://tinyurl.com/debugassproc.

Dimension security and parent/child hierarchies

Parent/child hierarchies handle dimension security in a different and much more complex way compared to standard dimensions. When a dimension contains a parent/child hierarchy:

· We cannot set up security directly on the key attribute - it will not appear on the drop-down list of hierarchies on the Dimension Data security tab in the Role Editor.

· Granting access to a member on a parent/child hierarchy will grant access to all of that member's descendants on the hierarchy.

· Granting access to a member on a parent/child hierarchy will grant access to all of its ancestors, so that the user can reach the selected member in the hierarchy. Enabling the Enable Visual Totals option will ensure that these ancestor members only display values derived from the accessible members.

Let's take an example of a dimension with a parent/child hierarchy, the Employee dimension. What we want to do is implement Dynamic dimension security on this dimension in a similar way to how we implemented it on regular dimensions in the previous section. We want to create a bridge table to model the relationship between Users and the Employees whose data they need to have access to.

As we've already seen, setting up security is as easy or as hard as finding the right set expression to enter in the Allowed Member Set pane. Unfortunately, in the case of parent/child hierarchies, finding the right set expression can be pretty hard.

Let's start with the requirements: we want to link Users and Employees using this table:

Here's a visual representation of what this means when the Employees are shown in the context of the parent/child relationship:

In the screenshot, the dark gray cells show where security User has been explicitly granted access to an Employee; the light gray cells show where a User has been granted access to an Employee indirectly, through being granted access to one of the Employee's ancestors. We can see, for example, that Alberto has access to Amy E. Alberts and so gets full access to that member's children; he also has access to Garret R. Vargas but not to that member's parent or siblings.

Now, here's the difficult part: how do we write an MDX set expression so that Analysis Services will grant access to only the members we've specified? Our first idea might be to filter the parent/child hierarchy using an expression like this:

NonEmpty (

Employee.Employees.Members,

(

[Measures].[Employee Count],

[Users].[User].[SQLBOOK\Marco]

)

)

Unfortunately, this will not work. The reason is that not only the Employees we want Marco to have access to will have a value for the Employee Count measure, their ancestors will too, due to the aggregation performed by the parent/child hierarchy. A screenshot will show this much better:

If we focus on Marco's column, we will see that the three highlighted cells contain a value, and they represent the Employees we've explicitly granted access to. Their ancestors have values too, as a result of aggregation, so our set definition will return a set containing Ken J. Sanchez and Brian S. Welcker, members which Marco should not have access to. Since, as we know, granting access to a member grants access to all of the descendants of that member, and since Ken J. Sanchez is the top member in the hierarchy, this means that Marco will have access to all of the Employees on the hierarchy. In fact, it follows that with this set expression if a User is explicitly granted access to any member on the hierarchy, then they will be implicitly granted access to all members on the hierarchy.

Let's try again. In order to return the correct set of members, we need to make Analysis Services ignore the presence of the parent/child hierarchy, at least initially. To do this, we need to filter on the key attribute and not the parent/child hierarchy. If we try this set:

NonEmpty (

[Employee].[Employee] .[Employee].Members,

(

[Measures].[Employee Count],

[Users].[User].[SQLBOOK\Marco]

)

)

We're getting closer to what we want: it returns only the three Employees that Marco should have access to, just from the wrong hierarchy. Of course, Analysis Services won't allow us to apply dimension security to the key attribute when there is a parent/child hierarchy.

Note

Notice that we used the set expression Employee.Employee.Employee.Members and not Employee.Employee.Members in the first parameter of the Nonempty function. This is very important because Employee.Employee.Members returns a set containing the All Member andEmployee.Employee.Employee.Members does not. We do not want the All Member to appear in the set: if it does, the technique will not work for the same reason that the previous set expression did not.

All we need now is a way to take the members this set expression returns and find the equivalent members on the parent/child hierarchy. Luckily, we can do this easily with the LinkMember function.

The LinkMember function takes a member and finds the member on another hierarchy that has the same key value. This expression:

LinkMember (

Employee.Employee.[Amy E. Alberts],

Employee.Employees

)

Takes the member Amy E.Alberts from the Employee hierarchy and returns the same member but on the parent/child hierarchy. What we need is to iterate over the set of members we've got from the key attribute (the Employee hierarchy) and use LinkMember to convert each of them into the equivalent members on the parent/child hierarchy (the Employees hierarchy), and for this we can use the Generate function.

The Generate function takes two sets: the first is the "source" set, the second one contains an MDX set expression that is evaluated for each member of the first set. In order to reference the current item from the first set, we can use the CurrentMember function on the hierarchy used in the first set. Here's what the complete set expression looks like:

Generate (

NonEmpty (

[Employee].[Employee].[Employee].Members,

(

[Measures].[Employee Count],

[Users].[User].[SQLBOOK\Marco]

)

), {

LinkMember (

Employee.Employee.CurrentMember,

Employee.Employees

)

}

)

This is what each part of the expression actually does:

· The first set in the Generate function is the expression we have already seen: it returns the set of Employee members from the key attribute that the user has access to.

· The Generate function iterates over this first set, and for each member it evaluates the LinkMember expression.

· The LinkMember function takes Employee.Employee.CurrentMember to return the current Employee from the first set, and finds the equivalent member on the parent/child hierarchy, Employee.Employees.

· The result of each evaluation of the LinkMember function is added to the final set that the Generate function returns.

Finally, we just need to replace the reference to SQLBOOK\Marco with an expression that dynamically evaluates the user based on the value returned by the UserName function. We already learned how to write this:

StrToMember ("[Users].[User].[" + UserName () + "]")

The final expression is as follows:

Generate (

NonEmpty (

[Employee].[Employee].[Employee].Members,

(

[Measures].[Employee Count],

StrToMember ('[Users].[User].[' + UserName () + ']')

)

), {

LinkMember (

Employee.Employee.CurrentMember,

Employee.Employees

)

}

)

Copying this expression in the Allowed Member set of the parent/child hierarchy and enabling the Enable Visual Totals option will do exactly what we need. Now, when Marco accesses the cube he will see this:

Bear in mind that enabling the Enable Visual Totals option on Dimension security on a parent/child hierarchy can have a significant impact on query performance. Parent/child hierarchies can have no aggregations within the hierarchy itself; the only member that can benefit from aggregations is the All Member. Denying access to some members in the hierarchy will invalidate any aggregations that have been built at the All Level, and this means that every single query at that level (and this includes queries that don't mention the Employee dimension at all) or higher will have to aggregate values up through the parent/child hierarchy. We already discouraged the reader from using parent/child hierarchies. The performance impact of security on parent/child hierarchies is yet another reason not to use them.

Dynamic cell security

We can implement Dynamic security for cell security in much the same way as we have seen with Dimension security. In this section, we'll use the same bridge table we used in the previous sections to model the relationship between users and resellers they have access to; our requirements will be as follows:

· Members of the role will have complete access to all measures, except for Gross Profit.

· For Gross Profit, users will only have access to values for it for resellers they have been granted access to base on the data in our bridge table.

· We also need to hide the All Member of the Reseller dimension to ensure that users cannot derive values for cells they don't have access to.

We can use this expression to control Read access in the Cell Data tab:

NOT (

Measures.CurrentMember IS Measures.[Gross Profit]

AND (

StrToMember ("[Users].[User].[" + UserName() + "]"),

Measures.[Bridge Reseller Users Count]) = 0

OR

Reseller.Reseller.CurrentMember IS Reseller.Reseller.[All]

)

)

The usage of NOT is very common in cell security expressions: it's usually easier to define the cells that users are not allowed access to. The expression may be read as follows: "if the user is not querying Gross Profit, then show everything. Otherwise, if the reseller they are querying is not present in the bridge table for that user, or the reseller is the All Member, then deny access to the cell".

The result will be as follows:

The user is allowed to see Sales Amount for all resellers but not Gross Profit, which is only available for Roadway Bicycle Supply.

As we see, the expression is very complex and evaluating this for every cell in the query can have a negative impact on query performance. If we want to optimize the performance of cell security expressions, we can leverage the MDX script, which as we know is evaluated before cell security.

In this case, we can define a new hidden calculated measure, HideGrossProfit, to hold the logic for our cell security expression. In the initial calculated measure definition we give it a default value of False; we then use SCOPE statements to overwrite its value for the parts of the cube where it should return True.

CREATE MEMBER CurrentCube.Measures.HideGrossProfit AS

False,

Visible = 0;

SCOPE (Measures.HideGrossProfit);

SCOPE (StrToMember ("[Users].[User].[" + UserName() + "]"));

THIS = (Measures.[Bridge Reseller Users Count] = 0);

END SCOPE;

SCOPE (Reseller.Reseller.[All]);

THIS = True;

END SCOPE;

END SCOPE;

Note the highlighted SCOPE statement, which is the key to making this technique work: we are scoping on a dynamically evaluated member expression.

Having created this calculated member, we can then reference it in a much-simplified cell security expression as follows:

NOT (

Measures.CurrentMember IS Measures.[Gross Profit]

AND HideGrossProfit

)

Now that the security logic is handled in MDX Script the query performance improvement is significant: instead of being re-evaluated for every cell, the expression determining which cells a user has access to be evaluated only once when the MDX Script is executed. Another benefit of moving the logic to the MDX Script is that it is much easier to define complex expressions and perform debugging there.

One last problem to solve with this technique is that the evaluation of the SCOPE statement requires that each user of the cube, regardless of whether they are a member of the dynamic role, be present in the Users dimension. If they aren't, the cube will raise an error because we'll be attempting to use the SCOPE statement on a member that doesn't exist. This can be worked around by altering our SCOPE statement to use a named set, and then in the named set definition return an empty set if the STRTOMEMBER function returns an error as follows:

CREATE MEMBER CurrentCube.Measures.HideGrossProfit AS False, Visible = 0;

CREATE SET CurrentCube.UserSet AS

IIF(

IsError(StrToMember ("[Users].[User].[" + UserName() + "]"))

, {}

, {StrToMember ("[Users].[User].[" + UserName() + "]")});

SCOPE (Measures.HideGrossProfit);

SCOPE (UserSet);

THIS = (Measures.[Bridge Reseller Users Count] = 0);

END SCOPE;

SCOPE (Reseller.Reseller.[All]);

THIS = True;

END SCOPE;

END SCOPE;

In our experience, there are very few scenarios where cell data security is really necessary; the scenarios where Dynamic cell security is necessary are rarer still. This is true, though, if and only if security has been correctly designed from the beginning of the project: when security is added onto a cube as an afterthought, very often cell security is the only approach possible.

Accessing Analysis Services from outside a domain

So far we've seen that in order to let a user access a cube, that user needs to be a user on the same domain as Analysis Services. We have seen that roles are based on Windows users and groups, and that Analysis Services does not implement any other forms of authentication.

Nevertheless, it is possible to let users outside a domain access Analysis Services if we set up HTTP access via Internet Information Server (IIS). This method uses a DLL called MSMDPUMP.DLL that acts as a bridge between IIS and Analysis Services; when called, this DLL connects to Analysis Services and impersonates a Windows user, which can be a domain user or a local user on the server hosting Analysis Services and IIS. The DLL is placed in a virtual directory in IIS; the client application then connects to the DLL via IIS and IIS therefore handles the authentication. Using integrated Windows authentication is the recommended option, but it is also possible to use other methods such as anonymous access or basic authentication, for example. For security reasons, it is much better to make users connect to IIS using HTTPS and not HTTP: this will avoid sending query results and possibly user credentials over the network as clear text.

Another benefit of HTTP access is that it allows users to connect to Analysis Services even in situations where a firewall blocks access to ports 2382 (for SQL Server Browser) or 2383 (the default port on which Analysis Services listens for client connections).

A complete walkthrough of how to set up HTTP connectivity is outside the scope of this book; the following blog entry contains links to all of the relevant white papers and collects a lot of other useful information on the subject at http://tinyurl.com/MSMDPUMP.

When HTTP connectivity is set up, we will be able to connect to Analysis Services using any client application, but instead of giving the Analysis Services server name in the connection, we instead give the URL of the virtual directory and the DLL.

Accessing a cube via HTTP can result in slower query performance when compared to a standard TCP/IP connection because the translation from TCP/IP to HTTP will require time and will increase packet size. Nevertheless, when the user has cannot connect directly to Analysis Services, using IIS is the only other option available.

Managing security

So far, we have only discussed how to set up security from the point of view of the developer. However, once the roles have been defined, security will still need to be managed: new users will appear, some users will disappear, and users may change which roles they are members of.

This task is not generally managed by the developers of the Analysis Services cube, it is usually handled by someone else such as a network administrator or a DBA whose job it is to look after the cube once it has gone into production. So, the question is: how should these people add new users to a role? The answer should not be that they need to open SSDT or SQL Management Studio and edit the roles directly. If we have followed our rule to only add Windows groups as role members, it will be possible to add or remove users purely by adding and removing these members from domain groups. This will mean that whoever is managing security will not need administrative access to the cube itself; it will also mean they do not need to learn how to use potentially unfamiliar tools such as SQL Management Studio.

A different and more complex situation is when the Analysis Services developer works in a domain that is not the same as the domain where Analysis Services is installed. In this case, we cannot define the relationship between roles and Windows groups in SSDT because we do not even have access to the domain where those roles exist. The only thing we can do is to create empty roles and let whoever is managing security create associations between roles and groups once the Analysis Services database has been deployed. What's more, when this happens, we can no longer deploy our cube from SSDT because doing so would overwrite the properly configured roles on the server with the empty roles in our solution. Instead, we will need to use the Analysis Services Deployment Wizard to deploy our solution: it gives us the option to either deploy only roles that do not exist already on the server, or to not deploy any of the roles in our solution at all.

Security and query performance

Applying security to a cube comes at a cost to query performance. However, since we don't usually have a choice about whether to apply security or not, we then need to understand which security features have the least impact on performance when choosing which ones to use. In this final section, we'll give some details on the relative impact on query performance of each type of security.

Cell security

Cell security has by far the biggest negative impact on query performance, and for this reason alone, you should use it when there is no other option. Remember also that there are two different types of cell security, Read and Read Contingent; Read Contingent cell security has more impact on query performance than Read. The reason why cell security is so bad is because it prevents Analysis Services from using many important query optimization techniques. For example, in many cases it appears to completely prevent the use of the Formula Engine cache, so the results of calculations are not cached from query to query, even when users are querying cells they do have access to.

Dimension security

Dimension security has less of an impact on performance than cell security, although it can still lead to noticeably slower queries.

As we've already noted, enabling the Enable Visual Totals option can prevent the use of aggregations at higher levels of granularity, which means Analysis Services may need to do more aggregation at query time. Setting the AggregationUsage property of the attribute that we're securing to Full, and setting it for any attributes at a higher granularity to None might, as a result, be a good idea to make sure we don't build any aggregations that the role cannot use.

Dimension security does not stop Analysis Services from caching the result of MDX calculations, but it does limit its ability to share cached calculated values between users. Only users that have exactly the same set of permissions and are members of exactly the same roles can share cached calculated values. So, for example, if user A ran a query that included a calculated measure, in most cases the second time user A ran the same query the calculated measure's values would be retrieved from the Formula Engine cache. However, if user B connected to the cube and ran the same query, that query would only be able to retrieve values from the Formula Engine cache if user B had exactly the same security rights as user A. On cubes with very complex calculations, the ability to share values in the Formula Engine cache between users can make a big difference to the overall performance of the cube.

Dynamic security

Dynamic security can slow down the process of connecting to a cube if the MDX expressions we're using to determine what data a user can see take a long time to be evaluated. In almost all cases, a purely data-driven approach, where security information is stored within the cube itself, will perform noticeably better than approaches that use Analysis Services Stored Procedures, so this another good reason to avoid using Stored Procedures for security.

Dynamic dimension security also completely prevents Analysis Services from sharing values in the Formula Engine cache between users. Unlike regular dimension security, with Dynamic dimension security, a user can never access values in the Formula Engine cache that are there as a result of queries run by other users. As a result, on cubes with very complex calculations, we recommend you try to use regular dimension security unless the number of roles that would be required is genuinely too many to manage.

Summary

In this chapter, we have learned that:

· The only form of authentication supported by Analysis Services is Windows authentication. If access need to be granted from outside the domain the Analysis Services server belongs to, it is possible to configure HTTP access to Analysis Services via IIS.

· Analysis Services security is based on roles. Only Windows users and groups can be members of roles.

· If a user belongs to multiple roles, they will have all of the permissions granted to them by every role they are a member of; there is no concept of "deny wins over grant".

· We can use Cell security to secure individual cell values within the cube. Cell security can cause poor query performance and should only be used if there is no other option.

· Dimension security lets us allow or deny access to members on a hierarchy. Using the Enable Visual Totals option then controls whether values at higher granularities are calculated based on all of the members on the hierarchy or just the members that the user has access to.

· Both Cell security and Dimension security can be static or dynamic. Dynamic security relies on the UserName function, which returns the name of the user who is connected to the cube; it can be used to write dynamic MDX expressions that return different sets for different users. This then means that one role can be used to handle security for many different users with different permissions.

· Dynamic security can be implemented in two ways: where the information on what data a user has access is to store in the cube itself, or where this information is retrieved using an Analysis Services stored procedure. The former, cube-based approach is recommended as it is likely to perform better.

· Implementing Dynamic security on a parent/child hierarchy requires some very complex MDX set expressions, and so this is another reason for avoiding both parent/child hierarchies and dynamic security.

· Keeping your security model as simple as possible will result in faster development and better query performance. Where possible use dimension security instead of Cell security, and static security instead of Dynamic security.

· Last of all, security is such a complex issue it must be planned for right at the beginning of a project and not treated as an afterthought!