Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Object Security in Analysis Services Tabular 2017


By:   |   Read Comments   |   Related Tips: > Analysis Services Security

FREE Webcast > 5 Easy SQL Server Query Performance Boosters


Problem

I have built a model using Analysis Services Tabular 2017. I would like to hide tables or columns for certain users so they arenít able to use them in reports or query them. It should look like they donít even exist at all. Is this possible?

Solution

There are different methods to restrict access to data for users in Analysis Services Tabular 2017:

  • Role-based security - Based on filters defined in a role, you can filter out data which you donít want the user to see. However, users are still able to view all of the metadata in the model.
  • Dynamic row-level security - Using a bridge table and role-based security, you can dynamically filter out rows depending on the user querying the model. However, all of the metadata is still visible to the user, just like in the previous method. If you want to learn more, this method is described in the tip Bi-Directional Cross-Filtering in Analysis Services Tabular 2016 for Dynamic Row Level Security - Part 2.
  • Object-level security - This feature is introduced in Analysis Services (SSAS) Tabular 2017. It allows you to secure objects such as tables and columns and it is the focus of this tip.

Thereís another construct in SSAS which allows you to hide objects from users: perspectives. We will investigate why this isnít an ideal solution for security.

In this tip, weíll use a Tabular model built open the WideWorldImporters data warehouse sample database. You can find more information on how to install the sample database in the tip Install SQL Server 2016 Sample Database: Wide World Importers Data Warehouse and you can download the Tabular model here.

Perspectives and Security: Not a Good Match

Letís take a look at perspectives first. Suppose we want to hide the Customer table and the Total Excluding Tax column from the Order table. We can achieve this by creating a perspective. Go to Model in the menu, go to Perspectives and select Create and Manage.

Create perspective

Click on the New Perspective button and give the perspective a name.

created new perspective called test

Now we need to choose which items to include in this perspective, which is everything except the Customer table and the Total Excluding Tax column:

choose to include objects for perspectives

Click on OK to finish. You can immediately test this perspective by going to Model > Perspectives > Select and then choosing the desired perspective.

test perspective in Visual Studio

Every object which wasnít selected is now hidden in the model designer. You can for example verify that there is no Customer table present:

no customer table

You can also easily check the behavior of a perspective in Excel or any other client tool. When connecting to the Tabular instance, youíll need to choose whether you want to connect to the model itself or to a perspective:

connecting to a perspective in Excel

In the Pivot Table field list, we can check the objects are still hidden:

excel pivot field list

But why arenít perspectives suited for security purposes? If we canít see the objects, we canít use them, right? Since perspectives arenít tied to a security role, everyone can connect to a perspective. Itís as easy as connecting to the model itself to circumvent the hidden objects of the perspective. When connecting to the model, role-based security kicks in where you donít have the option to hide objects.

Furthermore, everyone with a little bit of knowledge of MDX or DAX can query all of the objects of a perspective, even the hidden ones. In the following example we are querying the Customer table even though it is hidden:

querying customer table through Test perspective

It is clear perspectives are only a usability feature: their ideal use case is to present simplified versions to end users. This makes browsing large Tabular models easier and less complex. They are however not a security feature, as demonstrated.

Object-Level Security

In contrast with perspectives, object-level security will actually secure the items: you cannot access them through field lists or through queries. Object-level security is introduced as a new feature in Analysis Services 2017 and thus requires at least the 1400 compatibility level. Letís test it out. In the Model menu, go to Roles.

model > roles

In the editor, specify a name and assign read permissions to a new role.

role manager

Unlike perspectives, this time you need to only select the objects you want to exclude. We can test the role using Management Studio. When you browse the model, you can specify a specific role you want to impersonate. You can also use this to impersonate other users to test security. You can find the security context menu by clicking on the people icon in the top left corner.

change security context

When we are browsing the model with the ObjectTest role, we can see the Customer table isnít visible and neither is the Total Excluding Tax column.

testing security

When switching to query mode, we can also verify that this time querying doesnít work either.

error while querying

There are a couple of restrictions when working with object-level security:

  • For the moment itís not possible to secure measures directly. However, you can implicitly secure a measure by referencing a secured column. Measures belonging to a secured table are hidden as well.
  • You cannot secure a table if it will break a relationship. Relationships can reference a secured column as long as the columns table is not secured.
  • Row-level security and object-level security cannot be combined through different roles. This could lead to undesired access to secured data.

Conclusion

In this tip we have shown two methods for hiding objects in Analysis Services Tabular. Perspectives are easy to use and can hide every object of a model. However, they are merely a usability tool and certainly not a security feature. The new feature object-level security Ė which is introduced in Analysis Services Tabular 2017 Ė gets the job done: you can properly secure tables and columns. A secured object is hidden from users.

Next Steps


Last Update:



next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Send me SQL tips:

    



Learn more about SQL Server tools