Project Botticelli

Cube Security Purchase this course

17 July 2015 · 2 comments · 1121 views

SSAS Cube Design Series

Chris explains how to create dynamic cube security

In this 30-minute video, part of our Cube Design series, Chris Webb explains the SQL Server Analysis Services security. SSAS security is role-based, and all authentication is performed via Windows authentication—it is not possible to create usernames and passwords in SSAS. Users can be members of multiple roles, and in that case a user will have the accumulated permissions from all of the roles they are members of.

There are two types of SSAS security: administrative security and data security. Administrative security can be set at the server level and the database level, and it controls the ability to do things like: create or delete objects, process, and script to XMLA.

Data security controls what data users can see when they browse the cube. Data security can be set at three levels: at the cube level, at the dimension hierarchy level, and at the level of the individual cell. At the cube level, users can either query a cube or not. Dimension security, the most commonly-used type of security, controls which members on a dimension hierarchy a user can see. It is set up on the Dimension Data tab in the Role Editor in Visual Studio. Once you have done that, you can test the role in the Cube Browser tab by pressing the Change User button, or by using the EffectiveUserName or Roles connection string properties. Cell security controls whether users can see the values in individual cells—however, it is rarely used because it is hard to set up and can have a big impact on query performance. In some cases it may be possible to replace cell security with dimension security by remodelling your cube.

Dynamic security is the ability of a single role to return different permissions for different users. This is possible by using an MDX expression within the role that uses either the Username or Customdata functions.

Make sure to watch the remaining modules in this course to learn how to build cubes using SSAS.

Log in or purchase access below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Comments

goranbanjac · 9 November 2015

Hi Chris,

Another great course.
Do you have available example of security design using bridge dimension and “username” function (MDX script)?
I have many organizations that share same cube but shouldn’t see each other’s data.

Thank you!

Chris Webb · 9 November 2015

There's a full example in the book I co-wrote a few years ago, "Expert Cube Development". The first and the second editions have the same content.

Online Courses