Column-Level Security - 2 Methods for Restricting Column-Level Information

[ChrisMBarber]

Within a corporate hierarchy, it is generally reasonable to expect reports containing key information for yourself and anyone who reports to you. Information often found within this category includes job title, grade, salary, and bonus information. Using Row Level Security (RLS) we can achieve this in Power BI with relative simplicity (see my earlier blog https://community.powerbi.com/t5/Community-Blog/Dynamic-Row-Level-Security-Rls-and-Company-Hierarchi... ).

However, other columns of information – such as national insurance numbers & home addresses – should be restricted so either:

1. The column of data is completely hidden from individuals in the hierarchy except for Human Resources (HR)
2. The column of data is available, but you can only see your own information (i.e., you can see your own home address, but not that of your employees with HR having full access to this information)

Because there is no column-level security in Power BI, we need a method in order to achieve this by utilizing the RLS. I outline 2 possible methods below continuing from the example in the original blog:

Method 1: Creating two different tables

One method is to create two different tables for employees and then apply the RLS security differently to these 2 tables:

1. Employees – Applies RLS so that you can only see key information for yourself and anyone who reports to you (i.e., Salary)
2. Employees (Restricted) - Applies RLS so that either you can only see your own information or no information at all, but gives full access for this information to HR (i.e., national insurance number)

As there is RLS on both these tables, we need to make sure that security on the less stringent “Employees” table propagates to the more stringent security on the “Employees (Restricted)” table, but not vice-versa. That way when we apply the more stringent security to the “Employees (Restricted)” table it does not filter the “Employees” table stopping managers from seeing themselves and their employees. This is achieved by creating:

1. An index on these 2 tables
2. A “one-to-many” single filter directional relationship from the “Employees” to “Employees (restricted)”

For the below hierarchy, there are 2 PBIX files attached showing a worked example:

• Salaries and Commission – Personal information restricted to own information only
• Salaries and Commission – Personal information fully restricted

The security can be set so that when logged in as Daren Bussard, this user either does not see any personal information or they only see their own information. Examples of both are shown here:

1. No personal information

Daren can see his own salary and that of his reporting line, but no personal information:

1. Personal Information for user logged in only

Daren can see his own salary and that of his reporting line & only his own personal information:

Logging in as different members of HR (Helen Firth or Paul Taylor in our example) shows that they have access to the restricted employee information:

Paul Taylor

Can see the personal information for his own details and that of all the Sales Representatives

Helen Firth (HR Manager)

Can see the personal information for everyone

Method 2: Duplicating the row

Instead of creating two tables, another option is to duplicate the rows with one row containing the restricted data & the other not containing the restricted data. For instance, there are two rows shown below for Almeda Lanman:

The first of these rows does not contain the restricted data (address, national insurance number & telephone) whilst the second one does. The RLS can then be used to filter the table to either the row that contains the restricted data or the row that does not contain the restricted data depending on who logs in.

You can see this approach in the Salaries and Commission – Duplicating rows approach.

Conclusion

If you are unsure on which method, I would advise to use the first method as default. A separate table makes it clear as to which measures should be restricted and which should not. However, there are instances in which the duplicate rows may be more acceptable to the end user. For instance, if end users connect to your model via excel then they can see the “Employees” and “Employees (restricted)” table. This extra table adds complexity for this end user reducing usability, whereas in method 2 there is only one table. The main issue with the duplicating rows approach is you must be more careful with your DAX and make sure that users do not end up duplicating end results.

The final consideration is you could use measures to restrict the results, leaving all data in the columns but hiding them in the model. For instance, your measure could state that if a certain user logs in the measure behaves differently to if another user logs in thus restricting and unrestricting the data. Through this method most users will not be able to see the restricted data when they connect via excel or use your Power BI. However, the row containing the restricted data is not restricted & more sophisticated users can circumnavigate the security by live connecting to the model then using “view hidden” to show the hidden column or create their own measure.

Salaries and Commission - Duplicating Rows Approach.pbix

Salaries and Commission – Personal information fully restricted.pbix

Salaries and Commission - Personal information restricted to own information only.pbix