BISM – Tabular Security Model

The tabular model uses role based security in the same manner as traditional SSAS. This post looks at the creation of roles and the use of restriction of data through (roles).

In this example, the model is very simple only showing only sales by [ship date] and [Sales Territory] (below). We create a model and restrict what data a user can see through security roles.

Roles are administered via the Role Manager (accessed via Model > Roles… or the roles icon () in the menu bar) and apply to the entire model.

The role must have a name and permission before any restriction can be applied.

Permissions include (None, Read, Read and Refresh, Refresh and Administrator). When the role is initially created, it has ‘None’ permissions and therefore no filter can be applied (since it has no visibility of the data within model). The row filters dialog (below) is greyed (disallowing entry) as is the case with the Refresh permission (again no data is available to the role) and the Administrator Role (since security is not applicable). The only permissions that can restrict data views are the Read and ‘Read and Refresh’ roles.

When the role permits restriction, a DAX predicate can be applied to the table that the filter should be applied to. The predicate (in this example) takes the form of column (boolean operator) value (see below). For example, visibility of the united states is [Country]=”United States” whereas permission to everything other than the united states would be [Country]<>”United States”.

As is the case with SSAS, members are added through the members tab and allow ad groups and users (inc security principals).

The Effect of Table.Column Predicate

Role permissions are accumulative to accounts. For example, when an account is a member of the country_usa role (filter: [Country]=”United States”) and country_canada (filter: [Country]=”Canada”), the account will have access to both countries.

However, I have not found the expected inverse to be true. For example, when the account is permitted access to data in one role and denied it in another, they are allowed access to the data.


2 thoughts on “BISM – Tabular Security Model

  1. Pingback: BISM – Tabular Security Gotcha! (Denied Permissions) « Paul te Braak

  2. Pingback: SQL Server 2012 Tabular Data Model | just dave info

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s