The number of Administrators and users having access to Analysis services data can be restricted by setting the role definitions in Analysis manager. End users who have access to data through client applications can be restricted in various ways. Security levels can be set for various objects within the database such as cubes, dimensions and cells.
The Microsoft Windows NT or Windows 2000 Administrator role is used to control Analysis services Administrator security by creating a group named OLAP administrators. End user security is controlled using the authentication during the process of connecting to the Analysis server. Database, cube and mining model roles are defined in the Analysis manager.
The roles assigned to the various entities, determine the level of access available to them. Roles are assigned to the database first and thereafter assigned to the cubes and other objects that users access in the database. Some changes can be made to the roles at cube level, but these changes do not impact the roles assigned at database level. This feature enables the assignment of different role definitions for different cubes.
Analysis services support a windows integrated security system. In this lesson we will be studying the type of security provided by Analysis services:
- Understanding Administrator Security
- Securing User Authentication
- Understanding Database Roles
- Implementing Dimension Security
- Managing Cube roles.
Understanding Administrator Security
The Administrator in Analysis services is the person who performs administrative functions. The Administrator can define user roles, set database and cube level security roles and also maintain the various components of the database. Administrator roles are granted by membership in the OLAP Administrators group defined in Windows NT or Windows 2000. This group is created on the installation of Analysis Services and the logged on user is added to the group by default. Members of this group can access the Analysis server through Analysis Manager and perform administrative functions. He can perform programmatic functions with Decision Support Objects(DSO). The user manager window in Windows NT 4.0 or Computer Management Window in Windows 2000 can be used to manage OLAP Administrators group. Administrator security does not have multiple levels.
Normally the Administrator will have full access and all read write permissions on objects in the database. He can login to the server from client applications with all his permissions intact.
The Administrators access rights to cubes are listed in the Cube owner’s Control list (ACL). He will be able to access the cube from any client terminal provided the cube was created when he had logged into the domain account and not the server’s local account.
In the latter instance he will be denied access to the cube if he logs in from the workstation’s local account and tries to access the cube. Therefore it is important that cubes should be created by Administrators when they are logged into the Domain account. The Administrator should also assign a role to the cube after it is created so that it can be accessed from other computers on role based access.
There are a number of operational considerations while administrating the Analysis server. The Administrator has to set service logon Account permissions for access to data sources. The service for Analysis Services is named MSSQLOLAPService. In Windows NT 4.0 or Windows 2000, Windows integrated security is used and the logon account associated with this service must have permissions to access the data sources. Else the Administrator cannot process the objects maintained in the Analysis Manager. The Logon Account can be maintained by using the Services application in the control panel.
Protection of data is of paramount importance. Administrators who have access to Web browsers, productivity applications and emails should ensure that they refrain from accessing Web pages, productivity applications and email applications that support scripts and macros when logged on as administrators. Only trusted accounts and web pages should be accessed and security should be set to the highest priority. The Windows NT 4.0 or Windows 2000 user accounts can be used to establish special Administrator accounts for managing Analysis services.
The Administrator can control an end user’s access to a cube. He assigns the roles to end users for access to various objects in the database and the database itself. The End user security relies on the definition of user accounts and groups in Microsoft NT 4.0 or Windows 2000. When Analysis Services security roles are defined, a set of users and groups is defined within the Analysis Services. The Auto Synch Period property determines the time that lapses between the definition of the end user’s access rights and the point in time when the definitions become effective.
The value of the Auto Synch Period property controls the frequency of Client server synchronization and defaults at 10,000 milliseconds. It is passed in each connection string to the Analysis services. This default can be overridden by end users and client applications and therefore, can vary from one end user to another. If the Auto Synch Period property is set to Null, synchronization is not constant. It is impacted by end users actions. For instance if changes are made to an end user’s access rights while he is connected and browsing a cube, the changes will not be effective till he disconnects from the cube. He cannot be forcibly disconnected from the cube during a query session after access has been obtained. However if the Auto Synch Period is set to a non-null value, the end user’s access rights are reviewed or synchronized at the specified intervals and any changes made in the interregnum have an immediate impact. For instance if the value is set to a nonzero value and the end users’ access to the cube has been removed, the end user will be immediately disconnected from the cube.
The Administrator performs the following functions with reference to end users.
1. He has to review and revise Windows NT 4.0 or Windows 2000 user accounts and groups in accordance with the various access requirements of the end users.
2. He has to create security roles and assign each role to the cubes or data mining models that the users in the role are permitted to access.
3. Define each role assigned to a cube or mining model. Each role’s definition can vary for each cube or mining model to which it is assigned.
User Accounts and Groups in Microsoft SQL Server 2000 Analysis Services, is created after the user accounts and groups are created in the User Manager in Microsoft Windows NT 4.0 or in Computer management window in Windows 2000. The time required for role maintenance can be reduced if the Administrator has a clear charter or the memberships of groups before the roles are created in the Analysis Services. Where NTLM Security Support providers are used for authentication, all user accounts and groups must be in the same trusted domain if they are to be granted access rights to cubes. User accounts and groups in other domains will not be able to connect to the Analysis server in this case.
The Administrator assigns Database, cube and Mining model roles to end users. A role is defined as a set of user accounts and groups with the same access rights and permissions to Analysis Services data. Roles help the Administrator implement end user security by controlling access to data on the Analysis server. A database role can be assigned to multiple cubes or mining models in the database. In this instance the end users have access to the cubes or mining models contained in the database. The role provides defaults for cube or mining model roles of the same name. Once a database role is granted, the Administrator can specify the type and scope of access to dimension members for cubes. Database roles are defined at database level and are maintained by the Database role manager.
The database role, by default specifies a read only access. It also does not limit the dimension members or cube cells from being visible to end users. Once a user has a database role he can view the entire cube. However, the Administrator can specify a read/write access and limit the dimension members that are visible and updateable in both the database role and the cube role. We will see how this is done a little later in this tutorial. The mining model role confines the user to a read only access.
The Cube role is defined for single cubes. The default role is derived from the database role of the same name, but the Administrator can override these defaults in the cube role. The additional options in cube role settings enable the Administrator define cell security. Cube roles are created at the cube level after the database role is assigned to the cube. These roles are maintained in the Cube role manager. The Administrator can also indicate whether he wants to give the end user a right to drillthrough to the cell’s source data. To use this capability the cube or at least one partition of the cube should be write enabled.
Mining models roles are also defined for single models. If a database role has been assigned and the mining model carries the same name, the user derives a default mining role. This can, however, be overridden by the Administrator. Mining model roles are created when the database role is created and are maintained in the Mining Model Role Manager.
An end user may have multiple roles on an Analysis Server. Within the server the user has combined access to the objects specified in these roles. However, it is possible that there is a conflict of roles. The resolution of such conflicts is done through exceptions. Exceptions are custom rules in dimension security. It must be noted that all combinations of custom rules from multiple roles cannot be resolved.
