Securing your tabular model

  • Article

Now that RC0 has arrived I can finally post about security for tabular models. I have put off writing about security because I think the RC0 documentation on this subject is really quite good so I don’t have too much to add. Also, we had a minor feature add for RC0 that is very exciting. You can now create and delete roles in SSMS. Also, you can edit the row filters for a role in SSMS. The row filter editing functionality in SSMS is rather rudimentary (no autocomplete or semantic error validation), so I don’t recommend SSMS as your primary row filter editor, but for making small fix ups it works just fine.

Now on to the summary of the security model for tabular models for SQL Server 2012. There are two types of permissions for tabular models: database permissions and allowed row sets. A database permission is simple – a user can administer, read, or process a database. Granting a user read access to the database allows the user to see all data (but not metadata such as the DAX calculations included in the model). If you would like to restrict the amount of data the user in the read role can see, you can change the number of rows returned for a particular table by applying a row filter. Row filters affect related tables in the one to many direction, so applying a filter to a lookup table always affects the related fact tables. Aggregates in the table always aggregate the filtered row set. If multiple row filters are applied to a table, the user sees the intersection of the rows returned by each individual filter. If a user is a member of multiple roles, the user sees the union of all results returned for a particular role.

For more basic overview information, see the MSDN documentation on roles and Paul te Braak’s security overview post.  The Create Roles tutorial shows you how to create basic roles and set row filters. There is an awesome new supplemental lesson for RC0, called Implement Dynamic Security Using Row Filters, that shows how to do dynamic security. This is exactly the demo I gave for SQL PASS on dynamic security, except on AdventureWorks. The lesson shows how to add a table with a list of securable objects, “secure” the table, and then modify the allowed rowset for users using the USERNAME() function. We do not have a meaningful example that shows how to use CUSTOMDATA() at this time.

Notes and tips:

  • Perspectives, tables, and columns are not securable objects. Only databases and rows are securable objects.
  • There is no way to allow a user to view the aggregate data for all rows in the table while restricting permission to view individual rows in the table. Visual totals always apply.
  • As Paul te Braak notes in his blog, there is no concept of a strong deny. By setting security permissions, you are defining what the user in a particular role is allowed to see.
  • A user with process permission cannot process the database in SSMS. This is because the user is not allowed to see the metadata for the database. The user with process permission can only process via automation. See my management post for details on automation options.
  • To “secure” a table for a user, type =FALSE() as the DAX filter and then hide the table from view in the client tools. Note that hiding the table hides the table for all users in all rows. If you want the table itself to be visible in the field list so other users in other roles can manipulate the table, the user with the =FALSE() restriction will still be able to see the table metadata in the field list.
  • Only Windows authenticated users can be added to roles.
  • Security works best when you are using domain users, and not individual users on a local machine. Analysis Services just works best in a domain environment, and you may get strange results when trying to test out security in Excel or in the “cube browser” when working with users on a local machine.