Lesson 3: Managing Security

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

Cube-Level and Database-Level Security

OLAP Services cube-level and database-level security works as follows:

  • You add Windows NT users and groups to roles in databases and cubes on the OLAP Server.
  • You assign permissions to the roles on the OLAP Server.
  • A user logs on by using a Windows NT user account. Windows NT authenticates that the user is a valid Windows NT user. Windows NT identifies the groups of which the user is a member.
  • When the user uses the OLAP Server, OLAP Services checks the role membership of the user and the groups of which the user is a member. OLAP Services then determines the permissions of the user based on their role membership. Roles are discussed in Lesson 4 of Chapter 9, "Managing Cubes Using Storage Types and Partitions."

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:

  • Read. Provides read-only access to the data. Supported functionality includes browsing of data and data structures (metadata). This level does not allow modifying data and processing of data. Administration of this level of access control is supported explicitly via the OLAP Manager user interface.
  • Read/Write. Provides write access to a write-enabled cube. Supported functionality includes all read access functionality and modifying data in cubes designated and enabled for write-back. Administration of this level of access control is supported explicitly via the OLAP Manager user interface.
  • Admin. Provides access to the OLAP Manager user interface and allows processing of data. Upon installation, OLAP Services creates a Windows NT group named OLAP Administrators and adds the logged-in user to this group. Only members of this group can start the OLAP Manager user interface and use administrative functions, including security management. Use the Windows NT User Manager to manage this group.

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.

Cell-Level Security

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:

  • Readable cells
  • 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.

  • Contingent Readable cells
  • 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.

  • Write-Enabled cells
  • 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

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:

  • 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 databases subordinate cubes
  • Creating an MDX expression and saving it in the cube role Permissions collection

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.

Exercise 1: To Enable Security for an OLAP Services Database

In this exercise, you will create Windows NT user accounts and set up security in the Northwind_DSS database.

  • To create Windows NT user accounts
  • In this procedure, you will create the Windows NT users who will become members of the Northwind_DSS database roles.

    1. In the Administrative Tools group on the Start menu, start User Manager for Domains.
    2. Notice that there is already a group called OLAP Administrators. This group is created automatically during the OLAP Services installation.
    3. Add a new user called ReadOnlyUser. Deselect the User must change password at next logon. Select the User Cannot change password and Password never expires. Leave the password blank.
    4. Add a new user called ReadWriteUser. Deselect the User must change password at next logon. Select the User Cannot change password and Password never expires. Leave the password blank.
    5. Add a new group called OLAP Read Only. Add ReadOnlyUser to this group.
    6. Add a new group called OLAP Read Write. Add ReadWriteUser to this group.

  • To create roles in the Northwind_DSS database
  • 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.

    1. In OLAP Manager, right-click the Sales cube in the Northwind_DSS database, and then click Manage Roles.
    2. In the Manage Roles dialog, click New Role.
    3. Name the role Sales_ReadOnly.
    4. Click the Groups and Users button.
    5. In the Names list, click the OLAP Read Only Windows NT group. Click the Add button. Click the OK button to add the group to the Sales_ReadOnly role.
    6. Click the OK button to close the Create a Database Role dialog box.
    7. In the Manage Roles dialog, click New Role.
    8. Name the role Sales_ReadWrite.
    9. Click the Groups and Users button.
    10. In the Names list, click the OLAP Read Write Windows NT group. Click the Add button. Click the OK button to add the group to the Sales_ReadWrite role.
    11. Click the OK button to close the Create a Database Role dialog box.
    12. In the Manage Roles dialog box, click Sales_ReadWrite so that only Sales_ReadWrite is highlighted. Check the Grant Read/Write permission check box.
    13. Click the OK button to close the Manage Roles: Sales dialog box.

  • To write-enable the cube
  • In this procedure, you will write-enable the Sales cube in the Northwind_DSS database.

    1. Right-click the Sales cube in the Northwind_DSS database, and then select Write-Enable.
    2. Click the OK button to accept the defaults and write-enable the cube.

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.

Lesson Summary

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.



Microsoft Corporation - Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
ISBN: 0735606706
EAN: 2147483647
Year: 1999
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net