Defining Security

Now that you know how to create and enhance a cube to meet your business needs, you also need to know how to provide the right level of access to end users. Many people consider security to be a management task to be assigned to the administrators. However, your solution might be require Analysis Services to perform fine-grain security checks, which can adversely impact calculations. Hence, the cube developer should be actively involved in defining and testing security to verify users see the right data and experience good query performance.

Analysis Services 2005 provides you with fine-grain security to control access to metadata and data. You can also grant permissions to certain users who need the ability to process a database, but who do not need full control of the database. You can choose to secure data at the cube level, the dimension level, or even the cell level. Because security is an important topic for you to understand, especially with regard to dimension and cell security, Chapter 19 provides a complete example of a security definition. In this section, you learn the basic steps involved in granting write access permissions to a cube and its dimensions. The following steps will help you better understand how access permissions can be applied to your cube and dimensions:

1. Right-click the Roles folder in the Solution Explorer of the sample Adventure Works DW project and choose New Role. BIDS will create a new role called Role.role and opens the Roles designer as shown in Figure 9-76.

   
   Figure 9-76

2. In the General tab, you can give members you assign to this role full control of the database, or you can limit their activities to processing the database or simply reading the database definitions. Selecting Full Control automatically grants full access including write permissions to change the objects within the current database. The Process Database option grants access to the users so that they can read the metadata about database objects and to process the objects. Users who have Process Database control will not be able to make changes to dimensions and cubes or even data within these. Read Definition allows the users to read metadata of the objects within the database, however it does not give you access to the data. Select the option that is best-suited for your users and provide some description for the current role in the role description box.

3. Click the Membership tab. In the Membership tab, you can add the list of users for whom you want the specific access you have selected. Analysis Services accepts all domain users or machine users in this dialog. Analysis Services verifies that the user entered is a valid user and then stores the id of the user within the database. Add a local machine user or a domain user in your company to the membership list and then click the Data Sources tab.

4. Figure 9-77 shows the Data Sources tab which you use to restrict users from accessing the cube's data sources. By default, access to data sources is None. Typically, access to data sources might be needed in data mining scenarios in which you might query the relational source data for prediction. Enabling the Read Definition option allows the users to retrieve information such as database name, tables, views, and so on of the data source.

   
   Figure 9-77

5. The Cubes tab is shown in Figure 9-78. Here you have the option to grant access to specific cubes. By default, users have no access to any cube in a database to ensure that developers or administrators do not accidentally provide cube access to users who are not supposed to have access. You have the option of providing read access only or read/write access to each cube in the database. You can also see there is the option of Local Cube/Drillthrough Access. These options allow you to specifically grant users the ability to create local cubes or drill-through to more detailed data. In addition, you can limit access to Drillthrough only or to Drillthrough and Local Cube. Local cubes are typically created from Excel so that small versions of cubes can be shared with other users. Local cubes (also called offline cubes) are covered in Chapter 15. The Process option allows you to grant users the ability to process selected cubes. Select Read/Write access to the Adventure Works cube, and then click the Dimensions tab.

   
   Figure 9-78

6. In the Dimensions tab, you have the option to control which database dimensions or cube dimensions a user can access if they've already been granted access to the cube. Figure 9-79 shows how you can define security for the database dimensions or the cube dimensions of the Adventure Works cube. If you select the databases dimensions option, you will see all the dimensions listed with three columns in which you specify the type of access, Read Definition access, and Process capabilities. The Process column is used to provide process permission to specific dimensions. By default, Read access is applied to all the dimensions, but you can change the access type to Read/Write for any dimension. If you allow the Read/Write option, the users have the ability to alter the dimension structure or data. The Read Definition access allows users to query for certain properties of the dimension such as count of hierarchies, members, and levels in the dimension.

Figure 9-79

If you select the cube dimensions option, you will see two columns called Inherit and Access for each of the dimensions, as shown in Figure 9-80. By default, the Inherit column is selected, meaning that all the permissions specified for the database dimension are inherited by the cube dimension. You do have the option of overriding the database dimension access permissions. For example, you might provide Read access to all the database dimensions, Inherit for all the cube dimensions, and then Read/Write permissions on a specific dimension so that you allow certain users to writeback data to the dimension or alter dimension structure. In order to override the database dimension permission access, you need to deselect the Inherit option and then select the Read/Write option from the Access column.

Figure 9-80

As you can see, the role of designer lets you easily specify the right access permissions to the dimensions and cubes within an Analysis Services database. If you have provided access only to the Adventure Works cube, but not to the Mined Customers cube, then when you connect to Analysis Services through SQL Server Management Studio as one of the users listed in the role's membership, you will not see the Mined Customers cube. On the other hand, if you assign a user to two roles, with one role granting access to the Adventure Works cube and the other role granting access to the Mined Customers cube, the user will see both cubes because role permissions are additive. In BIDS, you can test the effect of security, including membership in multiple roles, by browsing the cube under a specific role or a user. If you try to browse a cube for which the current user or role has not been granted permissions, you will get an error message that says you do not have access to the cube.

In this section, you have learned how to define access permissions (read or write permissions) for cubes and dimensions. This ensures correct access restriction to a specific cube or a dimension as a whole to certain users querying the database. Some business scenarios call for restricted access to just a part of the dimension or cube. For example, if I am a sales manager in a chain of retail stores, I might only be given access to view sales information specific to my store. Defining the right security for dimension and cell data is best learned through a scenario; expect to find out more about restricting data access to users in Chapter 19. As for mining models, the Mining Structures tab allows you to define security for mining models, which we will not be covering in this book.