Microsoft SQL Server OLAP Services Cell-Level Security

Data Warehousing Team
Microsoft Corporation

April 1999

Summary: This article describes how SQL Server 7.0 (Service Pack 1) OLAP Services takes advantage of the user and group structure in Microsoft Windows NT to offer cell-level security, and describes several ways to tailor permissions to data across the enterprise. (15 printed pages)

Introduction
Cell-Level Security Overview
Implementing Cell-Level Security
Procedure
Example 1: Read Permission
Example 2: Contingent Read Permission
Example 3: Write Permission
Conclusion
Appendix: Access Rules and Checks
Access Rules
Rule 1: Readable Cells
Rule 2: Contingent Readable Cells
Rule 3: Write-Enabled Cells
Default Behavior
Access Checks

Introduction

This document explains the implementation and use of the cell-level security in Microsoft® SQL Server™ online analytical processing (OLAP) Services.

Note: Cell-level security is not supported in OLAP Services releases prior to the Service Pack 1 (SP1) release. However, the client-side components (Microsoft PivotTable® dynamic views) of SQL Server version 7.0 are compatible with this feature.

Security in a multidimensional data source involves different levels of granularity. In OLAP Services users are granted permissions, which allow access to databases, cubes, or the OLAP server. OLAP Services uses roles to administer permissions. Permissions that are granted on these objects typically allow users to view either all or none of the data. This level of security does not provide for users that have restricted access to specific portions of a cube's data.

For example, Sue is working on a budget for a department store's salary forecast for next year. She may need to review past salary expenses for that store, but she should not be able to examine salary data for other stores in the corporation. Permissions at the cube level would either allow full access to all salary information in the cube or prevent access to the cube.

Cell-level security can help address such situations by providing a finer degree of control. Using cell-level security, users can be granted or denied permission to access data down to individual cells within a cube.

Cell-Level Security Overview

Cell-level security is enforced through the use of security roles and access rules. Roles collections exist for both database and cube objects. The database role maintains groups and users (established using the administrative functions in Microsoft Windows NT® Server operating system) that have permission to access cubes. One or more database roles are then assigned to a member cube. The Roles collection for the cube contains a Permissions collection, which stores the rules for granting access to cell values.

Enforcing cell-level security does not eliminate or remove data from a cube to which a user has access. Security rules define a user's ability to retrieve a cell's value. If the test of a required security rule succeeds, then the value of the cell is returned. If the test fails, then access is denied.

A cell-level security rule is a defined as a multidimensional expressions (MDX) Boolean expression. Whenever an attempt is made to access a cell, the security rule is evaluated. If the rule evaluates to True, access to the cell is granted. If the rule evaluates to False, access to the cell is denied.

Two examples follow that illustrate typical security rules. The first example limits access to all cells that have the measure Salary as one of the cell coordinates:

Iif(Measures.CurrentMember.Name = "Salary", 0, 1)

The second example, a security rule, permits access only to cells that have branches that report to the southern region as one of the cell coordinates:

Iif(Ancestor(Organization.CurrentMember, Region).Name = "Southern Region", 1, 0)

Also, a user may be assigned to more than one role. Each role has its own security permissions defined. When multiple roles apply to a user, cell security permissions are logically "ORed". This means that if the security permission in one role denies access to a cell, but the second role grants the user Read permission to that cell, the user will have access to the cell.

There are three categories of cell-level security rules that can be identified:

Readable cells
Contingent Readable cells
Write-Enabled cells

For more information about cell access rules and the checks on allowable access that OLAP Services performs, see the Appendix: Access Rules and Checks section of this article.

Implementing Cell-Level Security

Implementing cell-level security is accomplished through MDX that is saved in the Permissions collection of a cube role.

MDX statements are used to define rules that evaluate to True (access allowed) or False (access denied). Using MDX, you can construct any expression that, based on its evaluation, determines whether an individual cell is accessible.

Procedure

Security within OLAP Services relies on both database and cube roles. Implementing cell-level security involves:

Setting up Windows NT users and/or groups (using the User Manager in Windows NT), group permissions, and members of a group
Assigning groups or users to an OLAP database role
Assigning the same database role to the Roles collection in one or more of the subordinate cubes of the databases
Creating an MDX expression and saving it in the cube role Permissions collection

While administering database and cube roles can be handled through the OLAP Manager, generating and storing the MDX expressions used to enforce security is done programmatically using Decision Support Objects (DSOs). The Permissions collection of the cube role is set with the SetPermissions method, which has the following syntax:

SetPermissions (Key As String,  PermissionsExpression As String) As Boolean

Note Although OLAP Services SP1 does not include a user interface to specify the cell-level security rules, it is possible to implement this type of user interface as an OLAP Manager add-in.

There are three types of security that can be applied at the cell level. The following table lists the type of access and the corresponding Permissions collection key:

Table 1. Access enforce types and Permissions collection keys

Access to enforce	Permissions Key
Readable	CellRead
Contingent Read	CellReadContingent
Write	CellWrite

For more information on access enforce types and Permissions keys, see the Appendix: Access Rules and Checks section of this article.

The following examples use the Microsoft Visual Basic® 6.0 development system and the FoodMart sample database that is supplied with OLAP Services. The first example implements a simple security measure that prevents a client from viewing store cost information from the Sales cube. The second and third examples demonstrate Contingent Read permission and Write permission.

Note If you are running the OLAP server on a local computer, you must log on as a user with membership in the Windows NT group OLAP Administrators to implement the code. For testing, you must log on as a user (or attach as a remote user) that does not belong to the OLAP Administrators group. This is because members of the OLAP Administrator's group have read-only permissions.

Example 1: Read Permission

In this example, a new role, No Cost, is used to prevent store cost information from being viewed.

Create a new application using Visual Basic, and then place the following code in the Form_Load procedure. Be sure that the OLAP server is installed and running and that Microsoft DSOs is included in Project References. The example assumes that the Windows NT group Users exists and has the user Test as a member. Run the example. When the method is complete (that is, a blank form is displayed), the security permissions are set.

Private Sub Form_Load()
   Const sNewRole = "No Cost"
    Dim dsoServer As DSO.Server
    Dim dsoDatabase As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    Dim dsoRole As Role
    
    'Connect to server
    Set dsoServer = New DSO.Server
    dsoServer.Connect ("LocalHost")
    
    'Find database FoodMart and cube Sales
    Set dsoDatabase = dsoServer.MDStores("FoodMart")
    Set dsoCube = dsoDatabase.MDStores("Sales")
    
    'Add the new role to the database and ensure that the user
    'Test is a member of the role.
    If Not dsoDatabase.Roles.Find(sNewRole) Then
        dsoDatabase.Roles.AddNew (sNewRole)
        Set dsoRole = dsoDatabase.Roles(sNewRole)
        dsoRole.UsersList = "Test;"
        dsoRole.Update
    End If
    
    'Add the role to the cube and construct the MDX statement
    'to set Permissions.
    If Not dsoCube.Roles.Find(sNewRole) Then
      dsoCube.Roles.AddNew (sNewRole)
    End If
    
    Set dsoRole = dsoCube.Roles(sNewRole)
    'This is the place where the security rule is defined!
    dsoRole.SetPermissions "CellRead", _
      "iif(Measures.CurrentMember.Name = ""Store Cost"", 1, 0)"
      
    'Must remove the role "All Users" from the cube because it
    'contains Everyone and has full permissions which will
    'override our security.
    If dsoCube.Roles.Find("All Users") Then
        dsoCube.Roles.Remove ("All Users")
    End If
    
    dsoCube.Update

    dsoServer.CloseServer
End Sub

Close the application. In the OLAP Manager verify that the No Cost role exists for the FoodMart database. Expand the Library folder and the Roles folder. Right-click No Cost, and then click Edit. Verify that the user (Test) is listed in the Groups and Users box. Expand the Cube folder, the Sales folder, and the Roles folder, and then verify that the No Cost role is listed as the only role for this cube.

Testing Cell-level Security

You can test cell-level security using client applications such as the MDX Sample Application, which is shipped with OLAP Services. You should not test the results of cell security settings using the OLAP Manager because, by default, users that are members of the OLAP Administrators group have full rights to view data.

To test the effects of Example 1 on the Sales cube, perform the following steps:

Log off, and then log back on as user Test.
Start the MDX Sample Application.
Connect to your local server. The provider should be MSOLAP.
Note: If the Visual Basic sample application fails to assign the No Cost role or place the user Test as a member of the role, you will be denied access to the FoodMart database.
Set the active database to FoodMart and the active cube to Sales.
On the Query menu, click New. Enter the following MDX expression:
```
SELECT Measures.members on columns from SALES
```

The Store Cost column should display #N/A. You can experiment further by removing the No Cost group from the Sales cube's role, and then execute the MDX query again. Additionally, you can experiment with the Secured Cell Value connection string parameters to see how an attempt to read a secured cell responds to the user's request.

Note You must log on as a user with membership in the OLAP Administrators group to modify the cube or database roles.

Example 2: Contingent Read Permission

Example 2 uses the calculated member Profit of the Sales cube. This cell is dependent on the Store Sales and Store Cost members (Profit = Sales - Cost). Because Contingent Read security depends on permissions granted on other cells, Read permission is first granted on the sales and cost cells. This example restricts viewing of data to stores located only in California.

Create a new application using Visual Basic, and then place the following code in the Form_Load procedure. Be sure that the OLAP server is installed and running and that Microsoft DSOs is included in Project References. The example assumes that the Windows NT group Users exists and has the user Test as a member. Run the example. When the method is complete a blank form is displayed), the security permissions are set.

Private Sub Form_Load()
  Const sNewRole = "CA Only"
  Dim dsoServer As DSO.Server
  Dim dsoDatabase As DSO.MDStore
  Dim dsoCube As DSO.MDStore
  Dim dsoRole As Role
    
  'Connect to server
  Set dsoServer = New DSO.Server
  dsoServer.Connect ("LocalHost")
    
  'Find database FoodMart and cube Sales
  Set dsoDatabase = dsoServer.MDStores("FoodMart")
    Set dsoCube = dsoDatabase.MDStores("Sales")
    'Add the new role to the database and ensure that the user
    'Test is a member of the role.
    If Not dsoDatabase.Roles.Find(sNewRole) Then
        dsoDatabase.Roles.AddNew (sNewRole)
        Set dsoRole = dsoDatabase.Roles(sNewRole)
        dsoRole.UsersList = "Test;"
        dsoRole.Update
    End If
'    dsoDatabase.Update
    
    'Add the role to the cube and construct the MDX statement
    'to set Permissions.
    If Not dsoCube.Roles.Find(sNewRole) Then
      dsoCube.Roles.AddNew (sNewRole)
    End If
    
    'The calculated member Profit is derived from Store Sales and
    'Store Cost (Profit=Sales-Cost). We must set Read permissions on
    'Store Sales and Store Cost to enable a Contingent Read on Profit.
    'Read permission allows data to only be returned for cells
    'Store Sales and Store Cost and only from stores located in California.
    Dim sPermission As String
    sPermission = "IIf((Measures.CurrentMember.Name = ""Store Sales"")"
    sPermission = sPermission & " OR (Measures.CurrentMember.Name = ""Store Cost""))"
    sPermission = sPermission & _
      " AND (Ancestor(Store.CurrentMember,[Store State]).Name = ""CA""), True, False)"
    Set dsoRole = dsoCube.Roles(sNewRole)
    dsoRole.SetPermissions "CellRead", sPermission
    
    'Set Contingent Read permission on Profit
    'This is the place where the security rule is defined!
    dsoRole.SetPermissions "CellReadContingent", _
      "IIf(Measures.CurrentMember.Name = ""Profit"", 1, 0)"
      
      
    'Must remove the Role "All Users" from the cube because it
    'contains Everyone and has full permissions, which will
    'override our security.
    If dsoCube.Roles.Find("All Users") Then
        dsoCube.Roles.Remove ("All Users")
    End If
    
    dsoCube.Update

    dsoServer.CloseServer
End Sub

Testing Example 2

Following the same procedure for testing Example 1, substituting the following MDX expression:

SELECT
   AddCalculatedMembers(Measures.members) on columns,
   [Store].[Store State].members on rows
from Sales

Because Example 2 grants Read permission on the sales and cost data for California and places a Contingent Read on profit, all cells should report #N/A with the exception of sales, cost, and profit pertaining to California.

Example 3: Write Permission

Example 3 enables Write permission on cells that pertain to Store Cost data and only for those stores located in California. This allows client applications to test scenarios where store cost projections are different from the actual data.

Create a new application using Visual Basic, and then place the following code in the Form_Load procedure. Be sure that the OLAP server is installed and running and that Microsoft DSOs is included in Project References. The example assumes that the Windows NT group Users exists and has the user Test as a member. Run the example. When the method is complete (a blank form is displayed), the security permissions are set.

Private Sub Form_Load()
    Const sNewRole = "Write Count"
    Dim dsoServer As DSO.Server
    Dim dsoDatabase As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    Dim dsoRole As Role
    
    'Connect to server
    Set dsoServer = New DSO.Server
    dsoServer.Connect ("LocalHost")
    
    'Find database FoodMart and cube Sales
    Set dsoDatabase = dsoServer.MDStores("FoodMart")
    Set dsoCube = dsoDatabase.MDStores("Sales")
    
    'Add the new role to the database and ensure that the user
    'Test is a member of the role.
    If Not dsoDatabase.Roles.Find(sNewRole) Then
        dsoDatabase.Roles.AddNew (sNewRole)
        Set dsoRole = dsoDatabase.Roles(sNewRole)
        dsoRole.UsersList = "Test;"
        dsoRole.Update
    End If
    
    'Add the role to the cube and construct the MDX statement
    'to set Permissions.
    If Not dsoCube.Roles.Find(sNewRole) Then
      dsoCube.Roles.AddNew (sNewRole)
    End If
    
    Set dsoRole = dsoCube.Roles(sNewRole)
    'This is the place where the security rule is defined!
    dsoRole.SetPermissions "CellWrite", _
      "(Measures.CurrentMember.Name = ""Store Cost"") AND     (Ancestor(Store.CurrentMember, [Store State]).Name = ""CA"")"
      
    'Must remove the Role "All Users" from the cube because it
    'contains Everyone and has full permissions, which will
    'override our security.
    If dsoCube.Roles.Find("All Users") Then
        dsoCube.Roles.Remove ("All Users")
    End If
    
    dsoCube.Update

    dsoServer.CloseServer
End Sub

Testing Example 3

Testing should be done using client applications that have Write permission.

Conclusion

OLAP Services provides security from the database level and cube level down to the cell level to help you tailor levels of access to meet your needs. Taking advantage of the user and group structure in Windows NT, OLAP Services security uses a combination of roles and levels of enforcement. Cell-level security gives users different levels of access to data within the cube, and Contingent Read permission helps prevent users from being able to deduce values of protected cells. You can use the examples this document provides to assist in implementing security for your OLAP Services enterprise.

Appendix: Access Rules and Checks

Access rules address situations where secured cell values can be deduced from nonsecured cell values. For example, a cell is accessible and contains calculated data based on other cells that are inaccessible. The dependent cell is granted Read permission; therefore, it will return a value such as the sum of a management team's salaries. This gives rise to the possibility of extrapolating confidential information from the protected cells. Because of this, the following access rules for cell-level security have been established.

Access Rules

Rules governing cell-level security fall into three categories:

Rule 1: Readable cells
Rule 2: Contingent Readable cells
Rule 3: Write-Enabled cells

Rule 1: Readable Cells

Note An attempt to read a cell where a required cell security rule evaluates to True returns the value of the cell. An attempt to read a cell where all security rules evaluate to False prevents the value of a cell from being returned.

This rule is enforced whether the cell is queried directly or indirectly through a calculated member. For example, consider Case 2 in the following rule table. Even though Cell 1 may be protected, access is granted on Cell 2. Therefore, the data contained in Cell 2 is viewable. This does not mean, however, that the business rule for obtaining the value of Cell 2 is known (for example, if sales are greater than "X", apply a higher commission).

Table 2. Rule table for readable cells (Rule 1)

	Cell 1	Cell 2 (derived from Cell 1)	Result for Cell 2
Case 1	Readable	Readable	Readable
Case 2	Protected	Readable	Readable
Case 3	Readable	Protected	Protected
Case 4	Protected	Protected	Protected

Rule 2: Contingent Readable Cells

Contingent Read permissions are important only for calculated members. If none of the members of the accessed cell are calculated, Contingent Read permissions are identical to standard Read permissions. However, access rights to cells with a calculated member(s) as one of the cell coordinates depend on how access rights are defined for the underlying cells from which these cells are derived.

Note An attempt to read a cell where a required cell security rule evaluates to True returns the value of the cell, provided the value of the cell is not derived from a protected cell. An attempt to read a cell where all security rules evaluate to False prevents the value of a cell from being returned.

This rule is similar to the first, and it holds true as long as a cell is derived from another cell that does not satisfy either Rules 1 or 2. For example, if Cell C is readable, and it is derived from a business rule involving Cells A and B (C = (A * B + 0.5) / 100) where either one or both of Cells A and B are protected, access to Cell C is denied.

Table 3. Rule table for contingent readable cells (Rule2)

Cell A	Cell B	Cell C (derived from Cells A and B)
Readable	Readable	Readable
Protected	Readable	Protected
Readable	Protected	Protected
Protected	Protected	Protected

Rule 3: Write-Enabled Cells

Note An attempt to write to a cell where a security rule evaluates to True allows the cell value to be changed. An attempt to write to a cell where a security rule evaluates to False fails. Cell values can be changed but not committed if a cell security rule evaluates to False.

Write access to a cell implies read access. Additionally, if the user has read-only access to a cube, the value of the cell can be changed (for example, a user performing "what-if" analysis), but the change cannot be committed or written back to the source data.

Default Behavior

In the event that access to a cell fails, OLAP Services returns a formatted value of #N/A. Client applications that do not use the Formatted Value should check the vt member of the variant argument passed to the GetCellData() method. If it is set to VT_ERROR and the scode member of the variant has a value of 0X800A07FA, access to the cell has failed. In general, applications should display the formatted value.

In addition, there are four other modes available that client applications can use to connect to the OLAP server. A connection mode is specified through an optional connection string parameter, Secured Cell Value. The general form is:

Secured Cell Value=mode

where mode is a value from 0 through 5. If no mode is specified (that is, the Secured Cell Value parameter is omitted), the default is 0. Note that mode 0 and mode 1 are equivalent. The following code example implements the default mode:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "provider=msolap;data source=localhost; _
Initial Catalog=FoodMart;Secured Cell Value=0"

The following table lists the connection modes and the returned values in the event of a failure when attempting to access the value of a cell:

Table 4. Default behavior: connection modes and returned values

Mode	Result
0 (Default)	NO ERROR is the system default value. Use this mode to ensure OLAP Services default behavior now and in the future. The current setting behaves as outlined in mode 1.
1	Returns: HRESULT = NO ERROR The string #N/A is returned for the Formatted Value. The Value member contains variant results.
2	An error is returned as the value of HRESULT.
3	NULL is returned on failure for both the Value and Formatted Value.
4	A numerical zero (0) is returned for the Value and a formatted zero (0) for the Formatted Value based on the formatting property.
5(Mode 5 returns #SEC for a cell value only if a query was not executed in isolation)	The string #SEC is returned for the Value and Formatted Value (not localizable).

Note The Secured Cell Value setting is sometimes ignored when isolated queries are relayed to the server. When a user attempts to retrieve the value of a cell to which read access is allowed and the query is in isolated mode, if the query is relayed to the server (as affected by the Execution Location setting), the Secured Cell Value setting is ignored. For example, using Secured Cell Value=4 may return #N/A, depending on the query.

Access Checks

Access validation is performed each time an attempt is made to read a cell. The validation procedure differs depending on the type of action a client is attempting to perform. Access rules are evaluated in different orders and combinations. If no security rules are defined, OLAP Services uses a default permission of Read/Write on all cells within a cube.

Reads

When a client attempts to read a cell, OLAP Services evaluates permissions in the following sequence:

If there are no read-access rules defined for the cell (or if there are no rules defined at all), OLAP Services does not perform any further checks and returns the value of the cell. This is the default state.
If security checks are disabled, OLAP Services does not perform any further checks and returns the value of the cell.
If Readable (Rule 1) or Write-enabled (Rule 3) access applies, evaluate the rules. If either rule is satisfied, OLAP Services does not perform any further checks and returns the value of the cell.
If Contingent Readable access (Rule 2) applies, evaluate the rule. If the rule is satisfied, OLAP Services does not perform any further checks and returns the value of the cell.
If one of the coordinates of the cells is a session or query calculated member (defined by the client application, as opposed to a global calculated member defined on the server by the DBA), OLAP Services returns the value of the cell.
If none of the above is satisfied, OLAP Services returns an error condition.

The following conditions apply during read access validation:

This sequence is recursive when a cell is a derived cell (one of its coordinates is a calculated member)
When a rule is being evaluated, security checks are disabled to avoid an instance of infinite recursion
The access validation rules will be enforced for SQL queries attempting to read the contents of a measure column in a rowset

Updates

When a client attempts to update a cell, OLAP Services evaluates permissions in the following sequence:

If the session is a read-only session (either the user has read-only access or the session was declared as read-only), OLAP Services updates the cell.
If Rule 3 applies, evaluate the rule. If the rule is satisfied, OLAP Services does not perform any further checks and updates the cell.
If none of the previous steps applies, the operation fails.

Commits

When a client attempts to commit a transaction the following applies:

Transaction commits will be performed only if the user has read/write access (as defined in the cube role) to the cube and the session was not declared as read-only.

Defaults

If no security rules are defined, the following defaults apply:

If no read access or contingent read rules (Rule 1 or Rule 2) are provided, read access to all cells is allowed.
If no write access rule (Rule 3) is provided, and the user has Write permission on the parent cube, write access to all readable cells is allowed.

---------------------------------------------

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Due to the nature of ongoing development work and because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft, the BackOffice logo, Visual Basic, PivotTable, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Other trademarks and tradenames mentioned herein are the property of their respective owners.

The names of companies, products, people, characters, and/or data mentioned herein are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted.