Introduction to Dimension Security in Analysis Services 2005

Introduction

Analysis Services 2005 has two security models for securing data: dimension security and cell security. Dimension security is used to permit or deny access to members of a dimension and any data associated with those members. Cell security permits or denies access to cell values only.

For example, consider the example of a cube containing a customers dimension with a country attribute hierarchy and two measures, sales and expense.

Measures Customer.Country	Sales	Expense
Canada	10	30
USA	30	18
Mexico	20	20

If this simple example secures the values for Mexico with cell security, a user will see something like this:

Measures Customer.Country	Sales	Expense
Canada	10	30
USA	30	18
Mexico	#N/A	#N/A

If Mexico is secured with dimension security, the result is this:

Measures Customer.Country	Sales	Expense
Canada	10	30
USA	30	18

Unlike the cell security example, the member doesn’t appear to exist.

Dimension security is defined on each attribute hierarchy and is applied wherever the attribute is used. For example, if dimension security is defined on Product.[Product Name] attribute hierarchy and the attribute sources the [Product Name] level in separate user hierarchy, dimension security is applied in both places.

AllowedSets and DeniedSets

Dimension security is defined with AllowedSets and DeniedSets on a dimension’s attribute hierarchies. As their names suggest, users can see members in the AllowedSet and can’t see them if included in DeniedSet (if included in both, the member is denied).

The interesting thing is how security one attribute affects other attributes. For allowed sets, the behavior is straightforward. If a member of another attribute exists with the allowed set, it is allowed unless explicitly disallowed. If a member does not exist with a member of the allowed set, it is disallowed (unless explicitly allowed).

For example, if the allowed set is {Customer.Country.USA}, all states, cities, customers in USA are implicitly allowed. States, cities and customers outside USA are not allowed. If the allowed set is {Customer.Country.All, Customer.Country.USA}, the only members of the country attribute hierarchy that are visible are these members and other hierarchies are unaffected (because every member of exists with an all member).

For denied sets, the behavior is different. Members of other attribute hierarchies that exist with the denied set are disallowed if the attribute on which the denied set is defined is directly or indirectly related to the other attribute. In other words, denied sets on an attribute A affect another attribute B only if there is relationship between them such that A is related directly or indirectly to B.

That might have to be read a couple times before it can be understood so let’s consider an example. If the denied set is {Customer.State.WA} and attribute relationships are defined as Customer.Name à Customer.City à Customer.State à Customer.Country (where the symbol à represents an attribute relationship), then all cities and names in Washington are denied because they only exist in WA and because State is directly related to City and indirectly related to Name. However, members of the Country attribute hierarchy are unaffected. Any other attributes such as gender, age or whatever would be unaffected.

Common Scenarios with AllowedSets and Denied Sets

Four common security scenarios are described below. In each it is assumed that an attribute relationship exists between each attribute sourcing the levels; that is, Customer.Name à Customer.City à Customer.State à Customer.Country.

Scenario 1: Ascendants and descendants of a member are allowed

Attribute	AllowedSet	DeniedSet
Country
State
City	{Customer.State.SJ}
Name

Scenario 2: Descendants of a member are not allowed

Attribute	AllowedSet	DeniedSet
Country
State		{Customer.State.OR}
City
Name

Scenario 3: Bottom Level Cut Off

Attribute	AllowedSet	DeniedSet
Country
State
City
Name		Customer.Name.Name.members

Scenario 4: Unbalanced Hierarchy

Security is defined such that more detail is available among some members of a hierarchy, but are secured in others:

Attribute	AllowedSet	DeniedSet
Country
State