Mastering OLAP: Cell-level Security

Set and manage security with the DSO COM interface

OLAP applications are hot these days. After users experience the power of using OLAP tools to access data and display materialized views, they won't want to do without OLAP. But as you know, business applications with proprietary information necessitate the "S" word. I'm not talking about scalability; I'm talking about security. By adding the OLAP Services cell-level security feature, Microsoft turned OLAP into a commodity most organizations will want to adopt.

If you've been using OLAP Services for a while, you might think that OLAP Services offers only cube-level security. But Service Pack 1 (SP1) for SQL Server 7.0 includes cell-level security for OLAP Services. Unfortunately, few people know about cell-level security because Microsoft still doesn't provide a GUI to manage it. However, with SP1 installed, you can use the Decision Support Object (DSO) COM interface to set and manage cell-level security.

Why should you use cell-level security? Cell-level security lets you tailor OLAP cube cell values, so different users can see only certain cell cubes. For example, if you have a cube of company expenses, you might want managers to see the cube's salary expenses by department rather than by individual employee. Also, you might want the department manager, but not the data-entry clerk, to see the individual employee salaries. As another example, you might want all employees to see sales performance by region, but not each sales rep's performance or commissions. Cell-level security lets different users see different views of the data without requiring a different cube (or virtual cube) for each group of users.

You use a programmable object model to customize OLAP Services. Programmers can access the COM programming interface, DSO, on the server running OLAP Services. The OLAP Manager is nothing more than a Visual Basic (VB) program written in DSO, which means that all the OLAP Manager's functionality is available to DSO programmers.

Let's discuss OLAP Services' security capabilities. Roles manage security. You create roles in an OLAP database and assign them to cubes in that database. Each role has a name, a description, and a list of Windows NT users and groups who have permission on the cube. You create the users and groups and manage them with administrative utilities, such as the NT User Manager. When you assign a database role to a cube, you give the role a permission. In the OLAP Manager, the permission must be read access or read and write access. (If it seems as if you're missing something, you're not. OLAP Services security isn't as flexible as the support SQL Server offers.) If you access DSO directly, you can create a cell-level permission that an MDX expression and permission level define. For any cell in a cube, the MDX expression must evaluate to true (1) or false (0). If the expression evaluates to true, the users in the role have the specified permission level. Setting security this way isn't for the faint-hearted.

Configuring Cell-Level Security

Here's a short VB program that uses DSO to configure cell-level security. In this example, you'll set the permissions so the role's users can see only cell values for the measures Store Sales and Store Sales Net. You can use the VB code in Listing 1 to set the permissions.

The DSO allows three permission levels—CellRead, CellWrite, and CellReadContingent. CellWrite is a superset of CellRead. If you can write to a cell, you can read from it. CellRead and CellRead-Contingent are similar. CellRead and CellRead-Contingent differ in how they treat calculated members. (A calculated member is a new dimension member whose value is defined by a formula and calculated at runtime. For more information, see Mastering OLAP, "Indispensable Calculated Members," June 1999.)

Let's say you have a formula for calculated member X that uses dimension member Y (X = f(Y)). If you have CellRead permissions on X, you can view X's value even when Y is protected. If you have CellRead-Contingent permissions on X, you must have CellRead permissions on Y to view X. In many cases, you'll want to let users view a derived number and not the actual number. For example, if you built a cube from an investment company's client portfolio information, it would make sense to let everyone see what percentage of an individual's portfolio he or she invests in various stocks, but not the total value of his or her portfolio. If you set up the cube with CellRead permissions on X (the percentages) and set up Y (the portfolio) as protected, the user can see the percentages, but can't determine the total value of the portfolio.

The VB program in Listing 2 creates new roles on the OLAP Services sample FoodMart Sales cube. First, make sure you compile the program on an NT machine with an NTFS file system and OLAP Services with SP1 applied. The security features of OLAP Services rely on an NTFS-type file system to enforce the permission levels. Then from the Project menu, select References and the check box labeled Microsoft Decision Support Objects, as Screen 1, page 62, shows. This selection ensures that the DSO object model is available to VB. Before you run this program, you need to log in as a user with OLAP Administrator access. You can verify that you have this access by running the User Manager in the Administrative Tools group of the NT start menu. Double-click the OLAP Administrators group in the bottom list box, and verify that your user name is in the displayed list. Finally, you need to create two users, Test1 and Test2, which aren't in the OLAP Administrators group. Any user in the OLAP Administrators group has read access to OLAP cubes on that machine regardless of the roles assigned to the cubes.

When you run the CellSecurity program, it sets cell-level privileges while the main form loads. After you see the form on the screen, the roles and privileges are established, and you can exit. You can then log off your computer, then log in as either Test1 or Test2 and use the MDX sample application to verify that the permissions were set correctly. But before you set up the cell-level privileges, let's discuss what the CellSecurity program does.

The AddRole subroutine does most of the work in this program. AddRole takes six parameters: the database name, the cube name in the database, the new role name, a list of users, a permission, and a permission string. Then the AddRole subroutine creates the role in the specified database and adds the role to the specified cube. AddRole then sets the new role's permissions to match the specified permission level and MDX expression.

The program logic is fairly easy to follow. At the start of the Form_Load subroutine, you create a new DSO Server object and connect to OLAP Services. Specifying LocalHost as a server name is equivalent to using the local machine name. After you connect to the local copy of OLAP Services, you call AddRole. AddRole finds the database and cube objects that the string parameters specify. Then the code determines whether the specified role name already exists in the database object's collection of roles. If the database role doesn't exist in the object's collection, the specified user list creates it.

Next, the CellSecurity program determines whether the DSO cube object contains the specified role. If the cube object doesn't contain the specified role, the AddRole adds the role to it and defines the permissions and MDX expression. After the AddRole subroutine successfully creates the first role, the Form_Load subroutine continues and defines the second role. The two roles that the CellSecurity program creates demonstrate the difference between CellRead and CellReadContingent permissions.

When you install OLAP Services, the FoodMart Sales cube has a role that gives users group-read access, so they can view the cube's contents. The CellSecurity program removes this role for all users, so the new users, Test1 and Test2, must rely on the new roles for read access.

Now, let's look at the roles that the CellSecurity program creates. The first role gives the Test1 user CellRead access to any cell, which includes the Store Sales or the Sales Count measure. With this role alone, the Test1 user can view the values of these two measures, but can't view the values of any other measures in the Sales cube.

The second role gives Test1 and Test2 CellReadContingent permissions to the Sales Average calculated member. Sales Average is a calculated member with the formula [Measures].[Store Sales]/[Measures].[Sales Count]. Remember, to view the contents of a calculated member's cell with CellReadContingent permissions, you must be able to read the dimension members that derive the calculated member.

To demonstrate the effects of the CellSecurity program, you can run the MDX query in the MDX sample application. You can find the sample application in your Start menu in the SQL Server 7.0, OLAP Services program group.

SELECT [Time].[Year].Members ON Columns,AddCalculatedMembers( [Measures].Members )   ON RowsFROM Sales

This MDX query lists all the measures available in the FoodMart Sales cube, including the calculated members on the rows and the years from the Time dimension on the columns. Screen 2 shows the result of running this query as the user Test1, and Screen 3 shows the results as user Test2.

The results show that only Test1 can view the values of Sales Average and Test2 can't view any values in the Sales cube. Test1 also has read access to the underlying members that the Sales Average is based on.

The CellSecurity program uses a simple type of MDX expression for permissions that demonstrates how to protect cells by measure name. You'll usually want to set security at a higher level of granularity than a measure name by protecting all dimension members descending from a common member. For example, you might have a security policy that states that only California sales reps can view cell values for customers in California. To set this value you can use the following expression:

   iif( Ancestor([Customers].CurrentMember,   [State Province]).Name = ""CA"", True,   False)   

This expression finds the ancestor of the currently selected member in the Customers dimension at the [State Province] level. If the name of this ancestor is CA, the expression returns true; otherwise, it returns false.

Security is an important part of any enterprise application particularly if the application exposes sensitive business information, which OLAP applications typically do. The cell-level security features in SQL Server 7.0 OLAP Services let the developer customize permissions to the most granular level in an OLAP cube. The first release of OLAP Server was a great initial offering. However, Microsoft should have taken the time to implement a more usable security model. How many people would use SQL Server if COM developers had to set all table and column security permissions programmatically? The next release of OLAP Server will have improved security management features.

For additional information about cell-level security, read "Microsoft SQL Server OLAP Services Cell-Level Security." You can download this white paper from http://www.microsoft.com/sql/ productinfo/celllevel.htm.