Creating Security Roles

Analysis Services security is based on and integrated with the security in Microsoft Windows NT (either Windows NT version 4 or Microsoft Windows 2000). When you define security in Analysis Services, you create roles in the OLAP database. Each role can contain one or more specific user accounts or user groups as defined in the operating system. Once you’ve created a database role, you can associate that role with cubes or virtual cubes in the OLAP database. In addition, you can fine-tune security within a cube by restricting access to metadata (the members on dimensions) as well as access to data (the values stored in the cubes).

Create Sample Users and Groups

Security in Analysis Services depends on operating system User and Group accounts. When a new person is granted the right to use a Windows 2000 or Windows NT server, a server administrator must first create a user account for that person. That user account should be assigned to one or more appropriate user groups. In this chapter, you’ll create security by using Windows users and groups. If you have only default users and groups, you should create some sample ones to use as a test. If you already have your own users and groups available in your Windows server, you can use them.

Although the general principles don’t change, the detailed steps for adding a new user or group vary depending on whether you’re using Windows NT, Windows 2000 with local users and groups, or Windows 2000 with Active Directory Users and Computers. The following instructions are suitable for creating local users and groups in Windows 2000 Server without Active Directory Services.

1.	In Windows 2000, click the Start menu, point to Programs, point to Administrative Tools, and click Computer Management. Under the System Tools folder, expand the Local Users And Groups folder. [View full size image]
2.	Right-click the Users folder, and click New User. Type _jsmith in the User Name box. (The underscore at the beginning of the name will make it easy to find and remove the sample users and groups.) Type Joe Smith in the Full Name box. Leave the Password box empty, and leave the User Must Change Password At Next Logon check box selected. Then click Create. The boxes in the New User dialog box clear when the user has been created.
3.	As a second sample user, type _mjturner in the User Name box. Type Mary Jane Turner in the Full Name box. Leave the Password box empty and the User Must Change Password At Next Logon check box selected. Click Create, and then click Close.
4.	Right-click the Groups folder, and click New Group. Type _Budget Analysts in the Group Name box.
5.	Click the Add button under the Members box. Select _jsmith, click Add, and then click OK. [View full size image]
6.	Click Create to create the group. Then, to create a second group, type _Budget Reviewers in the Group Name box.
7.	Click the Add button under the Members box. Click _jsmith, hold down the Ctrl key as you click _mjturner, click Add, and then click OK. Click Create to create the group and Close to close the New Group dialog box.
8.	Close the Computer Management window.

Creating a new user or a new group has no effect on Analysis Services until you explicitly add the user or group to a specific database. Of course, adding a user account to the existing OLAP Administrators group would change who could run the Analysis Manager application and browse cubes with no security applied.

Create a Role for all Users

When you create a new database or cube, it automatically gets a hidden, internal role that gives all power to members of the OLAP Administrators group. This hidden role allows administrators to use Analysis Manager. A client cube browser application can browse the cube provided that the user belongs to the OLAP Administrators group. Any user who does not belong to the OLAP Administrators group has no permission at all—either to administer or to browse the cubes. If you create a cube and make it available to other users in your organization, they will not be able to see anything in the cube.

In many situations, you want to allow all users to browse a cube but not to use Analysis Manager. To do that, you must create a new role.

1.	In the Analysis Manager console tree, expand the Chapter 11 OLAP database and the Cubes folder, right-click the Sales cube, and click Manage Roles. [View full size image] The Cube Role Manager dialog box appears. This dialog box shows all the roles that currently exist in the database. It also allows you to create a new role.
2.	Click the New button at the bottom of the dialog box. In the Create A Cube Role dialog box that appears, type All Users in the Role Name box. [View full size image] A role comprises one or more users or groups from the operating system. The users and groups must already exist before you can add them to a role.
3.	Click the Add button The Add Users And Groups dialog box show all the groups available on the current server. You can use the List Names From drop-down list to select a different server, if necessary. The Names list defaults to show only groups. If you want to show users as well as groups, click the Show Users button. In general, security is easier to manage if you use groups so that you don’t need to modify roles each time individual users come or go. For the new All Users role, you want to apply the role to all users. The standard Everyone group refers to any user connected to the server.
4.	Select Everyone, and click Add to add the group name to the Add Names list. Click OK. Note It’s not enough to select the group; you must click Add to move the group name into the Add Names list. After adding the group, the Membership tab of the Create A Group Role dialog box shows the users and groups that now compose the role. You can use the Remove button to remove individual users or groups; the Add button always displays the Add Users And Groups dialog box. [View full size image]
5.	Click OK to complete creating the role. The new role appears in the Cube Role Manager dialog box. [View full size image]
6.	Click Close to close the dialog box.

The Sales cube is now available for browsing by any user who can connect to the server. If you intend to make all your OLAP cubes freely available in the organization, this completes all that you need to know about security in Analysis Services.

Create Roles at the Database Level

Some cubes contain information that should not be made available to all users. To restrict access to different users, you must create roles and put specific users and groups into the roles. Roles always exist at the database level. Even when you create a new cube role, you really create a new database role. The role contains the same users and groups (that is, has the same membership) regardless of which cube in the database uses it. You can create roles directly at the database level and then apply those roles to cubes as needed. In this section, you’ll create roles for budget analysts and budget reviewers.

1.	In the console tree, right-click the Database Roles object under the Chapter 11 OLAP database. Then click Manage Roles. [View full size image] Database Role Manager appears quite similar to Cube Role Manager. They both show all the currently existing roles in the database. As you can see, the All Users role that you created in the Sales cube also appears in the list of database roles. You can use Cube Role Manager to create a new database role, but you must use Database Role Manager to delete one. As you work through this chapter, you’ll see additional similarities and differences between the two role managers.
2.	Click the New button to begin creating a new database role. Type Budget Analysts in the Role Name box, and click Add to add a new group to the role.
3.	Select the _Budget Analysts group or any group of your own choosing, and click Add.
4.	Click OK to add the group to the role membership. Click OK to create the role. [View full size image] The bottom of the dialog box displays a notice communicating that changes to the role membership will propagate to cubes that use this role. A role is global to a database but can be used by one or more cubes within the database. The membership is intrinsic to the role and will be the same for all uses of the role. Note The Create A Database Role dialog box contains a drop-down list labeled Enforce On, with two possible values: Server and Client. As explained in “Analysis Services User Tools” in Chapter 1, “A Data Analysis Foundation,” the PivotTable Service component maintains a cache of values on the client computer. This cache makes retrievals fast and minimizes network traffic. When you enable security, some of the values stored in the client cache might be restricted. If you choose to enforce security on the client, the PivotTable Service will determine whether to make values from the cache available to the client. Potentially restricted values will, however, be passing over the network connection, which opens the possibility that some nefarious soul could intercept them. If you choose to enforce security on the server, you effectively disable the client-side cache. Enforcing security on the client is more efficient but slightly less secure than enforcing security on the server.
5.	Click New, type Budget Reviewers, and click the Add button. Select the _Budget Reviewers operating system group (or another group of your choice). Click Add, and then click OK. [View full size image] When you create a new database role, the dialog box displays the Membership tab. Membership is the defining quality of a role. However, the dialog box also has other tabs. For example, you can assign a role to cubes as you create it.
6.	Click the Cubes tab to show the list of cubes in the database. Click the check boxes next to Finance and Sales. [View full size image]
7.	Click OK to create the role.

All three roles now appear in the list. As you can see in the Cubes & Mining Models column, the All Users role is currently assigned to the Sales cube, the Budget Reviewers role is assigned to the Sales and Finance cubes, and the Budget Analysts role is not assigned to any cubes at all.

Manage Database Roles

You can also use Database Role Manager to make other changes to existing roles. For example, if you need a new role that’s only slightly different from an existing role, you can make a duplicate copy of the existing role and then modify it.

1.	With the Budget Reviewers role selected, click the Duplicate button. The only thing different between the old role and the new role will be the name, so you’ll be prompted for a new name.
2.	When prompted, type Detailed Reviewers and click OK. [View full size image] The new role is an exact copy of the previous one. You can edit the role to make changes.
3.	Select the Detailed Reviewers role, and click the ellipsis (...) button in the Cubes & Mining Models column. The dialog that appears is identical to the Create A Database Role one, except the caption is different and you can’t change the name of the role.
4.	Clear the Sales cube check box, and select the Sales Forecast check box. [View full size image]
5.	Click OK to accept the changes to the role. You can also change the membership of an existing role.
6.	With the Detailed Reviewers role selected, click the Edit button. [View full size image] This displays the same Edit A Database Role dialog box as you saw earlier. There’s only one dialog box for defining a database role. You can simply get to it from any of several different directions: by creating a new role, by selecting a role and clicking the edit button, or by selecting a role and clicking the ellipsis button in one of the columns. Using the ellipsis button from a column simply preactivates the appropriate tab of the dialog box.
7.	Click Add, double-click _Budget Analysts (or a group of your choice), and click OK. Then click OK to accept the revised definition of the role. From within Database Role Manager, you can also delete a role.
8.	With the Detailed Reviewers role selected, click Delete. Click Yes when asked to confirm. Click Close to close the Database Role Manager window.

All databases have an internal role for the OLAP Administrators group, and that role is automatically applied with all power to all cubes in the database. Unless you explicitly create additional roles and assign them to cubes, only members of the OLAP Administrators group can use the cubes. When you use Cube Role Manager to create a new role, the role is created at the database level and automatically applied to the current cube. When you use Database Role Manager to create a new role, you must explicitly add that role to a cube. A default role allows unrestricted access to all dimension information, unrestricted access to read any cell value, and no permission to write back changes to the cube.