Last week I was asked to tackle a requirement by a customer adopting Analysis Services to enable data exploration and ad hoc analysis by their users. One of their requirements was to secure columns based on a grant related to a cost center. For example, a grant has several attributes, with some attributes being considered “sensitive” and other attributes considered “non-sensitive”. Non-sensitive grant attributes would accessible to all users while a subset of the attributes in the grant table considered “sensitive” would be accessible to users related to the corresponding cost center. The challenge here is that while Analysis Services supports column level security, dynamic column level security is not supported. So my colleague and friend, the great Steve Pontello, and I put our heads together to address the requirement.
I must admit the title of this blog post is a bit misleading as the solution isn’t really column level security, but rather a clever use of row level security to simply display or not display the values of sensitive fields. Also, its important to point out that this solution does not actually secure the column, but instead shows a value of “Not available” if the user does not have access to the value. For this customer, this was an acceptable solution for their requirement.
Interested in taking the Power BI certification exam 70-778? Review my Power BI certification exam study guide here.
To demonstrate the solution, I’ve created a data model based on the AdventureWorksDW2017 database, which you can download from here if you want to play along. Also, download the SQL script I created here to create the database objects I used in this example. While I’ve created the sample model in Power BI, you could just as easily use an Analysis Services tabular model in the same way. And finally, the Power BI workbook that I’m walking through in this blog post is available to download here.
For this solution and in this example, we’re breaking out the DimCustomer table into two objects: DimCustomer and DimCustomer_secured. The DimCustomer table contains all the “non-sensitive” columns and the DimCustomer_secured table contains the columns that are considered “sensitive”. This will allow us to control access to both the non-sensitive columns and the sensitive columns independently.
To control access to rows in the DimCustomer_secured table, we use a bridge table between DimCustomer_unsecured and DimCustomer_secured table. This bridge table is called vDimCustomer_secured_bridge and is actually a view in the database engine.
For each customer records in DimCustomer, two records would be in the bridge table: one record to correctly map a customer to the customer record in the secured table a second record to map a customer record to the “Unavailable” record (CustomerKey value of –2) in the DimCustomer_secured table. In this example, I’m using a small set of records in the bridge table for illustration purposes. The RLSKey value is an identity column on the database table so that we can efficiently match a user with permissions on a customer record.
Above you can see that in cases where a user has access to a row in the DimCustomer_unsecured table but should not see values for a row in the DimCustomer_secured table, the bridge table maps the Customer record to a row with a –2 CustomerKey that displays a value of “Not available” for the sensitive columns.
The third piece here is an additional table (actually a view in the database), called vDimCustomer_User, that contains the user security information. The table vDimCustomer_User and controls which UserID’s have access to which records in the vDimCustomer_secured_bridge table. You can see the mapping below.
Its also important to point out that the Apply security filter in both directions box is checked on the relationship between vDimCustomer_secured_bridge and DimCustomer_secured, as seen below.
Below you can see an example of how user1 does not have access to the row in the DimCustomer_secured table which contains the sensitive information. Notice the value for the CustomerKey_secured column for RLSKey 1 is –2. And then notice that user2 does have access to the coorspedonding record in DimCustomer_secured and therefor gets to see the sensitive information.
Now we can apply role based security based on the user principal name in Power BI or Analysis Services. So when user1 logs in to view the report, user1 will only see records for customers they have permissions on and only information on the sensitive columns he/she should see, as seen below. For example, take a look at CustomerKey 11017. User1 has access to the customer record but does not get to see the sensitive information in the EmailAddress, LastName, and BirthDate columns.
On the other hand, user2 also has access to CustomerKey 11017 but does have permissions to see the sensitive data in the EmailAddress, LastName, and BirthDate columns.
Resources
Download the sample Power BI file here.
Download the SQL script to create the supporting database objects.
Download the AdventureWorksDW2017 database backup file.
If you found this useful, please share with your friends and colleagues. I’d also like to give a big shoutout to Steve for his help with addressing the customer’s requirements. Please leave your comments and thoughts in the comments section below. Take care!