cancel
Showing results for 
Search instead for 
Did you mean: 

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

FarhanAhmed_1-1603796172230.png

 

Current RLS Table

 

User

DeptID

abcd@company.com

A

abcd@company.com

B

xyz@company.com

C

xyz@company.com

D

hhh@company.com

C

 

FarhanAhmed_2-1603796172232.png

 

Data Preparation

For this, you need to

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

User

AdminRole

abcd@company.com

0

xyz@company.com

0

hhh@company.com

1

  • 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

FarhanAhmed_3-1603796172235.png

 

 

Testing

Testing as a Normal User

FarhanAhmed_4-1603796172236.png

 

Testing as Admin User

FarhanAhmed_5-1603796172238.png

 

 

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