Introduction

Cubing Services provide multidimensional access to your relational data. The services allow MDX queries to be issued directly against the data warehouse, enabling the warehouse to become the single source of data for analytical tools and reducing the latency between when the warehouse is updated and when tools have access to the data in the warehouse. As a component of InfoSphere Warehouse, Cubing Services provide all the tools you need to design, deploy, and administer a multidimensional model of your relational data.

In InfoSphere Warehouse V9.5, Cubing Services enabled you to secure your cubes using the Administration Console. You could either grant or deny a role the privilege to query a cube. In V9.7, you can still restrict access to a cube, but you use the Design Studio to do so.

A role that is allowed to access a cube is allowed to access all the members of a dimension by default. Starting with V9.7, you can limit the members that a role can access to a subset of the members of the dimension. You might need to limit the members that a role can access to ensure that only legitimate users have access to the information they need.

For example, you might have a GO Sales and Marketing cube that contains information about retailers in different countries. The sales managers in one country might need access to information about the retailers in their own countries but not to information about the retailers outside their countries. With dimension security, you can limit the members of the Retailers dimension that sales managers can access to only those in the countries that they manage. Figure 1 shows the security restrictions that you could impose on the Retailers dimension of the GO Sales and Marketing cube for managers in the United States.

Members of the dimension are shown in hierarchical order. The members that appear with an asterisk are visible to the U.S. managers, while the ones without an asterisk are not. When U.S. managers query the cube for all the members of the dimension, they will see U.S.-only results, as shown in Figure 2.

The U.S. managers can see the sale totals for retailers in the United States; they cannot see the sale totals for retailers outside the United States.

While U.S. managers cannot see the individual sale totals for retailers outside the United States, they can deduce the aggregated sale total of those retailers. For example, the sale total for the Americas region includes the sale total for not only the United States but also for other countries in the Americas, such as Canada. From the sale totals for United States and the Americas, they can determine the sale total for other countries in the Americas.

To limit the cubes and members of a dimension that a role can access, you need to design and deploy a security model. The security model contains the rules for accessing OLAP objects. The security model does not contain the actual definition of these OLAP objects—the cube model does. Because the security model depends on the cube model, you should design and deploy the cube model before designing and deploying the security model.

Understanding the steps to design and deploy a security model

The high-level steps to design and deploy a security model are:

1. Create a security model in the Design Studio.
   
   
2. Export the security model from the Design Studio to an XML file.
   
   
3. Import the security model from the XML file to the InfoSphere Warehouse control database using the Administration Console.
   
   
4. View the security model in the Administration Console.
   
   
5. Instruct the Cube Server to refresh security and start enforcing the security model.
   
   
6. Modify the security model, if needed.
   
   
7. Use the Administration Console to delete the security model, if needed.

Figure 3 shows a flow of these steps among the components of Cubing Services.

Back to top

Creating a security model

The Cubing Services security model consists of the following elements:

• Resource - An OLAP object on which security is defined, such as cube or dimension
  
  
• Role - A group of users who share the same security privileges
  
  
• Policy - A rule for accessing a resource, such as allowing read access to a cube or denying read access to the ascendants and descendants of the U.S. members of the Retailers dimension
  
  
• Authorization - An association among a resource, role, and policy

Figure 4 shows these security model elements.

To create a security model, open your cube model in the Design Studio, and select in the Data Project Explorer the resource on which you want to define security. You can define security on cubes, cube hierarchies, and cube facts in the Design Studio. Cube hierarchies and cube facts in the Design Studio correspond to dimensions in the Cube Server. Note that you cannot define security on a virtual cube. If you need to secure a virtual cube, you need to define security on the non-virtual cubes on which the virtual cube is based. The virtual cube inherits the security definitions of its base cubes.

Define cube security

Before you can define security on a cube, you need to enable it. Enable security by opening the Properties view of the cube and checking the Enable Security box on the Authorizations tab, as shown in Figure 5.

After you enable security, you see two policies for the cube on the Policies tab: Read Allowed and Read Denied, as shown in Figure 6. The Read Allowed policy allows access to the cube while the Read Denied policy denies access to the cube.

To assign a policy to a role, you need to type in the name of the role and select the policy on the Authorizations tab. In Figure 7, the role United States Sales Managers has been assigned the policy Read Allowed.

Note that a role called Default Role appears in Figure 7. Users who do not belong to a role with explicit authorization on the cube are considered to be members of the default role. For example, if the user Joe is not a member of the role United States Sales Managers, then he is considered to be a member of the role Default Role.

In addition to the default role, each cube has a default policy. The default policy is simply the policy assigned to the default role. When security is first enabled on a cube, the default policy is the Read Allowed policy. It can be changed, however, to the Read Denied policy.

Together, the default role and default policy make up the default authorization: the implicit rule of access on the cube for users with no explicit rule of access.

Define dimension security

Non-measures dimension

Starting with InfoSphere Warehouse V9.7, you can define security on a dimension. You define security on a dimension by specifying the members of the dimension that a role can access. Select a hierarchy in the dimension in Data Project Explorer in the Design Studio. Then, enable security by checking the Enable Security box on the Authorizations tab in the Properties view, as shown in Figure 8.

After you enable security, you can create policies for the dimension on the Policies tab. A policy consists of a name, description, set of allowed members, and set of denied members. The sets of allowed and denied members must be MDX expressions that evaluate to sets of members of the dimension. From the fields for Allowed Members and Denied Members, you can launch the MDX Expression Builder to help you build the MDX expressions. Note that the MDX Expression Builder only checks for syntax errors when you click the Validate button.

You must specify an expression for Allowed Members or Denied Members. If you do not specify an expression for Allowed Members, all members of the dimension are considered to be accessible by a role that is assigned the policy. If you do not specify an expression for Denied Members, no members of the dimension are considered to be inaccessible by a role that is assigned the policy. If you specify expressions for both Allowed Members and Denied Members, then a role that is assigned the policy is allowed to access the members specified in Allowed Members, except the members specified in Denied Members.

For example, Figure 9 shows the policies of the Retailers dimension. The United States Retailers policy allows access to the ascendants and descendants of United States.

In Figure 9, you see two policies, an All Allowed policy that permits access to all members of the dimension and an All Denied policy that denies access to all members of the dimension. These policies are created automatically for your convenience. Note that any role that is assigned the All Denied policy will not have access to any cells of the cube, because the role cannot slice the cube along any member of the dimension.

You can assign policies to roles on the Authorizations tab. A policy can be assigned to more than one role, and a role can be assigned more than one policy. If a role is assigned more than one policy, a user in the role is authorized to access the union of the Allowed Members specified in the policies minus the union of the Denied Members specified in the policies. The same is true if a user is in multiple roles.

For example, Figure 10 shows that the United States Retailers policy has been assigned to the United States Sales Managers role.

Just as each cube has a default role and policy, each dimension has a default role and policy. All users who do not belong to a role with explicit authorization on the dimension are members of the default role, and they are able to access the dimension according to the default policy. The default policy can be any policy, including one that you create yourself.

Measures dimension

You can define security on the measures dimension in addition to a non-measures dimension. To define security on the measures dimension, select the Cube Facts object in Data Project Explorer and perform the same steps you followed for defining security for a non-measures dimension.

Figure 11 shows security on the measures dimension. It shows that United States Sales Managers are allowed to access only the Sale total measure.

Manage roles

To see the roles in your security model, select the OLAP roles object in Data Project Explorer, as shown in Figure 12.

You can add, modify, and delete roles in the Properties view of the OLAP roles object. Figure 13 shows that there is one role in the security model named United States Sales Managers.

Back to top

Exporting the security model

When you are finished designing your security model, you can export it to a file for import later using the Administration Console. An export wizard guides you through the export process. In the wizard, select the cubes to export, as shown in Figure 14.

The security objects associated with the selected cubes and its dimensions are exported. Only security-enabled cubes can be exported, and only the security-enabled dimensions contained in the cubes are exported. Of these security-enabled cubes and dimensions, only the roles and policies that are referenced by an authorization are exported.

Once you complete the export, the file you specified contains your security model. This file is separate and different from the file that is created when you export your cube model. You must keep both files in sync in order for the Cube Server to properly enforce your security rules. You should re-export and re-import your security model if you do the following:

1. Export the security model.
2. Import the security model.
3. Modify the cube model.
4. Export the cube model.
5. Import the cube model.

Back to top

Importing the security model

Before you import the file containing your security model, import the file containing your cube model into the InfoSphere Warehouse control database using the Administration Console.

You are prompted to select a database when you import the file containing your cube model. You need to select the same database when you import your security model, as shown in Figure 15.

As in previous releases of Cubing Services, this database is used to authenticate users when they query the cubes in your cube model.

After you select the database, select the security file to import, as shown in Figure 16.

After you select the security file to import, map roles. You can map roles that you created in the Design Studio to ones that were created in the Administration Console, as shown in Figure 17.

If no roles were created in the Administration Console, you can keep the default mapping and have the roles that you created in the Design Studio automatically created in the Administration Console. The Cube Server administrator, who manages roles and the assignment of users to roles, might have created roles in the Administration Console. Because the administrator might have used different names for roles than you did, the mapping of roles provides an opportunity to bridge the difference in names.

After you map roles, specify how collisions should be resolved if any are detected during the import of the security file. Collisions are detected at the resource level, and you have three options for resolving them:

• Merge Replace – If security metadata exists in the repository for a resource, the security metadata in the file will replace the security metadata in the repository for the resource.
  
  
• Merge Ignore – If security metadata exists in the repository for a resource, the security metadata in the file will be ignored and the security metadata in the repository will be kept for the resource.
  
  
• Replace – Any security metadata in the repository associated with the cubes in the security file will be removed, and all security metadata in the file will be added.

Figure 18 shows the options in the wizard.

Examples of merge options

Examples of the three options for resolving collisions appear below. The examples assume the following for the existing and incoming security models.

Existing security model

• Cube: GO Sales and Marketing
  • Policies: Read Allowed
  • Authorizations: (Default Role, Read Allowed)
  • Dimension: Retailers
    • Policies: All Allowed
    • Authorizations: (Default Role, All Allowed)
  • Dimension: Products
    • Policies: All Allowed
    • Authorizations: (Default Role, All Allowed)
• Cube: hr
  • Policies: Read Denied
  • Authorizations: (Default Role, Read Denied)

Incoming security model

• Cube: GO Sales and Marketing
  • Policies: Read Allowed, Read Denied
  • Authorizations: (Default Role, Read Denied), (United States Sales Managers, Read Allowed)
  • Dimension: Retailers
    • Policies: All Denied, United States Retailers
    • Authorizations: (Default Role, All Denied), (United States Sales Managers, United States Retailers)
  • Dimension: Measures
    • Policies: All Denied, Sale Total
    • Authorizations: (Default Role, All Denied), (United States Sales Managers, Sale Total)
• Cube: warehouse
  • Policies: Read Allowed
  • Authorizations: (Default Role, Read Allowed)

Example of Merge Replace option

If you select the Merge Replace option to resolve collisions between the existing security model and the incoming security model, each cube or dimension is handled as shown in bold on the left side of Table 1. The resulting security model is shown on the right side of Table 1.

If you select the Merge Replace option, the resulting security model contains the security metadata associated with the GO Sales and Marketing cube and the Retailers dimension from the incoming security model. The cube and dimension in the incoming security model collide with the cube and dimension in the existing security model, so the security metadata associated with the cube and dimension in the existing security model are replaced. Because the Products dimension and the hr cube in the existing security model do not appear in the incoming security model, the policies and authorizations associated with these resources remain in the resulting security model. Similarly, the policies and authorizations associated with the Measures dimension and warehouse cube in the incoming security model are added to the resulting security model, because the dimension and cube are not found in the existing security model.

Example of Merge Ignore option

If you select the Merge Ignore option to resolve collisions between the existing security model and the incoming security model, each cube or dimension is handled as shown in bold on the left side of Table 2. The resulting security model is shown on the right side of Table 2.

In you select the Merge Ignore option, the security metadata associated with the GO Sales and Marketing cube and the Retailers dimension in the incoming security model are ignored, because the cube and dimension collide with the cube and dimension in the existing security model. The security metadata associated with the cube and dimension in the existing security model are kept in the resulting security model. The Products dimension and hr cube in the existing security model do not appear in the incoming security model, so the policies and authorizations associated with these resources are also kept in the resulting security model. Because the Measures dimension and the warehouse cube in the incoming security model do not appear in the existing security model, the policies and authorizations associated with these resources are added to the resulting security model.

Example of Replace option

If you select the Replace option to resolve collisions between the existing security model and the incoming security model, each cube or dimension is handled as shown in bold on the left side of Table 3. The resulting security model is shown on the right side of Table 3.

If you select the Replace option, the resulting security model does not contain any of the security metadata associated with the GO Sales and Marketing cube and Retailers and Products dimensions from the existing security model. The policies and authorizations associated with the cube and its dimensions in the incoming security model replace all of the policies and authorizations associated with the cube and its dimensions in the existing security model. Because the hr cube in the existing security model does not collide with a cube in the incoming security model, the security metadata associated with this cube remains in the resulting security model. Also, the resulting security model contains the security metadata associated with the warehouse cube in the incoming security model, because the cube does not collide with a cube in the existing security model.

Back to top

Viewing the security model

After you finish importing the security model, you can view it as it exists in the repository using the Administration Console. The Administration Console provides two different views of the security metadata in the repository: one by cube and the other by role. Figure 19 shows the security metadata associated with the role United States Sales Managers.

Back to top

Enforcing the security model

Add users to roles

For the Cube Server to enforce your security model when users execute queries, you need to add users to roles. You can add users to roles using the Administration Console. If you select a role and click the Manage Users button on the Manage Roles page, you can add and remove users on the Manage Users page. Figure 20 shows that the role United States Sales Managers has one user named db2admin.

The users in a role must be DB2 users, because the Cube Server delegates the task of performing authentication to DB2. The specific requirements of the users depend on the security configuration of DB2. For example, if DB2 is configured with an operating-system-based authentication mechanism, users need valid operating system user IDs and passwords. Contact your database administration or refer to the DB2 documentation for more information about the security configuration of your DB2 instance.

Reload security

When users query a cube, the Cube Server makes contact with DB2 and requests that it authenticate the users. After DB2 authenticates the users, the Cube Server determines whether they are authorized to access the cube and dimension members referenced in the query. The security model that you imported using the Administration Console contains the authorizations that the Cube Server uses to make this determination.

The security model that you imported using the Administration Console affects all Cube Servers that make the cubes in the security model available. A Cube Server does not start enforcing the rules in the security model until the Cube Server is started. If the server is already running at the time of the import, it does not start enforcing the rules until it is restarted or it is instructed to reload security. Reloading security is less disruptive to Cube Server clients than restarting the Cube Server. It is recommended that you instruct a Cube Server to reload security instead of restarting it in order to update its security model. You can instruct a Cube Server to reload security by selecting it on the Manage Cube Servers page in the Administration Console and clicking Reload Security.

After you tell the Cube Server to reload security, it will authorize users according to the policies in your security model. Figure 21 shows what db2admin, a user in the United States Sales Managers role, would see if he queried for all the members of the Measures and Retailers dimensions.

Back to top

Modifying the security model

If you need to modify the security model after importing it into the repository, do the following:

1. Reopen the Design Studio.
2. Modify the security model in the Design Studio.
3. Repeat the steps you followed in this article to export, import, and enforce your security model.

You might need to modify or re-export the security model if you modify your cube model by, for example, deleting or renaming dimensions.

Back to top

Deleting the security model

To remove the security metadata associated with a cube in your cube model, use the Administration Console to do the following:

1. Select the cube model on the Manage Cube Models page.
2. Click the Edit icon.
3. Select the cube.
4. Click the Delete Cubes and Security icon, as shown in Figure 22.

You have the option to delete only the security metadata associated with the cube, as shown in Figure 23.

After you delete the security metadata associated with the cube, instruct the Cube Servers that make the cube available to reload security if they are running at the time of the delete. Doing so instructs the servers to stop enforcing security immediately.

Back to top

Conclusion

In this article, you learned about cube and dimension security in Cubing Services. Using IBM InfoSphere Warehouse V9.7, you learned that you can limit access to your cubes and dimensions by creating a security model in the Design Studio, exporting it to a file, importing it using the Administration Console, and instructing the Cube Server to enforce the security model. You also learned how to update the security model using the Design Studio and how to delete the security model using the Administration Console.

Resources

Learn

• Learn about data warehousing and business intelligence from the Data Warehousing area of developerWorks.
  
  
• Find information on how to use the features of IBM InfoSphere Warehouse V9.7 from the IBM DB2 Database for Linux®, UNIX®, and Windows® Information Center.
  
  
• Get in-depth information about Cubing Services by reading the IBM Redguide Enabling Robust Business Analytics with InfoSphere Warehouse Cubing Services and the IBM Redbooks® Multidimensional Analytics: Delivered with InfoSphere Warehouse Cubing Services and InfoSphere Warehouse: Cubing Services and Client Access Interfaces .
  
  
• Get product information about InfoSphere Warehouse.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Download a free trial version of IBM InfoSphere Warehouse 9.7 Enterprise Edition.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Understanding the steps to design and deploy a security model
• Creating a security model
• Exporting the security model
• Importing the security model
• Viewing the security model
• Enforcing the security model
• Modifying the security model
• Deleting the security model
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: InfoSphere Warehouse
• Demo: InfoSphere Warehouse introduction
• Buy: InfoSphere Warehouse

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers