Analysis Services Security Overview | Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)

In the summer of 2003, Microsoft took a timeout to review the security vulnerabilities of literally every product it delivers. This is often referred to as Microsoft's Trustworthy Computing initiative, and it resulted in a "secure by design, secure by default, and secure by deployment" approach. Many product configurations are shut off by default as a way of protecting consumers from potential security vulnerabilities that they might not even be aware of. This is certainly true of Analysis Services. Several options that were either not available in Analysis Services 2000 or were inactive by default are implemented in such a way as to make Analysis Services 2005 more secure with the default installation. Examples include disallowing anonymous access and encrypting authentication. You can change most of these options, but any such changes could make your server more vulnerable, so you should closely evaluate them to make sure you understand possible repercussions.

Analysis Services authentication works much the way that it did in previous versions. There are two authentication schemes: integrated security and HTTP (IIS). When you use integrated security, Analysis Services leverages Windows authentication and all the associated robustness. Analysis Services can be configured to allow anonymous access, but, as mentioned previously, that is not the default. It is recommended that you retain the defaults.

You can use HTTP for connecting to Analysis Services over the Internet. Various middletier scenarios can be implemented to provide manageability for scenarios when the users are connecting from outside a trusted domain.

Granular Permissions

Authorization within Analysis Services has changed to be more granular and to more closely resemble what is found in the SQL Server relational engine. Previous versions of Analysis Services provided a single, all-or-nothing administrative role. This meant that anyone who had permissions to perform administrative functions on one database had the same, all-inclusive access to every other database on the server. That user also had access to the server-level attributes. For instance, he or she could modify the memory settings or change logging characteristics.

Like Analysis Services 2000, Analysis Services 2005 has a single, serverwide role that enables its members to perform any activity within Analysis Services. This is similar to the System Administrator role in SQL Server. Any members of the local System Administrators group are automatically added as members of this group. A user in this role does not require any additional permission in order to perform administrative functions or to access any objects in any database on the relative server instance. This is the only means of gaining access to Analysis Services databases, unless explicit database access is granted through database roles.

In Analysis Services 2005, individual database roles more granularly define access within an individual database. Logins can be given access to the Full Control role to be given full access to the database. Much like the database owner role in SQL Server, no further permissions need to be granted to such a login. More granular permissions can be granted by using one of these database roles:

Process database
Read definition
Access a Data Source (Read/Write or None)
Access a Cube (Read, Read/Write, or None)
Access a Cell Data (Read, Read-contingent, Read/Write or None)
Access a Dimension (Read, Read/Write, or None)
Access a Mining Structure (Read or None)
Access a Mining Model (Read, Read/Write, or None)

More defined permissions can be granted to individual objects within a database. If a user is a member of the Analysis Services administrator role or has Full Control access within a database, he or she needs no further permissions to access any objects within the database. All other users require explicit access to objects.

Analysis Services objects can be secured at a number of levels, down to the cell data level. A user can be a member of more than one role and has access to objects based on the union of all access from all roles. For instance, if a user has Read permissions to the HR cube through membership in the Human Resources role and has Read/Write permissions to the HR cube through membership in the hr Admin role, that user has Read/Write permissions to this cube.

Dimension-Level Security

Probably the most integral aspects of end-user security have to do with access to dimensions. In Analysis Services 2000, this access was based on access to dimension members in the dimension hierarchy. In Analysis Services 2005, the concept of hierarchy has been dismantled, and security is granted to attributes because any attribute can participate in a dimension view. MDX is used to define AllowedSet and DeniedSet properties. As the names imply, an AllowedSet property defines a set of members that can be accessed, and a DeniedSet property defines a set of members to which access is denied. An additional property, ApplyDenied, is available on the DeniedSet property, and it defaults to True. The ApplyDenied property specifies whether additional members of the attribute hierarchy are denied based on the members specified in the DeniedSet property. For instance, if a role is denied access to the United States member of the Country attribute, having ApplyDenied set to true would also deny access to all members that were descendents to this member in this attribute hierarchy.

One of the primary issues with Analysis Services 2000 had to do with memory usage when dimension-level security was implemented. Dimension-level security controls the members to which a user has access. So, if a user is a member of the SouthCentral Sales role, he or she may be limited to sales information for the states of Texas, Louisiana, and Oklahoma. In Analysis Services 2000, this resulted in a replica dimension when any user with access to a different combination of dimension access connected to Analysis Services. Dimension-level security uses memory much more conservatively in Analysis Services 2005, so concerns about memory utilization should not be an issue when determining whether to use dimension-level security.

Role-Based Security

Analysis Services provides role-based security. The middle-tier application is responsible for authenticating the user and simply passes the roles to be used when determining authorization to objects in SSAS. The disadvantage to this scenario is that the session has no username, and dynamic security cannot be implemented. Two other connection string properties are EffectiveUser and EffectiveRoles. The EffectiveUser property provides the name of a user to impersonate. Authorization is determined based on the roles of which that user is a member. The EffectiveRoles property can provide a subset of the user's roles that are defined within SSAS.