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.
Click on the New Perspective button and give the perspective a name.
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:
Click on OK to finish. You can immediately test this perspective by going to Model > Perspectives > Select and then choosing the desired perspective.
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:
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:
In the Pivot Table field list, we can check the objects are still hidden:
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:
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.
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.
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.
When switching to query mode, we can also verify that this time querying doesn’t work either.
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
- If you want to try it out yourself, you can download the Tabular model here. The model uses a view to get rid of the geography data type in the City table, as described in the tip Using Calculated Tables in Analysis Services Tabular 2016 for Aggregate Tables - Part 3.
- If you want to learn more about this security feature, you can find useful information in the official documentation.
- You can find more Analysis Services tips in this overview.
