Row Level Security with Admin Control Table

Scenario

You have implemented Row Level Security to restrict users to view data accordingly but you want to create a dynamic Admin role that can be assigned to any user to view all data and at the same time, you do not want to assign Admin permission to the user.

Here is the current model

Current RLS Table

Data Preparation

For this, you need to

• Create a table that will have information about users who is Admin or Not.

• Now you need to disconnect your current RLS Table with the Model and create a new table which will contains all department for Admin role user and keep other users same

UserTable =
UNION(
    SUMMARIZE(RLS,RLS[User],RLS[DeptID]),  CROSSJOIN(SUMMARIZE(FILTER(User,User[AdminRole]=1),User[User]),SUMMARIZE(Department,Department[DeptID])))

Hide both Admin table & RLS table from the model and use this “UserTable” as RLS table to restrict users to view appropriate data

Testing

Testing as a Normal User

Testing as Admin User

You see that you can now control your users to make them admin thru the control table rather than assigning Admin permissions in the workspace or at any level.

Hopefully, this will help developers to have more control in assigning admin permission to users on data.

Regards

Farhan