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 Rows
FROM 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.
End of Article
Prev. page
1
[2]
next page -->
