Dynamic Row Level Security Based on Contract

[Anonymous]

Hi,

I am trying to set up Dynamic Row Level Security for a  large dashboard where each contract should only see their reporting metrcis.  There are multiple people in a contract so I know it needs to be based on email but everyone should see their own contract only.

I have set up a Users Table, Employee Table and obvioulsly data.

I cannot seem to get the synxtax right so that I could view each dataset based upon the contract selected only.

Can anyone help?  I know I need two tables that I have to use the Userprincipalname function but I am struggling with the rest of this.

Sales- Desired

Name	Email Address	Hire Date	Contract	EmployeeID	UNIQUE CONTRACT AND NAME	Sales
A	A@test.com	13-Jun-18	ABC	1	A-ABC	10
E	E@test.com	29-Jul-19	ABC	5	E-ABC	125
F	F@test.com	16-Dec-19	ABC	6	F-ABC	50
K	K@test.com	23-Oct-19	ABC	11	K-ABC	75

Employee Table

Name	EmailAddress	Hire Date	EmployeeID	Contract	Unique ID
A	A@test.com	14-Sep-98	1	ABC	A-ABC
B	B@test.com	16-Nov-98	2	CBD	B-CBD
C	C@test.com	3-Oct-14	3	DEF	C-DEF
D	D@test.com	27-Nov-17	4	GHI	D-GHI
E	E@test.com	9-Mar-18	5	ABC	E-ABC
F	F@test.com	29-Jun-01	6	ABC	F-ABC

Users Table

Name	Email Address	Hire Date	Contract	EmployeeID	UNIQUE CONTRACT AND NAME
A	A@test.com	13-Jun-18	ABC	1	A-ABC
B	B@test.com	18-Jun-18	CBD	2	B-CBD
C	C@test.com	25-Jan-21	DEF	3	C-DEF
D	D@test.com	25-Oct-17	GHI	4	D-GHI
E	E@test.com	29-Jul-19	ABC	5	E-ABC
F	F@test.com	16-Dec-19	ABC	6	F-ABC

[Anonymous]

Data is super sensitive.

In this case, how you you set up the formula in the manage roles?

[lbendlin]

In the Employees table:

[Email Address]=USERPRINCIPALNAME()

[Anonymous]

Right but I need everyone from the contract to be able to see it with many emails being associated with the same contract. I guess i am missing something.

[lbendlin]

Depending on the relationship between employees and contracts you can directly link the employees table to the contract table, or you use a mapping table that lists all employee-contract combinations.

[lbendlin]

The usual pitfalls with RLS are 

- you have users with workspace roles above viewer => RLS is ineffective

- your data model links are pointing towards the RLS table => ditto.

Other than that USERPRINCIPALNAME works "fine".

Personal note:  Do you really need RLS?  Is your data sensitive? Because if not then all you are achieving is to irritate your users.

[Anonymous]

I don't think I need both tables either but I had seen other examples where that worked.  I can never get the userprincipalname to work.  

Are you really just using one filter on the email? Do you have an example? Thanks!

[lbendlin]

yes, use USERPRINCIPALNAME and apply the RLS on the outermost dimension table of your data model.  

What's the reason for having a user table and an employee table?