Dimension Security in Analysis Services 2005 (by Richard Tkachuk)

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
City		Exists(Customer.City.City.members, Customer.State.CA)
Name		Customer.Name.Name.members

 

Visual Totals

If a member of an attribute hierarchy is secured, one has two choices to how data rolls up to the all member of the hierarchy:

-         users see the true totals
-         users see the totals of the data they are permitted to see 

This is best illustrated with an example. Returning to the original example of Sales in countries:

Customer.Country	Sales
All	60
Canada	10
USA	30
Mexico	20

 

 If Country.Mexico is secured, what is the value for the sales for all countries?

Customer.Country	Sales
All	???
Canada	10
USA	30

Is it 60 (the original total) or 40 (the aggregate of the values the user is permitted to see)?

The answer is up to the individual designing the security definition and how the Visual Totals  property is defined for the country attribute. If Visual Totals is off, the totals remain their true value; if on, users see the aggregate of what they are permitted to see.

Dimension Security Inheritance

Dimension security can be defined on the shared dimension or on the cube dimension. If dimension security is defined on the shared dimension, it is inherited in all cubes containing the dimension. If dimension security is defined on the cube dimension, this inheritance relationship is severed; however, if the dimension security on the cube dimension is completely eliminated the inheritance rules are restored.

This behavior reduces administration costs – dimension security can be defined in one place and automatically inherited wherever it is used.

Conclusion

Dimension security allows security to be defined such that almost any part of a dimension can be secured from view. Visual totals can be turned on or off such that users need not be aware that the dimension contains members they are not permitted to see.