• Article

Working with Roles

Note

  This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

Roles are used in Microsoft® SQL Server™ 2005 Analysis Services to provide security for databases, cubes, and mining models. Decision Support Objects (DSO) provides the Role object for administering all three types of roles.

Database Roles

A database role applies to a single Analysis Services database, and it includes a list of Microsoft Windows NT® 4.0 or Microsoft Windows® 2000 user accounts and groups. A database role does not control administrative access to an Analysis Services object; instead it determines read and write capabilities when a user is connected to an Analysis Services database through a client application. Database roles can be used to manage the dimension security for shared dimensions in a database for multiple cubes. For more information about dimension security, see Dimension Security.

When a database role is assigned to a cube or mining model, Analysis Services creates a corresponding cube or mining model role. The property values specified in the database role are then propagated to the newly created cube or mining model role. A cube or mining model role cannot exist without a corresponding database role.

If the property value of a database role is changed, any cube or mining model role based on that database role is also changed, but only if the cube or mining model role still uses the value propagated from the database role. In other words, if you change a cube or mining model role property directly, changing the corresponding database role property does not override the changed cube or mining model role property.

For more information about database roles, see Database Roles.

A database role is represented in DSO by a Role object of ClassType clsDatabaseRole. The available properties for the Role object are different for each type of role, and the behavior of some properties changes as well. For more information about the Role object, see Role Interface.

Use the following example to create a new database role. The database role created in the example will be used by the other examples in this topic.

This code example creates a new database role, named TestRole, in the FoodMart 2000 database:

Private Sub CreateDatabaseRole()
   Dim dsoServer As New DSO.Server
   Dim dsoDB As DSO.MDStore
   Dim dsoCube As DSO.MDStore
   Dim dsoRole As DSO.Role
   
   Dim sDimensionSecurity As String

   ' Connect to the local server.
   dsoServer.Connect "LocalHost"

   ' Connect to the FoodMart 2000 database.
   Set dsoDB = dsoServer.MDStores.Item("FoodMart 2000")
   
   ' Create a new database role named TestRole.
   Set dsoRole = dsoDB.Roles.AddNew("TestRole", sbclsRegular)
   
   ' Create the XML syntax to be used for the SetPermissions method
   ' of the Role object.
   sDimensionSecurity = "<MEMBERSECURITY " & _
      "DefaultMember=""[Store].[Store Country].&amp;[USA]"" " & _
      "VisualTotalsLowestLevel=""[Store].[Store City]"">" & _
         "<PERMISSION Access=""Read"" " & _
         "DeniedSet=""{[Store].[Store Country].&amp;[Canada]," & _
         "[Store].[Store Country].&amp;[Mexico]}"" " & _
         "Description=""USA Store Restriction""/>" & _
      "</MEMBERSECURITY>"
      
   ' The preceding XML syntax limits the users of the database role
   ' to viewing only stores in the USA, by denying read access to
   ' stores associated with the Mexico and Canada members of the
   ' [Store Country] level of the Stores dimension.
   
   ' Change the role properties for TestRole
   With dsoRole
      ' Lock the database role.
      .LockObject olapLockRead, "Creating Role"
      
      ' Set the list of users assigned to this role.
      .UsersList = "Everyone"
      
      ' Set the role description.
      .Description = "Test role"
      
      ' Set the EnforcementLocation permission key to enforce
      ' the role on the server side.
      .SetPermissions "EnforcementLocation", "Server"
      
      ' Set the Dimension key for the Store dimension to
      ' restrict users to viewing only USA stores.
      .SetPermissions "Dimension:Store", sDimensionSecurity
      
      ' Unlock the database role.
      .UnlockObject
   End With
   
   ' Update the database role.
   dsoRole.Update
      
End Sub

A database role, as a major object, can update itself using the Update method as shown in the preceding code example. The code example also features the use of Extensible Markup Language (XML) to set dimension security with the SetPermissions method of the Role object. For more information about the XML syntax of the Dimension key for the SetPermissions method, see SetPermissions.

Cube Roles

A cube role applies to a single cube in an Analysis Services database, includes a list of Windows NT 4.0 or Windows 2000 user accounts and groups, and indicates the objects in the cube those accounts can access and the kind of access they have to those objects. The cube role is based on a database role, but it supplies additional security measures for restricting the viewing of cells within a cube as well. For more information about cell security, see Cell Security.

Properties in a cube role can be changed to reflect different security options for a single cube. However, changes to some specifications in a cube role propagate to the corresponding database role and all cube roles with the same name as the changed cube role. These specifications include the list of user accounts and groups and read/write permissions for dimensions.

For more information about cube roles, see Cube Roles.

A cube role is represented in DSO by a Role object of ClassType clsCubeRole. The following example creates a new cube role for the TestCube cube, using the new TestRole database role created in the previous code example.

The TestRole cube role is created in the Sales cube, and receives its default property values from the TestRole database role created in the FoodMart 2000 database.

Private Sub CreateCubeRole()
   Dim dsoServer As New DSO.Server
   Dim dsoDB As DSO.MDStore
   Dim dsoCube As DSO.MDStore
   Dim dsoRole As DSO.Role
   
   ' Connect to the local server.
   dsoServer.Connect "LocalHost"

   ' Connect to the TestDB database.
   Set dsoDB = dsoServer.MDStores.Item("FoodMart 2000")
   
   ' Connect to the TestCube cube.
   Set dsoCube = dsoDB.MDStores("Sales")
   
   ' As the cube is about to be changed, lock the cube.
   dsoCube.LockObject olapLockRead
   
   ' Create a new cube role named TestRole, based on the database role
   ' named TestRole.
   Set dsoRole = dsoCube.Roles.AddNew("TestRole", sbclsRegular)
   
   ' Change the role properties for the cube role.
   With dsoRole
      ' All of the other properties are propagated from the
      ' TestRole database role.
      
      ' Prevent the users associated with this role from
      ' reading the [Store Cost] measure.
      .SetPermissions "CubeRead", _
         "Measures.CurrentMember.Name <> ""[Store Cost]"""
   End With
   
   ' Update the cube role by updating the cube.
   dsoCube.Update

   ' Unlock the cube.
   dsoCube.UnlockObject
      
End Sub

Because it is a minor object, a cube role cannot update itself using the Update method. A cube role is saved when the cube to which it is associated is updated, as demonstrated by the preceding code example. The code example also demonstrates the use of Multidimensional Expressions (MDX) syntax to set cell security with the SetPermissions method of the Role object. For more information about the use of MDX syntax of the CellRead key for the SetPermissions method, see SetPermissions.

Mining Model Roles

A mining model role applies to a single data mining model and includes a list of Windows NT 4.0 or Windows 2000 user accounts and groups that have access to the data mining model.

A mining model role is similar in many respects to a cube role. The user membership of a mining model role is directly related to its corresponding database role; changes to role membership in a mining model role propagate to the database role and all mining model roles with the same name as the changed mining model role.

For more information about mining model roles, see Mining Model Roles.

A mining model role is represented in DSO by a Role object of ClassType clsMiningModelRole. The following code example creates a mining model role, also based on the TestRole database role created in an earlier code example.

This code example creates a new mining model role, named TestRole, in the Member Card RDBMS mining model of the FoodMart 2000 database.

Private Sub CreateMiningModelRole()
   Dim dsoServer As New DSO.Server
   Dim dsoDB As DSO.MDStore
   Dim dsoDMM As DSO.MiningModel
   Dim dsoRole As DSO.Role
   
   ' Connect to the local server.
   dsoServer.Connect "LocalHost"

   ' Connect to the TestDB database.
   Set dsoDB = dsoServer.MDStores.Item("FoodMart 2000")
   
   ' Connect to the Member Card RDBMS mining model.
   Set dsoDMM = dsoDB.MiningModels("Member Card RDBMS")
   
   ' Because the mining model is about to be changed, lock the
   ' mining model.
   dsoDMM.LockObject olapLockProcess, "Changing mining model"
   
   ' Create a new mining model role named TestRole, based on
   ' the database role named TestRole.
   Set dsoRole = dsoDMM.Roles.AddNew("TestRole", sbclsRegular)
   ' All of the other properties are propagated, such as the
   ' user list, from the TestRole database role.
   
   ' Update the mining model role by updating the mining model.
   dsoDMM.Update
   
   ' Unlock the mining model.
   dsoDMM.UnlockObject
      
End Sub

A mining model role, like a cube role, is a minor object. A mining model role cannot update itself using the Update method; the role is written to the database when its associated data mining model is updated.