Dynamic row-level security implementation (part 1)

Data Power BI

Row-level security provides a facility to restrict or filter users’ access to data at the data row level. Applied within Power BI or a SSAS tabular data model, it essentially enables the ability to restrict data access via pre-defined roles which are applied to report user members.

As an example, a role can be set up to filter product data to only show one or more specific products . When this role is then assigned to a user, that user can only view the corresponding subset of products.

Image of employee, manager, department sales table data containing a manager and an employee’s access to specific sales records.

The above approach, however, can become a maintenance challenge, as new roles are created or the membership of users within a role changes. A more flexible approach is to implement dynamic row-level security whereby the filtering of the data is not determined by a role statically defined to an attribute.

Instead the filtering is determined by a combination of the user accessing the report and the data related to that user. In this way, users are effectively maintained in a control table, which removes the maintenance challenge associated with creating new roles or changing role membership.

Furthermore, it provides a centralised and scalable row security solution. In this approach, the restriction of user access to a subset of rows is achieved through the table relationships in the data model.

The example below illustrates the concept using a fictitious sales company. In this scenario visibility of employee sales data needs to be restricted to the department sales manager – a department sales manager should only be able to view sales data for their own staff (staff directly related to the manager), for example, a Bike Sales Manager is only allowed to see sales for employees within the Bikes department.

How do we configure this in Power BI?

Firstly, create the tables for Power BI importing data sources via Power Query, transforming data where appropriate and model table relationships into a star schema as follows:

Employee table: Contains all employees with assigned department and login email referenced as username.

Image of employee table data containing employees assigned to sales departments.

  • Sales table: Contains sales revenue by date associated to an employee.

Image of employee sales table data containing employees’ sales achieved by sale date.

In addition, we require the following tables created to implement dynamic row level security:

  • Manager Table: Contains all Manager logins that can access the report assigned a unique User Id.

Image of manager table data containing all managers login accounts.

  • Manager To Employee mapping table: This table maps the Manager who is accessing the report to the related employees a manager is authorised to view.

Image of table showing manager and employee relationship data.

The Power BI data model is illustrated below. We use the process of filtering via table relationships i.e. a given user (Manager) filters the manager/employee mapping table (ManagerToEmployee), which then filters the Employee table, which then filters the Sales table.

Image of data model illustrating table relationships between managers, employees, and sales data.

Note in this data model that we have a relationship between ManagerToEmployee and Employee tables which is applied in both directions. If we do not set the “Apply security filter in both directions” option in the relationship settings then the filtering on Employee will not be applied and the manager accessing the report will see all employee sales from all departments.

Image of Power BI data model relationship cardinality and cross filter properties for the employee table and manager / employee bridge table.

In Part 2 we will set up a role in Power BI to apply dynamic filtering to a report.