MSAS - Introducing Analysis Services Security

Security of data is of paramount importance to most industries and organizations. All organizations want to restrict access to data hierarchically or define role based access. Analysis Services provides for this need.

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:

1. Understanding Administrator Security
2. Securing User Authentication
3. Understanding Database Roles
4. Implementing Dimension Security
5. 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.