OLAP Services integrates with the security model of Windows NT and provides security at the database level, the cube level, and the cell level. This allows you to restrict access to cubes, virtual cubes, the underlying database, and individual cells.
After this lesson, you will be able to:
- Describe the security mechanisms provided in OLAP Services
- Differentiate between cell-level security and high security levels
Estimated lesson time: 45 minutes
OLAP Services cube-level and database-level security works as follows:
Database roles simplify the management of security in OLAP Server. Roles are created at the OLAP Services database level and can be assigned to specific cubes in the database. As an administrator, you can place Windows NT users and groups into roles, and assign a standard set of permissions to each role. This way, instead of having to add a certain set of permissions to a number of users, you assign that set of permissions to a role and merely place users in that role.
The three levels of access control permissions that you can assign are the following:
Permissions that are granted to cubes and databases typically allow users to view either all or none of the data. This level of security does not provide for users who need 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 helps to address situations that require 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.
NOTE
Cell-level security is not supported in OLAP Services releases prior to the Service Pack 1 (SP1) release. This section describes the basics of cell-level security. You can find more information in the CellSecurity document that can be found at http://www.microsoft.com/sql/productinfo/celllevel.HTM and when you install OLAP Services SP1.
Cell-level security is enforced through the use of security roles and access rules. Database roles maintain groups and users (established using the administrative functions in the 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 permissions for the roles assigned to a cube store 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. The security rules that you create define a user s ability to retrieve a cell s value. If the test of a required security rule succeeds, the value of the cell is returned. If the test fails, access is denied.
A cell-level security rule is defined as a multidimensional expression (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.
The following two examples illustrate typical security rules. These rules are defined for a role, and the members of the role will be granted access to cells for which the rule returns True (1) and will be denied access to cells for which the rule returns False (0).
The first example permits access only to cells that do not have the measure Salary as one of the cell coordinates.
IIf(Measures.CurrentMember.Name = "Salary", 0, 1) |
The second example 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", 1, 0) |
Users may be assigned to more than one role. Each role has its own defined security permissions. 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.
Three categories of cell-level security rules can be identified:
An attempt to read a cell, in which a required cell security rule evaluates to True and returns the value of the cell. An attempt to read a cell in which all security rules evaluate to False prevents the value of a cell from being returned.
An attempt to read a cell, in which a required cell security rule evaluates to True and 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 in which all security rules evaluate to False prevents the value of a cell from being returned.
An attempt to write to a cell, in which a security rule evaluates to True and allows the cell value to be changed. An attempt to write to a cell in which a security rule evaluates to False fails. Cell values can be changed but not committed if a cell security rule evaluates to False.
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 the following steps:
Administering database and cube roles can be handled through the OLAP Manager. You have to generate and store the MDX expressions used to enforce security programmatically, using the Decision Support Objects (DSO) SetPermissions statement.
In this exercise, you will create Windows NT user accounts and set up security in the Northwind_DSS database.
In this procedure, you will create the Windows NT users who will become members of the Northwind_DSS database roles.
In this procedure, you will add roles in the Northwind_DSS database. Then you will add to these roles the groups that you created in the previous procedure.
In this procedure, you will write-enable the Sales cube in the Northwind_DSS database.
NOTE
Although the cube is now write-enabled, there is no facility built in to the OLAP Manager to test the ability to write changes to the cube. A working write-back sample called VbAdoWriteback is located in the samples.exe self-extracting file in the \MSOLAP\samples directory on the SQL Server compact disc.
OLAP Services provides security from the database level and cube level down to the cell level to allow you to implement security according to the needs of your environment. OLAP Services security integrates with user and group structure in Windows NT to provide a combination of roles and levels of enforcement. Cell-level security, which became available with OLAP Services SP1, 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.